
--
-- Structure for view `active_feedback_cycle_and_questions`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `active_feedback_cycle_and_questions` AS
SELECT
    `feedback_form_cycles`.`cycle_id` AS `cycle_id`,
    `feedback_form_cycles`.`start_date` AS `cycle_start_date`,
    `feedback_form_cycles`.`end_date` AS `cycle_end_date`,
    `feedback_form_cycles`.`form_id` AS `form_id`,
    `feedback_forms`.`title` AS `title`,
    `feedback_forms`.`purpose` AS `purpose`,
    `feedback_form_cycles`.`question_ids` AS `question_ids`,
    (
        select
            json_object(
                'id',
                `feedback_form_questions`.`id`,
                'question',
                `feedback_form_questions`.`question`
            ) AS `data`
        from
            `feedback_form_questions`
        where
            `feedback_form_questions`.`id` = `feedback_form_cycles`.`question_ids`
    ) AS `questions`
FROM
    (
        `feedback_form_cycles`
        join `feedback_forms` on(
            `feedback_form_cycles`.`form_id` = `feedback_forms`.`id`
        )
    )
WHERE
    `feedback_forms`.`isActive` = 1
    AND `feedback_form_cycles`.`isActive` = 1
    AND curdate()
    AND curdate() between `feedback_form_cycles`.`start_date`
    and `feedback_form_cycles`.`end_date`;

-- --------------------------------------------------------
--
-- Structure for view `active_occupancy`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `active_occupancy` AS
SELECT
    `building_residence`.`building_id` AS `building_id`,
    `building_residence`.`id` AS `residence_id`,
    `building`.`name` AS `building_name`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `building_residence`.`unit_type` AS `unit_type`,
    `building_unit_types`.`Description` AS `description`,
    `building_residence`.`floor` AS `floor`,
    `building_unit_types`.`capacity` AS `capacity`,
    `residence_residents`.`active_residents` AS `occupied`
