-- ============================================================ -- 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;