USE BeezifiIOM;

ALTER TABLE products
    ADD COLUMN IF NOT EXISTS tracking_type ENUM('none', 'batch', 'serial') NOT NULL DEFAULT 'none' AFTER is_active;

CREATE TABLE IF NOT EXISTS batches (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id       INT UNSIGNED NOT NULL,
    product_id      INT UNSIGNED NOT NULL,
    batch_number    VARCHAR(100) NOT NULL,
    lot_number      VARCHAR(100) NULL,
    manufacture_date DATE NULL,
    expiry_date     DATE NULL,
    quantity        DECIMAL(10,3) NOT NULL DEFAULT 0,
    notes           TEXT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    INDEX idx_product (product_id),
    INDEX idx_expiry (expiry_date),
    UNIQUE KEY uq_tenant_product_batch (tenant_id, product_id, batch_number),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS serial_numbers (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id         INT UNSIGNED NOT NULL,
    product_id        INT UNSIGNED NOT NULL,
    serial_number     VARCHAR(255) NOT NULL,
    warehouse_id      INT UNSIGNED NULL,
    status            ENUM('in_stock', 'reserved', 'sold', 'returned', 'damaged') DEFAULT 'in_stock',
    order_id          INT UNSIGNED NULL,
    purchase_order_id INT UNSIGNED NULL,
    notes             TEXT NULL,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant (tenant_id),
    INDEX idx_product (product_id),
    INDEX idx_status (status),
    UNIQUE KEY uq_tenant_serial (tenant_id, serial_number),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

ALTER TABLE inventory_movements
    ADD COLUMN IF NOT EXISTS batch_id  INT UNSIGNED NULL AFTER reference_id,
    ADD COLUMN IF NOT EXISTS serial_id INT UNSIGNED NULL AFTER batch_id,
    ADD INDEX IF NOT EXISTS idx_batch  (batch_id),
    ADD INDEX IF NOT EXISTS idx_serial (serial_id);

CREATE TABLE IF NOT EXISTS batch_inventory (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id    INT UNSIGNED NOT NULL,
    batch_id     INT UNSIGNED NOT NULL,
    warehouse_id INT UNSIGNED NOT NULL,
    quantity     DECIMAL(10,3) NOT NULL DEFAULT 0,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_batch_warehouse (batch_id, warehouse_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (batch_id) REFERENCES batches(id) ON DELETE CASCADE
) ENGINE=InnoDB;
