-- ============================================
-- DFC COMPLETE SETUP & FIX SQL
-- Run this to ensure all tables and columns exist
-- ============================================

-- Create DFC Periods table
CREATE TABLE IF NOT EXISTS `dfc_periods` (
  `period_id` int(11) NOT NULL AUTO_INCREMENT,
  `period_name` varchar(255) NOT NULL,
  `site_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `cylinder_rate` decimal(10,2) DEFAULT 0.00,
  `benchmark_percent` decimal(5,2) DEFAULT 74.00,
  `is_locked` tinyint(1) DEFAULT 0,
  `locked_at` datetime DEFAULT NULL,
  `locked_by` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`period_id`),
  KEY `idx_dfcp_month_year` (`month`, `year`),
  KEY `idx_dfcp_dates` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create DFC Entries table with ALL columns
CREATE TABLE IF NOT EXISTS `dfc_entries` (
  `entry_id` int(11) NOT NULL AUTO_INCREMENT,
  `period_id` int(11) NOT NULL,
  `entry_date` date NOT NULL,
  `cash_sale_net` decimal(15,2) DEFAULT 0.00,
  `catering_btc_regular` decimal(15,2) DEFAULT 0.00,
  `catering_btc_extra` decimal(15,2) DEFAULT 0.00,
  `total_food_sale` decimal(15,2) DEFAULT 0.00,
  `housekeeping_lumpsum` decimal(15,2) DEFAULT 0.00,
  `laundry` decimal(15,2) DEFAULT 0.00,
  `total_sale` decimal(15,2) DEFAULT 0.00,
  `cash_purchase` decimal(15,2) DEFAULT 0.00,
  `catering_cost` decimal(15,2) DEFAULT 0.00,
  `total_food_cost` decimal(15,2) DEFAULT 0.00,
  `food_cost_percent` decimal(5,2) DEFAULT 0.00,
  `housekeeping_cost` decimal(15,2) DEFAULT 0.00,
  `housekeeping_cost_percent` decimal(5,2) DEFAULT 0.00,
  `manpower_days` decimal(10,2) DEFAULT 0.00,
  `manpower_cost` decimal(15,2) DEFAULT 0.00,
  `manpower_cost_percent` decimal(5,2) DEFAULT 0.00,
  `manpower_cost_per_sale` decimal(10,4) DEFAULT 0.00,
  `odc_cost` decimal(15,2) DEFAULT 0.00,
  `odc_cost_2` decimal(15,2) DEFAULT 0.00,
  `cylinder_quantity` decimal(10,2) DEFAULT 0.00,
  `cylinder_rate` decimal(10,2) DEFAULT 0.00,
  `total_cylinder_cost` decimal(15,2) DEFAULT 0.00,
  `cylinder_cost_percent` decimal(5,2) DEFAULT 0.00,
  `cylinder_cost_per_sale` decimal(10,4) DEFAULT 0.00,
  `expected_food_cost` decimal(15,2) DEFAULT 0.00,
  `variation_amount` decimal(15,2) DEFAULT 0.00,
  `variation_type` enum('positive','negative') DEFAULT 'positive',
  `additional_cost` decimal(15,2) DEFAULT 0.00,
  `total_cost` decimal(15,2) DEFAULT 0.00,
  `sr_no` int(11) DEFAULT 1,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entry_id`),
  KEY `fk_dfce_period` (`period_id`),
  KEY `idx_dfce_date` (`entry_date`),
  KEY `idx_dfce_sr_no` (`sr_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add odc_cost_2 column if missing
SET @dbname = DATABASE();
SET @tablename = "dfc_entries";
SET @columnname = "odc_cost_2";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = @tablename AND table_schema = @dbname AND column_name = @columnname
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD COLUMN ", @columnname, " decimal(15,2) DEFAULT 0.00 AFTER odc_cost")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Add foreign key constraint for period_id (if not exists)
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE table_name = 'dfc_entries' AND table_schema = @dbname 
    AND constraint_name = 'fk_dfce_period'
  ) > 0,
  "SELECT 1",
  "ALTER TABLE dfc_entries ADD CONSTRAINT fk_dfce_period FOREIGN KEY (period_id) REFERENCES dfc_periods(period_id) ON DELETE CASCADE"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
