-- =============================================================
-- ARCHIVO: 52-financial-journals.sql
-- DESCRIPCION: Diarios contables y periodos fiscales
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-20
-- DEPENDE DE: 50-financial-schema.sql, 51-financial-accounts.sql
-- =============================================================

-- =====================
-- TABLA: fiscal_years
-- Anos fiscales
-- =====================
CREATE TABLE IF NOT EXISTS financial.fiscal_years (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    company_id UUID,

    -- Identificacion
    name VARCHAR(100) NOT NULL,  -- Ej: "Ejercicio 2026"
    code VARCHAR(20) NOT NULL,   -- Ej: "FY2026"

    -- Periodo
    date_from DATE NOT NULL,
    date_to DATE NOT NULL,

    -- Estado
    status financial.period_status_enum DEFAULT 'open',

    -- Audit columns
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id),

    -- Validaciones
    CONSTRAINT chk_fiscal_years_dates CHECK (date_to > date_from),
    UNIQUE(tenant_id, code)
);

-- Indices para fiscal_years
CREATE INDEX IF NOT EXISTS idx_financial_fiscal_years_tenant ON financial.fiscal_years(tenant_id);
CREATE INDEX IF NOT EXISTS idx_financial_fiscal_years_code ON financial.fiscal_years(code);
CREATE INDEX IF NOT EXISTS idx_financial_fiscal_years_status ON financial.fiscal_years(status);
CREATE INDEX IF NOT EXISTS idx_financial_fiscal_years_dates ON financial.fiscal_years(date_from, date_to);

-- =====================
-- TABLA: fiscal_periods
-- Periodos fiscales (meses o trimestres dentro de un ano fiscal)
-- =====================
CREATE TABLE IF NOT EXISTS financial.fiscal_periods (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Relacion con ano fiscal
    fiscal_year_id UUID NOT NULL REFERENCES financial.fiscal_years(id) ON DELETE CASCADE,

    -- Identificacion
    code VARCHAR(20) NOT NULL,   -- Ej: "2026-01", "Q1-2026"
    name VARCHAR(100) NOT NULL,  -- Ej: "Enero 2026", "Primer Trimestre 2026"

    -- Periodo
    date_from DATE NOT NULL,
    date_to DATE NOT NULL,

    -- Estado
    status financial.period_status_enum DEFAULT 'open',

    -- Cierre
    closed_at TIMESTAMPTZ,
    closed_by UUID REFERENCES auth.users(id),

    -- Audit columns
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id),

    -- Validaciones
    CONSTRAINT chk_fiscal_periods_dates CHECK (date_to >= date_from),
    UNIQUE(tenant_id, fiscal_year_id, code)
);

-- Indices para fiscal_periods
CREATE INDEX IF NOT EXISTS idx_financial_fiscal_periods_tenant ON financial.fiscal_periods(tenant_id);
CREATE INDEX IF NOT EXISTS idx_financial_fiscal_periods_year ON financial.fiscal_periods(fiscal_year_id);
CREATE INDEX IF NOT EXISTS idx_financial_fiscal_periods_code ON financial.fiscal_periods(code);
CREATE INDEX IF NOT EXISTS idx_financial_fiscal_periods_status ON financial.fiscal_periods(status);
CREATE INDEX IF NOT EXISTS idx_financial_fiscal_periods_dates ON financial.fiscal_periods(date_from, date_to);
CREATE INDEX IF NOT EXISTS idx_financial_fiscal_periods_open ON financial.fiscal_periods(tenant_id, status) WHERE status = 'open';

-- =====================
-- TABLA: journals
-- Diarios contables (ventas, compras, caja, banco, general)
-- =====================
CREATE TABLE IF NOT EXISTS financial.journals (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Multi-tenant
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    company_id UUID,

    -- Identificacion
    name VARCHAR(255) NOT NULL,
    code VARCHAR(20) NOT NULL,

    -- Tipo de diario
    journal_type financial.journal_type_enum NOT NULL,

    -- Cuenta por defecto (para asientos automaticos)
    default_account_id UUID REFERENCES financial.accounts(id) ON DELETE SET NULL,

    -- Secuencia para numeracion
    sequence_id UUID,  -- FK a sistema de secuencias si existe

    -- Moneda preferida
    currency_id UUID,

    -- Estado
    active BOOLEAN DEFAULT TRUE,

    -- Audit columns con soft delete
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,

    -- Unicidad por tenant
    UNIQUE(tenant_id, code)
);

-- Indices para journals
CREATE INDEX IF NOT EXISTS idx_financial_journals_tenant ON financial.journals(tenant_id);
CREATE INDEX IF NOT EXISTS idx_financial_journals_company ON financial.journals(company_id);
CREATE INDEX IF NOT EXISTS idx_financial_journals_code ON financial.journals(code);
CREATE INDEX IF NOT EXISTS idx_financial_journals_type ON financial.journals(journal_type);
CREATE INDEX IF NOT EXISTS idx_financial_journals_default_account ON financial.journals(default_account_id);
CREATE INDEX IF NOT EXISTS idx_financial_journals_active ON financial.journals(tenant_id) WHERE active = TRUE AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_financial_journals_by_type_active ON financial.journals(tenant_id, journal_type) WHERE active = TRUE AND deleted_at IS NULL;

-- =====================
-- COMENTARIOS
-- =====================
COMMENT ON TABLE financial.fiscal_years IS 'Anos fiscales para organizacion contable';
COMMENT ON COLUMN financial.fiscal_years.code IS 'Codigo unico del ano fiscal (ej: FY2026)';
COMMENT ON COLUMN financial.fiscal_years.status IS 'Estado: open (permite movimientos), closed (no permite movimientos)';

COMMENT ON TABLE financial.fiscal_periods IS 'Periodos fiscales (meses o trimestres) dentro de un ano fiscal';
COMMENT ON COLUMN financial.fiscal_periods.code IS 'Codigo del periodo (ej: 2026-01, Q1-2026)';
COMMENT ON COLUMN financial.fiscal_periods.closed_at IS 'Fecha y hora de cierre del periodo';
COMMENT ON COLUMN financial.fiscal_periods.closed_by IS 'Usuario que cerro el periodo';

COMMENT ON TABLE financial.journals IS 'Diarios contables para agrupar asientos por tipo de operacion';
COMMENT ON COLUMN financial.journals.code IS 'Codigo unico del diario (ej: VTAS, COMP, CAJA, BCO)';
COMMENT ON COLUMN financial.journals.journal_type IS 'Tipo: sale (ventas), purchase (compras), cash (caja), bank (banco), general';
COMMENT ON COLUMN financial.journals.default_account_id IS 'Cuenta por defecto para asientos automaticos';
COMMENT ON COLUMN financial.journals.sequence_id IS 'Referencia a secuencia para numeracion automatica';