FROM
    (
        (
            (
                `building_residence`
                left join `building_unit_types` on(
                    `building_unit_types`.`unit_type_id` = `building_residence`.`unit_type`
                )
            )
            join `building` on(
                `building`.`id` = `building_residence`.`building_id`
            )
        )
        left join (
            select
                count(`complainants`.`id`) AS `active_residents`,
                `resident_residences`.`residence_id` AS `residence_id`
            from
                (
                    `complainants`
                    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;

-- --------------------------------------------------------
--
-- Structure for view `active_resident_view`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER 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`,
    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`,
        '$.local_guardians'
    ) AS `local_guardians`,
    `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`,
    ifnull(`pp`.`complete_in_per`, 0) AS `profile_complete`
FROM
    (
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            `complainants`
                                            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`
            )
        )
        left join (
            select
                `complainants`.`id` AS `resident_id`,
                round(
                    (
                        octet_length(
                            trim(
                                both ','
                                from
                                    concat(
                                        coalesce(`kyc`.`doc_filled`, ''),
                                        ',',
                                        coalesce(
                                            `resident_personal_details`.`resident_profile_setting_id`,
                                            ''
                                        )
                                    )
                            )
                        ) - octet_length(
                            replace(
                                trim(
                                    both ','
                                    from
                                        concat(
                                            coalesce(`kyc`.`doc_filled`, ''),
                                            ',',
                                            coalesce(
                                                `resident_personal_details`.`resident_profile_setting_id`,
                                                ''
                                            )
                                        )
                                ),
                                ',',
                                ''
                            )
                        ) + 1
                    ) / (
                        select
                            count(`resident_profile_settings`.`id`)
                        from
                            `resident_profile_settings`
                        where
                            `resident_profile_settings`.`isActive` = 1
                    ) * 100,
                    0
                ) AS `complete_in_per`
            from
                (
                    (
                        `complainants`
                        left join (
                            select
                                group_concat(
                                    distinct `resident_kyc`.`resident_profile_setting_id` separator ','
                                ) AS `doc_filled`,
                                `resident_kyc`.`resident_id` AS `resident_id`
                            from
                                `resident_kyc`
                            group by
                                `resident_kyc`.`resident_id`
                        ) `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
        ) `pp` on(`complainants`.`id` = `pp`.`resident_id`)
    )
WHERE
    `resident_residences`.`isActive` = 1
    AND `complainants`.`is_activated` = 'A'
ORDER BY
    `complainants`.`id` DESC;

-- --------------------------------------------------------
--
-- Structure for view `agent_outgoing_summary`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `agent_outgoing_summary` AS
SELECT
    count(`outgoing_calls`.`id`) AS `calls_dialled`,
    sec_to_time(sum(time_to_sec(`outgoing_calls`.`duration`))) AS `outgoing_call_time`,
    `calling_agents`.`id` AS `agent_id`
FROM
    (
        `calling_agents`
        join `outgoing_calls` on(
            `calling_agents`.`id` = `outgoing_calls`.`agent_id`
        )
    )
GROUP BY
    `calling_agents`.`id`;

-- --------------------------------------------------------
--
-- Structure for view `apartment_appuser_vs_residents`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `apartment_appuser_vs_residents` AS
SELECT
    concat(
        `building`.`name`,
        '-',
        `building_residence`.`apartment_name`
    ) AS `apartment_name`,
    count(`resident_user_view`.`id`) AS `app_users`,
    ifnull(`family_members`.`residents`, 0) AS `residents`,
    `building_residence`.`id` AS `residence_id`
FROM
    (
        (
            (
                `building_residence`
                join `building` on(
                    `building`.`id` = `building_residence`.`building_id`
                )
            )
            left join `resident_user_view` on(
                `resident_user_view`.`residence_id` = `building_residence`.`id`
            )
        )
        left join (
            select
                count(`complainants`.`id`) AS `residents`,
                `resident_residences`.`residence_id` AS `residence_id`
            from
                (
                    `complainants`
                    join `resident_residences` on(
                        `complainants`.`id` = `resident_residences`.`resident_id`
                        and `resident_residences`.`isActive` = 1
                    )
                )
            group by
                `resident_residences`.`residence_id`
        ) `family_members` on(
            `family_members`.`residence_id` = `building_residence`.`id`
        )
    )
WHERE
    `building_residence`.`id` > 2
GROUP BY
    `building_residence`.`id`;

-- --------------------------------------------------------
--
-- Structure for view `clarification_activity_view`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `clarification_activity_view` AS
SELECT
    ifnull(max(`complain_activities`.`id`), 0) AS `clarification_activity`,
    `complain_activities`.`complain_no` AS `complain_no`
FROM
    `complain_activities`
WHERE
    `complain_activities`.`activity_type` = 5
GROUP BY
    `complain_activities`.`complain_no`;

-- --------------------------------------------------------
--
-- Structure for view `complainants_feedback`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `complainants_feedback` AS
SELECT
    `feedback`.`id` AS `id`,
    `feedback`.`complain_no` AS `complain_no`,
    `feedback`.`feedback_question_id` AS `feedback_question_id`,
    `feedback`.`feedback_rating` AS `feedback_rating`,
    `feedback`.`feedback_text` AS `feedback_text`,
    `feedback`.`userid` AS `userid`,
    `feedback`.`source_id` AS `source_id`,
    `feedback`.`fdate` AS `fdate`,
    `feedback`.`ftime` AS `ftime`,
    `user`.`userRole` AS `userRole`,
    `user`.`name` AS `name`,
    `complain_source`.`complain_source_text` AS `complain_source_text`,
    `feedback_question`.`feedback_question` AS `feedback_question`
FROM
    (
        (
            (
                `feedback`
                join `complain_source` on(
                    `complain_source`.`complain_source_id` = `feedback`.`source_id`
                )
            )
            join `user` on(`user`.`id` = `feedback`.`userid`)
        )
        left join `feedback_question` on(
            `feedback_question`.`id` = `feedback`.`feedback_question_id`
        )
    )
WHERE
    `user`.`userRole` = 11
ORDER BY
    `feedback`.`id` DESC;

-- --------------------------------------------------------
--
-- Structure for view `complainant_view`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER 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`)
    );

-- --------------------------------------------------------
--
-- Structure for view `complaint_view`
--
DROP VIEW IF EXISTS `complaint_view`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `complaint_view` AS
SELECT
    `complaints`.`complain_id` AS `complain_id`,
    `complaints`.`complainant_id` AS `complainant_id`,
    `complainants`.`complainant_name` AS `complainant_name`,
    `complainants`.`contact_no` AS `contact_no`,
    `complainants`.`email` AS `email`,
    `complainants`.`divyaang` AS `divyaang`,
    `complaints`.`complain_type`, 
    `complaints`.`complain_facility_id`, 
    `common_facilities`.`facility_name`, 
    `common_facilities`.`building_ids` AS `facility_building_ids`,
    CASE
        WHEN `user`.`language` = 'EN' THEN `complain_subjects`.`subject_eng`
        ELSE `complain_subjects`.`subject_eng`
    END AS `complaint_subject`,
    `complaints`.`subject_id` AS `subject_id`,
    `complain_headers`.`header_id` AS `header_id`,
    `complain_headers`.`header` AS `header`,
    `complain_status`.`complain_status_name` AS `complaint_status`,
    `complain_status`.`complain_status_id` AS `complain_status_id`,
    `complaints`.`complaint_date` AS `complaint_date`,
    `complaints`.`complaint_time` AS `complaint_time`,
    `complaints`.`description` AS `description`,
    `complaints`.`remark` AS `remark`,
    `complaints`.`isAnonymous` AS `isAnonymous`,
    `complain_source`.`complain_source_text` AS `complain_source_text`,
    `complaints`.`complain_source_id` AS `complain_source_id`,
    `ca`.`assignDate` AS `assignDate`,
    `complaints`.`sla_id` AS `sla_id`,
    `complaints`.`priority` AS `priority`,
    `complain_priority`.`priority_desc` AS `priority_desc`,
    `complaints`.`latitude` AS `latitude`,
    `complaints`.`longitute` AS `longitute`,
    `complaints`.`scope` AS `scope`,
    `complain_scopes`.`scope_text` AS `scope_text`,
    `complaints`.`scope_reference` AS `scope_reference`,
    `user`.`userRole` AS `userRole`,
    `user`.`name` AS `created_by_name`,
    IF(`role`.`Alias` IS NULL , `role`.`role_name`, `role`.`Alias` ) as role_name,
    `user`.`language` AS `language`,
    `complaints`.`complaint` AS `complaint`,
    `complainants_feedback`.`feedback_rating` AS `complainant_feedback`,
    `department`.`department_name` AS `department_name`,
    `complaints`.`department_id` AS `department_id`,
    `department_team`.`member_name` AS `member_name`,
    `complaints`.`team_member_id` AS `team_member_id`,
    `department_team`.`member_contactno` AS `member_contactno`,
    `department_team`.`member_email` AS `member_email`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `complaints`.`residence_id` AS `residence_id`,
    `complaints`.`complain_floor` AS `complain_floor`,
    `complaints`.`complain_building_id` AS `complain_building_id`,
    `building_residence`.`floor` AS `floor`,
    `building`.`name` AS `building_name`,
    `building_residence`.`building_id` AS `building_id`,
    `slot_info`.`slot_history_id` AS `multi_slot_history_id`,
    `slot_info`.`slot_id` AS `multi_slot_id`,
    `slot_info`.`slot_date` AS `multi_slot_date`,
    `slot_info`.`slot_start` AS `multi_slot_start`,
    `slot_info`.`slot_end` AS `multi_slot_end`,
    `complaint_slot_history`.`slot_date` AS `slot_date`,
    `work_slots`.`slot_start` AS `slot_start`,
    `work_slots`.`slot_end` AS `slot_end`
FROM
    `complaints`
left join `complain_subjects` on `complaints`.`subject_id` = `complain_subjects`.`subject_id`
left join `complain_headers` on `complain_headers`.`header_id` = `complain_subjects`.`header_id`
left join `complain_scopes` on `complaints`.`scope` = `complain_scopes`.`scope_id`
left join `department_team` on `complaints`.`team_member_id` = `department_team`.`member_id`
left join `user` on `complaints`.`created_by` = `user`.`id`
left join `role` on `role`.`role_id` = `user`.`userRole`
left join `complain_priority` on `complaints`.`priority` = `complain_priority`.`priority_id`
left join `complainants_feedback` on `complainants_feedback`.`complain_no` = `complaints`.`complain_id`
join `department` on `complaints`.`department_id` = `department`.`department_id`
left join `complainants` on `complaints`.`complainant_id` = `complainants`.`id`
join `complain_source` on `complaints`.`complain_source_id` = `complain_source`.`complain_source_id`
join `complain_status` on `complaints`.`complaint_status` = `complain_status`.`complain_status_id`
left join (
    select
        max(`complain_activities`.`date`) AS `assignDate`,
        `complain_activities`.`complain_no` AS `complain_no`
    from
        `complain_activities`
    where
        `complain_activities`.`activity_type` = 4
    group by
        `complain_activities`.`complain_no`
) `ca` on(`complaints`.`complain_id` = `ca`.`complain_no`)
left join `building_residence` on `building_residence`.`id` = `complaints`.`residence_id`
left join `building` on (`building_residence`.`building_id` = `building`.`id` OR `complaints`.`complain_building_id` = `building`.`id` )
LEFT JOIN (
	SELECT
		`complaint_slot_history`.`complaint_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_history_id`) AS `slot_history_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_id`) AS `slot_id`,
		GROUP_CONCAT(`complaint_slot_history`.`slot_date`) AS `slot_date`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_start`, '')) AS `slot_start`,
		GROUP_CONCAT(IFNULL(`work_slots`.`slot_end`, '')) AS `slot_end`
	FROM
		`complaint_slot_history`
	LEFT JOIN `work_slots` ON `work_slots`.`id` = `complaint_slot_history`.`slot_id`
	where `complaint_slot_history`.`isActive` = 1 
	GROUP BY
		`complaint_slot_history`.`complaint_id`
) `slot_info` ON `complaints`.`complain_id` = `slot_info`.`complaint_id`
left join `complaint_slot_history` on `complaint_slot_history`.`slot_history_id` = `complaints`.`slot_history_id`
left join `work_slots` on `work_slots`.`id` = `complaint_slot_history`.`slot_id`
left join `common_facilities` on `complaints`.`complain_facility_id` = `common_facilities`.`id`
ORDER BY
    `complaints`.`complain_id` DESC;

