-- ============================================================
-- quoERP — Workflow de Aprobaciones
-- Migracion: 034_workflow.sql
-- ============================================================
-- Flujos de aprobacion configurables para compras, cotizaciones,
-- asientos manuales, ordenes de produccion y otros documentos.
-- Soporta: aprobacion secuencial, por rol, por usuario especifico,
-- con condiciones de monto y auditoria completa.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────
-- PLANTILLAS DE FLUJO — definen las reglas de aprobacion
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS workflow_templates (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    name            VARCHAR(150)    NOT NULL,
    document_type   ENUM(
        'purchase_order','sale_quote','sale_order',
        'credit_note','production_order','payroll_period',
        'journal_entry','fixed_asset','expense','contract'
    ) NOT NULL,
    -- Condicion de activacion
    condition_type  ENUM('always','amount_gt','amount_gte','amount_between')
                    NOT NULL DEFAULT 'always',
    condition_value1 DECIMAL(14,2)  DEFAULT NULL
        COMMENT 'Umbral principal (ej: monto > X)',
    condition_value2 DECIMAL(14,2)  DEFAULT NULL
        COMMENT 'Umbral superior para condicion between',
    -- Configuracion
    is_sequential   TINYINT(1)      NOT NULL DEFAULT 1
        COMMENT '1=los pasos se aprueban en orden, 0=cualquier aprobador puede actuar',
    is_active       TINYINT(1)      NOT NULL DEFAULT 1,
    description     TEXT            DEFAULT NULL,
    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_company (company_id),
    INDEX idx_doctype (document_type),
    INDEX idx_active  (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- PASOS DEL FLUJO — aprobadores por nivel
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS workflow_steps (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    template_id     INT UNSIGNED    NOT NULL,
    step_number     TINYINT UNSIGNED NOT NULL DEFAULT 1
        COMMENT 'Orden del paso (1=primero)',
    name            VARCHAR(100)    NOT NULL
        COMMENT 'Ej: Jefe de area, Gerencia, Directorio',
    approver_type   ENUM('user','role') NOT NULL DEFAULT 'role'
        COMMENT 'user=usuario especifico, role=cualquier usuario del rol',
    approver_user_id INT UNSIGNED   DEFAULT NULL,
    approver_role_slug VARCHAR(60)  DEFAULT NULL
        COMMENT 'slug del rol: admin, gerente, finanzas...',
    timeout_hours   SMALLINT UNSIGNED DEFAULT NULL
        COMMENT 'Horas antes de escalar o auto-aprobar',
    timeout_action  ENUM('escalate_next','auto_approve','auto_reject')
                    DEFAULT 'escalate_next',
    PRIMARY KEY (id),
    INDEX idx_template (template_id),
    INDEX idx_step     (step_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- INSTANCIAS — un documento esperando aprobacion
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS workflow_instances (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    template_id     INT UNSIGNED    NOT NULL,
    document_type   VARCHAR(50)     NOT NULL,
    document_id     INT UNSIGNED    NOT NULL,
    document_ref    VARCHAR(100)    DEFAULT NULL
        COMMENT 'Numero o etiqueta del documento (snapshot)',
    document_amount DECIMAL(14,2)   DEFAULT NULL
        COMMENT 'Monto del documento al momento de la solicitud',
    current_step    TINYINT UNSIGNED NOT NULL DEFAULT 1,
    status          ENUM('pending','approved','rejected','cancelled')
                    NOT NULL DEFAULT 'pending',
    requested_by    INT UNSIGNED    DEFAULT NULL,
    requester_name  VARCHAR(120)    DEFAULT NULL COMMENT 'Snapshot',
    requested_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at    DATETIME        DEFAULT NULL,
    completed_by    INT UNSIGNED    DEFAULT NULL,
    notes           TEXT            DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_doc (document_type, document_id, status),
    INDEX idx_company   (company_id),
    INDEX idx_status    (status),
    INDEX idx_doc       (document_type, document_id),
    INDEX idx_requested (requested_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- ACCIONES — historial completo de aprobaciones/rechazos
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS workflow_actions (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    instance_id     INT UNSIGNED    NOT NULL,
    step_id         INT UNSIGNED    NOT NULL,
    step_number     TINYINT UNSIGNED NOT NULL,
    step_name       VARCHAR(100)    NOT NULL COMMENT 'Snapshot',
    action          ENUM('approve','reject','request_info','delegate','auto_approve','submit')
                    NOT NULL,
    actor_id        INT UNSIGNED    DEFAULT NULL,
    actor_name      VARCHAR(120)    DEFAULT NULL COMMENT 'Snapshot',
    comment         TEXT            DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_instance (instance_id),
    INDEX idx_actor    (actor_id),
    INDEX idx_action   (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Columna de estado de aprobacion en documentos clave
-- Permite saber rapido si un doc esta pendiente de aprobar
-- ─────────────────────────────────────────────────────────────
ALTER TABLE purchase_orders
    ADD COLUMN IF NOT EXISTS workflow_status
        ENUM('none','pending','approved','rejected') NOT NULL DEFAULT 'none'
        COMMENT 'Estado del workflow de aprobacion',
    ADD COLUMN IF NOT EXISTS workflow_instance_id INT UNSIGNED DEFAULT NULL;

ALTER TABLE sale_quotes
    ADD COLUMN IF NOT EXISTS workflow_status
        ENUM('none','pending','approved','rejected') NOT NULL DEFAULT 'none',
    ADD COLUMN IF NOT EXISTS workflow_instance_id INT UNSIGNED DEFAULT NULL;

ALTER TABLE journal_entries
    ADD COLUMN IF NOT EXISTS workflow_status
        ENUM('none','pending','approved','rejected') NOT NULL DEFAULT 'none',
    ADD COLUMN IF NOT EXISTS workflow_instance_id INT UNSIGNED DEFAULT NULL;

ALTER TABLE production_orders
    ADD COLUMN IF NOT EXISTS workflow_status
        ENUM('none','pending','approved','rejected') NOT NULL DEFAULT 'none',
    ADD COLUMN IF NOT EXISTS workflow_instance_id INT UNSIGNED DEFAULT NULL;

SET FOREIGN_KEY_CHECKS = 1;

-- ─────────────────────────────────────────────────────────────
-- FIN DE MIGRACION
-- Tablas nuevas: workflow_templates, workflow_steps,
--                workflow_instances, workflow_actions
-- ALTER: purchase_orders, sale_quotes, journal_entries,
--        production_orders (workflow_status, workflow_instance_id)
-- ─────────────────────────────────────────────────────────────