-- ============================================================
-- quoERP — Conceptos de Nómina por Empleado
-- Migración: 019_payroll_concepts.sql
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Catálogo de tipos de concepto (globales + personalizados por empresa)
CREATE TABLE IF NOT EXISTS payroll_concept_types (
    id          INT UNSIGNED         NOT NULL AUTO_INCREMENT,
    company_id  INT UNSIGNED         DEFAULT NULL
        COMMENT 'NULL = tipo global del sistema',
    name        VARCHAR(120)         NOT NULL,
    type        ENUM('income','deduction') NOT NULL
        COMMENT 'income = ingreso | deduction = egreso/descuento',
    affects_gross TINYINT(1)         NOT NULL DEFAULT 0
        COMMENT '1 = suma al gross para calcular IESS y beneficios; 0 = fuera del gross',
    is_active   TINYINT(1)           NOT NULL DEFAULT 1,
    sort_order  TINYINT UNSIGNED     NOT NULL DEFAULT 0,
    created_at  DATETIME             NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_company (company_id),
    INDEX idx_type    (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Conceptos asignados a empleados
CREATE TABLE IF NOT EXISTS employee_concepts (
    id              INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    employee_id     INT UNSIGNED     NOT NULL,
    concept_type_id INT UNSIGNED     NOT NULL,
    amount          DECIMAL(12,2)    NOT NULL DEFAULT 0.00,
    description     VARCHAR(255)     DEFAULT NULL COMMENT 'Referencia interna (ej: cuota 3/6)',
    start_date      DATE             DEFAULT NULL COMMENT 'Desde (NULL = desde ahora)',
    end_date        DATE             DEFAULT NULL COMMENT 'Hasta (NULL = indefinido)',
    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_employee (employee_id),
    INDEX idx_concept  (concept_type_id),
    INDEX idx_active   (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SEED: tipos de concepto del sistema (id 1-20 reservados)
INSERT IGNORE INTO payroll_concept_types (id, company_id, name, type, affects_gross, sort_order) VALUES
-- INGRESOS
(1,  NULL, 'Alimentación',          'income',    0, 10),
(2,  NULL, 'Transporte',            'income',    0, 20),
(3,  NULL, 'Bono de desempeño',     'income',    1, 30),
(4,  NULL, 'Bono de producción',    'income',    1, 40),
(5,  NULL, 'Comisión',              'income',    1, 50),
(6,  NULL, 'Horas extra adicional', 'income',    1, 60),
(7,  NULL, 'Movilización',          'income',    0, 70),
(8,  NULL, 'Otros ingresos',        'income',    0, 80),
-- DESCUENTOS / EGRESOS
(10, NULL, 'Préstamo IESS',         'deduction', 0, 10),
(11, NULL, 'Préstamo empresa',      'deduction', 0, 20),
(12, NULL, 'Anticipo de sueldo',    'deduction', 0, 30),
(13, NULL, 'Multa / Sanción',       'deduction', 0, 40),
(14, NULL, 'Permiso sin sueldo',    'deduction', 0, 50),
(15, NULL, 'Seguro médico privado', 'deduction', 0, 60),
(16, NULL, 'Otros descuentos',      'deduction', 0, 70);

SET FOREIGN_KEY_CHECKS = 1;

-- ─────────────────────────────────────────────────────────────
-- FIN DE MIGRACIÓN
-- Tablas nuevas: payroll_concept_types, employee_concepts
-- ─────────────────────────────────────────────────────────────