-- =====================================================
-- SCHEMA: financial
-- PROPÓSITO: Contabilidad, facturas, pagos, finanzas
-- MÓDULOS: MGN-004 (Financiero Básico)
-- FECHA: 2025-11-24
-- =====================================================

-- Crear schema
CREATE SCHEMA IF NOT EXISTS financial;

-- =====================================================
-- TYPES (ENUMs)
-- =====================================================

CREATE TYPE financial.account_type AS ENUM (
  'asset',
  'liability',
  'equity',
  'revenue',
  'expense'
);

CREATE TYPE financial.journal_type AS ENUM (
  'sale',
  'purchase',
  'bank',
  'cash',
  'general'
);

CREATE TYPE financial.entry_status AS ENUM (
  'draft',
  'posted',
  'cancelled'
);

CREATE TYPE financial.invoice_type AS ENUM (
  'customer',
  'supplier'
);

CREATE TYPE financial.invoice_status AS ENUM (
  'draft',
  'open',
  'paid',
  'cancelled'
);

CREATE TYPE financial.payment_type AS ENUM (
  'inbound',
  'outbound'
);

CREATE TYPE financial.payment_method AS ENUM (
  'cash',
  'bank_transfer',
  'check',
  'card',
  'other'
);

CREATE TYPE financial.payment_status AS ENUM (
  'draft',
  'posted',
  'reconciled',
  'cancelled'
);

CREATE TYPE financial.tax_type AS ENUM (
  'sales',
  'purchase',
  'all'
);

CREATE TYPE financial.fiscal_period_status AS ENUM (
  'open',
  'closed'
);

-- =====================================================
-- TABLES
-- =====================================================

-- Tabla: account_types (Tipos de cuenta contable)
CREATE TABLE financial.account_types (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  account_type financial.account_type NOT NULL,
  description TEXT,

  -- Sin tenant_id: catálogo global
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Tabla: accounts (Plan de cuentas)
CREATE TABLE financial.accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  code VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  account_type_id UUID NOT NULL REFERENCES financial.account_types(id),
  parent_id UUID REFERENCES financial.accounts(id),

  -- Configuración
  currency_id UUID REFERENCES core.currencies(id),
  is_reconcilable BOOLEAN DEFAULT FALSE, -- ¿Permite conciliación?
  is_deprecated BOOLEAN DEFAULT FALSE,

  -- Notas
  notes TEXT,

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_accounts_code_company UNIQUE (company_id, code),
  CONSTRAINT chk_accounts_no_self_parent CHECK (id != parent_id)
);

-- Tabla: journals (Diarios contables)
CREATE TABLE financial.journals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  name VARCHAR(255) NOT NULL,
  code VARCHAR(20) NOT NULL,
  journal_type financial.journal_type NOT NULL,

  -- Configuración
  default_account_id UUID REFERENCES financial.accounts(id),
  sequence_id UUID REFERENCES core.sequences(id),
  currency_id UUID REFERENCES core.currencies(id),

  -- Control
  active BOOLEAN NOT NULL DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  deleted_at TIMESTAMP,
  deleted_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_journals_code_company UNIQUE (company_id, code)
);

-- Tabla: fiscal_years (Años fiscales)
CREATE TABLE financial.fiscal_years (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  name VARCHAR(100) NOT NULL,
  code VARCHAR(20) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status financial.fiscal_period_status NOT NULL DEFAULT 'open',

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_fiscal_years_code_company UNIQUE (company_id, code),
  CONSTRAINT chk_fiscal_years_dates CHECK (end_date > start_date)
);

-- Tabla: fiscal_periods (Períodos fiscales - meses)
CREATE TABLE financial.fiscal_periods (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  fiscal_year_id UUID NOT NULL REFERENCES financial.fiscal_years(id) ON DELETE CASCADE,

  name VARCHAR(100) NOT NULL,
  code VARCHAR(20) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status financial.fiscal_period_status NOT NULL DEFAULT 'open',

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_fiscal_periods_code_year UNIQUE (fiscal_year_id, code),
  CONSTRAINT chk_fiscal_periods_dates CHECK (end_date > start_date)
);

