-- WireAdmin Phase 2: Accounting & Finance
-- Prefix: wa_acc_

-- Tabla de Periodos Contables
CREATE TABLE IF NOT EXISTS wa_acc_periods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    year INT NOT NULL,
    month INT NOT NULL,
    status ENUM('open', 'closed', 'locked') DEFAULT 'open',
    closed_at TIMESTAMP NULL,
    closed_by INT NULL, -- User ID
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES wa_companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_period (company_id, year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Plan de Cuentas (Chart of Accounts)
CREATE TABLE IF NOT EXISTS wa_acc_chart_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    code VARCHAR(20) NOT NULL, -- Ej: 1.1.01.01
    name VARCHAR(100) NOT NULL,
    type ENUM('asset', 'liability', 'equity', 'revenue', 'expense') NOT NULL,
    parent_id INT NULL,
    is_imputable BOOLEAN DEFAULT TRUE, -- TRUE se puede usar en asientos, FALSE es solo agrupador
    level INT DEFAULT 1,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES wa_companies(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES wa_acc_chart_accounts(id) ON DELETE SET NULL,
    UNIQUE KEY unique_account_code (company_id, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cabecera de Asientos (Journal Entries)
CREATE TABLE IF NOT EXISTS wa_acc_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    period_id INT NOT NULL,
    number INT NOT NULL, -- Folio secuencial por año/empresa
    date DATE NOT NULL,
    description TEXT NOT NULL,
    type ENUM('income', 'expense', 'transfer', 'opening', 'adjustment') DEFAULT 'transfer',
    status ENUM('draft', 'posted', 'voided') DEFAULT 'posted',
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES wa_companies(id) ON DELETE CASCADE,
    FOREIGN KEY (period_id) REFERENCES wa_acc_periods(id),
    FOREIGN KEY (created_by) REFERENCES wa_users(id),
    UNIQUE KEY unique_entry_number (company_id, number) -- Simplificado, idealmente number debería reiniciar por año
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Líneas de Asiento (Entry Lines)
CREATE TABLE IF NOT EXISTS wa_acc_entry_lines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entry_id INT NOT NULL,
    account_id INT NOT NULL,
    debit DECIMAL(15,2) DEFAULT 0.00,
    credit DECIMAL(15,2) DEFAULT 0.00,
    description VARCHAR(255), -- Glosa específica de línea (opcional)
    reference VARCHAR(50), -- Nro documento, cheque, etc.
    FOREIGN KEY (entry_id) REFERENCES wa_acc_entries(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id) REFERENCES wa_acc_chart_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
