-- ============================================================
-- quoERP — Flujo de Caja Proyectado
-- Migracion: 036_cashflow.sql
-- ============================================================
-- Complementa la proyeccion automatica (CxC+CxP+Nomina) con
-- items fijos configurables: arriendo, cuotas, utilidades, etc.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS cashflow_fixed_items (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    name            VARCHAR(150)    NOT NULL
        COMMENT 'Ej: Arriendo oficina, Cuota prestamo, Utilidades',
    category        ENUM('income','expense') NOT NULL DEFAULT 'expense',
    amount          DECIMAL(14,2)   NOT NULL DEFAULT 0,
    frequency       ENUM('once','weekly','biweekly','monthly','quarterly','annual')
                    NOT NULL DEFAULT 'monthly',
    next_date       DATE            NOT NULL
        COMMENT 'Proxima fecha de ocurrencia',
    day_of_month    TINYINT UNSIGNED DEFAULT NULL
        COMMENT 'Para frecuencia mensual: dia del mes (1-31)',
    end_date        DATE            DEFAULT NULL
        COMMENT 'NULL = sin fecha de termino (indefinido)',
    notes           VARCHAR(300)    DEFAULT NULL,
    is_active       TINYINT(1)      NOT NULL DEFAULT 1,
    created_by      INT UNSIGNED    DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_company  (company_id),
    INDEX idx_active   (is_active),
    INDEX idx_next     (next_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;