
/*Mess tables*/
CREATE TABLE `kitchens` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` enum('veg','non-veg','jain') NOT NULL,
  `building_ids` varchar(200) DEFAULT NULL COMMENT 'comma separatored',
  `isActive` int(11) NOT NULL DEFAULT 1 COMMENT '1=active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT '1=Active',
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `kitchens` ADD PRIMARY KEY(`id`);
ALTER TABLE `kitchens` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;


CREATE TABLE `food_items` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` decimal(10,0) NOT NULL,
  `isActive` int(11) NOT NULL DEFAULT 1 COMMENT '1=active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `food_items` ADD PRIMARY KEY(`id`);
ALTER TABLE `food_items` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `kitchen_meal_mappings` (
  `id` int(11) NOT NULL,
  `kitchen_id` int(11) NOT NULL,
  `meal_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `kitchen_meal_mappings` ADD PRIMARY KEY(`id`);
ALTER TABLE `kitchen_meal_mappings` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;


CREATE TABLE `meals` (
  `id` int(11) NOT NULL,
  `meal_name` varchar(255) NOT NULL,
  `meal_type` enum('veg','non-veg','jain') NOT NULL,
  `meal_for` enum('breakfast','lunch','snacks','dinner') NOT NULL,
  `food_item_ids` varchar(255) NOT NULL COMMENT 'comma separated',
  `default_price` decimal(10,0) NOT NULL,
  `discount_amount` int(11) NOT NULL DEFAULT 0,
  `final_price` decimal(10,0) NOT NULL,
  `isActive` int(11) NOT NULL DEFAULT 1 COMMENT '1=active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `meals` ADD PRIMARY KEY(`id`);
ALTER TABLE `meals` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `meal_packages` (
  `id` int(11) NOT NULL,
  `package_name` varchar(255) NOT NULL,
  `package_type` enum('veg','non-veg','jain') NOT NULL,
  `kitchen_id` int(11) NOT NULL,
  `meal_ids` varchar(255) NOT NULL,
  `discount` double NOT NULL DEFAULT 0,
  `net_price` double NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `isActive` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `meal_packages` ADD PRIMARY KEY(`id`);
ALTER TABLE `meal_packages` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;


CREATE TABLE `kitchen_subscriptions` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `kitchen_id` int(11) NOT NULL,
  `package_id` int(11) NOT NULL,
  `amount` double NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `duration` int(11) NOT NULL DEFAULT 1,
  `subscription_date` date NOT NULL,
  `payment_status` enum('INITIATED','PENDING','COMPLETED','CANCELLED') NOT NULL DEFAULT 'PENDING',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `isActive` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `kitchen_subscriptions` ADD PRIMARY KEY(`id`);
ALTER TABLE `kitchen_subscriptions` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;


CREATE TABLE `kitchen_booking_transactions` (
  `id` bigint(20) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `kitchen_subscription_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `payment_medium` tinyint(4) NOT NULL,
  `order_id` varchar(100) DEFAULT NULL,
  `reference_number` varchar(60) DEFAULT NULL,
  `start_at` datetime NOT NULL DEFAULT current_timestamp(),
  `complete_at` datetime DEFAULT NULL,
  `transaction_complete_user` int(11) DEFAULT NULL,
  `amount` double NOT NULL,
  `tax` double NOT NULL DEFAULT 0,
  `discount` double NOT NULL DEFAULT 0,
  `net_amount` double NOT NULL DEFAULT 0,
  `notes` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `pay_gateway_id` tinyint(4) DEFAULT NULL,
  `status` enum('INITIATED','PENDING','COMPLETED','CANCELLED') NOT NULL DEFAULT 'INITIATED',
  `payment_proof` varchar(50) DEFAULT NULL,
  `is_verified_by_bank_at` datetime DEFAULT NULL,
  `isLatest` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `kitchen_booking_transactions` ADD PRIMARY KEY(`id`);
ALTER TABLE `kitchen_booking_transactions` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;


CREATE TABLE `food_preferences` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `preferences` text NOT NULL,
  `isActive` int(11) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `food_preferences` ADD PRIMARY KEY(`id`);
ALTER TABLE `food_preferences` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `kitchen_optins` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `meal_id` int(11) NOT NULL,
  `in_date` date DEFAULT NULL,
  `guest_count` int(11) NOT NULL DEFAULT 0,
  `guest_details` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `kitchen_optins` ADD PRIMARY KEY(`id`);
ALTER TABLE `kitchen_optins` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `kitchen_optouts` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `meal_id` int(11) NOT NULL,
  `out_date` date DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `kitchen_optouts` ADD PRIMARY KEY(`id`);
ALTER TABLE `kitchen_optouts` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;


CREATE TABLE `kitchen_time_schedules` (
  `id` int(11) NOT NULL,
  `kitchen_id` int(11) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `type` enum('breakfast','lunch','snacks','dinner') NOT NULL,
  `isActive` int(11) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `kitchen_time_schedules` ADD PRIMARY KEY(`id`);
ALTER TABLE `kitchen_time_schedules` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;


CREATE TABLE `meal_consumptions` (
  `id` int(11) NOT NULL,
  `meal_id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `kitchen_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `meal_consumptions` ADD PRIMARY KEY(`id`);
ALTER TABLE `meal_consumptions` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;


CREATE TABLE `mess_tokens` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `token_type` enum('universal','particular_kitchen') NOT NULL,
  `meal_id` int(11) DEFAULT NULL,
  `number_of_tokens` int(11) NOT NULL DEFAULT 0,
  `used_tokens` int(11) NOT NULL DEFAULT 0,
  `assined_by` int(11) NOT NULL DEFAULT 0,
  `activation_date` date DEFAULT NULL,
  `activation_end_date` date DEFAULT NULL,
  `isActive` int(11) NOT NULL DEFAULT 1 COMMENT '1=active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `mess_tokens` ADD PRIMARY KEY(`id`);
ALTER TABLE `mess_tokens` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

/*End tables*/


/*SP*/
DROP PROCEDURE IF EXISTS  `getBookedKitchenResidentHistory`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getBookedKitchenResidentHistory`(IN `resident_id` INT)
BEGIN
    SELECT
        kitchen_booking_transactions.*,
        complainants.complainant_name AS resident_name,
        complainants.gender AS gender,
        complainants.contact_no AS contactNo,
        complainants.email AS email,
        resident_academic_details.reference_number AS roll_no,
        resident_academic_details.registration_no AS registration_no,
        resident_academic_details.course_year AS course_year,
        resident_academic_details.branch AS branch,
        resident_academic_details.cgpa AS cgpa,
        resident_academic_details.course AS course,
        academic_courses.course_name AS course_name,
        academic_course_branches.branch_name AS branch_name,
        academic_course_branches.head_user_id AS head_user_id,
        student_academic_mappings.id AS sam_id,
        kitchens.id AS kitchen_id,
        kitchens.name AS kitchen_name,
        kitchen_subscriptions.package_id,
        meal_packages.package_name,
        payment_modes.mode_name,
        kitchen_subscriptions.from_date,
        kitchen_subscriptions.to_date,
        kitchen_subscriptions.payment_status
    FROM
        kitchen_subscriptions
    JOIN
        complainants ON complainants.id = kitchen_subscriptions.resident_id
    JOIN
        kitchen_booking_transactions ON kitchen_booking_transactions.kitchen_subscription_id = kitchen_subscriptions.id
    JOIN
        payment_modes ON payment_modes.payment_mode_id = kitchen_booking_transactions.payment_medium
    JOIN
        meal_packages ON meal_packages.id = kitchen_subscriptions.package_id
    JOIN
        kitchens ON kitchens.id = kitchen_subscriptions.kitchen_id
    LEFT JOIN
        resident_academic_details ON resident_academic_details.resident_id = complainants.id
    LEFT JOIN
        academic_courses ON academic_courses.id = resident_academic_details.course
    LEFT JOIN
        academic_course_branches ON academic_course_branches.branch_id = resident_academic_details.branch
    LEFT JOIN
        student_academic_mappings ON student_academic_mappings.course = resident_academic_details.course
        AND student_academic_mappings.branch = resident_academic_details.branch
        AND student_academic_mappings.year = resident_academic_details.course_year
        AND student_academic_mappings.gender = complainants.gender
    WHERE
        complainants.is_activated = 'A'
        AND kitchen_subscriptions.isActive = 1
        AND kitchen_subscriptions.resident_id = resident_id
    ORDER BY 
        kitchen_subscriptions.id DESC;
END$$
DELIMITER ;


DROP PROCEDURE IF EXISTS  `checkResidentKitchenExist`;
DELIMITER $$
CREATE PROCEDURE checkResidentKitchenExist(IN resident_id INT)
BEGIN
    SELECT k.*
    FROM kitchens k
    JOIN resident_user_view ruv ON FIND_IN_SET(ruv.building_id, k.building_ids) > 0
    WHERE ruv.resident_id = resident_id;
END $$

DELIMITER ;

DROP PROCEDURE IF EXISTS  `getAllTransactionsForKitchenBooking`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllTransactionsForKitchenBooking`(
    IN `start_date` VARCHAR(100), 
    IN `end_date` VARCHAR(100), 
    IN `status` VARCHAR(100), 
    IN `resident_id` INT
)
BEGIN
    DECLARE query TEXT;
    SET query = 'SELECT
        kitchen_booking_transactions.*,
        complainants.complainant_name AS resident_name,
        complainants.gender AS gender,
        complainants.contact_no AS contactNo,
        complainants.email AS email,
        resident_academic_details.reference_number AS roll_no,
        resident_academic_details.registration_no AS registration_no,
        resident_academic_details.course_year AS course_year,
        resident_academic_details.branch AS branch,
        resident_academic_details.cgpa AS cgpa,
        resident_academic_details.course AS course,
        academic_courses.course_name AS course_name,
        academic_course_branches.branch_name AS branch_name,
        academic_course_branches.head_user_id AS head_user_id,
        student_academic_mappings.id AS sam_id,
        kitchens.id AS kitchen_id,
        kitchens.name AS kitchen_name,
        kitchen_subscriptions.package_id,
        meal_packages.package_name
    FROM
        kitchen_booking_transactions
    JOIN
        complainants ON complainants.id = kitchen_booking_transactions.resident_id
    JOIN
        kitchen_subscriptions ON kitchen_subscriptions.id = kitchen_booking_transactions.kitchen_subscription_id
    JOIN
        kitchens ON kitchens.id = kitchen_subscriptions.kitchen_id
    LEFT JOIN
        resident_academic_details ON resident_academic_details.resident_id = complainants.id
    LEFT JOIN
        academic_courses ON academic_courses.id = resident_academic_details.course
    LEFT JOIN
        academic_course_branches ON academic_course_branches.branch_id = resident_academic_details.branch
    LEFT JOIN
        student_academic_mappings ON student_academic_mappings.course = resident_academic_details.course
        AND student_academic_mappings.branch = resident_academic_details.branch
        AND student_academic_mappings.year = resident_academic_details.course_year
        AND student_academic_mappings.gender = complainants.gender
    LEFT JOIN
        meal_packages ON meal_packages.id = kitchen_subscriptions.package_id
    WHERE
        complainants.is_activated = ''A''';

    IF status IS NOT NULL AND status != '' THEN
        SET query = CONCAT(query, ' AND kitchen_booking_transactions.status = ''', status, '''');
    END IF;

    IF resident_id > 0 THEN
        SET query = CONCAT(query, ' AND kitchen_booking_transactions.resident_id = ', resident_id);
    END IF;
    
    IF start_date IS NOT NULL AND start_date != '' AND end_date IS NOT NULL AND end_date != '' THEN
        SET query = CONCAT(query, ' AND kitchen_booking_transactions.created_at BETWEEN ''', start_date, ''' AND ''', end_date, '''');
    ELSEIF start_date IS NULL OR start_date = '' AND end_date IS NOT NULL AND end_date != '' THEN
        SET query = CONCAT(query, ' AND kitchen_booking_transactions.created_at <= ''', end_date, '''');
    END IF;

    PREPARE stmt FROM query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

/*SP end*/