-- Tabla: journal_entries (Asientos contables)
CREATE TABLE financial.journal_entries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  journal_id UUID NOT NULL REFERENCES financial.journals(id),
  name VARCHAR(100) NOT NULL, -- Número de asiento
  ref VARCHAR(255), -- Referencia externa
  date DATE NOT NULL,
  status financial.entry_status NOT NULL DEFAULT 'draft',

  -- Metadatos
  notes TEXT,

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  posted_at TIMESTAMP,
  posted_by UUID REFERENCES auth.users(id),
  cancelled_at TIMESTAMP,
  cancelled_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_journal_entries_name_journal UNIQUE (journal_id, name)
);

-- Tabla: journal_entry_lines (Líneas de asiento contable)
CREATE TABLE financial.journal_entry_lines (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  entry_id UUID NOT NULL REFERENCES financial.journal_entries(id) ON DELETE CASCADE,

  account_id UUID NOT NULL REFERENCES financial.accounts(id),
  partner_id UUID REFERENCES core.partners(id),

  -- Montos
  debit DECIMAL(15, 2) NOT NULL DEFAULT 0,
  credit DECIMAL(15, 2) NOT NULL DEFAULT 0,

  -- Analítica
  analytic_account_id UUID, -- FK a analytics.analytic_accounts (se crea después)

  -- Descripción
  description TEXT,
  ref VARCHAR(255),

  -- Multi-moneda
  currency_id UUID REFERENCES core.currencies(id),
  amount_currency DECIMAL(15, 2),

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT chk_journal_lines_debit_positive CHECK (debit >= 0),
  CONSTRAINT chk_journal_lines_credit_positive CHECK (credit >= 0),
  CONSTRAINT chk_journal_lines_not_both CHECK (
    (debit > 0 AND credit = 0) OR (credit > 0 AND debit = 0)
  )
);

-- Índices para journal_entry_lines
CREATE INDEX idx_journal_entry_lines_tenant_id ON financial.journal_entry_lines(tenant_id);
CREATE INDEX idx_journal_entry_lines_entry_id ON financial.journal_entry_lines(entry_id);
CREATE INDEX idx_journal_entry_lines_account_id ON financial.journal_entry_lines(account_id);

-- RLS para journal_entry_lines
ALTER TABLE financial.journal_entry_lines ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_journal_entry_lines ON financial.journal_entry_lines
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Tabla: taxes (Impuestos)
CREATE TABLE financial.taxes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  name VARCHAR(100) NOT NULL,
  code VARCHAR(20) NOT NULL,
  rate DECIMAL(5, 4) NOT NULL, -- 0.1600 para 16%
  tax_type financial.tax_type NOT NULL,

  -- Configuración contable
  account_id UUID REFERENCES financial.accounts(id),

  -- Control
  active BOOLEAN NOT NULL DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_taxes_code_company UNIQUE (company_id, code),
  CONSTRAINT chk_taxes_rate CHECK (rate >= 0 AND rate <= 1)
);

-- Tabla: payment_terms (Términos de pago)
CREATE TABLE financial.payment_terms (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  name VARCHAR(100) NOT NULL,
  code VARCHAR(20) NOT NULL,

  -- Configuración de términos (JSON)
  -- Ejemplo: [{"days": 0, "percent": 100}] = Pago inmediato
  -- Ejemplo: [{"days": 30, "percent": 100}] = 30 días
  -- Ejemplo: [{"days": 15, "percent": 50}, {"days": 30, "percent": 50}] = 50% a 15 días, 50% a 30 días
  terms JSONB NOT NULL DEFAULT '[]',

  -- Control
  active BOOLEAN NOT NULL DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_payment_terms_code_company UNIQUE (company_id, code)
);

