-- Migration: Create stock taking tables
-- Run this SQL script in your database to create the stock taking functionality

-- Stock Taking Sessions Table
CREATE TABLE IF NOT EXISTS `stock_takings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `reference` VARCHAR(50) NOT NULL,
  `mode` ENUM('category','item') NOT NULL DEFAULT 'item',
  `location_id` INT UNSIGNED NOT NULL,
  `status` ENUM('draft','completed') DEFAULT 'draft',
  `notes` TEXT,
  `user_id` INT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tenant_reference` (`tenant_id`,`reference`),
  KEY `tenant_id` (`tenant_id`),
  KEY `location_id` (`location_id`),
  KEY `status` (`status`),
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`location_id`) REFERENCES `locations`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Stock Taking Items Table
CREATE TABLE IF NOT EXISTS `stock_taking_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `stock_taking_id` INT UNSIGNED NOT NULL,
  `tenant_id` INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED NOT NULL,
  `category_id` INT UNSIGNED NULL,
  `current_stock` DECIMAL(15,3) DEFAULT 0,
  `counted_qty` DECIMAL(15,3) DEFAULT 0,
  `difference` DECIMAL(15,3) DEFAULT 0,
  `status` ENUM('shortage','over','equal') DEFAULT 'equal',
  `unit_cost` DECIMAL(15,2) DEFAULT 0,
  `total_amount` DECIMAL(15,2) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `stock_taking_id` (`stock_taking_id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `product_id` (`product_id`),
  KEY `category_id` (`category_id`),
  KEY `status` (`status`),
  FOREIGN KEY (`stock_taking_id`) REFERENCES `stock_takings`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