-- --------------------------------------------------------
--
-- Structure for view `complain_clarification_count`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `complain_clarification_count` AS
SELECT
    `complain_clarification`.`complain_id` AS `complain_id`,
    count(`complain_clarification`.`id`) AS `clarifications`
FROM
    `complain_clarification`
GROUP BY
    `complain_clarification`.`complain_id`;

-- --------------------------------------------------------
--
-- Structure for view `complete_complain_activity_view`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `complete_complain_activity_view` AS
SELECT
    max(`complain_activities`.`id`) AS `max(id)`,
    `complain_activities`.`date` AS `date`,
    `complain_activities`.`complain_no` AS `complain_no`
FROM
    `complain_activities`
WHERE
    `complain_activities`.`complain_no` in (
        select
            `complaints`.`complain_id`
        from
            `complaints`
        where
            `complaints`.`complaint_status` = 1
    )
    AND `complain_activities`.`activity_type` = 6
GROUP BY
    `complain_activities`.`complain_no`;

-- --------------------------------------------------------
--
-- Structure for view `day_out_7days`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `day_out_7days` AS
SELECT
    `day_out_requests`.`resident_id` AS `resident_id`,
    count(`day_out_requests`.`id`) AS `day_out_7day`
FROM
    `day_out_requests`
WHERE
    `day_out_requests`.`day_out_dt` > current_timestamp() - interval 7 day
    AND `day_out_requests`.`approved_at` is not null
    AND `day_out_requests`.`cancelled_at` is null