-- Tabla: invoices (Facturas)
CREATE TABLE financial.invoices (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  partner_id UUID NOT NULL REFERENCES core.partners(id),
  invoice_type financial.invoice_type NOT NULL,

  -- Numeración
  number VARCHAR(100), -- Número de factura (generado al validar)
  ref VARCHAR(100), -- Referencia del partner

  -- Fechas
  invoice_date DATE NOT NULL,
  due_date DATE,

  -- Montos
  currency_id UUID NOT NULL REFERENCES core.currencies(id),
  amount_untaxed DECIMAL(15, 2) NOT NULL DEFAULT 0,
  amount_tax DECIMAL(15, 2) NOT NULL DEFAULT 0,
  amount_total DECIMAL(15, 2) NOT NULL DEFAULT 0,
  amount_paid DECIMAL(15, 2) NOT NULL DEFAULT 0,
  amount_residual DECIMAL(15, 2) NOT NULL DEFAULT 0,

  -- Estado
  status financial.invoice_status NOT NULL DEFAULT 'draft',

  -- Configuración
  payment_term_id UUID REFERENCES financial.payment_terms(id),
  journal_id UUID REFERENCES financial.journals(id),

  -- Asiento contable (generado al validar)
  journal_entry_id UUID REFERENCES financial.journal_entries(id),

  -- Notas
  notes TEXT,

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  validated_at TIMESTAMP,
  validated_by UUID REFERENCES auth.users(id),
  cancelled_at TIMESTAMP,
  cancelled_by UUID REFERENCES auth.users(id),

  CONSTRAINT uq_invoices_number_company UNIQUE (company_id, number),
  CONSTRAINT chk_invoices_amounts CHECK (
    amount_total = amount_untaxed + amount_tax
  ),
  CONSTRAINT chk_invoices_residual CHECK (
    amount_residual = amount_total - amount_paid
  )
);

-- Tabla: invoice_lines (Líneas de factura)
CREATE TABLE financial.invoice_lines (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  invoice_id UUID NOT NULL REFERENCES financial.invoices(id) ON DELETE CASCADE,

  product_id UUID, -- FK a inventory.products (se crea después)
  description TEXT NOT NULL,

  -- Cantidades y precios
  quantity DECIMAL(12, 4) NOT NULL DEFAULT 1,
  uom_id UUID REFERENCES core.uom(id),
  price_unit DECIMAL(15, 4) NOT NULL,

  -- Impuestos (array de tax_ids)
  tax_ids UUID[] DEFAULT '{}',

  -- Montos calculados
  amount_untaxed DECIMAL(15, 2) NOT NULL,
  amount_tax DECIMAL(15, 2) NOT NULL,
  amount_total DECIMAL(15, 2) NOT NULL,

  -- Contabilidad
  account_id UUID REFERENCES financial.accounts(id),
  analytic_account_id UUID, -- FK a analytics.analytic_accounts

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP,

  CONSTRAINT chk_invoice_lines_quantity CHECK (quantity > 0),
  CONSTRAINT chk_invoice_lines_amounts CHECK (
    amount_total = amount_untaxed + amount_tax
  )
);

-- Índices para invoice_lines
CREATE INDEX idx_invoice_lines_tenant_id ON financial.invoice_lines(tenant_id);
CREATE INDEX idx_invoice_lines_invoice_id ON financial.invoice_lines(invoice_id);
CREATE INDEX idx_invoice_lines_product_id ON financial.invoice_lines(product_id);

-- RLS para invoice_lines
ALTER TABLE financial.invoice_lines ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_invoice_lines ON financial.invoice_lines
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Tabla: payments (Pagos)
CREATE TABLE financial.payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  partner_id UUID NOT NULL REFERENCES core.partners(id),
  payment_type financial.payment_type NOT NULL,
  payment_method financial.payment_method NOT NULL,

  -- Monto
  amount DECIMAL(15, 2) NOT NULL,
  currency_id UUID NOT NULL REFERENCES core.currencies(id),

  -- Fecha y referencia
  payment_date DATE NOT NULL,
  ref VARCHAR(255),

  -- Estado
  status financial.payment_status NOT NULL DEFAULT 'draft',

  -- Configuración
  journal_id UUID NOT NULL REFERENCES financial.journals(id),

  -- Asiento contable (generado al validar)
  journal_entry_id UUID REFERENCES financial.journal_entries(id),

  -- Notas
  notes TEXT,

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  posted_at TIMESTAMP,
  posted_by UUID REFERENCES auth.users(id),

  CONSTRAINT chk_payments_amount CHECK (amount > 0)
);

