package repository

import (
	"context"
	"lune/talentscale/internal/domain"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgxpool"
)

type postgresSubscriptionRepository struct {
	db *pgxpool.Pool
}

func NewPostgresSubscriptionRepository(db *pgxpool.Pool) domain.SubscriptionRepository {
	return &postgresSubscriptionRepository{db: db}
}

func (r *postgresSubscriptionRepository) Create(ctx context.Context, sub *domain.Subscription) error {
	query := `INSERT INTO subscriptions (id, company_id, package_id, start_date, end_date, status, created_at, updated_at)
	          VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`
	_, err := r.db.Exec(ctx, query,
		sub.ID, sub.CompanyID, sub.PackageID,
		sub.StartDate, sub.EndDate, sub.Status,
		sub.CreatedAt, sub.UpdatedAt,
	)
	return err
}
func (r *postgresSubscriptionRepository) GetByID(ctx context.Context, id uuid.UUID) (*domain.Subscription, error) {
	query := `SELECT id, company_id, package_id, start_date, end_date, status, created_at, updated_at
	          FROM subscriptions WHERE id = $1 AND deleted_at IS NULL`
	var sub domain.Subscription
	err := r.db.QueryRow(ctx, query, id).Scan(
		&sub.ID, &sub.CompanyID, &sub.PackageID,
		&sub.StartDate, &sub.EndDate, &sub.Status,
		&sub.CreatedAt, &sub.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &sub, nil
}

func (r *postgresSubscriptionRepository) ListByCompany(ctx context.Context, companyID uuid.UUID) ([]domain.Subscription, error) {
	query := `SELECT id, company_id, package_id, start_date, end_date, status, created_at, updated_at
	          FROM subscriptions WHERE company_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC`
	rows, err := r.db.Query(ctx, query, companyID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var subs []domain.Subscription
	for rows.Next() {
		var sub domain.Subscription
		if err := rows.Scan(
			&sub.ID, &sub.CompanyID, &sub.PackageID,
			&sub.StartDate, &sub.EndDate, &sub.Status,
			&sub.CreatedAt, &sub.UpdatedAt,
		); err != nil {
			return nil, err
		}
		subs = append(subs, sub)
	}
	return subs, nil
}

func (r *postgresSubscriptionRepository) GetActiveByCompanyID(ctx context.Context, companyID uuid.UUID) (*domain.Subscription, error) {
	query := `SELECT id, company_id, package_id, start_date, end_date, status, created_at, updated_at
	          FROM subscriptions
	          WHERE company_id = $1 AND status = 'ACTIVE' AND deleted_at IS NULL
	          ORDER BY created_at DESC LIMIT 1`

	var sub domain.Subscription
	err := r.db.QueryRow(ctx, query, companyID).Scan(
		&sub.ID, &sub.CompanyID, &sub.PackageID,
		&sub.StartDate, &sub.EndDate, &sub.Status,
		&sub.CreatedAt, &sub.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &sub, nil
}

func (r *postgresSubscriptionRepository) UpdateStatus(ctx context.Context, id uuid.UUID, status string) error {
	query := `UPDATE subscriptions SET status = $1, updated_at = $2 WHERE id = $3`
	_, err := r.db.Exec(ctx, query, status, time.Now(), id)
	return err
}

func (r *postgresSubscriptionRepository) Delete(ctx context.Context, id uuid.UUID) error {
	query := `UPDATE subscriptions SET deleted_at = $1 WHERE id = $2`
	_, err := r.db.Exec(ctx, query, time.Now(), id)
	return err
}