-- Migration: Create stock_transfers and stock_transfer_items tables
-- Date: 2026-02-06
-- Purpose: Track stock movements between shops

CREATE TABLE stock_transfers (
    id BIGINT NOT NULL AUTO_INCREMENT,
    reference_number VARCHAR(50) NOT NULL UNIQUE,
    from_shop_id BIGINT NOT NULL,
    to_shop_id BIGINT NOT NULL,
    transferred_by BIGINT NOT NULL,
    notes TEXT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_stock_transfers_reference (reference_number),
    INDEX idx_stock_transfers_from_shop (from_shop_id),
    INDEX idx_stock_transfers_to_shop (to_shop_id),
    CONSTRAINT fk_stock_transfers_from_shop FOREIGN KEY (from_shop_id) REFERENCES shops(id),
    CONSTRAINT fk_stock_transfers_to_shop FOREIGN KEY (to_shop_id) REFERENCES shops(id),
    CONSTRAINT fk_stock_transfers_user FOREIGN KEY (transferred_by) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE stock_transfer_items (
    id BIGINT NOT NULL AUTO_INCREMENT,
    transfer_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_stock_transfer_items_transfer (transfer_id),
    INDEX idx_stock_transfer_items_product (product_id),
    CONSTRAINT fk_stock_transfer_items_transfer FOREIGN KEY (transfer_id) REFERENCES stock_transfers(id) ON DELETE CASCADE,
    CONSTRAINT fk_stock_transfer_items_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;