-- ============================================================
-- quoERP — Módulo de Ventas y Facturación
-- Migración: 003_sales_module.sql
-- Versión: 1.1.0 | Fecha: 2026-06-04
-- ============================================================
-- Requiere: 001_create_all_tables.sql
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────
-- AJUSTE 1: sale_order_lines — product_id nullable + campos nuevos
-- Permite líneas libres (sin producto de catálogo)
-- ─────────────────────────────────────────────────────────────
ALTER TABLE sale_order_lines
    MODIFY COLUMN product_id INT UNSIGNED DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS description  VARCHAR(255) DEFAULT NULL AFTER product_id,
    ADD COLUMN IF NOT EXISTS product_name VARCHAR(200) DEFAULT NULL AFTER description;


-- ─────────────────────────────────────────────────────────────
-- AJUSTE 2: sale_orders — campos adicionales
-- ─────────────────────────────────────────────────────────────
ALTER TABLE sale_orders
    ADD COLUMN IF NOT EXISTS quote_id       INT UNSIGNED DEFAULT NULL AFTER customer_id,
    ADD COLUMN IF NOT EXISTS delivery_date  DATE         DEFAULT NULL AFTER due_date,
    ADD COLUMN IF NOT EXISTS terms          TEXT         DEFAULT NULL AFTER notes;


