-- =====================================================
-- Fortnight Stock Management - Direct SQL Migration
-- =====================================================
-- Run this file directly in phpMyAdmin or MySQL command line
-- This will create/fix all required tables and columns
-- =====================================================

-- Step 1: Create fortnight_periods table
CREATE TABLE IF NOT EXISTS `fortnight_periods` (
  `period_id` int(11) NOT NULL AUTO_INCREMENT,
  `period_name` varchar(100) DEFAULT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `month` int(2) NOT NULL,
  `year` int(4) NOT NULL,
  `site_id` int(11) NOT NULL DEFAULT '0',
  `is_locked` tinyint(1) DEFAULT '0',
  `created_by` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`period_id`),
  UNIQUE KEY `unique_period_site` (`start_date`, `end_date`, `site_id`),
  KEY `site_month_year` (`site_id`, `month`, `year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 2: Rename from_date/to_date to start_date/end_date if they exist
-- (Uncomment these lines if you have from_date/to_date columns)
-- ALTER TABLE `fortnight_periods` CHANGE COLUMN `from_date` `start_date` date NOT NULL;
-- ALTER TABLE `fortnight_periods` CHANGE COLUMN `to_date` `end_date` date NOT NULL;

-- Step 3: Add missing columns to fortnight_periods (run only if needed)
-- Check if column exists before running:
-- ALTER TABLE `fortnight_periods` ADD COLUMN `period_name` varchar(100) DEFAULT NULL AFTER `period_id`;
-- ALTER TABLE `fortnight_periods` ADD COLUMN `start_date` date NOT NULL AFTER `period_name`;
-- ALTER TABLE `fortnight_periods` ADD COLUMN `end_date` date NOT NULL AFTER `start_date`;
-- ALTER TABLE `fortnight_periods` ADD COLUMN `month` int(2) NOT NULL AFTER `end_date`;
-- ALTER TABLE `fortnight_periods` ADD COLUMN `year` int(4) NOT NULL AFTER `month`;
-- ALTER TABLE `fortnight_periods` ADD COLUMN `site_id` int(11) NOT NULL DEFAULT '0' AFTER `year`;
-- ALTER TABLE `fortnight_periods` ADD COLUMN `is_locked` tinyint(1) DEFAULT '0' AFTER `site_id`;
-- ALTER TABLE `fortnight_periods` ADD COLUMN `created_by` int(11) DEFAULT '0' AFTER `is_locked`;
-- ALTER TABLE `fortnight_periods` ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `created_by`;
-- ALTER TABLE `fortnight_periods` ADD COLUMN `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- Step 4: Create fortnight_inventory table
CREATE TABLE IF NOT EXISTS `fortnight_inventory` (
  `inventory_id` int(11) NOT NULL AUTO_INCREMENT,
  `period_id` int(11) NOT NULL,
  `sr_no` int(11) DEFAULT NULL,
  `material_hsn_code` varchar(50) DEFAULT NULL,
  `material_description` varchar(255) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `net_rate` decimal(10,2) DEFAULT '0.00',
  `opening_stock_qty` decimal(10,3) DEFAULT '0.000',
  `opening_stock_value` decimal(15,2) DEFAULT '0.00',
  `purchase_qty` decimal(10,3) DEFAULT '0.000',
  `purchase_value` decimal(15,2) DEFAULT '0.00',
  `consumption_qty` decimal(10,3) DEFAULT '0.000',
  `consumption_value` decimal(15,2) DEFAULT '0.00',
  `closing_stock_qty` decimal(10,3) DEFAULT '0.000',
  `closing_stock_value` decimal(15,2) DEFAULT '0.00',
  `supplier_name` varchar(255) DEFAULT NULL,
  `bill_challan_no` varchar(100) DEFAULT NULL,
  `site_id` int(11) NOT NULL DEFAULT '0',
  `created_by` int(11) DEFAULT '0',
  `updated_by` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  KEY `period_id` (`period_id`),
  KEY `site_id` (`site_id`),
  CONSTRAINT `fk_fi_period` FOREIGN KEY (`period_id`) REFERENCES `fortnight_periods` (`period_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 5: Add missing columns to fortnight_inventory (run only if needed)
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `sr_no` int(11) DEFAULT NULL AFTER `period_id`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `material_hsn_code` varchar(50) DEFAULT NULL AFTER `sr_no`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `material_description` varchar(255) NOT NULL DEFAULT '' AFTER `material_hsn_code`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `category` varchar(100) DEFAULT NULL AFTER `material_description`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `net_rate` decimal(10,2) DEFAULT '0.00' AFTER `category`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `opening_stock_qty` decimal(10,3) DEFAULT '0.000' AFTER `net_rate`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `opening_stock_value` decimal(15,2) DEFAULT '0.00' AFTER `opening_stock_qty`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `purchase_qty` decimal(10,3) DEFAULT '0.000' AFTER `opening_stock_value`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `purchase_value` decimal(15,2) DEFAULT '0.00' AFTER `purchase_qty`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `consumption_qty` decimal(10,3) DEFAULT '0.000' AFTER `purchase_value`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `consumption_value` decimal(15,2) DEFAULT '0.00' AFTER `consumption_qty`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `closing_stock_qty` decimal(10,3) DEFAULT '0.000' AFTER `consumption_value`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `closing_stock_value` decimal(15,2) DEFAULT '0.00' AFTER `closing_stock_qty`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `supplier_name` varchar(255) DEFAULT NULL AFTER `closing_stock_value`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `bill_challan_no` varchar(100) DEFAULT NULL AFTER `supplier_name`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `site_id` int(11) NOT NULL DEFAULT '0' AFTER `bill_challan_no`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `created_by` int(11) DEFAULT '0' AFTER `site_id`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `updated_by` int(11) DEFAULT '0' AFTER `created_by`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `updated_by`;
-- ALTER TABLE `fortnight_inventory` ADD COLUMN `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- Step 6: Create item_master table
CREATE TABLE IF NOT EXISTS `item_master` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `material_hsn_code` varchar(50) DEFAULT NULL,
  `material_name` varchar(255) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `rate` decimal(10,2) DEFAULT '0.00',
  `site_id` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `display_order` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`item_id`),
  KEY `site_id` (`site_id`),
  KEY `material_hsn_code` (`material_hsn_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 7: Create supplier_master table
CREATE TABLE IF NOT EXISTS `supplier_master` (
  `supplier_id` int(11) NOT NULL AUTO_INCREMENT,
  `supplier_code` varchar(50) DEFAULT NULL,
  `supplier_name` varchar(255) NOT NULL,
  `contact` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `site_id` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`supplier_id`),
  KEY `site_id` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migration Complete!
SELECT 'Fortnight tables created successfully!' AS status;
