CREATE TABLE `checkout_approval_users` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `building_id` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `checkout_users` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `building_id` int(11) NOT NULL,
  `residence_id` int(11) NOT NULL,
  `checkout_status` enum('Pending','Initiated','Hold','Rejected','Completed') NOT NULL DEFAULT 'Pending',
  `approved_users_id` varchar(255) DEFAULT NULL,
  `approval_hold_by_users` varchar(255) DEFAULT NULL,
  `approval_reject_by_users` varchar(255) DEFAULT NULL,
  `remark` text DEFAULT NULL,
  `is_damage_inventory` enum('No','Yes') NOT NULL DEFAULT 'No',
  `checkout_requested_date` date DEFAULT NULL,
  `checkout_by` int(11) NOT NULL,
  `checkout_date` date DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `check_in_users` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `inventory_ids` varchar(255) DEFAULT NULL,
  `damage_inventory_ids` varchar(255) DEFAULT NULL,
  `damage_inventory_remark` varchar(255) DEFAULT NULL,
  `check_in_by` int(11) DEFAULT NULL,
  `check_in_date` date NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `damage_inventory_on_checkout` (
  `id` int(11) NOT NULL,
  `checkout_id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `physical_verify_user_id` int(11) NOT NULL,
  `damage_inventory_id` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `department_building_work_timings` (
  `id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `building_id` varchar(255) NOT NULL,
  `start_time` time NOT NULL,
  `close_time` time NOT NULL,
  `updated_by` 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 COLLATE=utf8mb4_general_ci;


CREATE TABLE `feedback_forms` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `purpose` varchar(255) NOT NULL,
  `start_date` timestamp NULL DEFAULT NULL,
  `repeated_in_days` tinyint(255) DEFAULT NULL,
  `end_date` timestamp NULL DEFAULT NULL,
  `created_by` varchar(255) NOT NULL,
  `updated_by` varchar(255) NOT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `feedback_form_cycles` (
  `cycle_id` int(11) NOT NULL,
  `form_id` int(12) DEFAULT NULL,
  `question_ids` varchar(100) NOT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL,
  `updated_by` int(12) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `isActive` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `feedback_form_questions` (
  `id` int(10) UNSIGNED NOT NULL,
  `form_id` int(11) NOT NULL DEFAULT 0,
  `question` varchar(150) NOT NULL,
  `created_by` int(11) NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `feedback_question` (
  `id` int(11) NOT NULL,
  `feedback_question` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `complainOrGeneral` enum('C','G') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `notice_board_templates` (
  `template_id` int(11) NOT NULL,
  `template_text` longtext NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `is_active` int(11) NOT NULL DEFAULT 0 COMMENT 'active = 1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `checkout_approval_users`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `checkout_users`
--
ALTER TABLE `checkout_users`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `check_in_users`
--
ALTER TABLE `check_in_users`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `damage_inventory_on_checkout`
--
ALTER TABLE `damage_inventory_on_checkout`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `department_building_work_timings`
--
ALTER TABLE `department_building_work_timings`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `feedback_forms`
--
ALTER TABLE `feedback_forms`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `feedback_form_cycles`
--
ALTER TABLE `feedback_form_cycles`
  ADD PRIMARY KEY (`cycle_id`);

--
-- Indexes for table `feedback_form_questions`
--
ALTER TABLE `feedback_form_questions`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `feedback_question`
--
ALTER TABLE `feedback_question`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `notice_board_templates`
--
ALTER TABLE `notice_board_templates`
  ADD PRIMARY KEY (`template_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `checkout_approval_users`
--
ALTER TABLE `checkout_approval_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `checkout_users`
--
ALTER TABLE `checkout_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `check_in_users`
--
ALTER TABLE `check_in_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `damage_inventory_on_checkout`
--
ALTER TABLE `damage_inventory_on_checkout`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `department_building_work_timings`
--
ALTER TABLE `department_building_work_timings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `feedback_forms`
--
ALTER TABLE `feedback_forms`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `feedback_form_cycles`
--
ALTER TABLE `feedback_form_cycles`
  MODIFY `cycle_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `feedback_form_questions`
--
ALTER TABLE `feedback_form_questions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `feedback_question`
--
ALTER TABLE `feedback_question`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notice_board_templates`
--
ALTER TABLE `notice_board_templates`
  MODIFY `template_id` int(11) NOT NULL AUTO_INCREMENT;


ALTER TABLE `checkout_approval_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;


CREATE TABLE `admin_booking_session` (
  `id` int(11) NOT NULL,
  `booking_session_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `branch_ids` varchar(255) DEFAULT NULL,
  `building_ids` varchar(255) DEFAULT NULL,
  `opening_academic_year` varchar(100) NOT NULL,
  `booking_start_date` date NOT NULL,
  `booking_end_date` date NOT NULL,
  `isActive` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) NOT NULL,
  `updated_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `regular_night_out` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `residence_id` int(11) NOT NULL,
  `template_id` int(11) DEFAULT NULL,
  `building_id` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('Pending','Approved','Rejected','') NOT NULL,
  `approval_status` text DEFAULT NULL,
  `approve_by` varchar(50) DEFAULT NULL,
  `rejected_by` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `regular_nightout_checkin_checkout` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `regular_night_out_id` int(11) NOT NULL,
  `checkin_at` varchar(100) DEFAULT NULL,
  `checkin_time` datetime DEFAULT NULL,
  `checkout_time` datetime DEFAULT NULL,
  `checkin_by` int(11) DEFAULT NULL,
  `checkout_by` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `resident_nightout_approval_user_mapping` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) NOT NULL,
  `leave_approval_user` varchar(50) DEFAULT NULL,
  `regular_nightout_approval_user` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `student_details` (
  `id` int(11) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `roll_no` varchar(70) DEFAULT NULL,
  `contact` varchar(70) NOT NULL,
  `dob` date NOT NULL,
  `status` int(11) NOT NULL,
  `branch` varchar(100) NOT NULL,
  `course` varchar(100) NOT NULL,
  `year` varchar(100) NOT NULL,
  `admission_date` date NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `student_enquiry` (
  `id` int(11) NOT NULL,
  `student_enquiry_initiated_by` int(11) NOT NULL,
  `student_details_enquired_for` varchar(100) DEFAULT NULL,
  `academic_session_id` int(11) NOT NULL,
  `preferences` longtext DEFAULT NULL,
  `isActive` int(11) DEFAULT NULL,
  `isFake` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE `student_unit_type_bookings` (
  `id` int(11) NOT NULL,
  `application_no` varchar(100) DEFAULT NULL,
  `roll_number` varchar(100) DEFAULT NULL,
  `phone_no` varchar(100) NOT NULL,
  `unit_type_id` int(11) NOT NULL,
  `admin_session_id` int(11) NOT NULL,
  `student_enquiry_id` int(11) NOT NULL,
  `booking_status` enum('Pending','Allotted','Rejected','') NOT NULL DEFAULT 'Pending',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



ALTER TABLE `admin_booking_session`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `admin_booking_session`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `regular_night_out`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `regular_night_out`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `regular_nightout_checkin_checkout`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `regular_nightout_checkin_checkout`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;


ALTER TABLE `resident_nightout_approval_user_mapping`
  ADD PRIMARY KEY (`id`);





ALTER TABLE `resident_nightout_approval_user_mapping` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `student_details` ADD PRIMARY KEY (`id`);


ALTER TABLE `student_enquiry` ADD PRIMARY KEY (`id`);


ALTER TABLE `student_details` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;


ALTER TABLE `student_enquiry` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT ;

ALTER TABLE `student_unit_type_bookings` ADD PRIMARY KEY (`id`);

ALTER TABLE `student_unit_type_bookings` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;


INSERT INTO `community_charge_types` (`charge_type_id`, `charge_text`, `property_type`, `default_charge`, `isActive`, `updated_by`, `updated_at`, `type`) 
VALUES (NULL, 'Fine', 'HOSTEL', NULL, '1', NULL, current_timestamp(), 'ONCE');


ALTER TABLE `admin_booking_session` ADD `hostel_allotment_date` DATETIME NULL DEFAULT NULL AFTER `booking_end_date`;
ALTER TABLE `student_enquiry` ADD `status` ENUM('Pending','Allotted','Rejected','') NOT NULL AFTER `preferences`;


ALTER TABLE `academic_course_branches` CHANGE `branch_name` `branch_name` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;
ALTER TABLE `academic_courses` CHANGE `course_name` `course_name` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

ALTER TABLE `regular_night_out` ADD `required_approver` VARCHAR(50) NULL DEFAULT NULL AFTER `status`;

ALTER TABLE `regular_night_out` ADD `nightout_type` ENUM('everyday','days','') NOT NULL DEFAULT 'everyday' AFTER `to_date`, ADD `nightout_days` VARCHAR(255) NULL DEFAULT NULL AFTER `nightout_type`;

ALTER TABLE `regular_night_out` CHANGE `nightout_type` `nightout_type` ENUM('everyday','monthly','weekly','') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'everyday';

ALTER TABLE `regular_night_out` ADD `nightout_week` VARCHAR(50) NULL DEFAULT NULL AFTER `nightout_type`;

ALTER TABLE `regular_night_out` ADD `monthly_date` VARCHAR(255) NULL DEFAULT NULL AFTER `nightout_type`;



  ALTER TABLE `enquiry_form_setting` ADD `second_phone` INT(12) NULL DEFAULT NULL AFTER `whatsapp`, ADD `second_whatsapp`
   INT(12) NULL DEFAULT NULL AFTER `second_phone`, ADD `landline` INT(12) NULL DEFAULT NULL AFTER `second_whatsapp`;

   ALTER TABLE `enquiry_form_setting` CHANGE `second_phone` `second_phone` VARCHAR(34) NULL DEFAULT NULL,
 CHANGE `second_whatsapp` `second_whatsapp` VARCHAR(34) NULL DEFAULT NULL, CHANGE `landline` `landline` VARCHAR(34) NULL DEFAULT NULL;


 ALTER TABLE `feedback_forms` CHANGE `isActive` `isActive` TINYINT(1) NOT NULL DEFAULT '0';
 ALTER TABLE `regular_night_out` CHANGE `nightout_type` `nightout_frequency` ENUM('everyday','monthly','weekly') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'everyday';







DROP PROCEDURE if EXISTS `QuestionAvgWithCycleList`; 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `QuestionAvgWithCycleList`(IN `in_question_id` INT(11))
select user_feedbacks.cycle_id,feedback_form_cycles.`start_date` as cycle_start_date,feedback_form_cycles.`end_date` as cycle_end_date,ROUND(AVG(rating),2) AS cycle_wise_q_f_avg,COUNT(user_feedbacks.cycle_id) as no_of_user,group_concat(user_id) as user_ids from user_feedbacks INNER JOIN feedback_form_cycles on user_feedbacks.cycle_id = feedback_form_cycles.cycle_id WHERE question_id=in_question_id GROUP by user_feedbacks.cycle_id$$
DELIMITER ;



DROP PROCEDURE if EXISTS `UpdateComplainSubject`; 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateComplainSubject`(IN `in_subEng` VARCHAR(150), IN `in_subHindi` VARCHAR(150), IN `in_scope` VARCHAR(50), IN `in_csid` INT)
BEGIN
Update `complain_subjects`
SET subject_eng = in_subEng, 
subject_hindi = in_subHindi,
scope=in_scope

WHERE subject_id = in_csid;
END$$
DELIMITER ;



DROP PROCEDURE if EXISTS `GetOccupancyPaymentTransaction`; 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetOccupancyPaymentTransaction`(IN `in_trx_id` INT, IN `in_bill_id` INT, IN `in_user_id` INT, IN `in_status` VARCHAR(12))
    NO SQL
BEGIN
SET @sql =  "SELECT occupancy_payment_transaction.*,complainants.complainant_name as resident_name,complainants.contact_no,complainants.email,building_residence.apartment_name,building.name as building_name,payment_modes.mode_name,transaction_start.name as transaction_start_name, transaction_complete.name as transaction_complete_name
FROM `occupancy_payment_transaction` inner join payment_modes on payment_modes.payment_mode_id=occupancy_payment_transaction.payment_medium
INNER JOIN user transaction_start on transaction_start.id=occupancy_payment_transaction.user_id
left JOIN user transaction_complete on transaction_complete.id=occupancy_payment_transaction.transaction_complete_user
inner join community_occupancy_bills on community_occupancy_bills.bill_id=occupancy_payment_transaction.bill_id
INNER join complainants on complainants.id=community_occupancy_bills.resident_id
inner join building_residence on building_residence.id=community_occupancy_bills.residence_id
INNER join building on building.id=building_residence.building_id where 1=1 ";


if(in_trx_id>0) then
SET @sql = CONCAT(@sql," and occupancy_payment_transaction.t_id =",in_trx_id);
end if;
if(in_user_id>0) then
SET @sql = CONCAT(@sql, " and occupancy_payment_transaction.user_id = ",in_user_id);
end if;
if(in_bill_id>0) then
SET @sql = CONCAT(@sql, " and occupancy_payment_transaction.bill_id = ",in_bill_id);
end if;

if(in_status != 'ALL') then
SET @sql = CONCAT(@sql, " and occupancy_payment_transaction.status = '",in_status,"'");
end if;

SET @sql = CONCAT(@sql,  " order by occupancy_payment_transaction.t_id desc");

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;


ALTER TABLE `resident_academic_details` ADD `registration_no` VARCHAR(200) NULL DEFAULT NULL AFTER `resident_id`;

CREATE TABLE `checkout_approval_users_new` (
  `id` int(11) NOT NULL,
  `building_id` int(11) DEFAULT NULL,
  `approvers` text DEFAULT NULL,
  `supreme_approval` varchar(50) DEFAULT NULL,
  `approval_type` enum('simultaneously','ordered') NOT NULL DEFAULT 'simultaneously',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `checkout_approval_users_new`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `checkout_approval_users_new`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT

DROP PROCEDURE if EXISTS `UpdateComplainSubject`; 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateComplainSubject`(IN `in_subEng` VARCHAR(150), IN `in_subHindi` VARCHAR(150), IN `in_scope` VARCHAR(50), IN `in_csid` INT)
BEGIN
Update `complain_subjects`
SET subject_eng = in_subEng, 
subject_hindi = in_subHindi,
scope=in_scope

WHERE subject_id = in_csid;
END$$
DELIMITER ;




create view active_occupancy AS SELECT building_residence.building_id,building_residence.id as 
residence_id, building.name as building_name,apartment_name,building_residence.unit_type,building_unit_types.Description as description,
floor,building_unit_types.capacity,active_residents as occupied FROM `building_residence` left join building_unit_types on building_unit_types.unit_type_id=building_residence.unit_type 
INNER join building on building.id=building_residence.building_id left join (SELECT COUNT(complainants.id) as active_residents,resident_residences.residence_id 
from complainants inner join resident_residences on resident_residences.resident_id=complainants.id and resident_residences.isActive=1 
WHERE complainants.is_activated='A' GROUP by resident_residences.residence_id) residence_residents on residence_residents.residence_id=building_residence.id 
where building_residence.isBillable=1 and building.use_status>0;

--9th jan





ALTER TABLE `feedback_forms` CHANGE `repeated_in_days` `repeated_in_days` INT(22) NULL DEFAULT NULL;
------27th jan
ALTER TABLE `feature_packs` ADD `is_checkin_required` TINYINT(1) NOT NULL DEFAULT '0' AFTER `is_professional_required`;

DROP PROCEDURE `getGateParcel`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getGateParcel`(IN `in_booking_id` BIGINT, IN `in_residence_id` SMALLINT, IN `in_resident_id` SMALLINT, IN `in_view` ENUM('ALL','N','D','DP'), IN `in_limit` INT, IN `in_created_at` DATE, IN `in_delivered_at` DATE, IN `in_disposed_at` DATE) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN

SET @sql = "SELECT parcel_at_gate.*,visitors.name as visitor_name,visitors.contact_no as visitor_contact,CONCAT(visitors.file,'.',visitors.file_ext) as visitor_image,visitors.gender,building_residence.apartment_name,building.name as building,complainants.complainant_name as  name,complainants.contact_no as phoneNumber,creator.name as created_by_name,deliver.name as deliver_name from parcel_at_gate \r\ninner join visitors on visitors.id=parcel_at_gate.visitor_id\r\nLEFT join building_residence on building_residence.id=parcel_at_gate.residence_id \r\nleft join building on building.id=building_residence.building_id\r\nleft join complainants on complainants.id=parcel_at_gate.resident_id\r\nINNER join user as creator on creator.id=parcel_at_gate.created_by \r\nleft join user as deliver on deliver.id=parcel_at_gate.delivered_by \r\n where 1=1  ";
 IF ( in_booking_id > 0 ) THEN
       SET @sql = CONCAT(@sql,' AND parcel_at_gate.booking_id =',in_booking_id);
 
elseIF ( in_resident_id > 0 ) THEN
       SET @sql = CONCAT(@sql,' AND parcel_at_gate.resident_id =',in_resident_id);
 elseIF ( in_residence_id > 0 ) THEN
       SET @sql = CONCAT(@sql,' AND parcel_at_gate.residence_id =',in_residence_id);
 END IF;
 IF ( in_view = 'N' || in_view = 'D' || in_view = 'DP' ) THEN
       SET @sql = CONCAT(@sql," AND parcel_at_gate.status ='",in_view,"'");
 END IF;
 IF ( in_created_at is not null ) THEN
       SET @sql = CONCAT(@sql," AND date(parcel_at_gate.created_at) ='",in_created_at,"'");
 END IF;
 IF ( in_delivered_at is not null ) THEN
       SET @sql = CONCAT(@sql," AND date(parcel_at_gate.delivered_at) ='",in_delivered_at,"'");
 END IF;
 IF ( in_disposed_at is not null ) THEN
       SET @sql = CONCAT(@sql," AND date(parcel_at_gate.disposed_at) ='",in_disposed_at,"'");
 END IF;
 SET @sql = CONCAT(@sql,' order by parcel_at_gate.booking_id desc');
IF ( in_limit > 0 ) THEN
       SET @sql = CONCAT(@sql,' limit 0,',in_limit);
 END IF;
  
#SELECT @sql;    
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END


ALTER TABLE `feature_packs` CHANGE `is_academic_required` `isAcademicRequired` TINYINT(1) NOT NULL DEFAULT '0', CHANGE `is_professional_required` `isProfessionalRequired` TINYINT(1) NOT NULL DEFAULT '0', CHANGE `is_checkin_required` `isCheckinRequired` TINYINT(1) NOT NULL DEFAULT '0';

ALTER TABLE `feature_packs` CHANGE `isAcademicRequired` `isAcademicDetailsRequired` TINYINT(1) NOT NULL DEFAULT '0', CHANGE `isProfessionalRequired` `isProfessionalDetailsRequired` TINYINT(1) NOT NULL DEFAULT '0';

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES (NULL, 'CHECKOUT', 'is_inventory_check_required', 'Inventory checking while resident is checking out ', 'boolean', '0', '1', '2024-01-15 13:14:02');
ALTER TABLE `complainants` ADD `isCheckedIn` TINYINT(1) NOT NULL DEFAULT '0' AFTER `is_activated`;


ALTER TABLE `resident_residences` CHANGE `booked_tilled_date` `booked_up_to` DATETIME NULL DEFAULT NULL;
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES (NULL, 'LEAVE', 'is_missing_academic', 'show missing academic checkbox on user side', 'boolean', '1', '1', '2024-01-15 15:45:42');


ALTER TABLE `gates` CHANGE `building_id` `building_id` VARCHAR(90) NULL DEFAULT NULL;

ALTER TABLE `gates` CHANGE `is_active` `is_active` TINYINT(1) NOT NULL DEFAULT '0';

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`)
 VALUES (NULL, 'LEAVE', 'roles_allowed_for_leave_action', 'Only these roles are allowed to take action on leave request.', 'string', '2,3,5,6,7', '1', '2024-01-15 15:45:42');

 INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
 VALUES (NULL, 'DAYOUT', 'roles_allowed_for_day_out_action', 'Only these roles are allowed to take action on day out request', 'string', '2,3,5,6,7', '1', '2024-01-15 15:45:42');

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'LEAVE', 'roles_allowed_for_leave_view', 'Only these roles are allowed to view leave request.', 'string', '2,3,5,6,7', '1', '2024-01-15 15:45:42');

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'DAYOUT', 'roles_allowed_for_day_out__action', 'Only these roles are allowed to view day out request.', 'string', '2,3,5,6,7', '1', '2024-01-15 15:45:42');


UPDATE `complain_subjects` SET `subject_eng` = 'Need carpenter' WHERE `complain_subjects`.`subject_id` = 67;

ALTER VIEW `resident_academic_view`
 AS select `resident_academic_details`.`resident_id` AS `resident_id`,`resident_academic_details`.`reference_number` AS `reference_number`,`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`.`course` AS `course`,`academic_courses`.`course_name` AS `course_name`,`academic_course_branches`.`branch_name` AS `branch_name`,academic_course_branches.head_user_id 
from `resident_academic_details` 
join `academic_courses` on(`academic_courses`.`id` = `resident_academic_details`.`course`) 
join `academic_course_branches` on(`academic_course_branches`.`branch_id` = `resident_academic_details`.`branch`);

ALTER TABLE `leave_requests` CHANGE `approver_comment` `approver_comment` VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;


INSERT INTO `role` (`role_id`, `role_parent_id`, `role_name`, `Alias`, `isManagement`) VALUES ('13', '0', 'Mentor', 'Tutor Mentor', '1');
UPDATE role set Alias=null
 

DROP PROCEDURE `getRole`;
CREATE PROCEDURE `getRole`() NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN SELECT `role_id`,IF(Alias IS NULL ,`role_name`,`Alias`) as role_name FROM `role` WHERE role.isManagement=1; END



  ALTER TABLE `residence_academic_reservation` CHANGE `year` `course_year` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;

  /*20-02-2024*/

ALTER TABLE `residence_academic_reservation` ADD `isActive` ENUM('1','0') NOT NULL DEFAULT '1' AFTER `reservation_type`;

CREATE TABLE `residence_reservation_history` (
  `id` int(11) NOT NULL,
  `residence_id` int(11) NOT NULL,
  `course_year` varchar(50) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `branch` varchar(255) DEFAULT NULL,
  `gender` enum('B','M','F') NOT NULL DEFAULT 'B',
  `reservation_type` enum('Normal','Management','Divyang') NOT NULL DEFAULT 'Normal',
  `isActive` enum('1','0') NOT NULL DEFAULT '1',
  `created_by` int(12) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_by` int(12) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `residence_academic_reservation_temporary` (
  `id` int(11) NOT NULL,
  `residence_id` int(11) NOT NULL,
  `course_year` varchar(50) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `branch` varchar(255) DEFAULT NULL,
  `gender` enum('B','M','F') NOT NULL DEFAULT 'B',
  `reservation_type` enum('Normal','Management','Divyang') NOT NULL DEFAULT 'Normal',
  `isActive` enum('1','0') NOT NULL DEFAULT '1',
  `created_by` int(12) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_by` int(12) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  

create view res_academic_rev_tem_view as  SELECT residence_id,gender,reservation_type,created_by, 
GROUP_CONCAT(DISTINCT course) as course, GROUP_CONCAT(DISTINCT branch) as branch, GROUP_CONCAT(DISTINCT course_year)
           as course_year FROM residence_academic_reservation_temporary GROUP by residence_id


DROP PROCEDURE if EXISTS `res_academic_rev_temp`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `res_academic_rev_temp`()
 SELECT residence_id,gender,reservation_type,created_by, 
GROUP_CONCAT(DISTINCT course) as course, GROUP_CONCAT(DISTINCT branch) as branch, GROUP_CONCAT(DISTINCT course_year)
           as course_year FROM residence_academic_reservation_temporary GROUP by residence_id$$
DELIMITER ;

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'LEAVE', 'time_limit_to_apply_leave', 'Time limit to apply leave in hr', 'int', '0', '1', '2024-02-05 15:58:28');

ALTER TABLE `building_unit_types` ADD `air_conditioner` TINYINT(1) NOT NULL DEFAULT '0' AFTER `Description`;
UPDATE `community_charge_types` SET `charge_text` = 'Dining charges' WHERE `community_charge_types`.`charge_type_id` = 15;
UPDATE `community_charge_types` SET `charge_text` = 'Dining charges' WHERE `community_charge_types`.`charge_type_id` = 16;
UPDATE `community_charge_types` SET `default_charge` = '3500' WHERE `community_charge_types`.`charge_type_id` = 15;
ALTER TABLE `enquiry_details` ADD `company_name` VARCHAR(100) NULL DEFAULT NULL AFTER `branch`, ADD `designation` VARCHAR(100) NULL DEFAULT NULL AFTER `company_name`;

ALTER TABLE `leave_requests` ADD `maingate_exit_ack_by` INT NULL DEFAULT NULL AFTER `building_exit_date_time`;
ALTER TABLE `leave_requests` ADD `maingate_exit_at` DATE NULL DEFAULT NULL AFTER `maingate_exit_ack_by`;


ALTER TABLE `leave_requests` ADD `file_name` VARCHAR(255) NULL DEFAULT NULL AFTER `flag`, ADD `ext` VARCHAR(50) NULL DEFAULT NULL AFTER `file_name`;

ALTER TABLE `building` ADD `checkin_radious` INT(11) NOT NULL DEFAULT '0' AFTER `use_status`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateResidentPersonalDetails`(IN `in_resident_id` INT, IN `in_data` JSON, IN `in_profile_setting_id` VARCHAR(100), IN `in_emergency_name` VARCHAR(100), IN `in_emergency_contact` VARCHAR(100))
    NO SQL
BEGIN

INSERT INTO resident_personal_details(resident_id,personal_data,resident_profile_setting_id,emergency_contact_name,emergency_contact) VALUES (in_resident_id,in_data,profile_setting_id,emergency_name,emergency_contact)  ON DUPLICATE KEY UPDATE updated_at=now(),personal_data=in_data,resident_profile_setting_id=in_profile_setting_id,emergency_contact_name=in_emergency_name,emergency_contact=in_emergency_contact;


END$$
DELIMITER ;

ALTER TABLE `resident_kyc` CHANGE `id_type` `id_type` SMALLINT(6) NULL DEFAULT NULL;

ALTER TABLE `noticeboard` ADD `gender` ENUM('M','F','B') NOT NULL DEFAULT 'B' AFTER `recipient`;

create view view_profile_complted_in_perc AS select complainants.id as resident_id,doc_filled,resident_personal_details.resident_profile_setting_id as info_filled,
 TRIM(',' FROM CONCAT(COALESCE(`doc_filled`,''),',',COALESCE(resident_personal_details.resident_profile_setting_id,''),'')) as setting_ids ,
 round(( (LENGTH(TRIM(',' FROM CONCAT(COALESCE(`doc_filled`,''),',',COALESCE(resident_personal_details.resident_profile_setting_id,''),''))) -  LENGTH(REPLACE(TRIM(',' FROM CONCAT(COALESCE(`doc_filled`,''),',',COALESCE(resident_personal_details.resident_profile_setting_id,''),'')) ,",","")) + 1))/(SELECT COUNT(id) as total_required FROM `resident_profile_settings` WHERE isActive =1) *100) AS complete_in_per

 from complainants LEFT JOIN (SELECT GROUP_CONCAT(DISTINCT `resident_profile_setting_id`)as doc_filled,resident_id FROM resident_kyc GROUP BY `resident_id`) as kyc
on kyc.resident_id=complainants.id LEFT JOIN resident_personal_details ON resident_personal_details.resident_id=complainants.id 
WHERE (resident_personal_details.resident_profile_setting_id is not null OR kyc.doc_filled is not null);




INSERT INTO `complain_headers` (`header_id`, `header`, `header_hi`, `header_mr`, `header_ar`, `icon`, `opening_time`, `closing_time`) VALUES (NULL, 'Civil', 'Civil_hi', 'Civil_mr', 'Civil_ar', 'Civil.jpg', NULL, NULL);
INSERT INTO `complain_headers` (`header_id`, `header`, `header_hi`, `header_mr`, `header_ar`, `icon`, `opening_time`, `closing_time`) VALUES (NULL, 'Plumbing', 'Plumbing_hi', 'Plumbing_mr', 'Plumbing_ar', 'Plumbing.jpg', NULL, NULL);
INSERT INTO `complain_subjects` (`subject_id`, `subject_eng`, `subject_hindi`, `subject_mr`, `created_date`, `sequence`, `isActive`, `scope`, `isNotify`, `header_id`, `priority`)
 VALUES 
 (NULL, 'Broken Tiles', 'Broken Tiles', 'Broken Tiles', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2'),
(NULL, 'Door Threshold', 'Door Threshold', 'Door Threshold', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2'),
(NULL, 'Aluminum Grill', 'Aluminum Grill', 'Aluminum Grill', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2'),
(NULL, 'Mosquito Mesh', 'Mosquito Mesh', 'Mosquito Mesh', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2'),
(NULL, 'False ceiling', 'False ceiling', 'False ceiling', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2'),
(NULL, 'Window Sliding', 'Window Sliding', 'Window Sliding', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2'),
(NULL, 'Seepage in walls', 'Seepage in walls', 'Seepage in walls', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2'),
(NULL, 'Wall Plastering', 'Wall Plastering', 'Wall Plastering', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2'),
(NULL, 'Brick Work', 'Brick Work', 'Brick Work', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2'),
(NULL, 'Painting Work', 'Painting Work', 'Painting Work', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2'),
(NULL, 'Masson Works', 'Masson Works', 'Masson Works', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2');

INSERT INTO `complain_subjects` (`subject_id`, `subject_eng`, `subject_hindi`, `subject_mr`, `created_date`, `sequence`, `isActive`, `scope`, `isNotify`, `header_id`, `priority`)
 VALUES 

 (NULL, 'Door', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
 (NULL, 'Door Knob', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
 (NULL, 'Door Handel', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
 (NULL, 'Door Stopper', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
 (NULL, 'Main Door Frame Broken', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Bathroom Door Frame Broken', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Towerbolt/Chitkini', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Cupboard Door', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Cupboard Lock', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Cupboard Hinges', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Cupboard Handel', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Cupboard Key', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Curtain Rod', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Room Mirror', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Bath Mirror', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Study Table', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Lost Key', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Chair', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Bed', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2'),
(NULL, 'Others', '', '', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '13', '2');


INSERT INTO `complain_subjects` (`subject_id`, `subject_eng`, `subject_hindi`, `subject_mr`, `created_date`, `sequence`, `isActive`, `scope`, `isNotify`, `header_id`, `priority`)
 VALUES 
 (NULL, 'Wash Basin Block', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Health Faucet/Jet Spray Broken', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Tap Broken', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Tap Leakage', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Tap Handle Loose', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Tap Loose', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Shower ', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Wall Mixture Leakage', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Flush Problem', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'WC Seat cover Broken', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'WC Block', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'WC Leakage', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Bathroom Block', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Hot Water', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Drinking Water', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Bathwater', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Connection Pipe - Washbasin', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Connection Pipe - Health Faucet', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Connection Pipe - Geyser', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2'),
 (NULL, 'Others', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '19', '2');

 INSERT INTO `complain_subjects` (`subject_id`, `subject_eng`, `subject_hindi`, `subject_mr`, `created_date`, `sequence`, `isActive`, `scope`, `isNotify`, `header_id`, `priority`)
 VALUES 
 (NULL, 'Calling Bell', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Room Tube Light', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Fan', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Fan Regulator', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Switch', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Plug point', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Corridor Bulb/Tube', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Toilet Bulb/Tube', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Geyser', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Geyser Switch', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Exhaust Fan', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'AC', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'AC Switch', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'MCB', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Remote', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Water Cooler', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Lift', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2'),
 (NULL, 'Others', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '2', '2');

 INSERT INTO `department` (`department_id`, `department_name`, `department_name_HI`, `department_name_MR`, `department_head_name`, `department_head_mail`, `department_head_contact`, `isActive`, `isInternal`) 
 VALUES (NULL, 'Civil', NULL, NULL, NULL, NULL, NULL, '1', '0'),
   (NULL, 'Plumbing', NULL, NULL, NULL, NULL, NULL, '1', '0');

  INSERT INTO `complain_subjects` (`subject_id`, `subject_eng`, `subject_hindi`, `subject_mr`, `created_date`, `sequence`, `isActive`, `scope`, `isNotify`, `header_id`, `priority`) 
  VALUES (NULL, 'Others', 'Others', 'Others', '2020-11-21 12:13:27', '0', '1', 'Community', '1', '18', '2');

 INSERT INTO `department_subjects` (`map_id`, `dept_id`, `subject_id`, `isActive`) 
 VALUES 
(NULL, '18', '73', '1'),
(NULL, '18', '75', '1'),
(NULL, '18', '76', '1'),
(NULL, '18', '77', '1'),
(NULL, '18', '78', '1'),
(NULL, '18', '79', '1'),
(NULL, '18', '80', '1'),
(NULL, '18', '81', '1'),
(NULL, '18', '82', '1'),
(NULL, '18', '83', '1'),
(NULL, '18', '84', '1'),
(NULL, '18', '143', '1'),
(NULL, '19', '105', '1'),
(NULL, '19', '106', '1'),
(NULL, '19', '107', '1'),
(NULL, '19', '108', '1'),
(NULL, '19', '109', '1'),
(NULL, '19', '111', '1'),
(NULL, '19', '112', '1'),
(NULL, '19', '113', '1'),
(NULL, '19', '114', '1'),
(NULL, '19', '115', '1'),
(NULL, '19', '116', '1'),
(NULL, '19', '117', '1'),
(NULL, '19', '118', '1'),
(NULL, '19', '119', '1'),
(NULL, '19', '120', '1'),
(NULL, '19', '121', '1'),
(NULL, '19', '122', '1'),
(NULL, '19', '123', '1'),
(NULL, '19', '124', '1'),
(NULL, '13', '85', '1'),
(NULL, '13', '86', '1'),
(NULL, '13', '87', '1'),
(NULL, '13', '88', '1'),
(NULL, '13', '89', '1'),
(NULL, '13', '90', '1'),
(NULL, '13', '91', '1'),
(NULL, '13', '92', '1'),
(NULL, '13', '93', '1'),
(NULL, '13', '95', '1'),
(NULL, '13', '96', '1'),
(NULL, '13', '97', '1'),
(NULL, '13', '98', '1'),
(NULL, '13', '99', '1'),
(NULL, '13', '100', '1'),
(NULL, '13', '101', '1'),
(NULL, '13', '102', '1'),
(NULL, '13', '103', '1'),
(NULL, '13', '104', '1'),
(NULL, '9', '125', '1'),
(NULL, '9', '126', '1'),
(NULL, '9', '127', '1'),
(NULL, '9', '128', '1'),
(NULL, '9', '129', '1'),
(NULL, '9', '130', '1'),
(NULL, '9', '131', '1'),
(NULL, '9', '132', '1'),
(NULL, '9', '133', '1'),
(NULL, '9', '134', '1'),
(NULL, '9', '135', '1'),
(NULL, '9', '136', '1'),
(NULL, '9', '137', '1'),
(NULL, '9', '138', '1'),
(NULL, '9', '139', '1'),
(NULL, '9', '140', '1'),
(NULL, '9', '141', '1'),
(NULL, '9', '142', '1')




DELETE FROM department WHERE `department`.`department_id` = 19


INSERT INTO `complain_headers` (`header_id`, `header`, `header_hi`, `header_mr`, `header_ar`, `icon`, `opening_time`, `closing_time`) VALUES (NULL, 'IT', 'IT_hi', 'IT_mr', 'IT_ar', 'It.jpg', NULL, NULL);

INSERT INTO `complain_subjects` (`subject_id`, `subject_eng`, `subject_hindi`, `subject_mr`, `created_date`, `sequence`, `isActive`, `scope`, `isNotify`, `header_id`, `priority`)
 VALUES 
 (NULL, 'Wifi Issue', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '20', '2'),
(NULL, 'LAN Issue', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '20', '2'),
(NULL, 'Email ID password reset', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '20', '2'),
(NULL, 'Internet ID password reset', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '20', '2'),
(NULL, 'Biometrics punch not working', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '20', '2'),
(NULL, 'MESS card not working', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '20', '2'),
(NULL, 'Others', '', '', '2020-11-21 12:19:27', '0', '1', 'Community', '1', '20', '2')


INSERT INTO `department_subjects` (`map_id`, `dept_id`, `subject_id`, `isActive`) 
 VALUES 
(NULL, '8', '144', '1'),
(NULL, '8', '145', '1'),
(NULL, '8', '146', '1'),
(NULL, '8', '147', '1'),
(NULL, '8', '148', '1'),
(NULL, '8', '149', '1'),
(NULL, '8', '150', '1'),



ALTER TABLE `inventories` ADD UNIQUE( `label`);

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES (NULL, 'ACADEMIC', 'registration_no_alias', 'Registration no alias', 'string', 'Admission No', '1', '2024-03-06 11:55:32');

CREATE TABLE `leave_settings` (
  `id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `branch_id` int(11) NOT NULL,
  `course_year` int(11) NOT NULL,
  `gender` enum('0','1','ALL') NOT NULL,
  `is_warden_approval_required` int(11) DEFAULT 1,
  `is_parent_approval_required` int(11) NOT NULL DEFAULT 1,
  `is_edu_head_approval_required` int(11) NOT NULL DEFAULT 0,
  `warden_id` varchar(255) DEFAULT NULL COMMENT 'coma separated multiple ids ',
  `edu_head_id` varchar(255) DEFAULT NULL COMMENT 'coma separated multiple ids ',
  `is_notification_to_parent` int(11) NOT NULL DEFAULT 1,
  `created_by` 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;


CREATE TABLE `resident_leave_approval_mandatories` (
  `id` int(11) NOT NULL,
  `resident_id` int(11) 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 `building` CHANGE `checkin_radious` `checkin_radius` INT(11) NOT NULL DEFAULT '0';


ALTER TABLE `leave_settings` CHANGE `branch_id` `branch_id` INT(11) NULL DEFAULT NULL;


CREATE view  resident_leave_approval_settings_view
as 
SELECT resident_id,rad.course,rad.branch,rad.course_year,rad.registration_no,ls.is_warden_approval_required,ls.is_parent_approval_required,ls.is_notification_to_parent,ls.is_edu_head_approval_required,ls.approver_ids FROM `resident_academic_details` rad LEFT JOIN  leave_settings as ls on 
((rad.branch=ls.branch_id or rad.branch is null) and 
 (rad.course=ls.course_id ) and
 (rad.course_year=ls.course_year )
)
where ls.isActive=1;


ALTER TABLE `residence_academic_reservation` DROP INDEX `residence_id`;

ALTER TABLE `student_unit_type_bookings` DROP `residence_id`;


INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'NOTICEBOARD', 'Is_notice_skip_allowed', 'Is notice skip allowed', 'boolen', '1', '1', '2024-03-06 11:55:32');
INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
VALUES (NULL, 'ACADEMIC', 'is_registration_no_mandatory', 'Registration no', 'boolean', '1', '1', '2024-03-06 11:55:32');


ALTER TABLE `resident_academic_details` CHANGE `registration_no` `registration_no` VARCHAR(150) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;
DROP PROCEDURE IF EXISTS InsertBuilding; 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertBuilding`(IN `in_name` VARCHAR(100), IN `in_cordinates` VARCHAR(100), IN `in_floor` TINYINT, IN `in_unit_types` VARCHAR(100), IN `in_address` VARCHAR(255), IN `in_checkin_radious` INT, OUT `building_id` INT)
BEGIN
INSERT INTO `building`(name,cordinates,floor_count,unit_types,address,checkin_radious) VALUES (in_name,in_cordinates,in_floor,in_unit_types,in_address,in_checkin_radious);
SET building_id = LAST_INSERT_ID();
END$$
DELIMITER ;


ALTER  VIEW `complainant_view`  AS 
SELECT `complainants`.`id` AS `id`, `complainants`.`complainant_name` AS `complainant_name`, `complainants`.`contact_no` AS `contact_no`, 
`complainants`.`phone_prefix` AS `phone_prefix`, `complainants`.`email` AS `email`, `complainants`.`adhaar_no` AS `adhaar_no`, 
`complainants`.`ration_number` AS `ration_number`, `complainants`.`gender` AS `gender`, `complainants`.`language` AS `language`, 
`complainants`.`divyaang` AS `divyaang`, `resident_residences`.`residence_id` AS `residence_id`, `resident_residences`.`is_checked_in` AS `is_checked_in`, `complainants`.`is_activated` AS `is_activated`, 
`complainants`.`created_at` AS `created_at`, `creator`.`name` AS `created_by_name`, `complainants`.`updated_at` AS `updated_at`, `complainants`.`file` AS `file`, `complainants`.`ext` AS `ext`,
 `complainants`.`isContactDetailsPublic` AS `isContactDetailsPublic`, `complainants`.`created_by` AS `created_by`, `resident_residences`.`resident_status` AS `resident_status`, 
 `resident_residences`.`living_start_at` AS `living_start_at`, `complainants`.`isTest` AS `isTest`, `complainants`.`isFake` AS `isFake`, `complainants`.`approved_at` AS `approved_at`, 
 `complainants`.`approved_by` AS `approved_by`, `approver`.`name` AS `approved_by_name`, `complainants`.`disabled_at` AS `disabled_at`, `complainants`.`disabled_by` AS `disabled_by`, 
 `remover`.`name` AS `disabled_by_name`, `residence_status`.`residence_status_text` AS `residence_status_text`, `building_residence`.`apartment_name` AS `apartment_name`, 
 `building_residence`.`unit_type` AS `unit_type`, `building_residence`.`floor` AS `floor`, `building_unit_types`.`Description` AS `unit_name`, `building`.`name` AS `building_name`, 
 `building`.`cordinates` AS `building_cordinates`, `building`.`checkin_radius` AS `building_checkin_radius`, 
 `building_residence`.`building_id` AS `building_id` FROM ((((((((`complainants` left join `resident_residences` on(`resident_residences`.`resident_id` = `complainants`.`id` 
 	and `resident_residences`.`isActive` = 1)) left join `building_residence` on(`resident_residences`.`residence_id` = `building_residence`.`id`)) 
 left join `building_unit_types` on(`building_residence`.`unit_type` = `building_unit_types`.`unit_type_id`)) 
 left join `building` on(`building`.`id` = `building_residence`.`building_id`)) 
 left join `residence_status` on(`residence_status`.`residence_status_id` = `resident_residences`.`resident_status`)) 
 left join `user` `creator` on(`complainants`.`created_by` = `creator`.`id`)) left join `user` `approver` on(`complainants`.`approved_by` = `approver`.`id`)) 
 left join `user` `remover` on(`complainants`.`disabled_by` = `remover`.`id`)) ;

 INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES (NULL, 'ACADEMIC', 'Is_academic_skip_allowed', 'Is academic skip allowed', 'boolean', '0', '1', '2024-03-06 11:55:32');

 UPDATE `msg_templates` SET `template` = 'Dear team, please check the Complain no %complaint_no$s from %complainant_name$s-%complainant_contact$s about %complaint_subject$s', `anonymous_template` = 'Dear team, please check the request no %complaint_no$s about %complaint_subject$s', `template_hi` = 'Dear team, please check the Complain no %complaint_no$s from %complainant_name$s-%complainant_contact$s about %complaint_subject$s ' WHERE `msg_templates`.`template_id` = 3;

 ALTER TABLE `complain_headers` ADD `isActive` TINYINT(1) NOT NULL DEFAULT '1' AFTER `closing_time`;


 INSERT INTO `department_subjects` (`map_id`, `dept_id`, `subject_id`, `isActive`) 
 VALUES 
(NULL, '1', '150', '1'),
(NULL, '1', '151', '1'),
(NULL, '1', '152', '1'),
(NULL, '1', '153', '1'),
(NULL, '1', '154', '1'),
(NULL, '1', '155', '1'),
(NULL, '1', '156', '1'),
(NULL, '1', '157', '1'),
(NULL, '1', '158', '1'),

DROP PROCEDURE if EXISTS `InsertBuilding`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertBuilding`(IN `in_name` VARCHAR(100), IN `in_cordinates` VARCHAR(100), IN `in_floor` TINYINT, IN `in_unit_types` VARCHAR(100), IN `in_address` VARCHAR(255), IN `in_checkin_radious` INT, OUT `building_id` INT)
BEGIN
INSERT INTO `building`(name,cordinates,floor_count,unit_types,address,checkin_radious) VALUES (in_name,in_cordinates,in_floor,in_unit_types,in_address,in_checkin_radious);
SET building_id = LAST_INSERT_ID();
END$$
DELIMITER ;

RENAME TABLE `fretbox_hosteltest`.`checkout_users` TO `fretbox_hosteltest`.`resident_checkout_requests`;
 RENAME TABLE `fretbox_hosteltest`.`check_in_users` TO `fretbox_hosteltest`.`resident_checked_in`;

ALTER TABLE `resident_checkout_requests` ADD `inventory_verify_by` INT NOT NULL DEFAULT '0' AFTER `is_damage_inventory`;
ALTER TABLE `resident_checkout_requests` CHANGE `inventory_verify_by` `inventory_verify_by` INT(11) NULL DEFAULT NULL;

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`)
 VALUES (NULL, 'COMPLAINT', 'roles_can_edit_complaint_slots', 'Roles can edit Complaint slots', 'string', '1,2,3,8', '1', '2024-03-15 00:12:47');

INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
 VALUES (NULL, 'CHECKOUT', 'is_checkin_required', 'Residents needs to check in with inventory', 'boolean', '0', '1', '2024-01-15 13:14:02');

  


 ALTER TABLE `complainants` CHANGE `is_activated` `is_activated` ENUM('A','I','S','AC','FC') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'I';

 ALTER TABLE `student_enquiry` CHANGE `status` `status` ENUM('Pending','Allotted','Cancelled','Booked') 
 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Pending';


 INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`)
  VALUES (NULL, 'LEAVE', 'extension_allowed_before_leave_end', 'students should leave extension before leave ends ', 'string',
   '1', '1', '2024-02-05 15:58:28');
   ALTER TABLE `regular_night_out` ADD `reason` VARCHAR(255) NULL DEFAULT NULL AFTER `rejected_by`;


DROP PROCEDURE IF EXISTS `GetComplainantByEmail`
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainantByEmail`(IN `email` VARCHAR(100))
BEGIN
SELECT * from complainant_view
where complainant_view.email=email
ORDER by complainant_view.id DESC limit 0,1;
END$$
DELIMITER ;

ALTER TABLE `complainants` CHANGE `contact_no` `contact_no` BIGINT(10) NULL DEFAULT NULL;
ALTER TABLE `user` CHANGE `phoneNumber` `phoneNumber` BIGINT(20) NULL DEFAULT NULL;

DROP PROCEDURE IF EXISTS `InsertComplainant`; 
CREATE DEFINER=`root`@`localhost`
 PROCEDURE `InsertComplainant`(IN `complainantName` VARCHAR(70),
  IN `contactNo` BIGINT(11), IN `email` VARCHAR(70), IN `adhaarNo` BIGINT(20), IN `gender` VARCHAR(1), IN `divyaang` VARCHAR(1), 
  IN `in_residence_id` INT(8), IN `in_language` ENUM('en','hi','mr'), IN `in_resident_status` TINYINT(2), IN `in_user` INT, 
  OUT `in_phone_prefix` INT(3), OUT `complainantid` BIGINT) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 
  
  BEGIN INSERT INTO `complainants`(complainant_name,contact_no,email,adhaar_no,gender,Is_activated,divyaang,language,created_by,phone_prefix) V
  ALUES(complainantName,contactNo,email,adhaarNo,gender,'I',divyaang,in_language,in_user,in_phone_prefix); 
  SET complainantid = LAST_INSERT_ID(); 
  IF residence_id >0 THEN 
    INSERT into resident_residences (resident_id,residence_id,resident_status) 
    values(complainantid,in_residence_id,in_resident_status); 
  END IF; 
  END

 ALTER TABLE `regular_night_out` CHANGE `status` `status` ENUM('Initiated','Pending','Approved','Rejected','Canceled') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Initiated';

 DROP PROCEDURE IF EXISTS  `getResidentProfile`;
 DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentProfile`(IN `in_user_id` INT)
    NO SQL
BEGIN
set @resident_id=(SELECT user.relatedId FROM user where user.id=in_user_id);
SELECT complainant_view.id as resident_id,complainant_view.complainant_name,complainant_view.contact_no,
complainant_view.email,complainant_view.adhaar_no,complainant_view.gender,complainant_view.divyaang,
complainant_view.resident_status,complainant_view.residence_status_text,complainant_view.residence_id,
complainant_view.isContactDetailsPublic,complainant_view.apartment_name,complainant_view.building_name,
complainant_view.created_at,user.language,user.file,user.ext from complainant_view 
left join user on user.id=in_user_id
where complainant_view.id=@resident_id;


set @complain_count=(SELECT COUNT(complaints.complain_id) from complaints where complaints.complainant_id=@resident_id);
set @booking_count=(SELECT count(common_facility_booking.id) from common_facility_booking where common_facility_booking.resident_id=@resident_id);

set @suggestion_count=(SELECT count(suggestion.suggestion_id) from suggestion where suggestion.suggestion_id=@resident_id);

set @gate_pass_count=(SELECT COUNT(gate_pass.id) from gate_pass WHERE gate_pass.created_by=in_user_id);

SELECT @complain_count as total_complaints,@booking_count as total_bookings,@suggestion_count as total_suggestions,@gate_pass_count as total_gate_pass;

END$$
DELIMITER ;

ALTER TABLE `residence_academic_reservation` ADD UNIQUE( `sam_id`, `abs_id`);

DROP PROCEDURE IF EXISTS `InsertComplainant`;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertComplainant`(
    IN `complainantName` VARCHAR(70), 
    IN `contactNo` BIGINT(11), 
    IN `email` VARCHAR(70), 
    IN `adhaarNo` BIGINT(20), 
    IN `gender` VARCHAR(1), 
    IN `divyaang` VARCHAR(1),
    IN `in_residence_id` INT(8), 
    IN `in_language` ENUM('en','hi','mr'), 
    IN `in_resident_status` TINYINT(2), 
    IN `in_user` INT, 
    IN `in_living_end` DATETIME,
    OUT `in_phone_prefix` INT(3), 
    OUT `complainantid` BIGINT
)
BEGIN 
    INSERT INTO `complainants`(
        complainant_name,
        contact_no,
        email,
        adhaar_no,
        gender,
        Is_activated,
        divyaang,
        language,
        created_by,
        phone_prefix
    ) VALUES (
        complainantName,
        contactNo,
        email,
        adhaarNo,
        gender,
        'I',
        divyaang,
        in_language,
        in_user,
        in_phone_prefix
    ); 
    
    SET complainantid = LAST_INSERT_ID();

    IF in_residence_id > 0 THEN 
        INSERT INTO resident_residences (
            resident_id,
            residence_id,
            resident_status,
            booked_up_to
        ) VALUES (
            complainantid,
            in_residence_id,
            in_resident_status,
            in_living_end
        );
    END IF; 
END$$

DELIMITER ;


DROP PROCEDURE IF EXISTS `GetUserByID`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetUserByID`(IN `userid` INT)
BEGIN
SELECT u.id,u.userID,u.email,u.name,u.phoneNumber,u.isAdminUser,u.userRole,u.relatedId,u.language,u.isActive,
r.role_name 
FROM `user` u LEFT JOIN `role` r 
ON ( u.userRole =r.role_id) 
WHERE u.id=userid;
END$$
DELIMITER ;








-------
ALTER TABLE `regular_night_out` CHANGE `status` `status` ENUM('Initiated','Pending','Approved','Rejected','Canceled') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Pending';

ALTER TABLE `building_residence` ADD `updated_at` DATETIME on update CURRENT_TIMESTAMP NULL DEFAULT NULL AFTER `created_at`;

ALTER TABLE `building_residence` ADD `is_reserved` TINYINT(1) NOT NULL DEFAULT '0' AFTER `occupied_by`;


DROP PROCEDURE IF EXISTS `getNoticeBoard`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getNoticeBoard`(IN `in_type` ENUM('all','community','building','academic','residence','resident'), IN `in_reference_id` BIGINT, IN `from_dt` DATE, IN `to_dt` DATE)
    NO SQL
BEGIN
if(in_type='all') then
SELECT noticeboard.*,COUNT(notice_board_file.id) as files_count,user.name as publisher,role.role_name,building.name as building_name,building.id as building_id,building_residence.apartment_name,building_residence.id as residence_id from noticeboard


left join building_residence on (noticeboard.recipient='residence' and noticeboard.relatedid=building_residence.id)
LEFT join building on ((noticeboard.recipient='building' and noticeboard.relatedid=building.id) or (noticeboard.recipient='residence' and building_residence.building_id=building.id))
inner join user on user.id=noticeboard.published_by
LEFT join role on role.role_id=user.userRole
LEFT join notice_board_file on notice_board_file.notice_id=noticeboard.id
where noticeboard.publish_at is null OR date(noticeboard.created_at) BETWEEN from_dt and to_dt  
GROUP by noticeboard.id
order by noticeboard.id desc;

elseif(in_type='building') then
	SELECT noticeboard.*,COUNT(notice_board_file.id) as files_count,user.name as publisher,role.role_name,building.name as building_name,building.id as building_id,building_residence.apartment_name,building_residence.id as residence_id from noticeboard
	LEFT join building on ((noticeboard.recipient='building' and noticeboard.relatedid=building.id))
	left join building_residence on building_residence.building_id=building.id
	inner join user on user.id=noticeboard.published_by
	LEFT join role on role.role_id=user.userRole
	LEFT join notice_board_file on notice_board_file.notice_id=noticeboard.id
	where noticeboard.publish_at is null OR date(noticeboard.created_at) 
	BETWEEN from_dt and to_dt  
	GROUP by noticeboard.id
	order by noticeboard.id desc;

elseif(in_type='resident') then
	set @resident_id=(SELECT user.relatedId from user where user.id=in_reference_id);
	
    select complainant_view.residence_id,complainant_view.gender,complainant_view.resident_status,complainant_view.building_id into @residence_id,@gender,@resident_status,@building_id from complainant_view 	WHERE complainant_view.id=@resident_id;
	

	SELECT noticeboard.id,noticeboard.subject,noticeboard.body,noticeboard.created_at,noticeboard.updated_at,noticeboard.event_at,noticeboard.recipient, ifnull(JSON_EXISTS(noticeboard.read_by, CONCAT('$.',in_reference_id)),0) as isRead,user.name as publisher,role.role_name,COUNT(notice_board_file.id) as files_count from noticeboard
	inner join user on user.id=noticeboard.published_by
    LEFT join role on role.role_id=user.userRole
    LEFT join notice_board_file on notice_board_file.notice_id=noticeboard.id
	WHERE noticeboard.stage='published' and DATEDIFF(CURRENT_TIMESTAMP,noticeboard.publish_at)<21 and (recipient='community' or (recipient='tenant' and @resident_status in(3,5,6))  or (recipient='building' and noticeboard.relatedid=@building_id) or (recipient='residence' and noticeboard.relatedid=@residence_id) or (recipient='resident' and @gender='b')) 
    group by noticeboard.id
    order by noticeboard.id desc;
elseif(in_type='community') then
	
	SELECT noticeboard.id,noticeboard.subject,noticeboard.body,noticeboard.created_at,noticeboard.updated_at,noticeboard.event_at,noticeboard.recipient, ifnull(JSON_EXISTS(noticeboard.read_by, CONCAT('$.',in_reference_id)),0) as isRead,user.name as publisher,role.role_name,COUNT(notice_board_file.id) as files_count from noticeboard
	inner join user on user.id=noticeboard.published_by
    LEFT join role on role.role_id=user.userRole
    LEFT join notice_board_file on notice_board_file.notice_id=noticeboard.id
	WHERE noticeboard.stage='published' and recipient='community'  
    and date(noticeboard.created_at) BETWEEN from_dt and to_dt 
    group by noticeboard.id
    order by noticeboard.id desc;
end if;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `GetComplainants`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetComplainants`(IN `cid` BIGINT(11), IN `isResident` TINYINT(1))
BEGIN

SET @sql ='SELECT complainant_view.*,IFNULL(complete_in_per,0) as profile_complete,json_extract(resident_personal_details.personal_data, "$.parents") as parents,json_extract(resident_personal_details.personal_data, "$.blood_group") as blood_group,json_extract(resident_personal_details.personal_data, "$.dob") as dob,json_extract(resident_personal_details.personal_data, "$.local_guardians") as local_guardians from complainant_view
		left join resident_personal_details on resident_personal_details.resident_id=complainant_view.id
		LEFT JOIN (select complainants.id as resident_id,
		round(( (LENGTH(TRIM("," FROM CONCAT(COALESCE(`doc_filled`,""),",",COALESCE(resident_personal_details.resident_profile_setting_id,""),""))) -  LENGTH(REPLACE(TRIM("," FROM CONCAT(COALESCE(`doc_filled`,""),",",COALESCE(resident_personal_details.resident_profile_setting_id,""),"")) ,",","")) + 1))/(SELECT COUNT(id) as total_required FROM `resident_profile_settings` WHERE isActive =1) *100) AS complete_in_per
		from complainants LEFT JOIN (SELECT GROUP_CONCAT(DISTINCT `resident_profile_setting_id`)as doc_filled,resident_id FROM resident_kyc GROUP BY `resident_id`) as kyc
		on kyc.resident_id=complainants.id LEFT JOIN resident_personal_details ON resident_personal_details.resident_id=complainants.id 
		WHERE (resident_personal_details.resident_profile_setting_id is not null OR kyc.doc_filled is not null)) as pp
		on complainant_view.id=pp.resident_id
		WHERE complainant_view.isTest=0 ';
		
		IF (isResident=1) THEN
			SET @sql = CONCAT(@sql, ' and complainant_view.building_id is not null and complainant_view.apartment_name is not null');
		elseIF (isResident=0) THEN
			SET @sql = CONCAT(@sql, ' and complainant_view.building_id is  null and complainant_view.apartment_name is  null');	
		END IF;


	    IF (cid > 0) THEN
	    	SET @sql = CONCAT(@sql, ' AND complainant_view.id = ', cid);
	    END IF;

		SET @sql = CONCAT(@sql, ' ORDER BY complainant_view.created_at DESC');

	   -- SELECT @sql; -- Uncomment this line for debugging

	    PREPARE stmt FROM @sql;
	    EXECUTE stmt;
	    DEALLOCATE PREPARE stmt;


	END$$
DELIMITER ;


DROP PROCEDURE IF EXISTS `getResidentKycOverView`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getResidentKycOverView`(IN `building` INT(11))
BEGIN
SELECT
    complainant_view.id AS resident_id,
    complainant_name AS name,
    contact_no,
    complainant_view.building_name,
    complainant_view.apartment_name,
    p_doc.uplaoded_doc_id,
    uplaoded_doc_name,
    p_doc.uplaoded_doc_file_name
FROM
    complainant_view
LEFT JOIN (
    SELECT
        resident_id,
        GROUP_CONCAT(resident_kyc.resident_profile_setting_id) AS uplaoded_doc_id,
   		 GROUP_CONCAT(resident_profile_settings.name) AS uplaoded_doc_name,
     	GROUP_CONCAT(resident_kyc.file_name) AS uplaoded_doc_file_name
    FROM
        resident_kyc
    INNER JOIN
        resident_profile_settings ON resident_profile_settings.id = resident_kyc.resident_profile_setting_id
    WHERE
        resident_profile_settings.type = 'doc'
    GROUP BY
        resident_id
) AS p_doc ON complainant_view.id = p_doc.resident_id;

END$$
DELIMITER ;


ALTER VIEW `resident_academic_view`  AS

 SELECT `resident_academic_details`.`resident_id` AS `resident_id`, `complainants`.`contact_no` AS `contactNo`, `complainants`.`email` AS `email`, 
 `resident_academic_details`.`reference_number` 
 AS `reference_number`, `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`, `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` 
 FROM (((`resident_academic_details` join `complainants` on(`complainants`.`id` = `resident_academic_details`.`resident_id`)) 
 	join `academic_courses` on(`academic_courses`.`id` = `resident_academic_details`.`course`)) join `academic_course_branches` 
 on(`academic_course_branches`.`branch_id` = `resident_academic_details`.`branch`)) ;

 INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES (NULL, 'LEAVE', 'is_parents_approval_required', 'Send sms to parents on student arrival', 'boolean', '1', '1', '2024-01-15 15:45:42');
 INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES (NULL, 'GATE', 'is_gate_entry_exit_qr_show', 'gate entry exit qr show', 'boolean', '0', '1', '2023-12-02 21:30:22');

 INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES (NULL, 'ALLOTMENT', 'is_allotment_open', 'Is allotment open', 'boolean', '0', '1', '2024-03-19 16:48:25');

 ALTER TABLE `feature_packs` ADD `IsStudentDayPass` TINYINT(1) NOT NULL DEFAULT '1' AFTER `IsStudentLeave`, ADD `IsStudentNightPass` TINYINT(1) NOT NULL DEFAULT '1' AFTER `IsStudentDayPass`;
 ALTER TABLE `feature_packs` CHANGE `IsStudentLeave` `isStudentLeave` TINYINT(1) NOT NULL DEFAULT '1', CHANGE `IsStudentDayPass` `isStudentDayPass` TINYINT(1) NOT NULL DEFAULT '1', CHANGE `IsStudentNightPass` `isStudentNightPass` TINYINT(1) NOT NULL DEFAULT '1';

 INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) 
 VALUES (NULL, 'LABEL', 'ResidentLabel', 'What should we call resident in our property', 'string', 'Student', '1', '2024-03-19 16:48:25');



 ALTER TABLE `building_residence` ADD `is_reserved` TINYINT(1) NOT NULL DEFAULT '0' AFTER `occupied_by`;
 ALTER TABLE `department_team` CHANGE `working_area` `working_area` VARCHAR(150) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;


 INSERT INTO `settings` (`id`, `feature`, `rule_name`, `description`, `rule_type`, `rule_value`, `updated_by`, `updated_at`) VALUES 
 (NULL, 'ACADEMIC', 'Is_academic_student_edit_allowed', 'Is academic skip allowed', 'boolean', '0', '1', '2024-03-19 19:19:15');

 ALTER TABLE `building_unit_types` CHANGE `size` `size` INT(11) NOT NULL DEFAULT '1';


 ALTER TABLE `allotment_booking_transactions` CHANGE `status` `status` ENUM('INITIATED','PENDING','COMPLETED','CANCELLED') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'INITIATED';

 ALTER TABLE `inventory_categories` CHANGE `status` `status` TINYINT(1) NOT NULL DEFAULT '1';

 ALTER TABLE `feature_packs` ADD `indisciplinaryAction` TINYINT(0) NOT NULL AFTER `isHostelAllotment`;


14 june

ALTER TABLE `feedback_forms` CHANGE `updated_by` `updated_by` INT NULL DEFAULT NULL;
ALTER TABLE `feedback_forms` CHANGE `created_by` `created_by` INT NULL DEFAULT NULL;

ALTER TABLE `feedback_forms` CHANGE `created_at` `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `feedback_forms` CHANGE `updated_at` `updated_at` TIMESTAMP on update CURRENT_TIMESTAMP NULL DEFAULT NULL;


ALTER  VIEW `active_resident_view`  AS 

SELECT 
    `complainants`.`id` AS `id`, 
    `complainants`.`complainant_name` AS `complainant_name`, 
    `complainants`.`contact_no` AS `contact_no`, 
    `complainants`.`email` AS `email`,
    `complainants`.`file` AS `file`, 
    `complainants`.`ext` AS `ext`, 
    `complainants`.`gender` AS `gender`, 
    `complainants`.`divyaang` AS `divyaang`, 
    `complainants`.`created_at` AS `created_at`, 
    `complainants`.`is_pending_dues` AS `is_pending_dues`, 
    `resident_academic_details`.`reference_number` AS `reference_number`, 
    `resident_academic_details`.`registration_no` AS `registration_no`, 
    `resident_academic_details`.`course_year` AS `course_year`,
    `resident_academic_details`.`branch` AS `branch_id`, 
    `resident_academic_details`.`cgpa` AS `cgpa`, 
    `resident_academic_details`.`course` AS `course_id`, 
    `academic_courses`.`course_name` AS `course_name`, 
    `academic_course_branches`.`branch_name` AS `branch_name`, 
    `resident_personal_details`.`personal_data` AS `personal_data`, 
    json_unquote(json_extract(`resident_personal_details`.`personal_data`, '$.dob')) AS `dob`, 
    json_unquote(json_extract(`resident_personal_details`.`personal_data`, '$.parents[0].address')) AS `parent_address`, 
    json_unquote(json_extract(`resident_personal_details`.`personal_data`, '$.parents[0].name')) AS `parent_name`, 
    json_unquote(json_extract(`resident_personal_details`.`personal_data`, '$.parents[0].phone_number')) AS `parent_phone_number`, 
    json_unquote(json_extract(`resident_personal_details`.`personal_data`, '$.parents[0].email')) AS `parent_email`,
    `complainants`.`created_by` AS `created_by`,
    `resident_residences`.`resident_status` AS `resident_status`, 
    `resident_residences`.`living_start_at` AS `living_start_at`, 
    `resident_residences`.`residence_id` AS `residence_id`,
    `complainants`.`approved_at` AS `approved_at`, 
    `complainants`.`approved_by` AS `approved_by`, 
    `residence_status`.`residence_status_text` AS `residence_status_text`,
    `building_residence`.`apartment_name` AS `apartment_name`, 
    `building_residence`.`unit_type` AS `unit_type`,
    `building_residence`.`floor` AS `floor`,
    `building_unit_types`.`Description` AS `unit_name`, 
    `building`.`name` AS `building_name`, 
    `building_residence`.`building_id` AS `building_id`
FROM 
    `complainants`
    INNER JOIN `resident_residences` ON `complainants`.`id` = `resident_residences`.`resident_id`
    LEFT JOIN `resident_academic_details` ON `complainants`.`id` = `resident_academic_details`.`resident_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 `building_residence` ON `resident_residences`.`residence_id` = `building_residence`.`id`
    LEFT JOIN `building_unit_types` ON `building_residence`.`unit_type` = `building_unit_types`.`unit_type_id`
    LEFT JOIN `building` ON `building`.`id` = `building_residence`.`building_id`
    LEFT JOIN `residence_status` ON `residence_status`.`residence_status_id` = `resident_residences`.`resident_status`
    LEFT JOIN `resident_personal_details` ON `complainants`.`id` = `resident_personal_details`.`resident_id`

WHERE 
    `resident_residences`.`isActive` = 1 AND 
    `complainants`.`is_activated` = 'A'
ORDER BY 
    `complainants`.`id` DESC;