-- ─────────────────────────────────────────────────────────────
-- TABLA 1: sale_quotes — Cotizaciones
-- Flujo: borrador → enviada → aceptada/rechazada → convertida
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sale_quotes (
    id            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id    INT UNSIGNED    NOT NULL,
    office_id     INT UNSIGNED    NOT NULL,
    customer_id   INT UNSIGNED    DEFAULT NULL  COMMENT 'Puede ser nulo en cotizaciones a prospectos',
    number        VARCHAR(20)     NOT NULL      COMMENT 'Formato: COT-YYYY-NNNNN',
    date          DATE            NOT NULL,
    expiry_date   DATE            DEFAULT NULL  COMMENT 'Fecha de vencimiento de la oferta',
    subject       VARCHAR(255)    DEFAULT NULL  COMMENT 'Asunto / descripción corta',
    status        ENUM('borrador','enviada','aceptada','rechazada','vencida','convertida')
                                  NOT NULL DEFAULT 'borrador',
    notes         TEXT            DEFAULT NULL  COMMENT 'Notas internas',
    terms         TEXT            DEFAULT NULL  COMMENT 'Términos y condiciones para el cliente',
    subtotal      DECIMAL(12,2)   NOT NULL DEFAULT 0,
    discount      DECIMAL(12,2)   NOT NULL DEFAULT 0,
    tax           DECIMAL(12,2)   NOT NULL DEFAULT 0,
    total         DECIMAL(12,2)   NOT NULL DEFAULT 0,
    created_by    INT UNSIGNED    DEFAULT NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_sq_number (company_id, number),
    INDEX idx_company  (company_id),
    INDEX idx_customer (customer_id),
    INDEX idx_status   (status),
    INDEX idx_date     (date),

    CONSTRAINT fk_sq_company  FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE RESTRICT,
    CONSTRAINT fk_sq_office   FOREIGN KEY (office_id)   REFERENCES offices(id)    ON DELETE RESTRICT,
    CONSTRAINT fk_sq_customer FOREIGN KEY (customer_id) REFERENCES customers(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Cotizaciones / proformas de venta';


-- ─────────────────────────────────────────────────────────────
-- TABLA 2: sale_quote_lines — Líneas de cotización
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sale_quote_lines (
    id            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    quote_id      INT UNSIGNED     NOT NULL,
    sort_order    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    description   VARCHAR(255)     NOT NULL  COMMENT 'Descripción del ítem (libre)',
    qty           DECIMAL(12,4)    NOT NULL DEFAULT 1,
    unit_price    DECIMAL(12,4)    NOT NULL DEFAULT 0,
    discount_pct  DECIMAL(5,2)     NOT NULL DEFAULT 0,
    tax_rate      DECIMAL(5,2)     NOT NULL DEFAULT 12.00  COMMENT 'IVA Ecuador',
    subtotal      DECIMAL(12,2)    NOT NULL DEFAULT 0,

    PRIMARY KEY (id),
    INDEX idx_quote (quote_id),

    CONSTRAINT fk_sql_quote FOREIGN KEY (quote_id) REFERENCES sale_quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Líneas de detalle de cada cotización';


-- ─────────────────────────────────────────────────────────────
-- TABLA 3: sale_invoices — Facturas / comprobantes de venta
-- Los pagos se registran en sale_invoice_payments (múltiples por factura).
-- paid_amount y payment_status son campos denormalizados que se recalculan
-- automáticamente cada vez que se agrega o anula un pago.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sale_invoices (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    office_id           INT UNSIGNED    NOT NULL,
    order_id            INT UNSIGNED    DEFAULT NULL  COMMENT 'Orden de venta origen (opcional)',
    customer_id         INT UNSIGNED    DEFAULT NULL,
    customer_name_snap  VARCHAR(200)    DEFAULT NULL  COMMENT 'Snapshot del nombre del cliente',
    number              VARCHAR(20)     NOT NULL      COMMENT 'Formato: FAC-YYYY-NNNNN',
    date                DATE            NOT NULL,
    due_date            DATE            DEFAULT NULL,
    -- Estado de cobro (recalculado automáticamente desde sale_invoice_payments)
    payment_status      ENUM('pendiente','parcial','pagada','anulada')
                                        NOT NULL DEFAULT 'pendiente',
    subtotal            DECIMAL(12,2)   NOT NULL DEFAULT 0,
    discount            DECIMAL(12,2)   NOT NULL DEFAULT 0,
    tax                 DECIMAL(12,2)   NOT NULL DEFAULT 0,
    total               DECIMAL(12,2)   NOT NULL DEFAULT 0,
    -- Suma denormalizada de pagos activos (se actualiza con cada pago/anulación)
    paid_amount         DECIMAL(12,2)   NOT NULL DEFAULT 0,
    notes               TEXT            DEFAULT NULL,
    pdf_path            VARCHAR(255)    DEFAULT NULL,
    created_by          INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_si_number (company_id, number),
    INDEX idx_company  (company_id),
    INDEX idx_order    (order_id),
    INDEX idx_customer (customer_id),
    INDEX idx_status   (payment_status),
    INDEX idx_date     (date),

    CONSTRAINT fk_si_company  FOREIGN KEY (company_id)  REFERENCES companies(id)   ON DELETE RESTRICT,
    CONSTRAINT fk_si_office   FOREIGN KEY (office_id)   REFERENCES offices(id)     ON DELETE RESTRICT,
    CONSTRAINT fk_si_order    FOREIGN KEY (order_id)    REFERENCES sale_orders(id)  ON DELETE SET NULL,
    CONSTRAINT fk_si_customer FOREIGN KEY (customer_id) REFERENCES customers(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Facturas/comprobantes de venta. Los pagos se gestionan en sale_invoice_payments.';


-- ─────────────────────────────────────────────────────────────
-- TABLA 4: sale_invoice_lines — Líneas de factura
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sale_invoice_lines (
    id            INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    invoice_id    INT UNSIGNED     NOT NULL,
    sort_order    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    description   VARCHAR(255)     NOT NULL,
    qty           DECIMAL(12,4)    NOT NULL DEFAULT 1,
    unit_price    DECIMAL(12,4)    NOT NULL DEFAULT 0,
    discount_pct  DECIMAL(5,2)     NOT NULL DEFAULT 0,
    tax_rate      DECIMAL(5,2)     NOT NULL DEFAULT 12.00,
    subtotal      DECIMAL(12,2)    NOT NULL DEFAULT 0,

    PRIMARY KEY (id),
    INDEX idx_invoice (invoice_id),

    CONSTRAINT fk_sil_invoice FOREIGN KEY (invoice_id) REFERENCES sale_invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Líneas de detalle de cada factura';


-- ─────────────────────────────────────────────────────────────
-- TABLA 5: sale_invoice_payments — Pagos por factura
-- Una factura puede tener múltiples pagos con distintos métodos.
-- Cada pago puede llevar nota/referencia y un archivo comprobante.
-- Los pagos pueden anularse individualmente dejando observación.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sale_invoice_payments (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    invoice_id          INT UNSIGNED    NOT NULL,
    method              ENUM('efectivo','transferencia','cheque','tarjeta','otro')
                                        NOT NULL DEFAULT 'efectivo',
    amount              DECIMAL(12,2)   NOT NULL,
    payment_date        DATE            NOT NULL,
    -- Referencia / nota: N° cheque, N° transferencia, descripción, etc.
    note                TEXT            DEFAULT NULL,
    -- Comprobante adjunto (no obligatorio)
    attachment_path     VARCHAR(255)    DEFAULT NULL  COMMENT 'Ruta en public/uploads/payments/',
    attachment_name     VARCHAR(255)    DEFAULT NULL  COMMENT 'Nombre original del archivo',
    attachment_size     INT UNSIGNED    DEFAULT NULL  COMMENT 'Tamaño en bytes',
    -- Estado del pago
    status              ENUM('active','voided') NOT NULL DEFAULT 'active',
    void_reason         TEXT            DEFAULT NULL  COMMENT 'Motivo de anulación',
    voided_at           DATETIME        DEFAULT NULL,
    voided_by           INT UNSIGNED    DEFAULT NULL,
    created_by          INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_invoice (invoice_id),
    INDEX idx_status  (status),
    INDEX idx_date    (payment_date),

    CONSTRAINT fk_sip_invoice   FOREIGN KEY (invoice_id) REFERENCES sale_invoices(id) ON DELETE CASCADE,
    CONSTRAINT fk_sip_voided_by FOREIGN KEY (voided_by)  REFERENCES users(id)         ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Pagos individuales de facturas. Soporta múltiples formas de pago, adjuntos y anulación con observación.';


SET FOREIGN_KEY_CHECKS = 1;

-- ─────────────────────────────────────────────────────────────
-- FIN DE MIGRACIÓN
-- Tablas nuevas: 5
--   sale_quotes, sale_quote_lines,
--   sale_invoices, sale_invoice_lines,
--   sale_invoice_payments
-- ALTER TABLE: sale_order_lines (product_id nullable + description + product_name)
-- ALTER TABLE: sale_orders (quote_id + delivery_date + terms)
-- ─────────────────────────────────────────────────────────────
