-- Migration: Add employees table and update stock_transactions
-- Version: 2.0
-- Date: 2024

-- =====================================================
-- STEP 1: Create employees table
-- =====================================================
CREATE TABLE IF NOT EXISTS `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `employee_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `department` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_number` (`employee_number`),
  KEY `idx_employee_name` (`name`),
  KEY `idx_employee_department` (`department`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- STEP 2: Add employee_id and assignment_type to stock_transactions
-- Run this single command to add all columns, index, and constraint
-- =====================================================

ALTER TABLE `stock_transactions` 
ADD COLUMN `employee_id` int DEFAULT NULL AFTER `department_id`,
ADD COLUMN `assignment_type` enum('department','employee') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'department' AFTER `employee_id`,
ADD KEY `idx_employee_id` (`employee_id`),
ADD CONSTRAINT `fk_transactions_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL;
