-- ============================================
-- MIS REPORT COMPREHENSIVE TABLES
-- Based on Excel format with all sections
-- ============================================

-- MIS Report Master Table
CREATE TABLE IF NOT EXISTS `mis_reports` (
  `mis_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `report_month` int(11) NOT NULL,
  `report_year` int(11) NOT NULL,
  `entity` varchar(255),
  `segment` varchar(100),
  `location` varchar(100),
  `hm_manager_name` varchar(255),
  `site_accountant_name` varchar(255),
  `operation_head` varchar(255),
  `report_status` enum('draft','final','locked') DEFAULT 'draft',
  `is_locked` tinyint(1) DEFAULT 0,
  `locked_at` datetime,
  `locked_by` int(11),
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`mis_id`),
  UNIQUE KEY `unique_site_month_year` (`site_id`, `report_month`, `report_year`),
  KEY `fk_mis_site` (`site_id`),
  KEY `fk_mis_created_by` (`created_by`),
  KEY `fk_mis_updated_by` (`updated_by`),
  KEY `fk_mis_locked_by` (`locked_by`),
  CONSTRAINT `fk_mis_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_mis_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_mis_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_mis_locked_by` FOREIGN KEY (`locked_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS Sales Section
CREATE TABLE IF NOT EXISTS `mis_sales` (
  `sales_id` int(11) NOT NULL AUTO_INCREMENT,
  `mis_id` int(11) NOT NULL,
  `variable_sales` decimal(15,2) DEFAULT 0.00,
  `catering_sales_bill_to_company` decimal(15,2) DEFAULT 0.00,
  `laundry_sales_bill_to_company` decimal(15,2) DEFAULT 0.00,
  `catering_sales_cash` decimal(15,2) DEFAULT 0.00,
  `laundry_sales_cash` decimal(15,2) DEFAULT 0.00,
  `saloon_sale_cash` decimal(15,2) DEFAULT 0.00,
  `total_sales` decimal(15,2) DEFAULT 0.00,
  `lumsump` decimal(15,2) DEFAULT 0.00,
  `fixed_revenue_onetime` decimal(15,2) DEFAULT 0.00,
  `reimbursement` decimal(15,2) DEFAULT 0.00,
  `other_income` decimal(15,2) DEFAULT 0.00,
  `total_revenue` decimal(15,2) DEFAULT 0.00,
  `grand_total_sales` decimal(15,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`sales_id`),
  UNIQUE KEY `unique_mis_sales` (`mis_id`),
  CONSTRAINT `fk_mis_sales_mis` FOREIGN KEY (`mis_id`) REFERENCES `mis_reports` (`mis_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS Segment Wise Purchase Details
CREATE TABLE IF NOT EXISTS `mis_segment_purchases` (
  `segment_id` int(11) NOT NULL AUTO_INCREMENT,
  `mis_id` int(11) NOT NULL,
  `segment_name` varchar(255) NOT NULL,
  `vendor_name` varchar(255),
  `amount` decimal(15,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`segment_id`),
  KEY `fk_segment_mis` (`mis_id`),
  CONSTRAINT `fk_segment_mis` FOREIGN KEY (`mis_id`) REFERENCES `mis_reports` (`mis_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS Purchase Section
CREATE TABLE IF NOT EXISTS `mis_purchases` (
  `purchase_id` int(11) NOT NULL AUTO_INCREMENT,
  `mis_id` int(11) NOT NULL,
  `opening_stock_catering` decimal(15,2) DEFAULT 0.00,
  `opening_stock_hk_laundry` decimal(15,2) DEFAULT 0.00,
  `opening_stock_other` decimal(15,2) DEFAULT 0.00,
  `purchase_credit_catering` decimal(15,2) DEFAULT 0.00,
  `purchase_dairy_catering` decimal(15,2) DEFAULT 0.00,
  `purchase_provision_catering` decimal(15,2) DEFAULT 0.00,
  `material_in_attach_format_catering` decimal(15,2) DEFAULT 0.00,
  `material_out_attach_format_catering` decimal(15,2) DEFAULT 0.00,
  `closing_stock_catering` decimal(15,2) DEFAULT 0.00,
  `consumption_catering` decimal(15,2) DEFAULT 0.00,
  `cost_percent_catering` decimal(5,2) DEFAULT 0.00,
  `total_purchase_fuel` decimal(15,2) DEFAULT 0.00,
  `total_purchase_food` decimal(15,2) DEFAULT 0.00,
  `total_consumption` decimal(15,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`purchase_id`),
  UNIQUE KEY `unique_mis_purchase` (`mis_id`),
  CONSTRAINT `fk_mis_purchase_mis` FOREIGN KEY (`mis_id`) REFERENCES `mis_reports` (`mis_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS ODC (Other Direct Costs) Breakup
CREATE TABLE IF NOT EXISTS `mis_odc_breakup` (
  `odc_id` int(11) NOT NULL AUTO_INCREMENT,
  `mis_id` int(11) NOT NULL,
  `expense_category` varchar(255) NOT NULL,
  `dfc_credit` decimal(15,2) DEFAULT 0.00,
  `petty_cash` decimal(15,2) DEFAULT 0.00,
  `total_amount` decimal(15,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`odc_id`),
  KEY `fk_odc_mis` (`mis_id`),
  CONSTRAINT `fk_odc_mis` FOREIGN KEY (`mis_id`) REFERENCES `mis_reports` (`mis_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS Labour Section
CREATE TABLE IF NOT EXISTS `mis_labour` (
  `labour_id` int(11) NOT NULL AUTO_INCREMENT,
  `mis_id` int(11) NOT NULL,
  `gross_salary` decimal(15,2) DEFAULT 0.00,
  `no_of_crew` int(11) DEFAULT 0,
  `laundry_allowance` decimal(15,2) DEFAULT 0.00,
  `provision_for_bonus` decimal(15,2) DEFAULT 0.00,
  `provision_for_leave_salary` decimal(15,2) DEFAULT 0.00,
  `employee_statutory_contribution` decimal(15,2) DEFAULT 0.00,
  `staff_welfare_expenses` decimal(15,2) DEFAULT 0.00,
  `dist_head_cost` decimal(15,2) DEFAULT 0.00,
  `overtime` decimal(15,2) DEFAULT 0.00,
  `total_labour_cost` decimal(15,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`labour_id`),
  UNIQUE KEY `unique_mis_labour` (`mis_id`),
  CONSTRAINT `fk_mis_labour_mis` FOREIGN KEY (`mis_id`) REFERENCES `mis_reports` (`mis_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS Budget vs Actual
CREATE TABLE IF NOT EXISTS `mis_budget_vs_actual` (
  `budget_id` int(11) NOT NULL AUTO_INCREMENT,
  `mis_id` int(11) NOT NULL,
  `category` varchar(100) NOT NULL,
  `budget_amount` decimal(15,2) DEFAULT 0.00,
  `actual_amount` decimal(15,2) DEFAULT 0.00,
  `variance` decimal(15,2) DEFAULT 0.00,
  `variance_percent` decimal(5,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`budget_id`),
  KEY `fk_budget_mis` (`mis_id`),
  CONSTRAINT `fk_budget_mis` FOREIGN KEY (`mis_id`) REFERENCES `mis_reports` (`mis_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS Fuel Consumption Report
CREATE TABLE IF NOT EXISTS `mis_fuel_consumption` (
  `fuel_id` int(11) NOT NULL AUTO_INCREMENT,
  `mis_id` int(11) NOT NULL,
  `no_of_cylinders` int(11) DEFAULT 0,
  `credit_purchases` decimal(15,2) DEFAULT 0.00,
  `bio_gas_purchase` decimal(15,2) DEFAULT 0.00,
  `consumption` decimal(15,2) DEFAULT 0.00,
  `cost_percent` decimal(5,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`fuel_id`),
  UNIQUE KEY `unique_mis_fuel` (`mis_id`),
  CONSTRAINT `fk_mis_fuel_mis` FOREIGN KEY (`mis_id`) REFERENCES `mis_reports` (`mis_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS Summary Calculations
CREATE TABLE IF NOT EXISTS `mis_summary` (
  `summary_id` int(11) NOT NULL AUTO_INCREMENT,
  `mis_id` int(11) NOT NULL,
  `total_sales` decimal(15,2) DEFAULT 0.00,
  `total_purchase` decimal(15,2) DEFAULT 0.00,
  `total_labour_cost` decimal(15,2) DEFAULT 0.00,
  `total_other_direct_expenses` decimal(15,2) DEFAULT 0.00,
  `gop` decimal(15,2) DEFAULT 0.00,
  `gop_percent` decimal(5,2) DEFAULT 0.00,
  `petty_cash_closing_balance` decimal(15,2) DEFAULT 0.00,
  `sales_cash_closing_balance` decimal(15,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`summary_id`),
  UNIQUE KEY `unique_mis_summary` (`mis_id`),
  CONSTRAINT `fk_mis_summary_mis` FOREIGN KEY (`mis_id`) REFERENCES `mis_reports` (`mis_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