-- Tabla: payment_invoice (Conciliación pagos-facturas)
CREATE TABLE financial.payment_invoice (
  payment_id UUID NOT NULL REFERENCES financial.payments(id) ON DELETE CASCADE,
  invoice_id UUID NOT NULL REFERENCES financial.invoices(id) ON DELETE CASCADE,
  amount DECIMAL(15, 2) NOT NULL,

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (payment_id, invoice_id),
  CONSTRAINT chk_payment_invoice_amount CHECK (amount > 0)
);

-- Tabla: bank_accounts (Cuentas bancarias)
CREATE TABLE financial.bank_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID REFERENCES auth.companies(id),

  partner_id UUID REFERENCES core.partners(id), -- Puede ser de la empresa o de un partner

  bank_name VARCHAR(255) NOT NULL,
  account_number VARCHAR(50) NOT NULL,
  account_holder VARCHAR(255),

  -- Configuración
  currency_id UUID REFERENCES core.currencies(id),
  journal_id UUID REFERENCES financial.journals(id), -- Diario asociado (si es cuenta de la empresa)

  -- Control
  active BOOLEAN NOT NULL DEFAULT TRUE,

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id)
);

-- Tabla: reconciliations (Conciliaciones bancarias)
CREATE TABLE financial.reconciliations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
  company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,

  bank_account_id UUID NOT NULL REFERENCES financial.bank_accounts(id),

  -- Período de conciliación
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,

  -- Saldos
  balance_start DECIMAL(15, 2) NOT NULL,
  balance_end_real DECIMAL(15, 2) NOT NULL, -- Saldo real del banco
  balance_end_computed DECIMAL(15, 2) NOT NULL, -- Saldo calculado

  -- Líneas conciliadas (array de journal_entry_line_ids)
  reconciled_line_ids UUID[] DEFAULT '{}',

  -- Estado
  status financial.entry_status NOT NULL DEFAULT 'draft',

  -- Auditoría
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES auth.users(id),
  updated_at TIMESTAMP,
  updated_by UUID REFERENCES auth.users(id),
  validated_at TIMESTAMP,
  validated_by UUID REFERENCES auth.users(id),

  CONSTRAINT chk_reconciliations_dates CHECK (end_date >= start_date)
);

-- =====================================================
-- INDICES
-- =====================================================

-- Account Types
CREATE INDEX idx_account_types_code ON financial.account_types(code);

-- Accounts
CREATE INDEX idx_accounts_tenant_id ON financial.accounts(tenant_id);
CREATE INDEX idx_accounts_company_id ON financial.accounts(company_id);
CREATE INDEX idx_accounts_code ON financial.accounts(code);
CREATE INDEX idx_accounts_parent_id ON financial.accounts(parent_id);
CREATE INDEX idx_accounts_type_id ON financial.accounts(account_type_id);

-- Journals
CREATE INDEX idx_journals_tenant_id ON financial.journals(tenant_id);
CREATE INDEX idx_journals_company_id ON financial.journals(company_id);
CREATE INDEX idx_journals_code ON financial.journals(code);
CREATE INDEX idx_journals_type ON financial.journals(journal_type);

-- Fiscal Years
CREATE INDEX idx_fiscal_years_tenant_id ON financial.fiscal_years(tenant_id);
CREATE INDEX idx_fiscal_years_company_id ON financial.fiscal_years(company_id);
CREATE INDEX idx_fiscal_years_dates ON financial.fiscal_years(start_date, end_date);

-- Fiscal Periods
CREATE INDEX idx_fiscal_periods_tenant_id ON financial.fiscal_periods(tenant_id);
CREATE INDEX idx_fiscal_periods_year_id ON financial.fiscal_periods(fiscal_year_id);
CREATE INDEX idx_fiscal_periods_dates ON financial.fiscal_periods(start_date, end_date);