GROUP BY
    `day_out_requests`.`resident_id`;

-- --------------------------------------------------------
--
-- Structure for view `gate_security_user_with_building`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `gate_security_user_with_building` AS
SELECT
    `gates`.`id` AS `id`,
    `gates`.`gate_name` AS `gate_name`,
    `gates`.`gate_type` AS `gate_type`,
    `building`.`name` AS `building_name`,
    `building`.`id` AS `building_id`,
    `user`.`id` AS `user_id`,
    `user`.`name` AS `user_name`,
    `gates`.`is_active` AS `is_active`
FROM
    (
        (
            (
                `gates`
                left join `gate_security_user` on(`gates`.`id` = `gate_security_user`.`gate_id`)
            )
            left join `user` on(`gate_security_user`.`user_id` = `user`.`id`)
        )
        left join `building` on(`building`.`id` = `gates`.`building_id`)
    );

-- --------------------------------------------------------
--
-- Structure for view `helper_rating`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `helper_rating` AS
SELECT
    avg(`workers_review`.`rating`) AS `rating`,
    `workers_review`.`helper_id` AS `helper_id`
FROM
    `workers_review`
GROUP BY
    `workers_review`.`helper_id`;

-- --------------------------------------------------------
--
-- Structure for view `maintenance_current_charges`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `maintenance_current_charges` AS
SELECT
    `building_unit_types`.`unit_type_id` AS `unit_type_id`,
    `current_charge`.`building_id` AS `building_id`,
    `building_unit_types`.`Description` AS `Description`,
    `building_unit_types`.`size` AS `size`,
    `building_unit_types`.`capacity` AS `capacity`,
    `current_charge`.`amount` AS `amount`,
    `current_charge`.`amount_daily` AS `amount_daily`,
    `current_charge`.`amount_session` AS `amount_session`,
    `current_charge`.`currency` AS `currency`,
    `current_charge`.`effective_date` AS `effective_date`,
    `current_charge`.`created_at` AS `created_at`,
    `current_charge`.`created_by` AS `created_by`
FROM
    (
        `building_unit_types`
        left join (
            select
                `maintenance_charge_rate`.`unit_type` AS `unit_type`,
                `maintenance_charge_rate`.`building_id` AS `building_id`,
                `maintenance_charge_rate`.`amount` AS `amount`,
                `maintenance_charge_rate`.`amount_daily` AS `amount_daily`,
                `maintenance_charge_rate`.`amount_session` AS `amount_session`,
                `maintenance_charge_rate`.`currency` AS `currency`,
                `maintenance_charge_rate`.`effective_date` AS `effective_date`,
                `maintenance_charge_rate`.`created_at` AS `created_at`,
                `user`.`name` AS `created_by`
            from
                (
                    `maintenance_charge_rate`
                    join `user` on(
                        `user`.`id` = `maintenance_charge_rate`.`created_by`
                    )
                )
            where
                `maintenance_charge_rate`.`charge_id` in (
                    select
                        max(`maintenance_charge_rate`.`charge_id`)
                    from
                        `maintenance_charge_rate`
                    where
                        `maintenance_charge_rate`.`effective_date` <= curdate()
                    group by
                        `maintenance_charge_rate`.`unit_type`,
                        `maintenance_charge_rate`.`building_id`
                )
        ) `current_charge` on(
            `current_charge`.`unit_type` = `building_unit_types`.`unit_type_id`
        )
    )
WHERE
    `building_unit_types`.`isActive` = 1;

-- --------------------------------------------------------
--
-- Structure for view `no_download_residences`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `no_download_residences` AS
SELECT
    `building`.`name` AS `building_name`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `building_residence`.`floor` AS `floor`
