-- ============================================================
-- quoERP — Esquema de base de datos completo
-- MySQL 5.7+ / 8.0 compatible (cPanel)
-- MENRICORP © 2026
-- ============================================================
-- Ejecutar en orden. Usar: menrico1_quoerp (o el nombre que elijas en cPanel)

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── EMPRESAS ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS companies (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(150) NOT NULL,
    code        VARCHAR(10)  NOT NULL UNIQUE,
    ruc         VARCHAR(13)  UNIQUE,
    legal_name  VARCHAR(200),
    address     TEXT,
    phone       VARCHAR(20),
    email       VARCHAR(100),
    website     VARCHAR(100),
    color       VARCHAR(7)   DEFAULT '#1B3A6B',
    logo        VARCHAR(255),
    -- Configuración por empresa
    chat_retention_days INT UNSIGNED DEFAULT 90,
    chat_view_mode      ENUM('comfortable','compact','kanban') DEFAULT 'comfortable',
    -- Configuración fiscal Ecuador
    sri_environment ENUM('pruebas','produccion') DEFAULT 'pruebas',
    sri_signature   VARCHAR(255),  -- path al certificado .p12
    iess_code       VARCHAR(20),
    status      ENUM('active','inactive') DEFAULT 'active',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── OFICINAS / SUCURSALES ─────────────────────────────────
CREATE TABLE IF NOT EXISTS offices (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  INT UNSIGNED NOT NULL,
    name        VARCHAR(150) NOT NULL,
    city        VARCHAR(80),
    address     TEXT,
    phone       VARCHAR(20),
    is_main     TINYINT(1) DEFAULT 0,
    status      ENUM('active','inactive') DEFAULT 'active',
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── ROLES ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS roles (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  INT UNSIGNED,  -- NULL = rol global del sistema
    slug        VARCHAR(50)  NOT NULL,
    label       VARCHAR(80)  NOT NULL,
    level       TINYINT UNSIGNED DEFAULT 20,  -- 20=user, 40=supervisor, 60=manager, 80=admin, 100=superadmin
    permissions JSON,  -- {"module": ["view","create","edit","delete"]}
    description TEXT,
    is_system   TINYINT(1) DEFAULT 0,  -- 1 = no editable
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── USUARIOS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id    INT UNSIGNED NOT NULL,
    office_id     INT UNSIGNED NOT NULL,
    role_id       INT UNSIGNED NOT NULL,
    first_name    VARCHAR(80)  NOT NULL,
    last_name     VARCHAR(80)  NOT NULL,
    email         VARCHAR(150) NOT NULL UNIQUE,
    phone         VARCHAR(20),
    password_hash VARCHAR(255) NOT NULL,
    avatar        VARCHAR(255),  -- path relativo en storage/uploads/avatars/
    presence      ENUM('online','away','busy','offline') DEFAULT 'offline',
    last_seen_at  DATETIME,
    last_login_at DATETIME,
    -- Módulos con acceso adicional (override del rol)
    module_access JSON,  -- ["sales","inventory"] o null (usa los del rol)
    status        ENUM('active','inactive','pending') DEFAULT 'active',
    email_verified_at DATETIME,
    reset_token   VARCHAR(64),
    reset_token_at DATETIME,
    created_by    INT UNSIGNED,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (office_id)  REFERENCES offices(id)   ON DELETE RESTRICT,
    FOREIGN KEY (role_id)    REFERENCES roles(id)      ON DELETE RESTRICT,
    INDEX idx_company (company_id),
    INDEX idx_email   (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CHAT: CONVERSACIONES ──────────────────────────────────
CREATE TABLE IF NOT EXISTS chat_conversations (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  INT UNSIGNED NOT NULL,
    type        ENUM('direct','group','queue') DEFAULT 'direct',
    name        VARCHAR(150),  -- para grupos y colas
    color       VARCHAR(7),
    queue_id    INT UNSIGNED,  -- si es de tipo queue
    created_by  INT UNSIGNED NOT NULL,
    is_pinned   TINYINT(1) DEFAULT 0,
    is_archived TINYINT(1) DEFAULT 0,
    last_msg_at DATETIME,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company (company_id),
    INDEX idx_last_msg (last_msg_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CHAT: PARTICIPANTES ───────────────────────────────────
CREATE TABLE IF NOT EXISTS chat_participants (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT UNSIGNED NOT NULL,
    user_id         INT UNSIGNED NOT NULL,
    last_read_at    DATETIME,
    is_admin        TINYINT(1) DEFAULT 0,
    joined_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_participant (conversation_id, user_id),
    FOREIGN KEY (conversation_id) REFERENCES chat_conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CHAT: MENSAJES ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS chat_messages (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT UNSIGNED NOT NULL,
    user_id         INT UNSIGNED NOT NULL,
    body            TEXT,
    type            ENUM('text','file','image','system','module_ref') DEFAULT 'text',
    -- Referencia a módulo
    module_name     VARCHAR(50),
    module_label    VARCHAR(150),
    module_url      VARCHAR(255),
    -- Adjunto
    attachment_path VARCHAR(255),
    attachment_name VARCHAR(255),
    attachment_size INT UNSIGNED,
    -- Hilo
    reply_to_id     INT UNSIGNED,
    -- Tags
    is_urgent       TINYINT(1) DEFAULT 0,
    -- Eliminado (soft delete)
    deleted_at      DATETIME,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES chat_conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_conv_time (conversation_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CHAT: REACCIONES ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS chat_reactions (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message_id INT UNSIGNED NOT NULL,
    user_id    INT UNSIGNED NOT NULL,
    emoji      VARCHAR(10) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_reaction (message_id, user_id, emoji),
    FOREIGN KEY (message_id) REFERENCES chat_messages(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)    REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CHAT: COLAS ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS chat_queues (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  INT UNSIGNED NOT NULL,
    name        VARCHAR(100) NOT NULL,
    description TEXT,
    color       VARCHAR(7),
    assigned_to INT UNSIGNED,  -- usuario asignado actual
    status      ENUM('open','in_progress','resolved','closed') DEFAULT 'open',
    priority    ENUM('low','normal','high','urgent') DEFAULT 'normal',
    created_by  INT UNSIGNED,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── NOTIFICACIONES ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS notifications (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    type       VARCHAR(50) NOT NULL,
    title      VARCHAR(200),
    body       TEXT,
    data       JSON,
    read_at    DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_unread (user_id, read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── VENTAS: CLIENTES ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS customers (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id    INT UNSIGNED NOT NULL,
    ruc_cedula    VARCHAR(13),
    name          VARCHAR(200) NOT NULL,
    trade_name    VARCHAR(200),
    email         VARCHAR(150),
    phone         VARCHAR(20),
    address       TEXT,
    city          VARCHAR(80),
    contact_name  VARCHAR(150),
    credit_limit  DECIMAL(12,2) DEFAULT 0,
    credit_days   TINYINT UNSIGNED DEFAULT 30,
    tax_exempt    TINYINT(1) DEFAULT 0,
    status        ENUM('active','inactive') DEFAULT 'active',
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── INVENTARIO: CATEGORÍAS ────────────────────────────────
CREATE TABLE IF NOT EXISTS product_categories (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    name       VARCHAR(100) NOT NULL,
    parent_id  INT UNSIGNED,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── INVENTARIO: PRODUCTOS ─────────────────────────────────
CREATE TABLE IF NOT EXISTS products (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     INT UNSIGNED NOT NULL,
    category_id    INT UNSIGNED,
    sku            VARCHAR(50),
    barcode        VARCHAR(50),
    name           VARCHAR(200) NOT NULL,
    description    TEXT,
    unit           VARCHAR(20) DEFAULT 'unidad',
    cost_price     DECIMAL(12,4) DEFAULT 0,
    sale_price     DECIMAL(12,4) DEFAULT 0,
    tax_rate       DECIMAL(5,2)  DEFAULT 12.00,  -- IVA Ecuador
    min_stock      INT UNSIGNED  DEFAULT 0,
    max_stock      INT UNSIGNED  DEFAULT 0,
    image          VARCHAR(255),
    is_active      TINYINT(1)   DEFAULT 1,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_sku (company_id, sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── INVENTARIO: BODEGAS ───────────────────────────────────
CREATE TABLE IF NOT EXISTS warehouses (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    office_id  INT UNSIGNED,
    name       VARCHAR(100) NOT NULL,
    address    TEXT,
    is_main    TINYINT(1) DEFAULT 0,
    status     ENUM('active','inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── INVENTARIO: STOCK POR BODEGA ──────────────────────────
CREATE TABLE IF NOT EXISTS inventory_stock (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id   INT UNSIGNED NOT NULL,
    warehouse_id INT UNSIGNED NOT NULL,
    quantity     DECIMAL(12,4) DEFAULT 0,
    reserved_qty DECIMAL(12,4) DEFAULT 0,
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_stock (product_id, warehouse_id),
    FOREIGN KEY (product_id)   REFERENCES products(id)   ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── INVENTARIO: MOVIMIENTOS ───────────────────────────────
CREATE TABLE IF NOT EXISTS inventory_movements (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     INT UNSIGNED NOT NULL,
    product_id     INT UNSIGNED NOT NULL,
    warehouse_id   INT UNSIGNED NOT NULL,
    type           ENUM('in','out','transfer','adjustment') NOT NULL,
    qty            DECIMAL(12,4) NOT NULL,
    qty_before     DECIMAL(12,4),
    qty_after      DECIMAL(12,4),
    unit_cost      DECIMAL(12,4),
    reference_type VARCHAR(50),  -- sale_order, purchase_order, etc.
    reference_id   INT UNSIGNED,
    notes          TEXT,
    user_id        INT UNSIGNED,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)   REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (product_id)   REFERENCES products(id)   ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
    INDEX idx_product_date (product_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── VENTAS: ÓRDENES ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS sale_orders (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id    INT UNSIGNED NOT NULL,
    office_id     INT UNSIGNED NOT NULL,
    customer_id   INT UNSIGNED NOT NULL,
    number        VARCHAR(20)  NOT NULL,
    date          DATE         NOT NULL,
    due_date      DATE,
    status        ENUM('draft','confirmed','processing','completed','cancelled') DEFAULT 'draft',
    subtotal      DECIMAL(12,2) DEFAULT 0,
    discount      DECIMAL(12,2) DEFAULT 0,
    tax           DECIMAL(12,2) DEFAULT 0,
    total         DECIMAL(12,2) DEFAULT 0,
    notes         TEXT,
    created_by    INT UNSIGNED,
    created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)  ON DELETE RESTRICT,
    INDEX idx_company_date (company_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── VENTAS: LÍNEAS DE ORDEN ───────────────────────────────
CREATE TABLE IF NOT EXISTS sale_order_lines (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id      INT UNSIGNED NOT NULL,
    product_id    INT UNSIGNED NOT NULL,
    warehouse_id  INT UNSIGNED,
    qty           DECIMAL(12,4) NOT NULL,
    unit_price    DECIMAL(12,4) NOT NULL,
    discount_pct  DECIMAL(5,2)  DEFAULT 0,
    tax_rate      DECIMAL(5,2)  DEFAULT 12,
    subtotal      DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES sale_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── COMPRAS: PROVEEDORES ──────────────────────────────────
CREATE TABLE IF NOT EXISTS suppliers (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id   INT UNSIGNED NOT NULL,
    ruc          VARCHAR(13),
    name         VARCHAR(200) NOT NULL,
    email        VARCHAR(150),
    phone        VARCHAR(20),
    address      TEXT,
    contact_name VARCHAR(150),
    payment_terms TINYINT UNSIGNED DEFAULT 30,
    status       ENUM('active','inactive') DEFAULT 'active',
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── COMPRAS: ÓRDENES ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS purchase_orders (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id   INT UNSIGNED NOT NULL,
    office_id    INT UNSIGNED NOT NULL,
    supplier_id  INT UNSIGNED NOT NULL,
    number       VARCHAR(20)  NOT NULL,
    date         DATE         NOT NULL,
    expected_date DATE,
    status       ENUM('draft','sent','partial','received','cancelled') DEFAULT 'draft',
    subtotal     DECIMAL(12,2) DEFAULT 0,
    tax          DECIMAL(12,2) DEFAULT 0,
    total        DECIMAL(12,2) DEFAULT 0,
    notes        TEXT,
    created_by   INT UNSIGNED,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)  ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── FINANZAS: PLAN DE CUENTAS ─────────────────────────────
CREATE TABLE IF NOT EXISTS chart_of_accounts (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    code       VARCHAR(20)  NOT NULL,
    name       VARCHAR(200) NOT NULL,
    type       ENUM('asset','liability','equity','income','expense') NOT NULL,
    parent_id  INT UNSIGNED,
    level      TINYINT UNSIGNED DEFAULT 1,
    is_detail  TINYINT(1) DEFAULT 1,
    balance    DECIMAL(15,2) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_code (company_id, code),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── FINANZAS: ASIENTOS CONTABLES ─────────────────────────
CREATE TABLE IF NOT EXISTS journal_entries (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id   INT UNSIGNED NOT NULL,
    number       VARCHAR(20),
    date         DATE NOT NULL,
    description  TEXT,
    type         VARCHAR(50),
    reference    VARCHAR(100),
    status       ENUM('draft','posted','cancelled') DEFAULT 'draft',
    created_by   INT UNSIGNED,
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── FINANZAS: LÍNEAS DE ASIENTO ───────────────────────────
CREATE TABLE IF NOT EXISTS journal_entry_lines (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entry_id    INT UNSIGNED NOT NULL,
    account_id  INT UNSIGNED NOT NULL,
    description VARCHAR(255),
    debit       DECIMAL(15,2) DEFAULT 0,
    credit      DECIMAL(15,2) DEFAULT 0,
    FOREIGN KEY (entry_id)  REFERENCES journal_entries(id) ON DELETE CASCADE,
    FOREIGN KEY (account_id)REFERENCES chart_of_accounts(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── RRHH: EMPLEADOS ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS employees (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      INT UNSIGNED NOT NULL,
    office_id       INT UNSIGNED,
    user_id         INT UNSIGNED,  -- vínculo con usuario del sistema si aplica
    cedula          VARCHAR(10)  NOT NULL,
    first_name      VARCHAR(80)  NOT NULL,
    last_name       VARCHAR(80)  NOT NULL,
    email           VARCHAR(150),
    phone           VARCHAR(20),
    address         TEXT,
    birth_date      DATE,
    gender          ENUM('M','F','O'),
    hire_date       DATE         NOT NULL,
    termination_date DATE,
    department      VARCHAR(100),
    position        VARCHAR(100),
    contract_type   ENUM('indefinido','plazo_fijo','obra_servicio','pasantia') DEFAULT 'indefinido',
    salary          DECIMAL(10,2) NOT NULL DEFAULT 0,
    iess_affiliate  TINYINT(1) DEFAULT 1,
    iess_number     VARCHAR(20),
    bank_name       VARCHAR(80),
    bank_account    VARCHAR(30),
    bank_type       ENUM('corriente','ahorros') DEFAULT 'ahorros',
    status          ENUM('active','inactive','terminated') DEFAULT 'active',
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── NÓMINA: PERÍODOS ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS payroll_periods (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  INT UNSIGNED NOT NULL,
    year        SMALLINT UNSIGNED NOT NULL,
    month       TINYINT UNSIGNED NOT NULL,
    label       VARCHAR(50),
    status      ENUM('draft','approved','paid') DEFAULT 'draft',
    total       DECIMAL(12,2) DEFAULT 0,
    created_by  INT UNSIGNED,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    approved_at DATETIME,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── NÓMINA: ROLES DE PAGO ────────────────────────────────
CREATE TABLE IF NOT EXISTS payroll_slips (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    period_id          INT UNSIGNED NOT NULL,
    employee_id        INT UNSIGNED NOT NULL,
    salary             DECIMAL(10,2) DEFAULT 0,
    -- Ingresos
    hours_extra        DECIMAL(5,2)  DEFAULT 0,
    val_hours_extra    DECIMAL(10,2) DEFAULT 0,
    commission         DECIMAL(10,2) DEFAULT 0,
    bonus              DECIMAL(10,2) DEFAULT 0,
    other_income       DECIMAL(10,2) DEFAULT 0,
    gross_salary       DECIMAL(10,2) DEFAULT 0,
    -- Deducciones
    iess_employee      DECIMAL(10,2) DEFAULT 0,  -- 9.45%
    iess_employer      DECIMAL(10,2) DEFAULT 0,  -- 12.15%
    income_tax         DECIMAL(10,2) DEFAULT 0,
    advance            DECIMAL(10,2) DEFAULT 0,
    other_deductions   DECIMAL(10,2) DEFAULT 0,
    -- Neto
    net_salary         DECIMAL(10,2) DEFAULT 0,
    -- Beneficios
    decimo_tercero     DECIMAL(10,2) DEFAULT 0,
    decimo_cuarto      DECIMAL(10,2) DEFAULT 0,
    fondos_reserva     DECIMAL(10,2) DEFAULT 0,
    vacaciones         DECIMAL(10,2) DEFAULT 0,
    status             ENUM('draft','approved','paid') DEFAULT 'draft',
    paid_at            DATETIME,
    FOREIGN KEY (period_id)   REFERENCES payroll_periods(id) ON DELETE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees(id)       ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CONFIGURACIÓN DE EMPRESA ─────────────────────────────
CREATE TABLE IF NOT EXISTS company_settings (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    key_name   VARCHAR(100) NOT NULL,
    value      TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_setting (company_id, key_name),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AUDITORÍA ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_logs (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED,
    user_id    INT UNSIGNED,
    action     VARCHAR(50)  NOT NULL,
    model      VARCHAR(100),
    model_id   INT UNSIGNED,
    old_values JSON,
    new_values JSON,
    ip         VARCHAR(45),
    user_agent VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_company_date (company_id, created_at),
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;