package repositories

import (
	"context"
	"time"

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

	"lune/talentscale/internal/modules/billing/dto"
)

// DBExecutor abstracts pgxpool.Pool and pgx.Tx for repository methods
type DBExecutor interface {
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
	Exec(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
}

// BillingRepository defines all billing-related database operations
type BillingRepository interface {
	// ─── Packages ────────────────────────────────────────────────────────────
	GetActivePackages(ctx context.Context) ([]dto.PublicPackageResponse, error)
	GetPackageByID(ctx context.Context, id uuid.UUID) (*PackageRow, error)

	// ─── Invoices ────────────────────────────────────────────────────────────
	CreateInvoice(ctx context.Context, tx pgx.Tx, inv *InvoiceRow) error
	CreateInvoiceItem(ctx context.Context, tx pgx.Tx, item *InvoiceItemRow) error
	GetInvoiceByID(ctx context.Context, id uuid.UUID, companyID uuid.UUID) (*InvoiceRow, error)
	GetInvoicesByCompany(ctx context.Context, companyID uuid.UUID, status string, limit, offset int) ([]dto.InvoiceListItem, int64, error)
	UpdateInvoiceStatus(ctx context.Context, tx pgx.Tx, id uuid.UUID, status string) error
	GetInvoiceItemsByInvoiceID(ctx context.Context, invoiceID uuid.UUID) ([]dto.InvoiceItemDTO, error)
	GetNextInvoiceSequence(ctx context.Context, tx pgx.Tx, datePrefix string) (int, error)

	// ─── Payments ────────────────────────────────────────────────────────────
	CreatePaymentTransaction(ctx context.Context, tx pgx.Tx, pt *PaymentTransactionRow) error
	GetPaymentByOrderID(ctx context.Context, orderID string) (*PaymentTransactionRow, error)
	GetPaymentByInvoiceOrderID(ctx context.Context, invoiceID uuid.UUID) (*dto.PaymentDTO, error)
	UpdatePaymentStatus(ctx context.Context, tx pgx.Tx, orderID string, status string, paymentMethod string, rawPayload []byte, paidAt *time.Time) error
	LockPaymentForUpdate(ctx context.Context, tx pgx.Tx, orderID string) (*PaymentTransactionRow, error)

	// ─── Subscriptions ───────────────────────────────────────────────────────
	GetActiveSubscription(ctx context.Context, companyID uuid.UUID) (*SubscriptionRow, error)
	CreateSubscription(ctx context.Context, tx pgx.Tx, sub *SubscriptionRow) error
	ExtendSubscription(ctx context.Context, tx pgx.Tx, id uuid.UUID, newEndDate time.Time) error
	GetSubscriptionWithPackage(ctx context.Context, companyID uuid.UUID) (*dto.ActiveSubscriptionResponse, error)

	// ─── Usage / Quota ───────────────────────────────────────────────────────
	AddQuota(ctx context.Context, tx pgx.Tx, companyID uuid.UUID, quota int) error
	GetRemainingQuota(ctx context.Context, companyID uuid.UUID) (int, error)

	// ─── Webhook Logs ────────────────────────────────────────────────────────
	CreateWebhookLog(ctx context.Context, tx pgx.Tx, source string, eventType string, payload []byte, headers []byte) (int64, error)
	IsWebhookProcessed(ctx context.Context, orderID string) (bool, error)
	MarkWebhookProcessed(ctx context.Context, tx pgx.Tx, id int64) error

	// ─── Email Logs ──────────────────────────────────────────────────────────
	CreateEmailLog(ctx context.Context, log *EmailLogRow) error

	// ─── Company ─────────────────────────────────────────────────────────────
	GetCompanyByID(ctx context.Context, id uuid.UUID) (*CompanyRow, error)
	GetCompanyEmailRecipients(ctx context.Context, companyID uuid.UUID) ([]string, error)

	// ─── Transaction ─────────────────────────────────────────────────────────
	BeginTx(ctx context.Context) (pgx.Tx, error)
	Pool() *pgxpool.Pool
}

// ─── Row Types ───────────────────────────────────────────────────────────────

type PackageRow struct {
	ID             uuid.UUID
	Name           string
	Price          float64
	LimitCandidate int
	PeriodType     string
	CreatedAt      time.Time
	UpdatedAt      time.Time
	DeletedAt      *time.Time
}

type InvoiceRow struct {
	ID             uuid.UUID
	InvoiceNumber  string
	SubscriptionID uuid.UUID
	CompanyID      uuid.UUID
	PackageID      uuid.UUID
	ValidUntil     time.Time
	SubTotal       float64
	Tax            float64
	Total          float64
	Status         string
	CreatedAt      time.Time
	UpdatedAt      time.Time
	DeletedAt      *time.Time
}

type InvoiceItemRow struct {
	ID          uuid.UUID
	InvoiceID   uuid.UUID
	ItemName    string
	Quantity    int
	Price       float64
	Description string
	CreatedAt   time.Time
	UpdatedAt   time.Time
}

type PaymentTransactionRow struct {
	ID            uuid.UUID
	CompanyID     uuid.UUID
	UserID        *int64
	OrderID       string
	ExternalID    *string
	Amount        float64
	Currency      string
	PaymentMethod *string
	Status        string
	PaidAt        *time.Time
	ExpiredAt     *time.Time
	Payload       []byte
	CreatedAt     time.Time
}

type SubscriptionRow struct {
	ID        uuid.UUID
	CompanyID uuid.UUID
	PackageID uuid.UUID
	StartedAt time.Time
	EndAt     time.Time
	Status    string
	CreatedAt time.Time
	UpdatedAt time.Time
}

type EmailLogRow struct {
	ID           uuid.UUID
	Recipient    string
	EmailType    string
	Subject      string
	Status       string
	ErrorMessage *string
	SentAt       *time.Time
	CreatedAt    time.Time
}

type CompanyRow struct {
	ID        uuid.UUID
	Name      string
	Email     string
	PackageID *uuid.UUID
	Status    string
}