-- Journal Entries
CREATE INDEX idx_journal_entries_tenant_id ON financial.journal_entries(tenant_id);
CREATE INDEX idx_journal_entries_company_id ON financial.journal_entries(company_id);
CREATE INDEX idx_journal_entries_journal_id ON financial.journal_entries(journal_id);
CREATE INDEX idx_journal_entries_date ON financial.journal_entries(date);
CREATE INDEX idx_journal_entries_status ON financial.journal_entries(status);

-- Journal Entry Lines
CREATE INDEX idx_journal_entry_lines_entry_id ON financial.journal_entry_lines(entry_id);
CREATE INDEX idx_journal_entry_lines_account_id ON financial.journal_entry_lines(account_id);
CREATE INDEX idx_journal_entry_lines_partner_id ON financial.journal_entry_lines(partner_id);
CREATE INDEX idx_journal_entry_lines_analytic ON financial.journal_entry_lines(analytic_account_id);

-- Taxes
CREATE INDEX idx_taxes_tenant_id ON financial.taxes(tenant_id);
CREATE INDEX idx_taxes_company_id ON financial.taxes(company_id);
CREATE INDEX idx_taxes_code ON financial.taxes(code);
CREATE INDEX idx_taxes_type ON financial.taxes(tax_type);
CREATE INDEX idx_taxes_active ON financial.taxes(active) WHERE active = TRUE;

-- Payment Terms
CREATE INDEX idx_payment_terms_tenant_id ON financial.payment_terms(tenant_id);
CREATE INDEX idx_payment_terms_company_id ON financial.payment_terms(company_id);

-- Invoices
CREATE INDEX idx_invoices_tenant_id ON financial.invoices(tenant_id);
CREATE INDEX idx_invoices_company_id ON financial.invoices(company_id);
CREATE INDEX idx_invoices_partner_id ON financial.invoices(partner_id);
CREATE INDEX idx_invoices_type ON financial.invoices(invoice_type);
CREATE INDEX idx_invoices_status ON financial.invoices(status);
CREATE INDEX idx_invoices_number ON financial.invoices(number);
CREATE INDEX idx_invoices_date ON financial.invoices(invoice_date);
CREATE INDEX idx_invoices_due_date ON financial.invoices(due_date);

-- Invoice Lines
CREATE INDEX idx_invoice_lines_invoice_id ON financial.invoice_lines(invoice_id);
CREATE INDEX idx_invoice_lines_product_id ON financial.invoice_lines(product_id);
CREATE INDEX idx_invoice_lines_account_id ON financial.invoice_lines(account_id);

-- Payments
CREATE INDEX idx_payments_tenant_id ON financial.payments(tenant_id);
CREATE INDEX idx_payments_company_id ON financial.payments(company_id);
CREATE INDEX idx_payments_partner_id ON financial.payments(partner_id);
CREATE INDEX idx_payments_type ON financial.payments(payment_type);
CREATE INDEX idx_payments_status ON financial.payments(status);
CREATE INDEX idx_payments_date ON financial.payments(payment_date);

-- Payment Invoice
CREATE INDEX idx_payment_invoice_payment_id ON financial.payment_invoice(payment_id);
CREATE INDEX idx_payment_invoice_invoice_id ON financial.payment_invoice(invoice_id);

-- Bank Accounts
CREATE INDEX idx_bank_accounts_tenant_id ON financial.bank_accounts(tenant_id);
CREATE INDEX idx_bank_accounts_company_id ON financial.bank_accounts(company_id);
CREATE INDEX idx_bank_accounts_partner_id ON financial.bank_accounts(partner_id);

-- Reconciliations
CREATE INDEX idx_reconciliations_tenant_id ON financial.reconciliations(tenant_id);
CREATE INDEX idx_reconciliations_company_id ON financial.reconciliations(company_id);
CREATE INDEX idx_reconciliations_bank_account_id ON financial.reconciliations(bank_account_id);
CREATE INDEX idx_reconciliations_dates ON financial.reconciliations(start_date, end_date);

