package repository

import (
	"aggios-app/backend/internal/domain"
	"database/sql"
	"github.com/lib/pq"
)

type ERPRepository struct {
	db *sql.DB
}

func NewERPRepository(db *sql.DB) *ERPRepository {
	return &ERPRepository{db: db}
}

// ==================== FINANCE: CATEGORIES ====================

func (r *ERPRepository) CreateFinancialCategory(cat *domain.FinancialCategory) error {
	query := `
		INSERT INTO erp_financial_categories (id, tenant_id, name, type, color, is_active)
		VALUES ($1, $2, $3, $4, $5, $6)
		RETURNING created_at, updated_at
	`
	return r.db.QueryRow(
		query,
		cat.ID, cat.TenantID, cat.Name, cat.Type, cat.Color, cat.IsActive,
	).Scan(&cat.CreatedAt, &cat.UpdatedAt)
}

func (r *ERPRepository) GetFinancialCategoriesByTenant(tenantID string) ([]domain.FinancialCategory, error) {
	query := `
		SELECT id, tenant_id, name, type, color, is_active, created_at, updated_at
		FROM erp_financial_categories
		WHERE tenant_id = $1
		ORDER BY name ASC
	`
	rows, err := r.db.Query(query, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var categories []domain.FinancialCategory
	for rows.Next() {
		var c domain.FinancialCategory
		err := rows.Scan(&c.ID, &c.TenantID, &c.Name, &c.Type, &c.Color, &c.IsActive, &c.CreatedAt, &c.UpdatedAt)
		if err != nil {
			return nil, err
		}
		categories = append(categories, c)
	}
	return categories, nil
}

// ==================== FINANCE: BANK ACCOUNTS ====================

func (r *ERPRepository) CreateBankAccount(acc *domain.BankAccount) error {
	query := `
		INSERT INTO erp_bank_accounts (id, tenant_id, name, bank_name, initial_balance, current_balance, is_active)
		VALUES ($1, $2, $3, $4, $5, $6, $7)
		RETURNING created_at, updated_at
	`
	return r.db.QueryRow(
		query,
		acc.ID, acc.TenantID, acc.Name, acc.BankName, acc.InitialBalance, acc.InitialBalance, acc.IsActive,
	).Scan(&acc.CreatedAt, &acc.UpdatedAt)
}

func (r *ERPRepository) GetBankAccountsByTenant(tenantID string) ([]domain.BankAccount, error) {
	query := `
		SELECT id, tenant_id, name, bank_name, initial_balance, current_balance, is_active, created_at, updated_at
		FROM erp_bank_accounts
		WHERE tenant_id = $1
		ORDER BY name ASC
	`
	rows, err := r.db.Query(query, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var accounts []domain.BankAccount
	for rows.Next() {
		var a domain.BankAccount
		err := rows.Scan(&a.ID, &a.TenantID, &a.Name, &a.BankName, &a.InitialBalance, &a.CurrentBalance, &a.IsActive, &a.CreatedAt, &a.UpdatedAt)
		if err != nil {
			return nil, err
		}
		accounts = append(accounts, a)
	}
	return accounts, nil
}

// ==================== ENTITIES: CUSTOMERS & SUPPLIERS ====================

func (r *ERPRepository) CreateEntity(e *domain.Entity) error {
	query := `
		INSERT INTO erp_entities (id, tenant_id, name, document, email, phone, type, status, address, city, state, zip, notes)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
		RETURNING created_at, updated_at
	`
	return r.db.QueryRow(
		query,
		e.ID, e.TenantID, e.Name, e.Document, e.Email, e.Phone, e.Type, e.Status, e.Address, e.City, e.State, e.Zip, e.Notes,
	).Scan(&e.CreatedAt, &e.UpdatedAt)
}

func (r *ERPRepository) GetEntitiesByTenant(tenantID string, entityType string) ([]domain.Entity, error) {
	query := `
		SELECT id, tenant_id, name, document, email, phone, type, status, address, city, state, zip, notes, created_at, updated_at
		FROM erp_entities
		WHERE tenant_id = $1
	`
	var args []interface{}
	args = append(args, tenantID)

	if entityType != "" {
		query += " AND (type = $2 OR type = 'both')"
		args = append(args, entityType)
	}

	query += " ORDER BY name ASC"

	rows, err := r.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var entities []domain.Entity
	for rows.Next() {
		var e domain.Entity
		err := rows.Scan(
			&e.ID, &e.TenantID, &e.Name, &e.Document, &e.Email, &e.Phone, &e.Type, &e.Status, &e.Address, &e.City, &e.State, &e.Zip, &e.Notes, &e.CreatedAt, &e.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		entities = append(entities, e)
	}
	return entities, nil
}

// ==================== FINANCE: TRANSACTIONS ====================

func (r *ERPRepository) CreateTransaction(t *domain.FinancialTransaction) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	query := `
		INSERT INTO erp_financial_transactions (
			id, tenant_id, account_id, category_id, entity_id, crm_customer_id, company_id, description, amount, type, status, due_date, payment_date, payment_method, attachments, created_by
		) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
		RETURNING created_at, updated_at
	`
	err = tx.QueryRow(
		query,
		t.ID, t.TenantID, t.AccountID, t.CategoryID, t.EntityID, t.CRMCustomerID, t.CompanyID, t.Description, t.Amount, t.Type, t.Status, t.DueDate, t.PaymentDate, t.PaymentMethod, pq.Array(t.Attachments), t.CreatedBy,
	).Scan(&t.CreatedAt, &t.UpdatedAt)
	if err != nil {
		return err
	}

	// Update balance if paid
	if t.Status == "paid" && t.AccountID != nil {
		balanceQuery := ""
		if t.Type == "income" {
			balanceQuery = "UPDATE erp_bank_accounts SET current_balance = current_balance + $1 WHERE id = $2"
		} else {
			balanceQuery = "UPDATE erp_bank_accounts SET current_balance = current_balance - $1 WHERE id = $2"
		}
		_, err = tx.Exec(balanceQuery, t.Amount, t.AccountID)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

func (r *ERPRepository) GetTransactionsByTenant(tenantID string) ([]domain.FinancialTransaction, error) {
	query := `
		SELECT id, tenant_id, account_id, category_id, entity_id, crm_customer_id, company_id, description, amount, type, status, due_date, payment_date, payment_method, attachments, created_by, created_at, updated_at
		FROM erp_financial_transactions
		WHERE tenant_id = $1
		ORDER BY created_at DESC
	`
	rows, err := r.db.Query(query, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var transactions []domain.FinancialTransaction
	for rows.Next() {
		var t domain.FinancialTransaction
		err := rows.Scan(
			&t.ID, &t.TenantID, &t.AccountID, &t.CategoryID, &t.EntityID, &t.CRMCustomerID, &t.CompanyID, &t.Description, &t.Amount, &t.Type, &t.Status, &t.DueDate, &t.PaymentDate, &t.PaymentMethod, pq.Array(&t.Attachments), &t.CreatedBy, &t.CreatedAt, &t.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		transactions = append(transactions, t)
	}
	return transactions, nil
}

// ==================== PRODUCTS ====================

func (r *ERPRepository) CreateProduct(p *domain.Product) error {
	query := `
		INSERT INTO erp_products (id, tenant_id, name, sku, description, price, cost_price, type, stock_quantity, is_active)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
		RETURNING created_at, updated_at
	`
	return r.db.QueryRow(
		query,
		p.ID, p.TenantID, p.Name, p.SKU, p.Description, p.Price, p.CostPrice, p.Type, p.StockQuantity, p.IsActive,
	).Scan(&p.CreatedAt, &p.UpdatedAt)
}

func (r *ERPRepository) GetProductsByTenant(tenantID string) ([]domain.Product, error) {
	query := `
		SELECT id, tenant_id, name, sku, description, price, cost_price, type, stock_quantity, is_active, created_at, updated_at
		FROM erp_products
		WHERE tenant_id = $1
		ORDER BY name ASC
	`
	rows, err := r.db.Query(query, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var products []domain.Product
	for rows.Next() {
		var p domain.Product
		err := rows.Scan(&p.ID, &p.TenantID, &p.Name, &p.SKU, &p.Description, &p.Price, &p.CostPrice, &p.Type, &p.StockQuantity, &p.IsActive, &p.CreatedAt, &p.UpdatedAt)
		if err != nil {
			return nil, err
		}
		products = append(products, p)
	}
	return products, nil
}

// ==================== ORDERS ====================

func (r *ERPRepository) CreateOrder(o *domain.Order, items []domain.OrderItem) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	orderQuery := `
		INSERT INTO erp_orders (id, tenant_id, customer_id, entity_id, status, total_amount, notes, created_by)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
		RETURNING created_at, updated_at
	`
	err = tx.QueryRow(
		orderQuery,
		o.ID, o.TenantID, o.CustomerID, o.EntityID, o.Status, o.TotalAmount, o.Notes, o.CreatedBy,
	).Scan(&o.CreatedAt, &o.UpdatedAt)
	if err != nil {
		return err
	}

	itemQuery := `
		INSERT INTO erp_order_items (id, order_id, product_id, quantity, unit_price, total_price)
		VALUES ($1, $2, $3, $4, $5, $6)
	`
	for _, item := range items {
		_, err = tx.Exec(itemQuery, item.ID, o.ID, item.ProductID, item.Quantity, item.UnitPrice, item.TotalPrice)
		if err != nil {
			return err
		}

		// Update stock if product
		stockQuery := "UPDATE erp_products SET stock_quantity = stock_quantity - $1 WHERE id = $2 AND type = 'product'"
		_, err = tx.Exec(stockQuery, item.Quantity, item.ProductID)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

func (r *ERPRepository) GetOrdersByTenant(tenantID string) ([]domain.Order, error) {
	query := `
		SELECT id, tenant_id, customer_id, status, total_amount, notes, created_by, created_at, updated_at
		FROM erp_orders
		WHERE tenant_id = $1
		ORDER BY created_at DESC
	`
	rows, err := r.db.Query(query, tenantID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var orders []domain.Order
	for rows.Next() {
		var o domain.Order
		err := rows.Scan(&o.ID, &o.TenantID, &o.CustomerID, &o.Status, &o.TotalAmount, &o.Notes, &o.CreatedBy, &o.CreatedAt, &o.UpdatedAt)
		if err != nil {
			return nil, err
		}
		orders = append(orders, o)
	}
	return orders, nil
}
func (r *ERPRepository) UpdateTransaction(t *domain.FinancialTransaction) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Get old transaction to adjust balance
	var oldT domain.FinancialTransaction
	err = tx.QueryRow(`
		SELECT amount, type, status, account_id 
		FROM erp_financial_transactions 
		WHERE id = $1 AND tenant_id = $2`, t.ID, t.TenantID).
		Scan(&oldT.Amount, &oldT.Type, &oldT.Status, &oldT.AccountID)
	if err != nil {
		return err
	}

	// Falls back to old type if not provided in request
	if t.Type == "" {
		t.Type = oldT.Type
	}

	// Reverse old balance impact
	if oldT.Status == "paid" && oldT.AccountID != nil {
		balanceQuery := ""
		if oldT.Type == "income" {
			balanceQuery = "UPDATE erp_bank_accounts SET current_balance = current_balance - $1 WHERE id = $2"
		} else {
			balanceQuery = "UPDATE erp_bank_accounts SET current_balance = current_balance + $1 WHERE id = $2"
		}
		_, err = tx.Exec(balanceQuery, oldT.Amount, oldT.AccountID)
		if err != nil {
			return err
		}
	}

	query := `
		UPDATE erp_financial_transactions
		SET description = $1, amount = $2, type = $3, status = $4, due_date = $5, payment_date = $6, 
		    category_id = $7, entity_id = $8, crm_customer_id = $9, company_id = $10, account_id = $11, payment_method = $12, updated_at = NOW()
		WHERE id = $13 AND tenant_id = $14
	`
	_, err = tx.Exec(query, 
		t.Description, t.Amount, t.Type, t.Status, t.DueDate, t.PaymentDate, 
		t.CategoryID, t.EntityID, t.CRMCustomerID, t.CompanyID, t.AccountID, t.PaymentMethod, 
		t.ID, t.TenantID)
	if err != nil {
		return err
	}

	// Apply new balance impact
	if t.Status == "paid" && t.AccountID != nil {
		balanceQuery := ""
		if t.Type == "income" {
			balanceQuery = "UPDATE erp_bank_accounts SET current_balance = current_balance + $1 WHERE id = $2"
		} else {
			balanceQuery = "UPDATE erp_bank_accounts SET current_balance = current_balance - $1 WHERE id = $2"
		}
		_, err = tx.Exec(balanceQuery, t.Amount, t.AccountID)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

func (r *ERPRepository) DeleteTransaction(id, tenantID string) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Adjust balance before delete
	var t domain.FinancialTransaction
	err = tx.QueryRow(`
		SELECT amount, type, status, account_id 
		FROM erp_financial_transactions 
		WHERE id = $1 AND tenant_id = $2`, id, tenantID).
		Scan(&t.Amount, &t.Type, &t.Status, &t.AccountID)
	if err != nil {
		return err
	}

	if t.Status == "paid" && t.AccountID != nil {
		balanceQuery := ""
		if t.Type == "income" {
			balanceQuery = "UPDATE erp_bank_accounts SET current_balance = current_balance - $1 WHERE id = $2"
		} else {
			balanceQuery = "UPDATE erp_bank_accounts SET current_balance = current_balance + $1 WHERE id = $2"
		}
		_, err = tx.Exec(balanceQuery, t.Amount, t.AccountID)
		if err != nil {
			return err
		}
	}

	_, err = tx.Exec("DELETE FROM erp_financial_transactions WHERE id = $1 AND tenant_id = $2", id, tenantID)
	if err != nil {
		return err
	}

	return tx.Commit()
}


func (r *ERPRepository) UpdateEntity(e *domain.Entity) error {
	query := `
		UPDATE erp_entities
		SET name = $1, document = $2, email = $3, phone = $4, type = $5, status = $6, 
		    address = $7, city = $8, state = $9, zip = $10, notes = $11, updated_at = NOW()
		WHERE id = $12 AND tenant_id = $13
	`
	_, err := r.db.Exec(query, e.Name, e.Document, e.Email, e.Phone, e.Type, e.Status, e.Address, e.City, e.State, e.Zip, e.Notes, e.ID, e.TenantID)
	return err
}

func (r *ERPRepository) DeleteEntity(id, tenantID string) error {
	_, err := r.db.Exec("DELETE FROM erp_entities WHERE id = $1 AND tenant_id = $2", id, tenantID)
	return err
}

func (r *ERPRepository) UpdateProduct(p *domain.Product) error {
	query := `
		UPDATE erp_products
		SET name = $1, sku = $2, description = $3, price = $4, cost_price = $5, 
		    type = $6, stock_quantity = $7, is_active = $8, updated_at = NOW()
		WHERE id = $9 AND tenant_id = $10
	`
	_, err := r.db.Exec(query, p.Name, p.SKU, p.Description, p.Price, p.CostPrice, p.Type, p.StockQuantity, p.IsActive, p.ID, p.TenantID)
	return err
}

func (r *ERPRepository) DeleteProduct(id, tenantID string) error {
	_, err := r.db.Exec("DELETE FROM erp_products WHERE id = $1 AND tenant_id = $2", id, tenantID)
	return err
}

func (r *ERPRepository) UpdateBankAccount(a *domain.BankAccount) error {
	query := `
		UPDATE erp_bank_accounts
		SET name = $1, bank_name = $2, initial_balance = $3, is_active = $4, updated_at = NOW()
		WHERE id = $5 AND tenant_id = $6
	`
	_, err := r.db.Exec(query, a.Name, a.BankName, a.InitialBalance, a.IsActive, a.ID, a.TenantID)
	return err
}

func (r *ERPRepository) DeleteBankAccount(id, tenantID string) error {
	_, err := r.db.Exec("DELETE FROM erp_bank_accounts WHERE id = $1 AND tenant_id = $2", id, tenantID)
	return err
}

func (r *ERPRepository) DeleteOrder(id, tenantID string) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Deleta os itens do pedido primeiro
	_, err = tx.Exec("DELETE FROM erp_order_items WHERE order_id = $1", id)
	if err != nil {
		return err
	}

	// Deleta o pedido
	_, err = tx.Exec("DELETE FROM erp_orders WHERE id = $1 AND tenant_id = $2", id, tenantID)
	if err != nil {
		return err
	}

	return tx.Commit()
}