FROM
    (
        (
            `building_residence`
            join `building` on(
                `building`.`id` = `building_residence`.`building_id`
            )
        )
        left join `resident_user_view` on(
            `resident_user_view`.`residence_id` = `building_residence`.`id`
        )
    )
WHERE
    `resident_user_view`.`id` is null
    AND `building_residence`.`id` > 2;

-- --------------------------------------------------------
--
-- Structure for view `pending_assigned_2_days`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `pending_assigned_2_days` AS
SELECT
    max(`complain_activities`.`id`) AS `assigned_activity`,
    `complain_activities`.`complain_no` AS `complain_no`
FROM
    `complain_activities`
WHERE
    `complain_activities`.`activity_type` = 4
    AND to_days(curdate()) - to_days(`complain_activities`.`date`) >= 2
    AND `complain_activities`.`complain_no` in (
        select
            `complaints`.`complain_id`
        from
            `complaints`
        where
            `complaints`.`complaint_status` = 0 & `complaints`.`team_member_id` <> 0
    )
GROUP BY
    `complain_activities`.`complain_no`;

-- --------------------------------------------------------
--
-- Structure for view `regular_visitor_last_entry`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `regular_visitor_last_entry` AS
SELECT
    `regular_visitor_gate_pass`.`id` AS `id`,
    `regular_visitor_gate_pass`.`visitor_id` AS `visitor_id`,
    `regular_visitor_gate_pass`.`entry_at` AS `entry_at`,
    `regular_visitor_gate_pass`.`exit_at` AS `exit_at`
FROM
    `regular_visitor_gate_pass`
WHERE
    `regular_visitor_gate_pass`.`id` in (
        select
            max(`regular_visitor_gate_pass`.`id`)
        from
            `regular_visitor_gate_pass`
        group by
            `regular_visitor_gate_pass`.`visitor_id`
    );

-- --------------------------------------------------------
--
-- Structure for view `reminder_1_dept_activity`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `reminder_1_dept_activity` AS
SELECT
    `complain_activities`.`id` AS `id`,
    `complain_activities`.`complain_no` AS `complain_no`,
    `complain_activities`.`date` AS `date`
FROM
    `complain_activities`
WHERE
    `complain_activities`.`activity_type` = 17;

-- --------------------------------------------------------
--
-- Structure for view `reminder_2_dept_activity`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `reminder_2_dept_activity` AS
SELECT
    `complain_activities`.`id` AS `id`,
    `complain_activities`.`complain_no` AS `complain_no`,
    `complain_activities`.`date` AS `date`
FROM
    `complain_activities`
WHERE
    `complain_activities`.`activity_type` = 18;

-- --------------------------------------------------------
--
-- Structure for view `reminder_3_dept_activity`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `reminder_3_dept_activity` AS
SELECT
    `complain_activities`.`id` AS `id`,
    `complain_activities`.`complain_no` AS `complain_no`,
    `complain_activities`.`date` AS `date`
FROM
    `complain_activities`
WHERE
    `complain_activities`.`activity_type` = 19;

-- --------------------------------------------------------
--
-- Structure for view `residents_not_on_leave_today`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `residents_not_on_leave_today` AS
SELECT
    `complainant_view`.`id` AS `id`,
    `complainant_view`.`complainant_name` AS `resident_name`,
    `complainant_view`.`contact_no` AS `contact_no`,
    `complainant_view`.`email` AS `email`,
    `complainant_view`.`residence_id` AS `residence_id`,
    `complainant_view`.`apartment_name` AS `apartment_name`,
    `complainant_view`.`building_id` AS `building_id`,
    `complainant_view`.`building_name` AS `building_name`,
    `complainant_view`.`floor` AS `floor`,
    `complainant_view`.`gender` AS `gender`,
    `resident_academic_view`.`course_name` AS `course_name`,
    `resident_academic_view`.`branch_name` AS `branch_name`,
    `resident_academic_view`.`reference_number` AS `reference_number`,
    `resident_academic_view`.`course_year` AS `course_year`
FROM
    (
        `complainant_view`
        left join `resident_academic_view` on(
            `resident_academic_view`.`resident_id` = `complainant_view`.`id`
        )
    )
WHERE
    !(
        `complainant_view`.`id` in (
            select
                `leave_requests`.`resident_id`
            from
                `leave_requests`
            where
                `leave_requests`.`approved_at` is not null
                AND `leave_requests`.`cancelled_at` is null
                AND curdate() between `leave_requests`.`from_date`
                and `leave_requests`.`to_date`
        )
    )
    AND `complainant_view`.`resident_status` = 6
    AND `complainant_view`.`is_activated` = 'A';

