-- =============================================================================
-- XPERT Billing - Esquema inicial (esqueleto)
-- Motor de tarifario/facturacion para operaciones de deposito, pensado
-- para conectarse a uno o mas WMS externos (ej. xpert-3pl) via un
-- endpoint de exportacion de solo lectura - no comparte base de datos
-- con ningun WMS. Ver README.md para el diseño completo.
-- =============================================================================

-- ── 1. USUARIOS Y ROLES (personal de quien opera Facturación) ──────────────
CREATE TABLE IF NOT EXISTS `roles` (
    `code`      VARCHAR(30) PRIMARY KEY,
    `name`      VARCHAR(100) NOT NULL,
    `is_system` TINYINT(1)   NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `roles` (`code`, `name`, `is_system`) VALUES
    ('admin',    'Administrador',        1),
    ('contador', 'Contador/Facturación', 0),
    ('viewer',   'Solo Lectura',         0)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

CREATE TABLE IF NOT EXISTS `users` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username`       VARCHAR(50)  NOT NULL,
    `password_hash`  VARCHAR(255) NOT NULL,
    `full_name`      VARCHAR(150) NOT NULL,
    `email`          VARCHAR(150) NULL,
    `role`           VARCHAR(30)  NOT NULL,
    `is_active`      TINYINT(1)   NOT NULL DEFAULT 1,
    `last_login_at`  DATETIME     NULL,
    `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_users_username` (`username`),
    CONSTRAINT `fk_users_role` FOREIGN KEY (`role`) REFERENCES `roles`(`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 2. EMPRESAS CLIENTE (espejo local, ver CompanyRefRepository) ───────────
CREATE TABLE IF NOT EXISTS `companies_ref` (
    `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `external_system`     VARCHAR(30)  NOT NULL COMMENT 'Ej: XPERT3PL - de que WMS viene esta empresa',
    `external_company_id` VARCHAR(30)  NOT NULL COMMENT 'id de la empresa en el sistema de origen',
    `code`                VARCHAR(30)  NOT NULL,
    `name`                VARCHAR(150) NOT NULL,
    `is_active`           TINYINT(1)   NOT NULL DEFAULT 1,
    `synced_at`           DATETIME     NULL,
    `created_at`          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_companiesref_external` (`external_system`, `external_company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='No es la fuente de verdad - espejo minimo sincronizado desde el WMS de origen';

-- ── 3. TARIFARIO (que le cobramos a cada empresa) ──────────────────────────
CREATE TABLE IF NOT EXISTS `billing_rules` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`    INT UNSIGNED NOT NULL,
    `rule_type`     ENUM(
        'STORAGE_PER_PALLET_DAY','STORAGE_PER_M3_DAY','STORAGE_PER_BIN_DAY','STORAGE_PER_UNIT_DAY',
        'HANDLING_RECEIPT','HANDLING_PICK','HANDLING_PACK','HANDLING_DISPATCH','HANDLING_ORDER',
        'MINIMUM_MONTHLY','CUSTOM'
    ) NOT NULL,
    `unit_price`    DECIMAL(12,4) NOT NULL,
    `uom_basis`     VARCHAR(30)  NULL COMMENT 'Ej: pallet, m3, bin, unidad, pedido, mes',
    `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
    `valid_from`    DATE         NULL,
    `valid_to`      DATE         NULL,
    `notes`         VARCHAR(255) NULL,
    `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `idx_billingrules_company` (`company_id`),
    CONSTRAINT `fk_billingrules_company` FOREIGN KEY (`company_id`) REFERENCES `companies_ref`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Tarifas configurables por empresa - un contrato real es una combinacion de varias filas vigentes';

-- ── 4. CARGOS GENERADOS (calculados a partir de los eventos exportados) ───
CREATE TABLE IF NOT EXISTS `billing_events` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`        INT UNSIGNED NOT NULL,
    `billing_rule_id`   INT UNSIGNED NOT NULL,
    `event_type`        VARCHAR(50)  NOT NULL,
    `external_reference` VARCHAR(100) NULL COMMENT 'id/doc_number del evento de origen (WMS), para trazar/evitar duplicados',
    `quantity`          DECIMAL(15,4) NOT NULL,
    `unit_price`        DECIMAL(12,4) NOT NULL,
    `amount`            DECIMAL(14,4) NOT NULL,
    `event_date`        DATE         NOT NULL,
    `invoice_line_id`   INT UNSIGNED NULL COMMENT 'Se llena cuando este cargo ya quedo incluido en una factura emitida',
    `created_at`        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_billingevents_company` (`company_id`),
    KEY `idx_billingevents_date` (`event_date`),
    CONSTRAINT `fk_billingevents_company` FOREIGN KEY (`company_id`) REFERENCES `companies_ref`(`id`),
    CONSTRAINT `fk_billingevents_rule` FOREIGN KEY (`billing_rule_id`) REFERENCES `billing_rules`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Base para reportes de costeo y para armar facturas';

-- ── 5. FACTURAS (emision - todavia sin logica de Facturacion Electronica) ─
CREATE TABLE IF NOT EXISTS `invoices` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `company_id`     INT UNSIGNED NOT NULL,
    `invoice_number` VARCHAR(30)  NOT NULL,
    `period_from`    DATE         NOT NULL,
    `period_to`      DATE         NOT NULL,
    `status`         ENUM('DRAFT','ISSUED','PAID','CANCELLED') NOT NULL DEFAULT 'DRAFT',
    `currency`       VARCHAR(3)   NOT NULL DEFAULT 'PYG',
    `total_amount`   DECIMAL(14,4) NOT NULL DEFAULT 0,
    `issued_at`      DATETIME     NULL,
    `paid_at`        DATETIME     NULL,
    `notes`          TEXT         NULL,
    `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_by`     INT UNSIGNED NULL,
    UNIQUE KEY `uq_invoices_number` (`invoice_number`),
    KEY `idx_invoices_company` (`company_id`),
    CONSTRAINT `fk_invoices_company` FOREIGN KEY (`company_id`) REFERENCES `companies_ref`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `invoice_lines` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `invoice_id`      INT UNSIGNED NOT NULL,
    `description`     VARCHAR(255) NOT NULL,
    `quantity`        DECIMAL(15,4) NOT NULL,
    `unit_price`      DECIMAL(12,4) NOT NULL,
    `amount`          DECIMAL(14,4) NOT NULL,
    `created_at`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_invoicelines_invoice` (`invoice_id`),
    CONSTRAINT `fk_invoicelines_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `billing_events`
    ADD CONSTRAINT `fk_billingevents_invoiceline` FOREIGN KEY (`invoice_line_id`) REFERENCES `invoice_lines`(`id`);

-- ── 6. SECUENCIAS DE NUMERACION ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `doc_sequences` (
    `sequence_key`   VARCHAR(30) PRIMARY KEY,
    `prefix`         VARCHAR(10) NOT NULL DEFAULT '',
    `current_value`  INT UNSIGNED NOT NULL DEFAULT 0,
    `pad_length`     TINYINT UNSIGNED NOT NULL DEFAULT 8
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `doc_sequences` (`sequence_key`, `prefix`, `current_value`, `pad_length`) VALUES
    ('INVOICE', 'FACT-', 0, 8)
ON DUPLICATE KEY UPDATE `prefix` = VALUES(`prefix`);
