-- ============================================================
-- KopierSFlow ERP — 004_inventory_wms.sql
-- Importar CUARTO en phpMyAdmin
-- ============================================================

CREATE TABLE product_categories (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id  BIGINT UNSIGNED NOT NULL,
    name        VARCHAR(150)   NOT NULL,
    parent_id   BIGINT UNSIGNED NULL,
    type        ENUM('dry','refrigerated','frozen','other') DEFAULT 'dry',
    description TEXT,
    is_active   TINYINT(1) DEFAULT 1,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id)  REFERENCES product_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id          BIGINT UNSIGNED NOT NULL,
    sku                 VARCHAR(60)    NOT NULL,
    upc                 VARCHAR(30),
    name                VARCHAR(250)   NOT NULL,
    description         TEXT,
    category_id         BIGINT UNSIGNED,
    storage_type        ENUM('dry','refrigerated','frozen') DEFAULT 'dry',
    unit_of_measure     VARCHAR(20)    DEFAULT 'EA',
    case_pack           TINYINT UNSIGNED DEFAULT 1,
    weight_lbs          DECIMAL(10,4),
    volume_cf           DECIMAL(10,4),
    cost                DECIMAL(14,4)  DEFAULT 0,
    price               DECIMAL(14,4)  DEFAULT 0,
    min_margin_pct      DECIMAL(6,4)   DEFAULT 0,
    reorder_point       DECIMAL(14,4)  DEFAULT 0,
    reorder_qty         DECIMAL(14,4)  DEFAULT 0,
    shelf_life_days     SMALLINT       DEFAULT 0,
    track_lots          TINYINT(1)     DEFAULT 1,
    track_expiration    TINYINT(1)     DEFAULT 0,
    track_temperature   TINYINT(1)     DEFAULT 0,
    min_temp_f          DECIMAL(6,2),
    max_temp_f          DECIMAL(6,2),
    fda_product_code    VARCHAR(30),
    country_of_origin   CHAR(3),
    vendor_id           BIGINT UNSIGNED,
    is_active           TINYINT(1)     DEFAULT 1,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)  REFERENCES companies(id)           ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES product_categories(id)  ON DELETE SET NULL,
    UNIQUE KEY uq_company_sku (company_id, sku),
    INDEX idx_upc  (upc),
    INDEX idx_name (company_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE warehouses (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id       BIGINT UNSIGNED NOT NULL,
    code             VARCHAR(20)    NOT NULL,
    name             VARCHAR(150)   NOT NULL,
    address          TEXT,
    city             VARCHAR(100),
    state            CHAR(2),
    zip              VARCHAR(10),
    has_dry          TINYINT(1) DEFAULT 1,
    has_refrigerated TINYINT(1) DEFAULT 0,
    has_frozen       TINYINT(1) DEFAULT 0,
    is_active        TINYINT(1) DEFAULT 1,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY uq_company_code (company_id, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE warehouse_locations (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    company_id   BIGINT UNSIGNED NOT NULL,
    code         VARCHAR(30)    NOT NULL,
    zone         VARCHAR(20),
    aisle        VARCHAR(10),
    rack         VARCHAR(10),
    shelf        VARCHAR(10),
    bin          VARCHAR(10),
    type         ENUM('storage','receiving','staging','shipping','quarantine') DEFAULT 'storage',
    storage_type ENUM('dry','refrigerated','frozen') DEFAULT 'dry',
    is_active    TINYINT(1) DEFAULT 1,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
    UNIQUE KEY uq_wh_code (warehouse_id, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lots (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id        BIGINT UNSIGNED NOT NULL,
    product_id        BIGINT UNSIGNED NOT NULL,
    lot_number        VARCHAR(100)   NOT NULL,
    manufacture_date  DATE,
    expiration_date   DATE,
    received_date     DATE,
    vendor_id         BIGINT UNSIGNED,
    vendor_lot        VARCHAR(100),
    country_of_origin CHAR(3),
    status            ENUM('active','quarantine','recalled','expired','consumed') DEFAULT 'active',
    notes             TEXT,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    UNIQUE KEY uq_company_product_lot (company_id, product_id, lot_number),
    INDEX idx_expiration (product_id, expiration_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE inventory_balances (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id    BIGINT UNSIGNED NOT NULL,
    warehouse_id  BIGINT UNSIGNED NOT NULL,
    location_id   BIGINT UNSIGNED,
    product_id    BIGINT UNSIGNED NOT NULL,
    lot_id        BIGINT UNSIGNED,
    qty_on_hand   DECIMAL(14,4)  DEFAULT 0,
    qty_reserved  DECIMAL(14,4)  DEFAULT 0,
    average_cost  DECIMAL(14,4)  DEFAULT 0,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (product_id)  REFERENCES products(id),
    FOREIGN KEY (lot_id)      REFERENCES lots(id),
    UNIQUE KEY uq_balance (company_id, warehouse_id, product_id, lot_id),
    INDEX idx_product (company_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE inventory_movements (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id       BIGINT UNSIGNED NOT NULL,
    movement_date    DATE           NOT NULL,
    type             ENUM('receive','ship','transfer','adjust','return','cycle_count','write_off') NOT NULL,
    product_id       BIGINT UNSIGNED NOT NULL,
    lot_id           BIGINT UNSIGNED,
    warehouse_id     BIGINT UNSIGNED NOT NULL,
    location_id      BIGINT UNSIGNED,
    to_warehouse_id  BIGINT UNSIGNED,
    to_location_id   BIGINT UNSIGNED,
    quantity         DECIMAL(14,4)  NOT NULL  COMMENT 'Positivo=entrada, Negativo=salida',
    unit_cost        DECIMAL(14,4)  DEFAULT 0,
    total_cost       DECIMAL(18,4)  DEFAULT 0,
    reference_type   VARCHAR(60),
    reference_id     BIGINT UNSIGNED,
    notes            TEXT,
    created_by       BIGINT UNSIGNED,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    journal_entry_id BIGINT UNSIGNED,
    FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE CASCADE,
    FOREIGN KEY (product_id)  REFERENCES products(id),
    INDEX idx_product_date (company_id, product_id, movement_date),
    INDEX idx_reference    (reference_type, reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_transfers (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    transfer_number VARCHAR(30)    NOT NULL,
    from_warehouse  BIGINT UNSIGNED NOT NULL,
    to_warehouse    BIGINT UNSIGNED NOT NULL,
    transfer_date   DATE           NOT NULL,
    status          ENUM('draft','in_transit','received','cancelled') DEFAULT 'draft',
    notes           TEXT,
    created_by      BIGINT UNSIGNED,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cycle_counts (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id   BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    count_date   DATE           NOT NULL,
    status       ENUM('planned','in_progress','completed','posted') DEFAULT 'planned',
    counted_by   BIGINT UNSIGNED,
    approved_by  BIGINT UNSIGNED,
    notes        TEXT,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cycle_count_lines (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cycle_count_id BIGINT UNSIGNED NOT NULL,
    product_id     BIGINT UNSIGNED NOT NULL,
    lot_id         BIGINT UNSIGNED,
    location_id    BIGINT UNSIGNED,
    system_qty     DECIMAL(14,4)  DEFAULT 0,
    counted_qty    DECIMAL(14,4),
    unit_cost      DECIMAL(14,4),
    FOREIGN KEY (cycle_count_id) REFERENCES cycle_counts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE picking_batches (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id      BIGINT UNSIGNED NOT NULL,
    batch_number    VARCHAR(30)    NOT NULL,
    warehouse_id    BIGINT UNSIGNED NOT NULL,
    status          ENUM('pending','in_progress','completed','cancelled') DEFAULT 'pending',
    picker_id       BIGINT UNSIGNED,
    sales_order_ids JSON,
    started_at      TIMESTAMP NULL,
    completed_at    TIMESTAMP NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cold_chain_logs (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id   BIGINT UNSIGNED NOT NULL,
    entity_type  ENUM('warehouse','vehicle','shipment','delivery') NOT NULL,
    entity_id    BIGINT UNSIGNED NOT NULL,
    lot_id       BIGINT UNSIGNED,
    recorded_at  DATETIME       NOT NULL,
    temp_f       DECIMAL(6,2),
    humidity_pct DECIMAL(5,2),
    device_id    VARCHAR(100),
    is_alert     TINYINT(1) DEFAULT 0,
    alert_reason VARCHAR(255),
    INDEX idx_entity (entity_type, entity_id, recorded_at),
    INDEX idx_alert  (company_id, is_alert, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE traceability_events (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id     BIGINT UNSIGNED NOT NULL,
    event_type     ENUM('receive','transform','ship','hold','release','dispose') NOT NULL,
    event_date     DATETIME       NOT NULL,
    lot_id         BIGINT UNSIGNED NOT NULL,
    product_id     BIGINT UNSIGNED NOT NULL,
    quantity       DECIMAL(14,4),
    location_desc  VARCHAR(255),
    reference_type VARCHAR(60),
    reference_id   BIGINT UNSIGNED,
    performed_by   BIGINT UNSIGNED,
    notes          TEXT,
    INDEX idx_lot     (lot_id, event_date),
    INDEX idx_product (company_id, product_id, event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE recall_events (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id    BIGINT UNSIGNED NOT NULL,
    recall_number VARCHAR(60)    NOT NULL,
    initiated_at  DATETIME       NOT NULL,
    product_id    BIGINT UNSIGNED NOT NULL,
    reason        TEXT           NOT NULL,
    fda_recall_id VARCHAR(100),
    class         ENUM('I','II','III'),
    status        ENUM('active','closed') DEFAULT 'active',
    scope         TEXT,
    action_taken  TEXT,
    closed_at     TIMESTAMP NULL,
    created_by    BIGINT UNSIGNED,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE recall_items (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    recall_event_id BIGINT UNSIGNED NOT NULL,
    lot_id          BIGINT UNSIGNED NOT NULL,
    qty_affected    DECIMAL(14,4),
    qty_recovered   DECIMAL(14,4)  DEFAULT 0,
    customer_id     BIGINT UNSIGNED,
    invoice_id      BIGINT UNSIGNED,
    status          ENUM('outstanding','recovered','disposed') DEFAULT 'outstanding',
    notes           TEXT,
    FOREIGN KEY (recall_event_id) REFERENCES recall_events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;