-- =====================================================
-- FUNCTIONS
-- =====================================================

-- Función: validate_entry_balance
-- Valida que un asiento esté balanceado (debit = credit)
CREATE OR REPLACE FUNCTION financial.validate_entry_balance(p_entry_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
  v_total_debit DECIMAL;
  v_total_credit DECIMAL;
BEGIN
  SELECT
    COALESCE(SUM(debit), 0),
    COALESCE(SUM(credit), 0)
  INTO v_total_debit, v_total_credit
  FROM financial.journal_entry_lines
  WHERE entry_id = p_entry_id;

  IF v_total_debit != v_total_credit THEN
    RAISE EXCEPTION 'Journal entry % is not balanced: debit=% credit=%',
      p_entry_id, v_total_debit, v_total_credit;
  END IF;

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION financial.validate_entry_balance IS 'Valida que un asiento contable esté balanceado (debit = credit)';

-- Función: post_journal_entry
-- Contabiliza un asiento (cambiar estado a posted)
CREATE OR REPLACE FUNCTION financial.post_journal_entry(p_entry_id UUID)
RETURNS VOID AS $$
BEGIN
  -- Validar balance
  PERFORM financial.validate_entry_balance(p_entry_id);

  -- Actualizar estado
  UPDATE financial.journal_entries
  SET status = 'posted',
      posted_at = CURRENT_TIMESTAMP,
      posted_by = get_current_user_id()
  WHERE id = p_entry_id
    AND status = 'draft';

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Journal entry % not found or already posted', p_entry_id;
  END IF;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION financial.post_journal_entry IS 'Contabiliza un asiento contable después de validar su balance';

-- Función: calculate_invoice_totals
-- Calcula los totales de una factura a partir de sus líneas
CREATE OR REPLACE FUNCTION financial.calculate_invoice_totals(p_invoice_id UUID)
RETURNS VOID AS $$
DECLARE
  v_amount_untaxed DECIMAL;
  v_amount_tax DECIMAL;
  v_amount_total DECIMAL;
BEGIN
  SELECT
    COALESCE(SUM(amount_untaxed), 0),
    COALESCE(SUM(amount_tax), 0),
    COALESCE(SUM(amount_total), 0)
  INTO v_amount_untaxed, v_amount_tax, v_amount_total
  FROM financial.invoice_lines
  WHERE invoice_id = p_invoice_id;

  UPDATE financial.invoices
  SET amount_untaxed = v_amount_untaxed,
      amount_tax = v_amount_tax,
      amount_total = v_amount_total,
      amount_residual = v_amount_total - amount_paid,
      updated_at = CURRENT_TIMESTAMP,
      updated_by = get_current_user_id()
  WHERE id = p_invoice_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION financial.calculate_invoice_totals IS 'Calcula los totales de una factura a partir de sus líneas';

-- Función: update_invoice_paid_amount
-- Actualiza el monto pagado de una factura
CREATE OR REPLACE FUNCTION financial.update_invoice_paid_amount(p_invoice_id UUID)
RETURNS VOID AS $$
DECLARE
  v_amount_paid DECIMAL;
BEGIN
  SELECT COALESCE(SUM(amount), 0)
  INTO v_amount_paid
  FROM financial.payment_invoice
  WHERE invoice_id = p_invoice_id;

  UPDATE financial.invoices
  SET amount_paid = v_amount_paid,
      amount_residual = amount_total - v_amount_paid,
      status = CASE
        WHEN v_amount_paid >= amount_total THEN 'paid'::financial.invoice_status
        WHEN v_amount_paid > 0 THEN 'open'::financial.invoice_status
        ELSE status
      END
  WHERE id = p_invoice_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION financial.update_invoice_paid_amount IS 'Actualiza el monto pagado y estado de una factura';

-- =====================================================
-- TRIGGERS
-- =====================================================

-- Trigger: Actualizar updated_at
CREATE TRIGGER trg_accounts_updated_at
  BEFORE UPDATE ON financial.accounts
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

CREATE TRIGGER trg_journals_updated_at
  BEFORE UPDATE ON financial.journals
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

CREATE TRIGGER trg_fiscal_years_updated_at
  BEFORE UPDATE ON financial.fiscal_years
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

CREATE TRIGGER trg_fiscal_periods_updated_at
  BEFORE UPDATE ON financial.fiscal_periods
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

CREATE TRIGGER trg_journal_entries_updated_at
  BEFORE UPDATE ON financial.journal_entries
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

CREATE TRIGGER trg_taxes_updated_at
  BEFORE UPDATE ON financial.taxes
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

CREATE TRIGGER trg_payment_terms_updated_at
  BEFORE UPDATE ON financial.payment_terms
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

CREATE TRIGGER trg_invoices_updated_at
  BEFORE UPDATE ON financial.invoices
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

CREATE TRIGGER trg_payments_updated_at
  BEFORE UPDATE ON financial.payments
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

CREATE TRIGGER trg_bank_accounts_updated_at
  BEFORE UPDATE ON financial.bank_accounts
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

CREATE TRIGGER trg_reconciliations_updated_at
  BEFORE UPDATE ON financial.reconciliations
  FOR EACH ROW
  EXECUTE FUNCTION auth.update_updated_at_column();

-- Trigger: Validar balance antes de contabilizar
CREATE OR REPLACE FUNCTION financial.trg_validate_entry_before_post()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'posted' AND OLD.status = 'draft' THEN
    PERFORM financial.validate_entry_balance(NEW.id);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_journal_entries_validate_balance
  BEFORE UPDATE OF status ON financial.journal_entries
  FOR EACH ROW
  EXECUTE FUNCTION financial.trg_validate_entry_before_post();

-- Trigger: Actualizar totales de factura al cambiar líneas
CREATE OR REPLACE FUNCTION financial.trg_update_invoice_totals()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    PERFORM financial.calculate_invoice_totals(OLD.invoice_id);
  ELSE
    PERFORM financial.calculate_invoice_totals(NEW.invoice_id);
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_invoice_lines_update_totals
  AFTER INSERT OR UPDATE OR DELETE ON financial.invoice_lines
  FOR EACH ROW
  EXECUTE FUNCTION financial.trg_update_invoice_totals();

-- Trigger: Actualizar monto pagado al conciliar
CREATE OR REPLACE FUNCTION financial.trg_update_invoice_paid()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    PERFORM financial.update_invoice_paid_amount(OLD.invoice_id);
  ELSE
    PERFORM financial.update_invoice_paid_amount(NEW.invoice_id);
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_payment_invoice_update_paid
  AFTER INSERT OR UPDATE OR DELETE ON financial.payment_invoice
  FOR EACH ROW
  EXECUTE FUNCTION financial.trg_update_invoice_paid();

-- =====================================================
-- TRACKING AUTOMÁTICO (mail.thread pattern)
-- =====================================================

-- Trigger: Tracking automático para facturas
CREATE TRIGGER track_invoice_changes
  AFTER INSERT OR UPDATE OR DELETE ON financial.invoices
  FOR EACH ROW EXECUTE FUNCTION system.track_field_changes();

COMMENT ON TRIGGER track_invoice_changes ON financial.invoices IS
'Registra automáticamente cambios en facturas (estado, monto, cliente, fechas)';

-- Trigger: Tracking automático para asientos contables
CREATE TRIGGER track_journal_entry_changes
  AFTER INSERT OR UPDATE OR DELETE ON financial.journal_entries
  FOR EACH ROW EXECUTE FUNCTION system.track_field_changes();

COMMENT ON TRIGGER track_journal_entry_changes ON financial.journal_entries IS
'Registra automáticamente cambios en asientos contables (estado, fecha, diario)';

-- =====================================================
-- ROW LEVEL SECURITY (RLS)
-- =====================================================

ALTER TABLE financial.accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.journals ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.fiscal_years ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.fiscal_periods ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.journal_entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.taxes ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.payment_terms ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.bank_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.reconciliations ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_accounts ON financial.accounts
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_journals ON financial.journals
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_fiscal_years ON financial.fiscal_years
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_fiscal_periods ON financial.fiscal_periods
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_journal_entries ON financial.journal_entries
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_taxes ON financial.taxes
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_payment_terms ON financial.payment_terms
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_invoices ON financial.invoices
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_payments ON financial.payments
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_bank_accounts ON financial.bank_accounts
  USING (tenant_id = get_current_tenant_id());

CREATE POLICY tenant_isolation_reconciliations ON financial.reconciliations
  USING (tenant_id = get_current_tenant_id());

-- =====================================================
-- SEED DATA
-- =====================================================

-- Tipos de cuenta estándar
INSERT INTO financial.account_types (code, name, account_type, description) VALUES
('ASSET_CASH', 'Cash and Cash Equivalents', 'asset', 'Efectivo y equivalentes'),
('ASSET_RECEIVABLE', 'Accounts Receivable', 'asset', 'Cuentas por cobrar'),
('ASSET_CURRENT', 'Current Assets', 'asset', 'Activos circulantes'),
('ASSET_FIXED', 'Fixed Assets', 'asset', 'Activos fijos'),
('LIABILITY_PAYABLE', 'Accounts Payable', 'liability', 'Cuentas por pagar'),
('LIABILITY_CURRENT', 'Current Liabilities', 'liability', 'Pasivos circulantes'),
('LIABILITY_LONG', 'Long-term Liabilities', 'liability', 'Pasivos a largo plazo'),
('EQUITY_CAPITAL', 'Capital', 'equity', 'Capital social'),
('EQUITY_RETAINED', 'Retained Earnings', 'equity', 'Utilidades retenidas'),
('REVENUE_SALES', 'Sales Revenue', 'revenue', 'Ingresos por ventas'),
('REVENUE_OTHER', 'Other Revenue', 'revenue', 'Otros ingresos'),
('EXPENSE_COGS', 'Cost of Goods Sold', 'expense', 'Costo de ventas'),
('EXPENSE_OPERATING', 'Operating Expenses', 'expense', 'Gastos operativos'),
('EXPENSE_ADMIN', 'Administrative Expenses', 'expense', 'Gastos administrativos')
ON CONFLICT (code) DO NOTHING;

-- =====================================================
-- COMENTARIOS
-- =====================================================

COMMENT ON SCHEMA financial IS 'Schema de contabilidad, facturas, pagos y finanzas';
COMMENT ON TABLE financial.account_types IS 'Tipos de cuentas contables (asset, liability, equity, revenue, expense)';
COMMENT ON TABLE financial.accounts IS 'Plan de cuentas contables';
COMMENT ON TABLE financial.journals IS 'Diarios contables (ventas, compras, bancos, etc.)';
COMMENT ON TABLE financial.fiscal_years IS 'Años fiscales';
COMMENT ON TABLE financial.fiscal_periods IS 'Períodos fiscales (meses)';
COMMENT ON TABLE financial.journal_entries IS 'Asientos contables';
COMMENT ON TABLE financial.journal_entry_lines IS 'Líneas de asientos contables (partida doble)';
COMMENT ON TABLE financial.taxes IS 'Impuestos (IVA, retenciones, etc.)';
COMMENT ON TABLE financial.payment_terms IS 'Términos de pago (inmediato, 30 días, etc.)';
COMMENT ON TABLE financial.invoices IS 'Facturas de cliente y proveedor';
COMMENT ON TABLE financial.invoice_lines IS 'Líneas de factura';
COMMENT ON TABLE financial.payments IS 'Pagos y cobros';
COMMENT ON TABLE financial.payment_invoice IS 'Conciliación de pagos con facturas';
COMMENT ON TABLE financial.bank_accounts IS 'Cuentas bancarias de la empresa y partners';
COMMENT ON TABLE financial.reconciliations IS 'Conciliaciones bancarias';

-- =====================================================
-- FIN DEL SCHEMA FINANCIAL
-- =====================================================