-- --------------------------------------------------------
--
-- Structure for view `residents_with_booking_session`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `residents_with_booking_session` AS
SELECT
    `academic_booking_sessions`.`id` AS `id`,
    `academic_booking_sessions`.`session_name` AS `session_name`,
    `academic_booking_sessions`.`session_start_date` AS `session_start_date`,
    `academic_booking_sessions`.`session_end_date` AS `session_end_date`,
    `academic_booking_sessions`.`d_living_start_date` AS `student_living_start_date`,
    `academic_booking_sessions`.`d_living_end_date` AS `student_living_end_date`,
    `academic_booking_sessions`.`payment_start_date` AS `payment_start_date`,
    `academic_booking_sessions`.`payment_end_date` AS `payment_end_date`,
    `academic_booking_sessions`.`booking_start_date` AS `booking_start_date`,
    `academic_booking_sessions`.`booking_end_date` AS `booking_end_date`,
    `student_unit_type_bookings`.`student_enquiry_id` AS `student_session_id`,
    `student_unit_type_bookings`.`resident_id` AS `fretbox_id`,
    `student_unit_type_bookings`.`booking_status` AS `booking_status`,
    `student_unit_type_bookings`.`payment_status` AS `payment_status`,
    `resident_only_academic_view`.`reference_number` AS `roll_no`,
    `active_student`.`name` AS `name`,
    `active_student`.`contact_no` AS `contact_no`,
    `active_student`.`email` AS `email`,
    `resident_only_academic_view`.`branch_name` AS `branch_name`,
    `resident_only_academic_view`.`course_name` AS `course_name`,
    `building`.`id` AS `building_id`,
    `building`.`name` AS `hostel_name`,
    `building_unit_types`.`unit_type_id` AS `unit_type_id`,
    `building_unit_types`.`Description` AS `unit_name`,
    `active_resident`.`apartment_name` AS `room_name`,
    `active_resident`.`residence_id` AS `room_id`,
    `trns`.`order_id` AS `fertbox_order_id`,
    `trns`.`reference_number` AS `transaction_reference_number`,
    `trns`.`amount` AS `amount`,
    `trns`.`discount` AS `discount`,
    `trns`.`net_amount` AS `net_amount`,
    `trns`.`pay_mode` AS `pay_mode`,
    `trns`.`created_at` AS `transaction_created`
FROM
    (
        (
            (
                (
                    (
                        (
                            (
                                `student_unit_type_bookings`
                                join `building` on(
                                    `student_unit_type_bookings`.`building_id` = `building`.`id`
                                )
                            )
                            join (
                                select
                                    `complainants`.`id` AS `id`,
                                    `complainants`.`complainant_name` AS `name`,
                                    `complainants`.`contact_no` AS `contact_no`,
                                    `complainants`.`email` AS `email`
                                from
                                    `complainants`
                                where
                                    `complainants`.`is_activated` = 'A'
                            ) `active_student` on(
                                `active_student`.`id` = `student_unit_type_bookings`.`resident_id`
                            )
                        )
                        join `building_unit_types` on(
                            `building_unit_types`.`unit_type_id` = `student_unit_type_bookings`.`unit_type_id`
                        )
                    )
                    join `academic_booking_sessions` on(
                        `academic_booking_sessions`.`id` = `student_unit_type_bookings`.`academic_session_id`
                    )
                )
                left join `resident_only_academic_view` on(
                    `resident_only_academic_view`.`resident_id` = `student_unit_type_bookings`.`resident_id`
                )
            )
            left join (
                select
                    `resident_residences`.`resident_id` AS `resident_id`,
                    `resident_residences`.`residence_id` AS `residence_id`,
                    `building_residence`.`apartment_name` AS `apartment_name`
                from
                    (
                        `resident_residences`
                        join `building_residence` on(
                            `resident_residences`.`residence_id` = `building_residence`.`id`
                        )
                    )
                where
                    `resident_residences`.`isActive` = 1
            ) `active_resident` on(
                `active_resident`.`resident_id` = `student_unit_type_bookings`.`resident_id`
            )
        )
        left join (
            select
                `allotment_booking_transactions`.`resident_id` AS `resident_id`,
                `allotment_booking_transactions`.`enquiry_id` AS `enquiry_id`,
                `allotment_booking_transactions`.`order_id` AS `order_id`,
                `allotment_booking_transactions`.`reference_number` AS `reference_number`,
                `allotment_booking_transactions`.`amount` AS `amount`,
                `allotment_booking_transactions`.`net_amount` AS `net_amount`,
                `allotment_booking_transactions`.`discount` AS `discount`,
                `allotment_booking_transactions`.`created_at` AS `created_at`,
                `payment_modes`.`mode_name` AS `pay_mode`
            from
                (
                    `allotment_booking_transactions`
                    join `payment_modes` on(
                        `payment_modes`.`payment_mode_id` = `allotment_booking_transactions`.`payment_medium`
                    )
                )
            where
                `allotment_booking_transactions`.`isLatest` = 1
                and `allotment_booking_transactions`.`status` = 'Completed'
        ) `trns` on(
            `trns`.`resident_id` = `student_unit_type_bookings`.`resident_id`
        )
    );

