-- ============================================================
-- hqsMEDIKAL — Fundación Multi-País
-- Migración: 038_multiCountry_foundation.sql
-- Agrega soporte multi-país a la base quoERP sin romper
-- la instalación Ecuador existente (default: EC)
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── Perfiles de país ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS country_profiles (
    country_code          CHAR(2)      NOT NULL,
    name                  VARCHAR(100) NOT NULL,
    currency_code         CHAR(3)      NOT NULL DEFAULT 'USD',
    currency_symbol       VARCHAR(5)   NOT NULL DEFAULT '$',
    currency_decimals     TINYINT      NOT NULL DEFAULT 2,
    -- Fiscal
    tax_name              VARCHAR(20)  NOT NULL DEFAULT 'IVA',
    tax_rate_default      DECIMAL(5,4) NOT NULL DEFAULT 0.15,
    tax_agency_name       VARCHAR(50)  NOT NULL DEFAULT 'SRI',
    efiscal_provider      VARCHAR(50)  NOT NULL DEFAULT 'sri_ec',
    -- Nómina / Seguridad social
    social_security_name  VARCHAR(50)  DEFAULT NULL,
    employee_ss_rate      DECIMAL(6,4) NOT NULL DEFAULT 0.0945,
    employer_ss_rate      DECIMAL(6,4) NOT NULL DEFAULT 0.1215,
    minimum_wage          DECIMAL(10,2) NOT NULL DEFAULT 460.00,
    -- Módulos específicos del país
    has_ars_module        TINYINT(1)   NOT NULL DEFAULT 0,
    has_ats_report        TINYINT(1)   NOT NULL DEFAULT 0,
    has_retention_vouchers TINYINT(1)  NOT NULL DEFAULT 0,
    check_digit_algorithm VARCHAR(20)  NOT NULL DEFAULT 'modulo11',
    -- Zona horaria
    timezone              VARCHAR(60)  NOT NULL DEFAULT 'America/Guayaquil',
    date_format           VARCHAR(20)  NOT NULL DEFAULT 'd/m/Y',
    PRIMARY KEY (country_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ecuador
INSERT IGNORE INTO country_profiles VALUES (
    'EC','Ecuador','USD','$',2,
    'IVA',0.15,'SRI','sri_ec',
    'IESS',0.0945,0.1215,460.00,
    0,1,1,'modulo11','America/Guayaquil','d/m/Y'
);

-- República Dominicana
INSERT IGNORE INTO country_profiles VALUES (
    'DO','República Dominicana','DOP','RD$',2,
    'ITBIS',0.18,'DGII','dgii_do',
    'TSS',0.0987,0.1770,23160.00,
    1,0,0,'modulo10','America/Santo_Domingo','d/m/Y'
);

-- ── Agregar country_code a companies (default EC) ────────
ALTER TABLE companies
    ADD COLUMN IF NOT EXISTS country_code CHAR(2) NOT NULL DEFAULT 'EC'
        COMMENT 'ISO 3166-1 alpha-2: EC, DO, CO...'
        AFTER color,
    ADD COLUMN IF NOT EXISTS base_currency CHAR(3) NOT NULL DEFAULT 'USD'
        AFTER country_code;

-- ── Reglas de nómina por país y vigencia ─────────────────
CREATE TABLE IF NOT EXISTS payroll_country_rules (
    id             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    country_code   CHAR(2)       NOT NULL,
    rule_key       VARCHAR(60)   NOT NULL
        COMMENT 'employee_ss_rate | employer_ss_rate | minimum_wage | bonus_months...',
    rule_value     DECIMAL(14,6) NOT NULL,
    effective_from DATE          NOT NULL,
    effective_to   DATE          DEFAULT NULL
        COMMENT 'NULL = vigente',
    description    VARCHAR(200)  DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX idx_country_key (country_code, rule_key),
    INDEX idx_dates       (effective_from, effective_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ecuador 2026
INSERT IGNORE INTO payroll_country_rules (country_code,rule_key,rule_value,effective_from,description) VALUES
('EC','employee_iess_rate',  0.0945, '2024-01-01', 'Aporte personal IESS'),
('EC','employer_iess_rate',  0.1215, '2024-01-01', 'Aporte patronal IESS'),
('EC','reserve_fund_rate',   0.0833, '2024-01-01', 'Fondos de reserva (8.33%)'),
('EC','vacation_rate',       0.0417, '2024-01-01', 'Vacaciones 1/24 sueldo'),
('EC','thirteenth_rate',     0.0833, '2024-01-01', 'Décimo tercero 1/12 mensual'),
('EC','fourteenth_rate',     0.0833, '2024-01-01', 'Décimo cuarto 1/12 SBU'),
('EC','minimum_wage',      460.00,   '2024-01-01', 'SBU Ecuador 2024');

-- República Dominicana 2026
INSERT IGNORE INTO payroll_country_rules (country_code,rule_key,rule_value,effective_from,description) VALUES
('DO','employee_sfs_rate',   0.0304, '2024-01-01', 'SFS empleado (salud)'),
('DO','employer_sfs_rate',   0.0710, '2024-01-01', 'SFS patronal (salud)'),
('DO','employee_afp_rate',   0.0287, '2024-01-01', 'AFP empleado (pensión)'),
('DO','employer_afp_rate',   0.0710, '2024-01-01', 'AFP patronal (pensión)'),
('DO','employer_arl_rate',   0.0060, '2024-01-01', 'ARL patronal (riesgos)'),
('DO','employee_ss_rate',    0.0304, '2024-01-01', 'TSS empleado total (SFS)'),
('DO','employer_ss_rate',    0.1770, '2024-01-01', 'TSS patronal total'),
('DO','christmas_bonus_rate',0.0833, '2024-01-01', 'Regalía pascual 1/12'),
('DO','severance_rate',      0.0833, '2024-01-01', 'Cesantía 1/12 por año'),
('DO','minimum_wage',      23160.00, '2024-01-01', 'Salario mínimo RD 2024 (DOP)');

-- ── Plantilla plan de cuentas por país ───────────────────
CREATE TABLE IF NOT EXISTS coa_country_templates (
    id             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    country_code   CHAR(2)       NOT NULL,
    code           VARCHAR(20)   NOT NULL,
    name           VARCHAR(200)  NOT NULL,
    type           ENUM('asset','liability','equity','income','expense') NOT NULL,
    level          TINYINT       NOT NULL DEFAULT 1,
    normal_balance ENUM('debit','credit') NOT NULL,
    parent_code    VARCHAR(20)   DEFAULT NULL,
    is_detail      TINYINT(1)    NOT NULL DEFAULT 0,
    sort_order     SMALLINT      NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    INDEX idx_country (country_code),
    UNIQUE KEY uq_country_code (country_code, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Secuencias fiscales por empresa ──────────────────────
-- Reemplaza la lógica hardcodeada Ecuador en bootstrap.php
CREATE TABLE IF NOT EXISTS fiscal_sequences (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED  NOT NULL,
    doc_type        VARCHAR(30)   NOT NULL
        COMMENT 'invoice|credit_note|debit_note|retention|ncf_b01|ncf_b02|ncf_b14|ncf_b15',
    series          VARCHAR(20)   DEFAULT NULL
        COMMENT 'EC: 001-001  /  DO: B01',
    current_seq     BIGINT        NOT NULL DEFAULT 0,
    max_seq         BIGINT        DEFAULT NULL
        COMMENT 'Máximo autorizado (DGII RD tiene límite por serie)',
    environment     ENUM('test','production') NOT NULL DEFAULT 'production',
    is_active       TINYINT(1)    NOT NULL DEFAULT 1,
    updated_at      DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_seq (company_id, doc_type, series, environment),
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Tipos de cambio (para RD con DOP) ────────────────────
CREATE TABLE IF NOT EXISTS exchange_rates (
    id            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    company_id    INT UNSIGNED  NOT NULL,
    from_currency CHAR(3)       NOT NULL,
    to_currency   CHAR(3)       NOT NULL,
    rate          DECIMAL(15,6) NOT NULL,
    rate_date     DATE          NOT NULL,
    source        VARCHAR(30)   NOT NULL DEFAULT 'manual',
    created_by    INT UNSIGNED  DEFAULT NULL,
    created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_company_date (company_id, rate_date),
    INDEX idx_currencies   (from_currency, to_currency)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
