-- Stock Transfers Header Table
CREATE TABLE `stock_transfers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `transfer_number` VARCHAR(50) NOT NULL UNIQUE,
    `source_branch_id` INT UNSIGNED NOT NULL,
    `destination_branch_id` INT UNSIGNED NOT NULL,
    `status` ENUM(
        'Pending',
        'In_Transit',
        'Completed',
        'Cancelled'
    ) DEFAULT 'Pending',
    `notes` TEXT,
    `created_by` INT UNSIGNED NOT NULL,
    `received_by` INT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `sent_at` DATETIME DEFAULT NULL,
    `received_at` DATETIME DEFAULT NULL,
    FOREIGN KEY (`source_branch_id`) REFERENCES `branches` (`id`),
    FOREIGN KEY (`destination_branch_id`) REFERENCES `branches` (`id`),
    FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
    FOREIGN KEY (`received_by`) REFERENCES `users` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Stock Transfer Items Table
CREATE TABLE `stock_transfer_items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `transfer_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `quantity_requested` INT NOT NULL,
    `quantity_sent` INT DEFAULT 0,
    `quantity_received` INT DEFAULT 0,
    FOREIGN KEY (`transfer_id`) REFERENCES `stock_transfers` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Index for faster queries
CREATE INDEX `idx_transfer_source` ON `stock_transfers` (`source_branch_id`);

CREATE INDEX `idx_transfer_dest` ON `stock_transfers` (`destination_branch_id`);

CREATE INDEX `idx_transfer_status` ON `stock_transfers` (`status`);