-- --------------------------------------------------------
--
-- Structure for view `resident_academic_view`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `resident_academic_view` AS
SELECT
    `resident_academic_details`.`resident_id` AS `resident_id`,
    `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 `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` AS `cgpa`,
    `resident_academic_details`.`course` AS `course`,
    `academic_courses`.`course_name` AS `course_name`,
    `resident_academic_details`.`batch` AS `batch`,
    `batches`.`name` AS `batch_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`
                )
            )
            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 `batches` on(
            `batches`.`id` = `resident_academic_details`.`batch`
        )
    );

-- --------------------------------------------------------
--
-- Structure for view `resident_availed_leaves_6_month`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `resident_availed_leaves_6_month` AS
SELECT
    `leave_requests`.`resident_id` AS `resident_id`,
    sum(
        to_days(`leave_requests`.`reporting_dt`) - to_days(`leave_requests`.`from_date`)
    ) AS `availed_leaves_6_month`
FROM
    `leave_requests`
WHERE
    `leave_requests`.`to_date` > current_timestamp() - interval 6 month
    AND `leave_requests`.`approved_at` is not null
    AND `leave_requests`.`cancelled_at` is null
GROUP BY
    `leave_requests`.`resident_id`;

-- --------------------------------------------------------
--
-- Structure for view `resident_community_deposit_balance`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `resident_community_deposit_balance` AS
SELECT
    `resident_community_deposit`.`resident_id` AS `resident_id`,
    `resident_community_deposit`.`balance` AS `balance`
FROM
    `resident_community_deposit`
WHERE
    `resident_community_deposit`.`deposit_id` in (
        select
            max(`resident_community_deposit`.`deposit_id`)
        from
            `resident_community_deposit`
        group by
            `resident_community_deposit`.`resident_id`
    );

-- --------------------------------------------------------
--
-- Structure for view `resident_last_gate_entry`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `resident_last_gate_entry` AS
SELECT
    `resident_gate_pass`.`id` AS `id`,
    `resident_gate_pass`.`resident_id` AS `resident_id`,
    `resident_gate_pass`.`created_at` AS `created_at`,
    `resident_gate_pass`.`user_id` AS `user_id`,
    `resident_gate_pass`.`status` AS `status`,
    `resident_gate_pass`.`purpose` AS `purpose`
FROM
    `resident_gate_pass`
WHERE
    `resident_gate_pass`.`id` in (
        select
            max(`resident_gate_pass`.`id`)
        from
            `resident_gate_pass`
        where
            `resident_gate_pass`.`status` = 'ENTRY'
        group by
            `resident_gate_pass`.`resident_id`
    );

-- --------------------------------------------------------
--
-- Structure for view `resident_last_gate_exit`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `resident_last_gate_exit` AS
SELECT
    `resident_gate_pass`.`id` AS `id`,
    `resident_gate_pass`.`resident_id` AS `resident_id`,
    `resident_gate_pass`.`created_at` AS `created_at`,
    `resident_gate_pass`.`user_id` AS `user_id`,
    `resident_gate_pass`.`status` AS `status`,
    `resident_gate_pass`.`purpose` AS `purpose`
FROM
    `resident_gate_pass`
WHERE
    `resident_gate_pass`.`id` in (
        select
            max(`resident_gate_pass`.`id`)
        from
            `resident_gate_pass`
        where
            `resident_gate_pass`.`status` = 'EXIT'
        group by
            `resident_gate_pass`.`resident_id`
    );

-- --------------------------------------------------------
--
-- Structure for view `resident_leave_approval_settings_view`
--
DROP VIEW IF EXISTS `resident_leave_approval_settings_view`;

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `resident_leave_approval_settings_view` AS
SELECT
    `rad`.`resident_id` AS `resident_id`,
    `rad`.`course` AS `course`,
    `rad`.`branch` AS `branch`,
    `rad`.`course_year` AS `course_year`,
    `rad`.`reference_number` AS `reference_number`,
    `rad`.`gender` AS `gender`,
    `rad`.`registration_no` AS `registration_no`,
    `ls`.`feature` AS `feature`,
    `ls`.`is_warden_view_required` AS `is_warden_view_required`,
    `ls`.`is_warden_approval_required` AS `is_warden_approval_required`,
    `ls`.`is_parent_approval_required` AS `is_parent_approval_required`,
    `ls`.`is_notification_to_parent` AS `is_notification_to_parent`,
    `ls`.`is_edu_head_approval_required` AS `is_edu_head_approval_required`,
    `ls`.`approver_ids` AS `approver_ids`
FROM
    (
        `resident_academic_view` `rad`
        left join `leave_settings` `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`
            and `rad`.`gender` = `ls`.`gender`
        )
    )
WHERE
    `ls`.`isActive` = 1;

-- --------------------------------------------------------
--
-- Structure for view `resident_only_academic_view`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `resident_only_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`.`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`
FROM
    (
        (
            `resident_academic_details`
            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`
        )
    );

