package repository

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

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

type postgresInvoiceRepository struct {
	db *pgxpool.Pool
}

func NewPostgresInvoiceRepository(db *pgxpool.Pool) domain.InvoiceRepository {
	return &postgresInvoiceRepository{db: db}
}

func (r *postgresInvoiceRepository) Create(ctx context.Context, invoice *domain.Invoice) error {
	query := `INSERT INTO invoices (id, invoice_number, company_id, status, subtotal_amount, tax_amount, grand_total_amount, created_at, updated_at)
	          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`
	_, err := r.db.Exec(ctx, query, invoice.ID, invoice.InvoiceNumber, invoice.CompanyID, invoice.Status, invoice.SubtotalAmount, invoice.TaxAmount, invoice.GrandTotalAmount, invoice.CreatedAt, invoice.UpdatedAt)
	return err
}

func (r *postgresInvoiceRepository) GetByID(ctx context.Context, id uuid.UUID) (*domain.Invoice, error) {
	query := `SELECT id, invoice_number, company_id, status, subtotal_amount, tax_amount, grand_total_amount, created_at, updated_at
	          FROM invoices WHERE id = $1 AND deleted_at IS NULL`
	var inv domain.Invoice
	err := r.db.QueryRow(ctx, query, id).Scan(&inv.ID, &inv.InvoiceNumber, &inv.CompanyID, &inv.Status, &inv.SubtotalAmount, &inv.TaxAmount, &inv.GrandTotalAmount, &inv.CreatedAt, &inv.UpdatedAt)
	if err != nil {
		return nil, err
	}
	return &inv, nil
}

func (r *postgresInvoiceRepository) ListByCompany(ctx context.Context, companyID uuid.UUID) ([]domain.Invoice, error) {
	query := `SELECT id, invoice_number, company_id, status, subtotal_amount, tax_amount, grand_total_amount, created_at, updated_at
	          FROM invoices 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 invoices []domain.Invoice
	for rows.Next() {
		var inv domain.Invoice
		if err := rows.Scan(&inv.ID, &inv.InvoiceNumber, &inv.CompanyID, &inv.Status, &inv.SubtotalAmount, &inv.TaxAmount, &inv.GrandTotalAmount, &inv.CreatedAt, &inv.UpdatedAt); err != nil {
			return nil, err
		}
		invoices = append(invoices, inv)
	}
	return invoices, nil
}

func (r *postgresInvoiceRepository) UpdateStatus(ctx context.Context, id uuid.UUID, status domain.InvoiceStatus) error {
	query := `UPDATE invoices SET status = $1, updated_at = $2 WHERE id = $3 AND deleted_at IS NULL`
	_, err := r.db.Exec(ctx, query, status, time.Now(), id)
	return err
}

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

func (r *postgresInvoiceRepository) CreateItem(ctx context.Context, item *domain.InvoiceItem) error {
	query := `INSERT INTO invoice_items (id, invoice_id, description, amount, created_at, updated_at)
	          VALUES ($1, $2, $3, $4, $5, $6)`
	_, err := r.db.Exec(ctx, query, item.ID, item.InvoiceID, item.Description, item.Amount, item.CreatedAt, item.UpdatedAt)
	return err
}

func (r *postgresInvoiceRepository) GetItemsByInvoiceID(ctx context.Context, invoiceID uuid.UUID) ([]domain.InvoiceItem, error) {
	query := `SELECT id, invoice_id, description, amount, created_at, updated_at
	          FROM invoice_items WHERE invoice_id = $1 AND deleted_at IS NULL`
	rows, err := r.db.Query(ctx, query, invoiceID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []domain.InvoiceItem
	for rows.Next() {
		var item domain.InvoiceItem
		if err := rows.Scan(&item.ID, &item.InvoiceID, &item.Description, &item.Amount, &item.CreatedAt, &item.UpdatedAt); err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, nil
}