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

-- MIS Report Header/Metadata
CREATE TABLE IF NOT EXISTS `mis_report_headers` (
  `header_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_name` varchar(255),
  `segment` varchar(100),
  `location` varchar(255),
  `hm_manager_name` varchar(255),
  `site_accountant_name` varchar(255),
  `operation_head` varchar(255),
  `created_by` int(11),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`header_id`),
  UNIQUE KEY `unique_site_month_year` (`site_id`, `report_month`, `report_year`),
  KEY `fk_mis_header_site` (`site_id`),
  KEY `fk_mis_header_created_by` (`created_by`),
  CONSTRAINT `fk_mis_header_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_mis_header_created_by` FOREIGN KEY (`created_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,
  `header_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_subtotal` 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_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_sales_header` (`header_id`),
  CONSTRAINT `fk_mis_sales_header` FOREIGN KEY (`header_id`) REFERENCES `mis_report_headers` (`header_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,
  `header_id` int(11) NOT NULL,
  `segment_name` varchar(255) NOT NULL,
  `vendor_name` varchar(255),
  `amount` decimal(15,2) DEFAULT 0.00,
  `display_order` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`segment_id`),
  KEY `fk_mis_segment_header` (`header_id`),
  CONSTRAINT `fk_mis_segment_header` FOREIGN KEY (`header_id`) REFERENCES `mis_report_headers` (`header_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS Purchase Section (Catering, HK & Laundry, Other)
CREATE TABLE IF NOT EXISTS `mis_purchases` (
  `purchase_id` int(11) NOT NULL AUTO_INCREMENT,
  `header_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_credit_hk_laundry` decimal(15,2) DEFAULT 0.00,
  `purchase_credit_other` decimal(15,2) DEFAULT 0.00,
  `purchase_dairy_catering` decimal(15,2) DEFAULT 0.00,
  `purchase_dairy_hk_laundry` decimal(15,2) DEFAULT 0.00,
  `purchase_dairy_other` decimal(15,2) DEFAULT 0.00,
  `purchase_provision_catering` decimal(15,2) DEFAULT 0.00,
  `purchase_provision_hk_laundry` decimal(15,2) DEFAULT 0.00,
  `purchase_provision_other` decimal(15,2) DEFAULT 0.00,
  `material_in_attach_format_catering` decimal(15,2) DEFAULT 0.00,
  `material_in_attach_format_hk_laundry` decimal(15,2) DEFAULT 0.00,
  `material_in_attach_format_other` decimal(15,2) DEFAULT 0.00,
  `material_out_attach_format_catering` decimal(15,2) DEFAULT 0.00,
  `material_out_attach_format_hk_laundry` decimal(15,2) DEFAULT 0.00,
  `material_out_attach_format_other` decimal(15,2) DEFAULT 0.00,
  `closing_stock_catering` decimal(15,2) DEFAULT 0.00,
  `closing_stock_hk_laundry` decimal(15,2) DEFAULT 0.00,
  `closing_stock_other` decimal(15,2) DEFAULT 0.00,
  `consumption_catering` decimal(15,2) DEFAULT 0.00,
  `consumption_hk_laundry` decimal(15,2) DEFAULT 0.00,
  `consumption_other` decimal(15,2) DEFAULT 0.00,
  `cost_percent_catering` decimal(5,2) DEFAULT 0.00,
  `cost_percent_hk_laundry` decimal(5,2) DEFAULT 0.00,
  `cost_percent_other` 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_purchase_header` (`header_id`),
  CONSTRAINT `fk_mis_purchase_header` FOREIGN KEY (`header_id`) REFERENCES `mis_report_headers` (`header_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS ODC Breakup (Other Direct Costs)
CREATE TABLE IF NOT EXISTS `mis_odc_breakup` (
  `odc_id` int(11) NOT NULL AUTO_INCREMENT,
  `header_id` int(11) NOT NULL,
  `expense_name` varchar(255) NOT NULL,
  `dfc_credit` decimal(15,2) DEFAULT 0.00,
  `petty_cash` decimal(15,2) DEFAULT 0.00,
  `display_order` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`odc_id`),
  KEY `fk_mis_odc_header` (`header_id`),
  CONSTRAINT `fk_mis_odc_header` FOREIGN KEY (`header_id`) REFERENCES `mis_report_headers` (`header_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,
  `header_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_labour_header` (`header_id`),
  CONSTRAINT `fk_mis_labour_header` FOREIGN KEY (`header_id`) REFERENCES `mis_report_headers` (`header_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,
  `header_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,
  `display_order` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`budget_id`),
  KEY `fk_mis_budget_header` (`header_id`),
  CONSTRAINT `fk_mis_budget_header` FOREIGN KEY (`header_id`) REFERENCES `mis_report_headers` (`header_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,
  `header_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_fuel_header` (`header_id`),
  CONSTRAINT `fk_mis_fuel_header` FOREIGN KEY (`header_id`) REFERENCES `mis_report_headers` (`header_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- MIS Summary (Calculated Totals)
CREATE TABLE IF NOT EXISTS `mis_summary` (
  `summary_id` int(11) NOT NULL AUTO_INCREMENT,
  `header_id` int(11) NOT NULL,
  `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,
  `total_credit_procurement` 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_summary_header` (`header_id`),
  CONSTRAINT `fk_mis_summary_header` FOREIGN KEY (`header_id`) REFERENCES `mis_report_headers` (`header_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

