-- ============================================================
-- quoERP — Módulo Sistema Financiero / Contabilidad
-- Migración: 008_finance_module.sql
-- Versión: 1.0.0 | Fecha: 2026-06-05
-- ============================================================
-- Plan de cuentas basado en NIIF — Superintendencia de Compañías
-- y Valores del Ecuador (resolución SCVS-INC-DNCDN-2016-011)
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────
-- MEJORA: chart_of_accounts — campos adicionales NIIF
-- ─────────────────────────────────────────────────────────────
ALTER TABLE chart_of_accounts
    ADD COLUMN IF NOT EXISTS normal_balance ENUM('debit','credit') NOT NULL DEFAULT 'debit'
        COMMENT 'Saldo normal de la cuenta: debit=activo/gasto, credit=pasivo/patrimonio/ingreso',
    ADD COLUMN IF NOT EXISTS is_active      TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS allow_entries  TINYINT(1) NOT NULL DEFAULT 1
        COMMENT '0 = cuenta de agrupación, no acepta asientos directos',
    ADD COLUMN IF NOT EXISTS notes          TEXT DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;


-- ─────────────────────────────────────────────────────────────
-- MEJORA: journal_entries — campos adicionales para asientos automáticos
-- ─────────────────────────────────────────────────────────────
ALTER TABLE journal_entries
    ADD COLUMN IF NOT EXISTS source_type VARCHAR(50)  DEFAULT NULL
        COMMENT 'Módulo origen: sale_invoice, purchase_receipt, payment, payroll, manual',
    ADD COLUMN IF NOT EXISTS source_id   INT UNSIGNED DEFAULT NULL
        COMMENT 'ID del registro origen en su módulo',
    ADD COLUMN IF NOT EXISTS posted_at   DATETIME     DEFAULT NULL
        COMMENT 'Fecha/hora en que fue contabilizado',
    ADD COLUMN IF NOT EXISTS posted_by   INT UNSIGNED DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Índice separado: CREATE INDEX no soporta IF NOT EXISTS en MySQL 5.7,
-- se usa ALTER TABLE ADD INDEX sin condicional (falla silenciosamente si ya existe)
ALTER TABLE journal_entries ADD INDEX idx_source (source_type, source_id);


-- ─────────────────────────────────────────────────────────────
-- MEJORA: journal_entry_lines — orden y referencia
-- ─────────────────────────────────────────────────────────────
ALTER TABLE journal_entry_lines
    ADD COLUMN IF NOT EXISTS sort_order  TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER entry_id;


-- ─────────────────────────────────────────────────────────────
-- TABLA: bank_accounts — Cuentas bancarias / tesorería
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bank_accounts (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    office_id       INT UNSIGNED    DEFAULT NULL,
    coa_account_id  INT UNSIGNED    DEFAULT NULL
        COMMENT 'Cuenta en el plan de cuentas (1.1.01 Efectivo...)',
    name            VARCHAR(150)    NOT NULL
        COMMENT 'Nombre descriptivo: Banco Pichincha Cte., Caja chica, etc.',
    bank_name       VARCHAR(100)    NOT NULL,
    account_number  VARCHAR(40)     NOT NULL,
    account_type    ENUM('corriente','ahorros','caja') NOT NULL DEFAULT 'corriente',
    currency        VARCHAR(3)      NOT NULL DEFAULT 'USD',
    opening_balance DECIMAL(15,2)   NOT NULL DEFAULT 0,
    current_balance DECIMAL(15,2)   NOT NULL DEFAULT 0
        COMMENT 'Se actualiza con cada transacción',
    is_main         TINYINT(1)      NOT NULL DEFAULT 0,
    status          ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_company (company_id),

    CONSTRAINT fk_ba_company FOREIGN KEY (company_id) REFERENCES companies(id)        ON DELETE RESTRICT,
    CONSTRAINT fk_ba_office  FOREIGN KEY (office_id)  REFERENCES offices(id)          ON DELETE SET NULL,
    CONSTRAINT fk_ba_coa     FOREIGN KEY (coa_account_id) REFERENCES chart_of_accounts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Cuentas bancarias y de caja. Vinculadas al plan de cuentas.';


-- ─────────────────────────────────────────────────────────────
-- TABLA: finance_periods — Períodos contables
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS finance_periods (
    id          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id  INT UNSIGNED    NOT NULL,
    year        SMALLINT UNSIGNED NOT NULL,
    month       TINYINT UNSIGNED NOT NULL,
    status      ENUM('open','closed') NOT NULL DEFAULT 'open',
    closed_at   DATETIME        DEFAULT NULL,
    closed_by   INT UNSIGNED    DEFAULT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_period (company_id, year, month),
    INDEX idx_company (company_id),

    CONSTRAINT fk_fp_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Períodos contables por empresa. Un período cerrado no acepta asientos nuevos.';


SET FOREIGN_KEY_CHECKS = 1;

-- ─────────────────────────────────────────────────────────────
-- FIN DE MIGRACIÓN
-- Tablas nuevas: bank_accounts, finance_periods
-- ALTER: chart_of_accounts (normal_balance, is_active, allow_entries, notes)
-- ALTER: journal_entries (source_type, source_id, posted_at, posted_by)
-- ALTER: journal_entry_lines (sort_order)
-- ─────────────────────────────────────────────────────────────