-- --------------------------------------------------------
--
-- Structure for view `resident_user_view`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `resident_user_view` AS
SELECT
    `user`.`id` AS `id`,
    `user`.`name` AS `name`,
    `user`.`phoneNumber` AS `phoneNumber`,
    `user`.`email` AS `email`,
    `user`.`language` AS `language`,
    `user`.`file` AS `file`,
    `user`.`ext` AS `ext`,
    `user`.`created_at` AS `created_at`,
    `complainants`.`id` AS `resident_id`,
    `complainants`.`is_activated` AS `is_activated`,
    `resident_residences`.`residence_id` AS `residence_id`,
    `building_residence`.`apartment_name` AS `apartment_name`,
    `building_residence`.`building_id` AS `building_id`,
    `building`.`name` AS `building_name`,
    `resident_residences`.`resident_status` AS `resident_status`,
    `residence_status`.`residence_status_text` AS `residence_status_text`
FROM
    (
        (
            (
                (
                    (
                        `user`
                        join `complainants` on(
                            `complainants`.`id` = `user`.`relatedId`
                            and `user`.`userRole` = 11
                        )
                    )
                    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` on(
                `building`.`id` = `building_residence`.`building_id`
            )
        )
        left join `residence_status` on(
            `residence_status`.`residence_status_id` = `resident_residences`.`resident_status`
        )
    );

-- --------------------------------------------------------
--
-- Structure for view `resident_wallet_balance`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `resident_wallet_balance` AS
SELECT
    `user_wallet`.`resident_id` AS `resident_id`,
    `user_wallet`.`community_balance` AS `community_balance`,
    `user_wallet`.`fretbox_balance` AS `fretbox_balance`
FROM
    `user_wallet`
WHERE
    `user_wallet`.`id` in (
        select
            max(`user_wallet`.`id`)
        from
            `user_wallet`
        group by
            `user_wallet`.`resident_id`
    );

-- --------------------------------------------------------
--
-- Structure for view `student_view`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `student_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`
FROM
    (
        (
            (
                (
                    (
                        `complainants`
                        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 `resident_residences` on(
                `complainants`.`id` = `resident_residences`.`resident_id`
            )
        )
        left join `resident_personal_details` on(
            `complainants`.`id` = `resident_personal_details`.`resident_id`
        )
    )
WHERE
    `resident_residences`.`resident_id` is null
    AND `complainants`.`is_activated` = 'A'
ORDER BY
    `complainants`.`id` DESC;

-- --------------------------------------------------------
--
-- Structure for view `unit_type_bookings_deatils`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `unit_type_bookings_deatils` AS
SELECT
    `student_unit_type_bookings`.`resident_id` AS `resident_id`,
    `student_unit_type_bookings`.`student_enquiry_id` AS `student_enquiry_id`,
    `building`.`name` AS `building_name`,
    `building_unit_types`.`Description` AS `unit_name`
FROM
    (
        (
            `student_unit_type_bookings`
            join `building` on(
                `student_unit_type_bookings`.`building_id` = `building`.`id`
            )
        )
        join `building_unit_types` on(
            `building_unit_types`.`unit_type_id` = `student_unit_type_bookings`.`unit_type_id`
        )
    );

-- --------------------------------------------------------
--
-- Structure for view `view_complain_pending_hours`
--

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `view_complain_pending_hours` AS
SELECT
    `complaints`.`complain_id` AS `complain_id`,
    timestamp(
        `complaints`.`complaint_date`,
        `complaints`.`complaint_time`
    ) AS `complaint_date`,
    current_timestamp() AS `CURRENT_TIMESTAMP`,
    timestampdiff(
        HOUR,
        timestamp(
            `complaints`.`complaint_date`,
            `complaints`.`complaint_time`
        ),
        current_timestamp()
    ) AS `diff_hours`
FROM
    `complaints`
WHERE
    `complaints`.`complaint_status` = 0;

CREATE view  resident_kyc_details_view as
SELECT c.id,c.complainant_name,c.contact_no,res_kyc.name as doc_name,res_kyc.id as doc_id,res_kyc.file_name,res_kyc.created_at as uploaded_at,user.name as uploaded_by,is_activated as resident_status,
building_residence.building_id AS building_id
FROM complainants as c
LEFT JOIN 
(SELECT resident_id,uploaded_by,resident_kyc.created_at,file_name,ext,resident_profile_settings.name,resident_profile_settings.id FROM `resident_kyc` 
INNER JOIN resident_profile_settings on resident_kyc.resident_profile_setting_id=resident_profile_settings.id)as res_kyc
on c.id=res_kyc.resident_id
LEFT JOIN `user` on res_kyc.uploaded_by=`user`.`id`
LEFT JOIN resident_residences ON resident_residences.resident_id = c.id AND resident_residences.isActive = 1
LEFT JOIN building_residence ON resident_residences.residence_id = building_residence.id
LEFT JOIN building ON building.id = building_residence.building_id
WHERE c.is_activated in ('A','S');