--
-- Host: localhost    Database: spaceLight
-- ------------------------------------------------------


--
-- Current Database: `spaceLight`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `spaceLight` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `spaceLight`;

--
-- Temporary view structure for view `EmployeeAttendanceSummary`
--

/*!50001 DROP VIEW IF EXISTS `EmployeeAttendanceSummary`*/;
/*!50001 CREATE VIEW `EmployeeAttendanceSummary` AS SELECT 
 1 AS `user_hash`,
 1 AS `work_hour`,
 1 AS `work_hour_target`,
 1 AS `over_time`,
 1 AS `cut_off_hours`,
 1 AS `total_adjusted_minutes`,
 1 AS `finger_limit_out`,
 1 AS `finger_limit_out_leave`,
 1 AS `cut_hours_penatly`,
 1 AS `date`,
 1 AS `cut_off_days`,
 1 AS `absence_hour`,
 1 AS `sick_vacation_hour`,
 1 AS `total_regular_leave_hours`,
 1 AS `total_time_leave_hours`,
 1 AS `duty_hour`,
 1 AS `total_unpaid_leave_hours`,
 1 AS `total_weekly_off_hours`,
 1 AS `vacation_hour`,
 1 AS `vacation_limit_hour`,
 1 AS `vacation_limit_out`,
 1 AS `hourly_hour_price`*/;

--
-- Temporary view structure for view `EmployeeAttendanceSummary_backup`
--

/*!50001 DROP VIEW IF EXISTS `EmployeeAttendanceSummary_backup`*/;
/*!50001 CREATE VIEW `EmployeeAttendanceSummary_backup` AS SELECT 
 1 AS `user_hash`,
 1 AS `work_hour`,
 1 AS `work_hour_target`,
 1 AS `over_time`,
 1 AS `cut_off_hours`,
 1 AS `total_adjusted_minutes`,
 1 AS `finger_limit_out`,
 1 AS `finger_limit_out_leave`,
 1 AS `cut_hours_penatly`,
 1 AS `date`,
 1 AS `cut_off_days`,
 1 AS `absence_hour`,
 1 AS `sick_vacation_hour`,
 1 AS `total_regular_leave_hours`,
 1 AS `total_time_leave_hours`,
 1 AS `duty_hour`,
 1 AS `total_unpaid_leave_hours`,
 1 AS `total_weekly_off_hours`,
 1 AS `vacation_hour`,
 1 AS `vacation_limit_hour`,
 1 AS `vacation_limit_out`,
 1 AS `hourly_hour_price`*/;

--
-- Table structure for table `PositionRelationship`
--

CREATE TABLE `PositionRelationship` (
  `position_id` int NOT NULL,
  `work_with_who_id` int NOT NULL,
  PRIMARY KEY (`position_id`,`work_with_who_id`),
  KEY `PositionRelationship_ibfk_2` (`work_with_who_id`),
  CONSTRAINT `PositionRelationship_ibfk_1` FOREIGN KEY (`position_id`) REFERENCES `Positions` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `PositionRelationship_ibfk_2` FOREIGN KEY (`work_with_who_id`) REFERENCES `Positions` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `PositionTypes`
--

CREATE TABLE `PositionTypes` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `Positions`
--

CREATE TABLE `Positions` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `PositionTypeID` int NOT NULL,
  `Name` varchar(255) NOT NULL,
  `ParentID` int DEFAULT NULL,
  `Permissions` text NOT NULL,
  `KPI` text,
  `Specifications` text,
  `Percentage` decimal(10,2) DEFAULT NULL,
  `Item` int DEFAULT NULL,
  `Todos` varchar(500) DEFAULT NULL,
  `Flag` varchar(50) DEFAULT 'position',
  `lead_hash` bigint DEFAULT '16599872021296496',
  PRIMARY KEY (`ID`),
  KEY `Positions_ibfk_1` (`PositionTypeID`),
  KEY `Positions_ibfk_2` (`ParentID`),
  KEY `fk_lead_hash` (`lead_hash`),
  CONSTRAINT `fk_lead_hash` FOREIGN KEY (`lead_hash`) REFERENCES `emp_information` (`user_hash`),
  CONSTRAINT `Positions_ibfk_1` FOREIGN KEY (`PositionTypeID`) REFERENCES `PositionTypes` (`ID`),
  CONSTRAINT `Positions_ibfk_2` FOREIGN KEY (`ParentID`) REFERENCES `Positions` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=229 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `RequestPayment`
--

CREATE TABLE `RequestPayment` (
  `PaymentID` int NOT NULL AUTO_INCREMENT,
  `price` decimal(10,2) NOT NULL,
  `date` date NOT NULL,
  `notes` varchar(45) DEFAULT NULL,
  `goverment` varchar(25) NOT NULL,
  `projectId` bigint DEFAULT NULL,
  `userId` bigint NOT NULL,
  PRIMARY KEY (`PaymentID`),
  KEY `ibk3_projectIdForeignKey_idx` (`projectId`) /*!80000 INVISIBLE */,
  CONSTRAINT `ibk3_projectIdForeignKey` FOREIGN KEY (`projectId`) REFERENCES `project` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `Roles`
--

CREATE TABLE `Roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `priority` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `RolesPages`
--

CREATE TABLE `RolesPages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `page_id` int DEFAULT NULL,
  `role_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `RolesPages_ibfk_1` (`page_id`),
  KEY `RolesPages_ibfk_2` (`role_id`),
  CONSTRAINT `RolesPages_ibfk_1` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `RolesPages_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `Roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `RolesPermissions`
--

CREATE TABLE `RolesPermissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_id` int NOT NULL,
  `permission_id` int NOT NULL,
  `is_allowed` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_permission` (`role_id`,`permission_id`),
  KEY `RolesPermissions_ibfk_1_idx` (`permission_id`),
  CONSTRAINT `RolesPermissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `Roles` (`id`),
  CONSTRAINT `RolesPermissions_ibfk_3` FOREIGN KEY (`permission_id`) REFERENCES `permissions_for_roles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `UserRoles`
--

CREATE TABLE `UserRoles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint DEFAULT NULL,
  `role_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_role` (`user_hash`,`role_id`),
  KEY `role_id` (`role_id`),
  KEY `UserRoles_ibfk_1_idx` (`user_hash`),
  CONSTRAINT `UserRoles_ibfk_1` FOREIGN KEY (`user_hash`) REFERENCES `system_users` (`hash`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `UserRoles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `Roles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2209 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `accounting_tree`
--

CREATE TABLE `accounting_tree` (
  `id` int NOT NULL AUTO_INCREMENT,
  `hash` bigint NOT NULL,
  `name` varchar(45) NOT NULL,
  `number` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=111302 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `actual_schedules`
--

CREATE TABLE `actual_schedules` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint DEFAULT NULL,
  `date` date NOT NULL,
  `schedule_data` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `allowance_time` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_date` (`user_hash`,`date`),
  CONSTRAINT `actual_schedules_ibfk_1` FOREIGN KEY (`user_hash`) REFERENCES `emp_information` (`user_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=9219 DEFAULT CHARSET=utf8mb3;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`redha`@`%`*/ /*!50003 TRIGGER `before_schedule_data_update` BEFORE UPDATE ON `actual_schedules` FOR EACH ROW BEGIN
    IF OLD.schedule_data != NEW.schedule_data THEN
        INSERT INTO schedule_data_history (
            user_hash,
            date,
            schedule_data_before,
            schedule_data_after,
            update_time
        ) VALUES (
            OLD.user_hash,
            OLD.date,
            OLD.schedule_data,
            NEW.schedule_data,
            CURRENT_TIME
        );
    END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `administrative_order_type`
--

CREATE TABLE `administrative_order_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3 COMMENT='انواع الاوامر الادارية';

--
-- Table structure for table `administrative_orders`
--

CREATE TABLE `administrative_orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint DEFAULT NULL,
  `order_title_hash` bigint DEFAULT NULL,
  `details` varchar(1000) DEFAULT NULL,
  `number` varchar(100) DEFAULT NULL,
  `date` date DEFAULT NULL COMMENT 'الاوامر الادارية',
  `attachment` varchar(1000) DEFAULT NULL,
  `hash` bigint unsigned DEFAULT NULL COMMENT 'الاوامر الادارية',
  `amount` int DEFAULT '0',
  `create_record_date` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1224 DEFAULT CHARSET=utf8mb3 COMMENT='الاوامر الادارية';

--
-- Table structure for table `advance`
--

CREATE TABLE `advance` (
  `id` int NOT NULL AUTO_INCREMENT,
  `price` decimal(13,0) DEFAULT NULL,
  `user_hash` bigint DEFAULT NULL,
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_deleted` tinyint(1) DEFAULT '0',
  `is_blocked` tinyint(1) DEFAULT '0',
  `user_admin` bigint DEFAULT NULL,
  `advancecol` varchar(45) DEFAULT NULL,
  `status_id` int NOT NULL DEFAULT '4',
  PRIMARY KEY (`id`),
  KEY `advance_ibfk_1` (`user_hash`),
  CONSTRAINT `advance_ibfk_1` FOREIGN KEY (`user_hash`) REFERENCES `system_users` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `advance_payment`
--

CREATE TABLE `advance_payment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `price` decimal(10,2) NOT NULL,
  `date` date DEFAULT NULL,
  `invoice_number` int NOT NULL,
  `invoice_date` date NOT NULL,
  `notes` text,
  `attachment` varchar(255) NOT NULL,
  `projectId` bigint DEFAULT NULL,
  `userId` bigint NOT NULL,
  `requestPaymentId` int NOT NULL,
  `categoryId` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_number_UNIQUE` (`invoice_number`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `advance_status`
--

CREATE TABLE `advance_status` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_UNIQUE` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `advance_steps`
--

CREATE TABLE `advance_steps` (
  `id` int NOT NULL AUTO_INCREMENT,
  `advance_id` int DEFAULT NULL,
  `status_id` int DEFAULT '4',
  `date` datetime DEFAULT CURRENT_TIMESTAMP,
  `notes` text,
  `user_hash` bigint DEFAULT NULL,
  `user_admin` bigint DEFAULT NULL,
  `is_finish` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_admin` (`user_admin`),
  KEY `advance_steps_ibfk_2` (`status_id`),
  KEY `advance_steps_ibfk_1` (`advance_id`),
  CONSTRAINT `advance_steps_ibfk_1` FOREIGN KEY (`advance_id`) REFERENCES `advance` (`id`),
  CONSTRAINT `advance_steps_ibfk_2` FOREIGN KEY (`status_id`) REFERENCES `advance_status` (`id`),
  CONSTRAINT `advance_steps_ibfk_3` FOREIGN KEY (`user_admin`) REFERENCES `system_users` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=234 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `announce`
--

CREATE TABLE `announce` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `details` varchar(1000) DEFAULT NULL,
  `number` varchar(50) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `attachment` varchar(1000) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  `create_record` datetime DEFAULT CURRENT_TIMESTAMP,
  `reward_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ibfk1_reward_id_announce_idx` (`reward_id`),
  CONSTRAINT `ibfk1_reward_id_announce` FOREIGN KEY (`reward_id`) REFERENCES `penalties_rewards_employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=335 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `announce_to_user`
--

CREATE TABLE `announce_to_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `announce_hash` bigint DEFAULT NULL,
  `user_hash` bigint DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16171 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `blocked_pages`
--

CREATE TABLE `blocked_pages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pageId` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `check_in`
--

CREATE TABLE `check_in` (
  `id` int NOT NULL AUTO_INCREMENT,
  `note` varchar(1000) DEFAULT NULL,
  `location` point DEFAULT NULL,
  `user_hash` bigint DEFAULT NULL,
  `data_entry` datetime DEFAULT CURRENT_TIMESTAMP,
  `hash` bigint DEFAULT NULL,
  `in_out` varchar(10) DEFAULT NULL,
  `fingerprint_time` datetime DEFAULT NULL,
  `mobile_info` varchar(255) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `clients`
--

CREATE TABLE `clients` (
  `name` varchar(100) NOT NULL,
  `address` varchar(255) NOT NULL,
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `hash` bigint unsigned NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

--
-- Temporary view structure for view `customer_FTTH_not_working`
--

/*!50001 DROP VIEW IF EXISTS `customer_FTTH_not_working`*/;
/*!50001 CREATE VIEW `customer_FTTH_not_working` AS SELECT 
 1 AS `customer_id`,
 1 AS `customer_displayValue`,
 1 AS `username`,
 1 AS `zone_displayValue`,
 1 AS `expires`,
 1 AS `zone`,
 1 AS `zoneNumber`,
 1 AS `address`,
 1 AS `updated_at`*/;

--
-- Table structure for table `daily_flexible_schedule_targets`
--

CREATE TABLE `daily_flexible_schedule_targets` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `date` date NOT NULL,
  `target_hours` float NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_deleted` tinyint DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_date` (`date`,`user_hash`),
  KEY `user_hash` (`user_hash`),
  CONSTRAINT `daily_flexible_schedule_targets_ibfk_1` FOREIGN KEY (`user_hash`) REFERENCES `emp_information` (`user_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=12179 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `days_of_week`
--

CREATE TABLE `days_of_week` (
  `id` int NOT NULL,
  `label` varchar(20) NOT NULL,
  `en_label` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `debug_logs`
--

CREATE TABLE `debug_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `operation` varchar(255) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=549 DEFAULT CHARSET=utf8mb3;

--
-- Temporary view structure for view `detect_fraud`
--

/*!50001 DROP VIEW IF EXISTS `detect_fraud`*/;
/*!50001 CREATE VIEW `detect_fraud` AS SELECT 
 1 AS `id`,
 1 AS `user_hash`,
 1 AS `fingerprint_type`,
 1 AS `fingerprint_time`,
 1 AS `in_out`,
 1 AS `data_entry`,
 1 AS `hash`,
 1 AS `location`,
 1 AS `user_create_record`,
 1 AS `user_update_record`,
 1 AS `mobile_info`*/;

--
-- Table structure for table `device_log`
--

CREATE TABLE `device_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `user_hash` bigint DEFAULT NULL,
  `old_mobile_info` varchar(1000) DEFAULT NULL,
  `new_mobile_info` varchar(1000) DEFAULT NULL,
  `status` varchar(45) DEFAULT '1',
  `hash` bigint DEFAULT NULL,
  `record_date` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1753 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `document_permissions`
--

CREATE TABLE `document_permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `doc_id` int NOT NULL,
  `user_id` bigint NOT NULL,
  `can_edit` tinyint(1) DEFAULT '1',
  `can_download` tinyint(1) DEFAULT '1',
  `can_review` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `doc_id` (`doc_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `document_permissions_ibfk_1` FOREIGN KEY (`doc_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `document_permissions_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `system_users` (`hash`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `document_templates`
--

CREATE TABLE `document_templates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `doc_type` enum('word','spreadsheet','presentation','pdf') NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `owner_id` bigint DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `file_key` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `owner_id` (`owner_id`),
  CONSTRAINT `document_templates_ibfk_1` FOREIGN KEY (`owner_id`) REFERENCES `system_users` (`hash`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `document_workspaces`
--

CREATE TABLE `document_workspaces` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `user_id` bigint NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_UNIQUE` (`user_id`),
  CONSTRAINT `document_workspaces_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `system_users` (`hash`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `documents`
--

CREATE TABLE `documents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `document_workspace_id` int NOT NULL,
  `doc_type` enum('word','spreadsheet','presentation','pdf') NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` bigint NOT NULL,
  `file_key` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `document_workspace_id` (`document_workspace_id`),
  CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `system_users` (`hash`) ON DELETE CASCADE,
  CONSTRAINT `documents_ibfk_2` FOREIGN KEY (`document_workspace_id`) REFERENCES `document_workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `emp_fingerprint`
--

CREATE TABLE `emp_fingerprint` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `fingerprint_type` bigint NOT NULL,
  `fingerprint_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `in_out` varchar(100) NOT NULL,
  `data_entry` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `hash` bigint DEFAULT NULL,
  `location` point DEFAULT NULL,
  `user_create_record` bigint DEFAULT NULL,
  `user_update_record` bigint DEFAULT NULL,
  `mobile_info` varchar(1000) DEFAULT NULL COMMENT 'الموبايل الي بصم بي',
  `day_print` date GENERATED ALWAYS AS (cast(`fingerprint_time` as date)) STORED,
  `emp_test` varchar(45) DEFAULT NULL,
  `schedule_id` varchar(255) DEFAULT NULL,
  `is_flexible` tinyint(1) NOT NULL DEFAULT '0',
  `status` varchar(45) DEFAULT NULL,
  `in_out_reference` int DEFAULT NULL,
  `islos` tinyint(1) DEFAULT NULL,
  `app_version` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_UNIQUE` (`hash`),
  KEY `emp_fingerprint_user_hash_index` (`user_hash`),
  KEY `index5` (`user_hash`,`day_print`) USING BTREE,
  KEY `fk_in_out_reference` (`in_out_reference`),
  CONSTRAINT `fk_in_out_reference` FOREIGN KEY (`in_out_reference`) REFERENCES `emp_fingerprint` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17341 DEFAULT CHARSET=utf8mb3 COMMENT='بصمة الموظفين';
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`redha`@`%`*/ /*!50003 TRIGGER `update_scheduler_status_after_time_update` AFTER UPDATE ON `emp_fingerprint` FOR EACH ROW BEGIN
    IF NEW.fingerprint_time != OLD.fingerprint_time THEN
        -- Update fingerprint_scheduler where the record is referenced as either in or out fingerprint
        UPDATE finger_print_scheduler
        SET scheduler_status = 'pending'
        WHERE fingerprint_in_id = NEW.id 
           OR fingerprint_out_id = NEW.id;
    END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `emp_fingerprint_approval`
--

CREATE TABLE `emp_fingerprint_approval` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `fingerprint_type` bigint NOT NULL,
  `fingerprint_time` datetime NOT NULL,
  `in_out` varchar(100) NOT NULL,
  `location` point DEFAULT NULL,
  `mobile_info` varchar(1000) DEFAULT NULL,
  `schedule_id` varchar(255) DEFAULT NULL,
  `is_flexible` tinyint(1) NOT NULL DEFAULT '0',
  `reason_for_approval` varchar(255) DEFAULT NULL COMMENT 'e.g., No schedule, wrong location, on vacation',
  `status` varchar(45) DEFAULT 'pending' COMMENT 'pending, approved, rejected',
  `admin_comment` varchar(1000) DEFAULT NULL COMMENT 'Admin remarks or corrections',
  `submitted_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1260 DEFAULT CHARSET=utf8mb3 COMMENT='Pending fingerprint entries awaiting admin approval';

--
-- Table structure for table `emp_fingerprint_backup`
--

CREATE TABLE `emp_fingerprint_backup` (
  `id` int NOT NULL DEFAULT '0',
  `user_hash` bigint NOT NULL,
  `fingerprint_type` bigint NOT NULL,
  `fingerprint_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `in_out` varchar(100) NOT NULL,
  `data_entry` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `hash` bigint DEFAULT NULL,
  `location` point DEFAULT NULL,
  `user_create_record` bigint DEFAULT NULL,
  `user_update_record` bigint DEFAULT NULL,
  `mobile_info` varchar(1000) DEFAULT NULL COMMENT 'الموبايل الي بصم بي',
  `day_print` date DEFAULT NULL,
  `emp_test` varchar(45) DEFAULT NULL,
  `schedule_id` varchar(255) DEFAULT NULL,
  `is_flexible` tinyint(1) NOT NULL DEFAULT '0',
  `status` varchar(45) DEFAULT NULL,
  `in_out_reference` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `emp_fingerprint_error_log`
--

CREATE TABLE `emp_fingerprint_error_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` varchar(255) NOT NULL,
  `fingerprint_time` datetime NOT NULL,
  `fingerprint_type` varchar(50) NOT NULL,
  `in_out` enum('in','out') NOT NULL,
  `location` point NOT NULL,
  `mobile_info` text,
  `hash` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `message` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2035 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `emp_fingerprint_test`
--

CREATE TABLE `emp_fingerprint_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `fingerprint_type` bigint NOT NULL,
  `fingerprint_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `in_out` varchar(100) NOT NULL,
  `data_entry` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `hash` bigint DEFAULT NULL,
  `location` point DEFAULT NULL,
  `user_create_record` bigint DEFAULT NULL,
  `user_update_record` bigint DEFAULT NULL,
  `mobile_info` varchar(1000) DEFAULT NULL COMMENT 'Ø§Ù„Ù…ÙˆØ¨Ø§ÙŠÙ„ Ø§Ù„ÙŠ Ø¨ØµÙ… Ø¨ÙŠ',
  `day_print` date GENERATED ALWAYS AS (cast(`fingerprint_time` as date)) STORED,
  `emp_test` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_UNIQUE` (`hash`),
  KEY `emp_fingerprint_test_user_hash_index` (`user_hash`),
  KEY `index5` (`user_hash`,`day_print`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=89894 DEFAULT CHARSET=utf8mb3 COMMENT='Ø¨ØµÙ…Ø© Ø§Ù„Ù…ÙˆØ¸ÙÙŠÙ†';

--
-- Temporary view structure for view `emp_info`
--

/*!50001 DROP VIEW IF EXISTS `emp_info`*/;
/*!50001 CREATE VIEW `emp_info` AS SELECT 
 1 AS `emp_id`,
 1 AS `emp_hash`,
 1 AS `emp_name`,
 1 AS `primary_salary`,
 1 AS `vacations_limit`,
 1 AS `sick_leave`,
 1 AS `work_from`,
 1 AS `work_to`,
 1 AS `allow_time`,
 1 AS `location`*/;

--
-- Table structure for table `emp_information`
--

CREATE TABLE `emp_information` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `primary_salary` double DEFAULT '500',
  `vacations_limit` int DEFAULT '16',
  `sick_leave` int DEFAULT '16',
  `status` varchar(45) DEFAULT NULL,
  `work_from` time DEFAULT '08:00:00',
  `work_to` time DEFAULT '17:00:00',
  `allow_time` time DEFAULT '08:15:00',
  `location_of_work` bigint DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  `phone1` varchar(45) DEFAULT NULL,
  `phone2` varchar(45) DEFAULT NULL,
  `partment` varchar(45) DEFAULT NULL,
  `disable_in_finger_time` time DEFAULT '12:00:00',
  `disable_out_finger_time` time DEFAULT '18:00:00',
  `start_work_date` date DEFAULT '2022-08-01',
  `emp_id` int DEFAULT NULL,
  `jop_type` int DEFAULT '0',
  `is_work` tinyint DEFAULT '1',
  `job_number` varchar(45) DEFAULT NULL,
  `work_in_check_distance` varchar(5) DEFAULT '1',
  `work_out_check_distance` varchar(5) DEFAULT '1',
  `advance_agreement` varchar(5) DEFAULT '0',
  `advance_request` varchar(5) DEFAULT '0',
  `position` varchar(45) DEFAULT NULL,
  `annual_percentage_increase` int DEFAULT '0',
  `annual_price_increase` int DEFAULT '0',
  `salary_type` int DEFAULT '1',
  `position_other` int DEFAULT NULL,
  `pagesPermissions` set('169','166','167','150','149','127','147') DEFAULT '169,166,167,150,149,127,147',
  `IBN` varchar(255) DEFAULT NULL,
  `number_of_year_exp` int DEFAULT '0',
  `fingerprint_required` tinyint DEFAULT '1',
  `work_schedule` int DEFAULT NULL,
  `flexible_schedule` tinyint DEFAULT '0',
  `target_hours` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stop_work_date` date DEFAULT NULL,
  `job_title_id` int DEFAULT NULL,
  `send_myLocation` tinyint(1) DEFAULT '1',
  `can_change_flag_to_send_myLocation` tinyint(1) DEFAULT '1',
  `imposter_flag` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_hash_UNIQUE` (`user_hash`) /*!80000 INVISIBLE */,
  KEY `position_other_idx` (`position_other`),
  KEY `fk_work_schedule_emp_information_table` (`work_schedule`),
  KEY `fk_job_title` (`job_title_id`),
  CONSTRAINT `fk_job_title` FOREIGN KEY (`job_title_id`) REFERENCES `job_title` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_work_schedule_emp_information_table` FOREIGN KEY (`work_schedule`) REFERENCES `schedule_groups` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `position_other` FOREIGN KEY (`position_other`) REFERENCES `positions_other` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=657 DEFAULT CHARSET=utf8mb3 COMMENT='معلومات الموظفين';
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`redha`@`%`*/ /*!50003 TRIGGER `after_emp_information_update` AFTER INSERT ON `emp_information` FOR EACH ROW BEGIN
    IF NEW.work_schedule IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1 
            FROM vacations v 
            WHERE v.user_hash = NEW.user_hash 
              AND CURDATE() BETWEEN v.date_start AND IFNULL(v.date_end, v.date_start)
        ) THEN
            INSERT INTO actual_schedules (user_hash, date, schedule_data, allowance_time)
            SELECT
                NEW.user_hash,
                CURDATE() AS date,
                JSON_UNQUOTE(JSON_EXTRACT(sg.schedule_data, CONCAT('$.', DAYNAME(CURDATE())))) AS schedule_data,
                sg.allowance_time
            FROM
                schedule_groups sg
            WHERE
                sg.id = NEW.work_schedule
            ON DUPLICATE KEY UPDATE
                schedule_data = VALUES(schedule_data),
                allowance_time = VALUES(allowance_time),
                updated_at = CURRENT_TIMESTAMP;
        END IF;
    END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `emp_information_log`
--

CREATE TABLE `emp_information_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id1` int NOT NULL,
  `user_hash` bigint DEFAULT NULL,
  `primary_salary` double DEFAULT '500',
  `vacations_limit` int DEFAULT '16',
  `sick_leave` int DEFAULT '16',
  `status` varchar(45) DEFAULT NULL,
  `work_from` time DEFAULT '08:00:00',
  `work_to` time DEFAULT '17:00:00',
  `allow_time` time DEFAULT '08:15:00',
  `location_of_work` bigint DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  `phone1` varchar(45) DEFAULT NULL,
  `phone2` varchar(45) DEFAULT NULL,
  `partment` varchar(45) DEFAULT NULL,
  `disable_in_finger_time` time DEFAULT '12:00:00',
  `disable_out_finger_time` time DEFAULT '18:00:00',
  `start_work_date` date DEFAULT '2022-08-01',
  `emp_id` int DEFAULT NULL,
  `jop_type` int DEFAULT '0',
  `is_work` tinyint DEFAULT '1',
  `date_of_insert` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2280 DEFAULT CHARSET=utf8mb3 COMMENT='معلومات الموظفين';

--
-- Table structure for table `emp_information_old`
--

CREATE TABLE `emp_information_old` (
  `id` int NOT NULL,
  `user_hash` bigint DEFAULT NULL,
  `primary_salary` double DEFAULT '500',
  `vacations_limit` int DEFAULT '16',
  `sick_leave` int DEFAULT '16',
  `status` varchar(45) DEFAULT NULL,
  `work_from` time DEFAULT '08:00:00',
  `work_to` time DEFAULT '17:00:00',
  `allow_time` time DEFAULT '08:15:00',
  `location_of_work` bigint DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  `phone1` varchar(45) DEFAULT NULL,
  `phone2` varchar(45) DEFAULT NULL,
  `partment` varchar(45) DEFAULT NULL,
  `disable_in_finger_time` time DEFAULT '12:00:00',
  `disable_out_finger_time` time DEFAULT '18:00:00',
  `start_work_date` datetime DEFAULT NULL,
  `emp_id` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_hash_UNIQUE` (`user_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='معلومات الموظفين';

--
-- Table structure for table `emp_payroll`
--

CREATE TABLE `emp_payroll` (
  `id` int NOT NULL AUTO_INCREMENT,
  `emp_id` bigint NOT NULL,
  `emp_name` text,
  `user_qualifcations` text,
  `vacactions_for_this_month` int DEFAULT NULL,
  `primary_salary` int DEFAULT NULL,
  `specification_qualification` text,
  `specification_position` text,
  `differences_salary` int DEFAULT '0',
  `total_salary` int DEFAULT NULL,
  `flat_salary` int DEFAULT NULL,
  `notes` text,
  `approved` tinyint DEFAULT '0',
  `specification_others` text,
  `finiancial_discounts` text,
  `year_months` varchar(7) NOT NULL,
  `from` date NOT NULL,
  `to` date NOT NULL,
  `price_increase_per_year` int DEFAULT NULL,
  `salary_after_cut_off` int DEFAULT NULL,
  `primary_salary_after_cut_off` int DEFAULT NULL,
  `discountAmount` int DEFAULT NULL,
  `salaryAfterDiscount` int DEFAULT NULL,
  `total_Yearly_salary` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `hash` bigint DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sum_penalties` int DEFAULT NULL,
  `sum_rewards` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `ibk1_emp_id` (`emp_id`),
  CONSTRAINT `ibk1_emp_id` FOREIGN KEY (`emp_id`) REFERENCES `emp_information` (`user_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=1376 DEFAULT CHARSET=utf8mb3;

--
-- Temporary view structure for view `emp_today_status`
--

/*!50001 DROP VIEW IF EXISTS `emp_today_status`*/;
/*!50001 CREATE VIEW `emp_today_status` AS SELECT 
 1 AS `name`,
 1 AS `hash`,
 1 AS `status`,
 1 AS `time`,
 1 AS `arrive_time`*/;

--
-- Temporary view structure for view `emp_vacation_summery`
--

/*!50001 DROP VIEW IF EXISTS `emp_vacation_summery`*/;
/*!50001 CREATE VIEW `emp_vacation_summery` AS SELECT 
 1 AS `name`,
 1 AS `count`,
 1 AS `year`*/;

--
-- Table structure for table `emp_worked_hours_flags`
--

CREATE TABLE `emp_worked_hours_flags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `calculate_full_late` tinyint(1) NOT NULL,
  `penalty_multiplier` decimal(5,2) NOT NULL DEFAULT '1.00',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `calc_by_missing` tinyint(1) NOT NULL DEFAULT '0',
  `is_penalty_based_on_lateArrival` tinyint(1) NOT NULL DEFAULT '1',
  `is_main` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `max_finger_in_hours` int DEFAULT NULL,
  `over_time_allowance` decimal(5,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `employee_absences`
--

CREATE TABLE `employee_absences` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `date` date NOT NULL,
  `schedule_data` json NOT NULL,
  `inserted_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_date` (`user_hash`,`date`),
  CONSTRAINT `fk_employee_absence_user_hash` FOREIGN KEY (`user_hash`) REFERENCES `emp_information` (`user_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=5179 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `employee_movements`
--

CREATE TABLE `employee_movements` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_hash` bigint DEFAULT NULL,
  `lat` float DEFAULT NULL,
  `lng` float DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `name` varchar(45) DEFAULT NULL,
  `imageProfile` varchar(500) DEFAULT NULL,
  `finger_print` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `mobile_info` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fbk1_employee_movements_user_hash` (`user_hash`),
  CONSTRAINT `fbk1_employee_movements_user_hash` FOREIGN KEY (`user_hash`) REFERENCES `emp_information` (`user_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2129755 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `employee_movements_config`
--

CREATE TABLE `employee_movements_config` (
  `id` int NOT NULL AUTO_INCREMENT,
  `meters` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `employees_worked_hours`
--

CREATE TABLE `employees_worked_hours` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `date` date NOT NULL,
  `total_late_arrival_minutes` int NOT NULL DEFAULT '0',
  `total_early_departure_minutes` int NOT NULL DEFAULT '0',
  `total_missed_hours` decimal(5,2) NOT NULL DEFAULT '0.00',
  `total_worked_hours` decimal(5,2) NOT NULL DEFAULT '0.00',
  `target_hours` decimal(5,2) NOT NULL DEFAULT '0.00',
  `overtime_hours` decimal(5,2) NOT NULL DEFAULT '0.00',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_flexible` tinyint(1) DEFAULT '0',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `details` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_date` (`user_hash`,`date`),
  KEY `idx_user_hash` (`user_hash`),
  KEY `idx_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=40009 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `error_logs_fingerPrint`
--

CREATE TABLE `error_logs_fingerPrint` (
  `id` int NOT NULL AUTO_INCREMENT,
  `error_message` longtext NOT NULL,
  `error_code` int NOT NULL,
  `error_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `event_log`
--

CREATE TABLE `event_log` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `message` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=923 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `example_table`
--

CREATE TABLE `example_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `financial_discounts`
--

CREATE TABLE `financial_discounts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `price` int DEFAULT NULL,
  `percentage` float DEFAULT NULL,
  `maximumPrice` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `ibk_price_idx` (`price`),
  KEY `ibk_id_idx` (`id`),
  KEY `ibk_id2_idx` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb3;

--
-- Temporary view structure for view `finger_print`
--

/*!50001 DROP VIEW IF EXISTS `finger_print`*/;
/*!50001 CREATE VIEW `finger_print` AS SELECT 
 1 AS `user_hash`,
 1 AS `type1`,
 1 AS `in_at`,
 1 AS `type2`,
 1 AS `out_at`*/;

--
-- Table structure for table `finger_print_report`
--

CREATE TABLE `finger_print_report` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `emp_hash` bigint DEFAULT '0',
  `vacation_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الاجازة',
  `vacation_limit_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الاجازة المسموح بها ',
  `duty_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الواجب',
  `vacation_limit_out` time DEFAULT '00:00:00' COMMENT 'تجاوز الساعات المسموح بها',
  `work_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الحضور',
  `finger_limit_out` time DEFAULT '00:00:00' COMMENT 'ساعات التاخير البصمة',
  `work_hour_target` time DEFAULT '00:00:00' COMMENT 'ساعات الدوام المطلوبة',
  `cut_off_hours` time DEFAULT '00:00:00' COMMENT 'ساعات الخصم',
  `over_time` time DEFAULT '00:00:00' COMMENT 'ساعات اضافية',
  `cut_off_amount` double DEFAULT '0' COMMENT 'مبلغ الخصم',
  `sick_vacation_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الاجازة المرضية',
  `cut_off_days` double DEFAULT '0' COMMENT 'ايام  الخصم',
  `absence_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الغياب',
  `finger_limit_out_leave` time DEFAULT '00:00:00' COMMENT 'ساعات المغادره قبل انتهاء الدوام الرسمي',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `finger_print_scheduler`
--

CREATE TABLE `finger_print_scheduler` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `fingerprint_in_id` int DEFAULT NULL,
  `fingerprint_out_id` int DEFAULT NULL,
  `dead_line` timestamp NOT NULL,
  `date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `scheduler_status` varchar(50) DEFAULT 'pending',
  `is_flexible` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fp_sch_user_hash` (`user_hash`),
  KEY `fp_sch_out_id` (`fingerprint_out_id`),
  KEY `fp_sch_in_id` (`fingerprint_in_id`),
  CONSTRAINT `fp_sch_in_id` FOREIGN KEY (`fingerprint_in_id`) REFERENCES `emp_fingerprint` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fp_sch_out_id` FOREIGN KEY (`fingerprint_out_id`) REFERENCES `emp_fingerprint` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fp_sch_user_hash` FOREIGN KEY (`user_hash`) REFERENCES `emp_information` (`user_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=5429 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `fingerprint_type`
--

CREATE TABLE `fingerprint_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3 COMMENT='انواع البصمات';

--
-- Table structure for table `flyway_schema_history`
--

CREATE TABLE `flyway_schema_history` (
  `installed_rank` int NOT NULL,
  `version` varchar(50) DEFAULT NULL,
  `description` varchar(200) NOT NULL,
  `type` varchar(20) NOT NULL,
  `script` varchar(1000) NOT NULL,
  `checksum` int DEFAULT NULL,
  `installed_by` varchar(100) NOT NULL,
  `installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `execution_time` int NOT NULL,
  `success` tinyint(1) NOT NULL,
  PRIMARY KEY (`installed_rank`),
  KEY `flyway_schema_history_s_idx` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `ftth_auth`
--

CREATE TABLE `ftth_auth` (
  `id` int NOT NULL AUTO_INCREMENT,
  `token` varchar(3000) NOT NULL,
  `modefilDate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `ftth_customers_expired_table`
--

CREATE TABLE `ftth_customers_expired_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `updated_at` datetime DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `zone` varchar(255) DEFAULT NULL,
  `fat` varchar(255) DEFAULT NULL,
  `board` varchar(255) DEFAULT NULL,
  `hasActiveSession` tinyint(1) DEFAULT NULL,
  `customer_displayValue` varchar(255) DEFAULT NULL,
  `customer_id` varchar(255) DEFAULT NULL,
  `expires` datetime DEFAULT NULL,
  `ipAddress` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zone` (`zone`),
  KEY `fat` (`fat`),
  KEY `board` (`board`),
  KEY `username` (`username`),
  KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8mb3;

--
-- Temporary view structure for view `ftth_primaty_informations`
--

/*!50001 DROP VIEW IF EXISTS `ftth_primaty_informations`*/;
/*!50001 CREATE VIEW `ftth_primaty_informations` AS SELECT 
 1 AS `updated_at`,
 1 AS `username`,
 1 AS `zone`,
 1 AS `fat`,
 1 AS `board`,
 1 AS `hasActiveSession`,
 1 AS `customer_displayValue`,
 1 AS `customer_id`,
 1 AS `expires`,
 1 AS `ipAddress`*/;

--
-- Table structure for table `ftth_primaty_informations_table`
--

CREATE TABLE `ftth_primaty_informations_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `updated_at` datetime DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `zone` varchar(255) DEFAULT NULL,
  `fat` varchar(255) DEFAULT NULL,
  `board` varchar(255) DEFAULT NULL,
  `hasActiveSession` tinyint(1) DEFAULT NULL,
  `customer_displayValue` varchar(255) DEFAULT NULL,
  `customer_id` varchar(255) DEFAULT NULL,
  `expires` datetime DEFAULT NULL,
  `ipAddress` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zone` (`zone`),
  KEY `fat` (`fat`),
  KEY `board` (`board`),
  KEY `username` (`username`),
  KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8193 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `holiday`
--

CREATE TABLE `holiday` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `type` char(45) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=utf8mb3 COMMENT='جدول العطل والمناسبات';

--
-- Table structure for table `inventory_items`
--

CREATE TABLE `inventory_items` (
  `item_id` int NOT NULL AUTO_INCREMENT,
  `item_name` varchar(255) DEFAULT NULL,
  `item_type` int NOT NULL,
  `availability_status` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`item_id`),
  KEY `ibk1_item_item_idx` (`item_type`),
  CONSTRAINT `ibk1_item_item` FOREIGN KEY (`item_type`) REFERENCES `inventory_items_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=132 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `inventory_items_types`
--

CREATE TABLE `inventory_items_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `item_assignments`
--

CREATE TABLE `item_assignments` (
  `assignment_id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint DEFAULT NULL,
  `item_id` int DEFAULT NULL,
  `assignment_date` date DEFAULT NULL,
  PRIMARY KEY (`assignment_id`),
  UNIQUE KEY `uc_user_item` (`user_hash`,`item_id`),
  KEY `user_hash` (`user_hash`),
  KEY `item_assignments_ibfk_2` (`item_id`),
  CONSTRAINT `item_assignments_ibfk_1` FOREIGN KEY (`user_hash`) REFERENCES `system_users` (`hash`),
  CONSTRAINT `item_assignments_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `inventory_items` (`item_id`) ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=512 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `job_title`
--

CREATE TABLE `job_title` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `maximum_salary` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `log_procedure`
--

CREATE TABLE `log_procedure` (
  `id` int NOT NULL AUTO_INCREMENT,
  `msg` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `message_reads`
--

CREATE TABLE `message_reads` (
  `id` int NOT NULL AUTO_INCREMENT,
  `task_hash` bigint NOT NULL,
  `message_id` int NOT NULL,
  `user_hash` varchar(255) NOT NULL,
  `seen_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `message_id` (`message_id`,`user_hash`),
  KEY `task_hash_messages` (`task_hash`),
  CONSTRAINT `message_reads_ibfk_1` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1866 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `messages`
--

CREATE TABLE `messages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `task_hash` bigint NOT NULL,
  `user_hash` bigint NOT NULL,
  `text` text NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` varchar(50) DEFAULT 'text',
  PRIMARY KEY (`id`),
  KEY `task_hash` (`task_hash`),
  KEY `user_hash` (`user_hash`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`task_hash`) REFERENCES `task` (`hash`) ON DELETE CASCADE,
  CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`user_hash`) REFERENCES `system_users` (`hash`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=420 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `version` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `mobile_detection_logs`
--

CREATE TABLE `mobile_detection_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` varchar(5000) DEFAULT NULL,
  `user_agent` text,
  `is_mobile` tinyint(1) DEFAULT '0',
  `detection_result` text,
  `ip_address` varchar(45) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `additional_info` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1337 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `notification`
--

CREATE TABLE `notification` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(45) DEFAULT NULL,
  `text` varchar(1000) DEFAULT NULL,
  `data_entry` datetime DEFAULT CURRENT_TIMESTAMP,
  `isreaded` tinyint DEFAULT '0',
  `title` varchar(100) DEFAULT NULL,
  `user_hash` bigint DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=44840 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `notifications_payroll`
--

CREATE TABLE `notifications_payroll` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `message` text,
  `from_month` date DEFAULT NULL,
  `to_month` date DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_user_hash` (`user_hash`),
  CONSTRAINT `fk_user_hash` FOREIGN KEY (`user_hash`) REFERENCES `system_users` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=299 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `notify_sms_type`
--

CREATE TABLE `notify_sms_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `ar_label` varchar(255) NOT NULL,
  `hash_type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_type_UNIQUE` (`hash_type`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `notify_whatsapp_type`
--

CREATE TABLE `notify_whatsapp_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `ar_label` varchar(255) NOT NULL,
  `hash_type` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `office_location`
--

CREATE TABLE `office_location` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `location` point DEFAULT NULL,
  `allow_merters` varchar(45) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb3 COMMENT='مواقع العمل';

--
-- Table structure for table `office_to_user`
--

CREATE TABLE `office_to_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint DEFAULT NULL,
  `office_hash` bigint DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=106108 DEFAULT CHARSET=utf8mb3 COMMENT='مواقع العمل بلنسبة للموظف';

--
-- Table structure for table `others_specifications`
--

CREATE TABLE `others_specifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `pages`
--

CREATE TABLE `pages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `location` varchar(45) NOT NULL,
  `category` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `partments`
--

CREATE TABLE `partments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `payroll_conf`
--

CREATE TABLE `payroll_conf` (
  `id` int NOT NULL AUTO_INCREMENT,
  `from` date NOT NULL,
  `to` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `penalties_rewards`
--

CREATE TABLE `penalties_rewards` (
  `id` int NOT NULL AUTO_INCREMENT,
  `penalties_rewards_types_id` int NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `date_added` date NOT NULL,
  `notes` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `penalties_rewards_ibfk_1` (`penalties_rewards_types_id`),
  CONSTRAINT `penalties_rewards_ibfk_1` FOREIGN KEY (`penalties_rewards_types_id`) REFERENCES `penalties_rewards_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `penalties_rewards_employees`
--

CREATE TABLE `penalties_rewards_employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `emp_id` bigint DEFAULT NULL,
  `penalties_rewards_id` int DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `date_added` date DEFAULT NULL,
  `penalties_rewards_types_id` int DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_id` (`emp_id`),
  KEY `penalties_rewards_id` (`penalties_rewards_id`),
  KEY `fk_penalties_rewards_types` (`penalties_rewards_types_id`),
  CONSTRAINT `fk_penalties_rewards_types` FOREIGN KEY (`penalties_rewards_types_id`) REFERENCES `penalties_rewards_types` (`id`),
  CONSTRAINT `penalties_rewards_employees_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `emp_information` (`user_hash`),
  CONSTRAINT `penalties_rewards_employees_ibfk_2` FOREIGN KEY (`penalties_rewards_id`) REFERENCES `penalties_rewards` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=171 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `penalties_rewards_types`
--

CREATE TABLE `penalties_rewards_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `permission`
--

CREATE TABLE `permission` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint DEFAULT NULL,
  `page_id` int DEFAULT NULL,
  `access` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_permission` (`user_hash`,`page_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7094 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `permission_change_log`
--

CREATE TABLE `permission_change_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` varchar(255) DEFAULT NULL,
  `field_name` varchar(255) DEFAULT NULL,
  `old_value` varchar(255) DEFAULT NULL,
  `new_value` varchar(255) DEFAULT NULL,
  `changed_by` varchar(255) DEFAULT NULL,
  `changed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `permissions_for_roles`
--

CREATE TABLE `permissions_for_roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `places_markers`
--

CREATE TABLE `places_markers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `lat` decimal(9,6) NOT NULL,
  `lng` decimal(9,6) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `positions_other`
--

CREATE TABLE `positions_other` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `project`
--

CREATE TABLE `project` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `hash` bigint NOT NULL,
  `title` varchar(255) NOT NULL,
  `project_type` varchar(255) DEFAULT NULL,
  `client_hash` varchar(255) NOT NULL,
  `description` text,
  `start_date` date DEFAULT NULL,
  `deadline` date DEFAULT NULL,
  `price` decimal(15,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_on_hash` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `project_employees`
--

CREATE TABLE `project_employees` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `employee_id` bigint NOT NULL,
  `role_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `employee_id` (`employee_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `fk_employee_id` FOREIGN KEY (`employee_id`) REFERENCES `emp_information` (`user_hash`) ON DELETE CASCADE,
  CONSTRAINT `fk_project_id` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_role_id` FOREIGN KEY (`role_id`) REFERENCES `project_roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=326 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `project_roles`
--

CREATE TABLE `project_roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `qualifications`
--

CREATE TABLE `qualifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `salary_types`
--

CREATE TABLE `salary_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `schedule_data_history`
--

CREATE TABLE `schedule_data_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` varchar(255) NOT NULL,
  `date` date NOT NULL,
  `schedule_data_before` json NOT NULL,
  `schedule_data_after` json NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `update_time` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `schedule_groups`
--

CREATE TABLE `schedule_groups` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `schedule_data` json DEFAULT NULL,
  `allowance_time` int DEFAULT NULL,
  `work_schedule` int DEFAULT NULL,
  `emp_inforamtion` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_work_schedule_emp_inforamtion` (`work_schedule`),
  CONSTRAINT `fk_work_schedule` FOREIGN KEY (`work_schedule`) REFERENCES `schedule_groups` (`id`),
  CONSTRAINT `fk_work_schedule_emp_inforamtion` FOREIGN KEY (`work_schedule`) REFERENCES `schedule_groups` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=utf8mb3;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`redha`@`%`*/ /*!50003 TRIGGER `before_schedule_groups_schedule_data_update` BEFORE UPDATE ON `schedule_groups` FOR EACH ROW BEGIN
    IF OLD.schedule_data <> NEW.schedule_data THEN
        INSERT INTO schedule_groups_history (
            schedule_group_id, name, schedule_data, allowance_time, 
            work_schedule, emp_inforamtion, created_at, updated_at, change_made_at, changed_column, old_value
        ) VALUES (
            OLD.id, OLD.name, OLD.schedule_data, OLD.allowance_time, 
            OLD.work_schedule, OLD.emp_inforamtion, OLD.created_at, OLD.updated_at, CURRENT_TIMESTAMP, 'schedule_data', OLD.schedule_data
        );
    END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `schedule_groups_history`
--

CREATE TABLE `schedule_groups_history` (
  `history_id` int NOT NULL AUTO_INCREMENT,
  `schedule_group_id` int DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `schedule_data` json DEFAULT NULL,
  `allowance_time` int DEFAULT NULL,
  `work_schedule` int DEFAULT NULL,
  `emp_inforamtion` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `change_made_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `changed_column` varchar(255) DEFAULT NULL,
  `old_value` text,
  PRIMARY KEY (`history_id`)
) ENGINE=InnoDB AUTO_INCREMENT=317 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `scheduled_tasks`
--

CREATE TABLE `scheduled_tasks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `task_type` varchar(50) NOT NULL,
  `task_data` json NOT NULL,
  `status` enum('pending','in_progress','completed','failed') NOT NULL DEFAULT 'pending',
  `execute_at` datetime NOT NULL,
  `executed_at` datetime DEFAULT NULL,
  `retry_count` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `specifications`
--

CREATE TABLE `specifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `specifications_type` int NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `percentage` int DEFAULT NULL,
  `price` int DEFAULT NULL,
  `maximumPrice` int DEFAULT NULL,
  `nameValue` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `ibkf1_specifc_type_idx` (`specifications_type`),
  CONSTRAINT `ibkf1_specifc_type` FOREIGN KEY (`specifications_type`) REFERENCES `specifications_types` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `specifications_types`
--

CREATE TABLE `specifications_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `table` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `subscriber_ftth_data_combined`
--

CREATE TABLE `subscriber_ftth_data_combined` (
  `customer_id` varchar(55) DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `mobile` varchar(255) DEFAULT NULL,
  `zone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `fat` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customer_displayValue` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `ipAddress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `expires` datetime DEFAULT NULL,
  `hasActiveSession` tinyint(1) DEFAULT NULL,
  `board` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `subscriber_info_snapshot`
--

CREATE TABLE `subscriber_info_snapshot` (
  `id` int NOT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `mobile` varchar(255) DEFAULT NULL,
  `customer_id` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `subscriber_info_table`
--

CREATE TABLE `subscriber_info_table` (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `customer_id` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (`user_name`),
  KEY `idx_mobile` (`mobile`),
  KEY `idx_customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `subscribers_customers_ftth`
--

CREATE TABLE `subscribers_customers_ftth` (
  `id` int NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `mobile` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,_utf8mb4'$[*].primaryContact.mobile'))) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `subscribers_customers_ftth_all_expired`
--

CREATE TABLE `subscribers_customers_ftth_all_expired` (
  `id` int NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `subscribers_customers_information_ftth`
--

CREATE TABLE `subscribers_customers_information_ftth` (
  `id` int NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `subscribers_sms_message_send_log`
--

CREATE TABLE `subscribers_sms_message_send_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `notify_type` int NOT NULL,
  `send_date` datetime NOT NULL,
  `expires` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `notify_type` (`notify_type`),
  CONSTRAINT `subscribers_sms_message_send_log_ibfk_1` FOREIGN KEY (`notify_type`) REFERENCES `notify_sms_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6874 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `subscribers_whatsapp_message_send_log`
--

CREATE TABLE `subscribers_whatsapp_message_send_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `notify_type` int DEFAULT NULL,
  `send_date` datetime NOT NULL,
  `expires` datetime NOT NULL,
  `status` enum('success','pending','failed','unknown_error') NOT NULL DEFAULT 'unknown_error',
  `details` text,
  PRIMARY KEY (`id`),
  KEY `notify_type` (`notify_type`),
  CONSTRAINT `subscribers_whatsapp_message_send_log_ibfk_1` FOREIGN KEY (`notify_type`) REFERENCES `notify_whatsapp_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20371 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `suggestion`
--

CREATE TABLE `suggestion` (
  `id` int NOT NULL AUTO_INCREMENT,
  `text` varchar(500) DEFAULT NULL,
  `user_hash` varchar(45) NOT NULL,
  `hash` varchar(45) NOT NULL DEFAULT '0',
  `type` int DEFAULT '1',
  `data_entry` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sender_hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=246 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `system_configration`
--

CREATE TABLE `system_configration` (
  `id` int NOT NULL AUTO_INCREMENT,
  `work_from` time DEFAULT NULL,
  `work_to` time DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `system_group_name`
--

CREATE TABLE `system_group_name` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `hash` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb3 COMMENT='اسماء الصلاحيات';

--
-- Table structure for table `system_privilage_role`
--

CREATE TABLE `system_privilage_role` (
  `id` int NOT NULL AUTO_INCREMENT,
  `group_hash` bigint NOT NULL,
  `table_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `select_op` int NOT NULL DEFAULT '1',
  `update_op` int NOT NULL DEFAULT '1',
  `insert_op` int NOT NULL DEFAULT '1',
  `delete_op` int NOT NULL DEFAULT '1',
  `hash` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=613 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `system_put_role_to_users`
--

CREATE TABLE `system_put_role_to_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `group_hash` bigint NOT NULL,
  `user_hash` bigint NOT NULL,
  `hash` bigint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=239 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `system_users`
--

CREATE TABLE `system_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `username` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `user_type` bigint NOT NULL DEFAULT '16587740479514765',
  `vication_agreement` int DEFAULT NULL,
  `image_profile` varchar(1000) DEFAULT 'https://zone-app.org/spaceLight/app/uploads/user_image_profile/default.png',
  `insert_record_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mobile_token` varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `is_ios` int DEFAULT NULL,
  `mobile_info` varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  `task_agreement` int DEFAULT '0',
  `employment_code` bigint DEFAULT NULL,
  `token` varchar(10000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_UNIQUE` (`hash`),
  KEY `index2` (`hash`) /*!80000 INVISIBLE */,
  KEY `index4` (`user_type`),
  KEY `idx_system_users_hash` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=878 DEFAULT CHARSET=utf8mb3 COMMENT='المستخدمين';

--
-- Table structure for table `system_users_type`
--

CREATE TABLE `system_users_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `hash` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `insert_record_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_UNIQUE` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3 COMMENT='انواع المستخدمين';

--
-- Table structure for table `task`
--

CREATE TABLE `task` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `discription` varchar(1000) NOT NULL,
  `owner` bigint NOT NULL,
  `start_task` datetime DEFAULT NULL,
  `deadline` datetime DEFAULT NULL,
  `score` int DEFAULT NULL,
  `emergency` int NOT NULL,
  `tag` bigint DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  `sender` bigint DEFAULT NULL,
  `receiver` bigint DEFAULT NULL,
  `status` bigint DEFAULT NULL,
  `note` text,
  `project_hash` bigint unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `isaccept` int DEFAULT '2',
  `acceptnote` varchar(1000) DEFAULT NULL,
  `accept_date` datetime DEFAULT NULL,
  `action_note` text,
  `action_date` datetime DEFAULT NULL,
  `is_simple` tinyint NOT NULL DEFAULT '0',
  `minutes_deadline` int DEFAULT NULL,
  `accept_user` bigint DEFAULT NULL,
  `rejected_user` varchar(255) DEFAULT NULL,
  `rejected_date` datetime DEFAULT NULL,
  `phone_num` varchar(15) DEFAULT NULL,
  `task_name_subscriber` varchar(255) DEFAULT NULL,
  `direct_task` tinyint(1) DEFAULT '0',
  `lat` decimal(10,6) DEFAULT NULL,
  `lng` decimal(10,6) DEFAULT NULL,
  `send_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`receiver`),
  KEY `fk_accept_user` (`accept_user`),
  KEY `idx_task_hash` (`hash`),
  CONSTRAINT `fk_accept_user` FOREIGN KEY (`accept_user`) REFERENCES `system_users` (`hash`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `check_deadline` CHECK ((((`deadline` is not null) and (`minutes_deadline` is null)) or ((`deadline` is null) and (`minutes_deadline` is not null)) or ((`deadline` is not null) and (`minutes_deadline` is not null)))),
  CONSTRAINT `check_start_task` CHECK ((((`start_task` is null) and (`minutes_deadline` is not null)) or ((`start_task` is not null) and (`minutes_deadline` is null))))
) ENGINE=InnoDB AUTO_INCREMENT=1833 DEFAULT CHARSET=utf8mb3 COMMENT='الواجبات';

--
-- Table structure for table `task_attachment`
--

CREATE TABLE `task_attachment` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `task_hash` bigint DEFAULT NULL,
  `user_hash` bigint DEFAULT NULL COMMENT 'مرفقات الواجبات',
  `link` varchar(500) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  `note` text,
  `create_record` datetime DEFAULT CURRENT_TIMESTAMP,
  `attachment_type` enum('created','status_change') NOT NULL DEFAULT 'created',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=637 DEFAULT CHARSET=utf8mb3 COMMENT='مرفقات الواجبات';

--
-- Temporary view structure for view `task_completion_view`
--

/*!50001 DROP VIEW IF EXISTS `task_completion_view`*/;
/*!50001 CREATE VIEW `task_completion_view` AS SELECT 
 1 AS `name`,
 1 AS `status`,
 1 AS `task_completion_minutes`,
 1 AS `accept_time`,
 1 AS `deliver_time`,
 1 AS `project_hash`*/;

--
-- Table structure for table `task_scheduler`
--

CREATE TABLE `task_scheduler` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `task_hash` bigint DEFAULT NULL,
  `task_name` varchar(255) NOT NULL,
  `due_date` timestamp NOT NULL,
  `notification_sent` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `fk_task_hash` (`task_hash`),
  CONSTRAINT `fk_task_hash` FOREIGN KEY (`task_hash`) REFERENCES `task` (`hash`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `task_status`
--

CREATE TABLE `task_status` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3 COMMENT='حالات الواجبات';

--
-- Table structure for table `task_team`
--

CREATE TABLE `task_team` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `status` varchar(255) NOT NULL,
  `finish_date` datetime NOT NULL,
  `task_hash` bigint NOT NULL,
  `score` int NOT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='الفريق الخاص بالواجب';

--
-- Table structure for table `task_users`
--

CREATE TABLE `task_users` (
  `task_id` int NOT NULL,
  `user_hash` bigint NOT NULL,
  PRIMARY KEY (`task_id`,`user_hash`),
  KEY `user_hash` (`user_hash`),
  CONSTRAINT `task_users_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`) ON DELETE CASCADE,
  CONSTRAINT `task_users_ibfk_2` FOREIGN KEY (`user_hash`) REFERENCES `emp_information` (`user_hash`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `total_customers_view`
--

CREATE TABLE `total_customers_view` (
  `zone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `FAT` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `total_customers` bigint NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `user_financial_discounts`
--

CREATE TABLE `user_financial_discounts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `emp_info_id` bigint NOT NULL,
  `financial_discounts_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ibk2_emp_id_info` (`emp_info_id`),
  KEY `ibk3_financial_discount_id_idx` (`financial_discounts_id`),
  CONSTRAINT `ibk2_emp_id_info` FOREIGN KEY (`emp_info_id`) REFERENCES `emp_information` (`user_hash`),
  CONSTRAINT `ibk4_financial_discount_id` FOREIGN KEY (`financial_discounts_id`) REFERENCES `financial_discounts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `user_specifications`
--

CREATE TABLE `user_specifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `emp_info_id` bigint NOT NULL,
  `specification_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ibk1_emp_id_info` (`emp_info_id`),
  KEY `ibk2_specifaction_id_idx` (`specification_id`),
  CONSTRAINT `ibk1_emp_id_info` FOREIGN KEY (`emp_info_id`) REFERENCES `emp_information` (`user_hash`),
  CONSTRAINT `ibk2_specifaction_id` FOREIGN KEY (`specification_id`) REFERENCES `specifications` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=278 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `user_vacations`
--

CREATE TABLE `user_vacations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `day_of_week_id` int NOT NULL,
  `date` date DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_hash` (`user_hash`),
  KEY `day_of_week_id` (`day_of_week_id`),
  CONSTRAINT `user_vacations_ibfk_1` FOREIGN KEY (`user_hash`) REFERENCES `emp_information` (`user_hash`) ON DELETE CASCADE,
  CONSTRAINT `user_vacations_ibfk_2` FOREIGN KEY (`day_of_week_id`) REFERENCES `days_of_week` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=818 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `users_attachment`
--

CREATE TABLE `users_attachment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint DEFAULT NULL,
  `title_attachment` varchar(255) DEFAULT NULL,
  `attachment` varchar(1000) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  `attachment_type` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1157 DEFAULT CHARSET=utf8mb3 COMMENT='المرفقات الخاصة بالموظفين';

--
-- Table structure for table `users_penatly`
--

CREATE TABLE `users_penatly` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `date` date NOT NULL,
  `shifts` json NOT NULL,
  `total_penalty` varchar(50) DEFAULT NULL,
  `total_missing_hours` varchar(50) DEFAULT NULL,
  `total_hours_worked` varchar(50) DEFAULT NULL,
  `totalLateArrivalMinutes` int DEFAULT NULL,
  `totalEarlyDepartureMinutes` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_date` (`user_hash`,`date`),
  KEY `idx_user_date` (`user_hash`,`date`),
  CONSTRAINT `users_penatly_ibfk_1` FOREIGN KEY (`user_hash`) REFERENCES `emp_information` (`user_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `vacations`
--

CREATE TABLE `vacations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` bigint NOT NULL,
  `vication_type_hash` bigint NOT NULL,
  `duration_hour` int NOT NULL,
  `duration_day` int NOT NULL,
  `admin` bigint DEFAULT NULL COMMENT 'أخر resiver',
  `status` bigint DEFAULT '444',
  `date_start` datetime NOT NULL,
  `date_end` datetime DEFAULT NULL,
  `note` text,
  `datetime` datetime DEFAULT CURRENT_TIMESTAMP,
  `hash` bigint DEFAULT NULL,
  `vication_owner_token` varchar(1000) DEFAULT NULL,
  `is_work` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_UNIQUE` (`hash`) /*!80000 INVISIBLE */,
  KEY `index3` (`user_hash`) /*!80000 INVISIBLE */,
  KEY `index5` (`vication_type_hash`),
  KEY `index6` (`hash`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12214 DEFAULT CHARSET=utf8mb3 COMMENT='الاجازات';

--
-- Table structure for table `vacations_agreement`
--

CREATE TABLE `vacations_agreement` (
  `id` int NOT NULL AUTO_INCREMENT,
  `vacations_hash` bigint NOT NULL,
  `sender` bigint NOT NULL,
  `receiver` bigint NOT NULL,
  `status` bigint NOT NULL,
  `note` text NOT NULL,
  `hash` bigint DEFAULT NULL,
  `flage` int DEFAULT '0',
  `date_entry` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_UNIQUE` (`hash`) /*!80000 INVISIBLE */,
  KEY `index3` (`vacations_hash`) USING BTREE,
  KEY `index4` (`sender`) USING BTREE,
  KEY `index5` (`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb3 COMMENT='موافقات الاجازة';

--
-- Table structure for table `vacations_status`
--

CREATE TABLE `vacations_status` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_UNIQUE` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COMMENT='حالات الاجازة';

--
-- Table structure for table `vacations_type`
--

CREATE TABLE `vacations_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  `hour_day` varchar(45) DEFAULT NULL,
  `hash` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_UNIQUE` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3 COMMENT='انواع الاجازة';

--
-- Temporary view structure for view `view_customer_status`
--

/*!50001 DROP VIEW IF EXISTS `view_customer_status`*/;
/*!50001 CREATE VIEW `view_customer_status` AS SELECT 
 1 AS `zone`,
 1 AS `FAT`,
 1 AS `total_customers`,
 1 AS `customers_not_working_count`*/;

--
-- Temporary view structure for view `view_customers_FTTH_expire_soon`
--

/*!50001 DROP VIEW IF EXISTS `view_customers_FTTH_expire_soon`*/;
/*!50001 CREATE VIEW `view_customers_FTTH_expire_soon` AS SELECT 
 1 AS `customer_displayValue`,
 1 AS `username`,
 1 AS `zone_displayValue`,
 1 AS `expires`*/;

--
-- Temporary view structure for view `view_subscriber_info`
--

/*!50001 DROP VIEW IF EXISTS `view_subscriber_info`*/;
/*!50001 CREATE VIEW `view_subscriber_info` AS SELECT 
 1 AS `id`,
 1 AS `user_name`,
 1 AS `mobile`,
 1 AS `customer_id`*/;

--
-- Temporary view structure for view `view_zone`
--

/*!50001 DROP VIEW IF EXISTS `view_zone`*/;
/*!50001 CREATE VIEW `view_zone` AS SELECT 
 1 AS `zone`,
 1 AS `zoneNumber`*/;

--
-- Temporary view structure for view `view_zone_customer_summary`
--

/*!50001 DROP VIEW IF EXISTS `view_zone_customer_summary`*/;
/*!50001 CREATE VIEW `view_zone_customer_summary` AS SELECT 
 1 AS `zone`,
 1 AS `FAT`,
 1 AS `updated_at`,
 1 AS `customers_not_working_count`*/;

--
-- Table structure for table `whatsAppTemplates`
--

CREATE TABLE `whatsAppTemplates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `notify_whatsapp_type` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_notify_whatsapp_type` (`notify_whatsapp_type`),
  CONSTRAINT `fk_notify_whatsapp_type` FOREIGN KEY (`notify_whatsapp_type`) REFERENCES `notify_whatsapp_type` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `whatsapp`
--

CREATE TABLE `whatsapp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sender` varchar(45) DEFAULT NULL,
  `message` varchar(1000) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `obj_backup` json DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `time` int DEFAULT NULL,
  `media` varchar(2048) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3451 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `work_schedules`
--

CREATE TABLE `work_schedules` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `zk_devices`
--

CREATE TABLE `zk_devices` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `ip` varchar(45) NOT NULL,
  `port` int NOT NULL,
  `lat` decimal(10,6) NOT NULL DEFAULT '0.000000',
  `lng` decimal(10,6) NOT NULL DEFAULT '0.000000',
  `device_info` varchar(255) GENERATED ALWAYS AS (concat(`ip`,_utf8mb3':',`port`)) STORED,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `zk_fingerprints_log`
--

CREATE TABLE `zk_fingerprints_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `indRegId` int DEFAULT NULL,
  `dateTime` datetime DEFAULT NULL,
  `user_hash` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `device_hash` varchar(255) DEFAULT NULL,
  `device_info` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_fingerprint_log` (`user_hash`,`dateTime`,`indRegId`,`device_info`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `zk_queue`
--

CREATE TABLE `zk_queue` (
  `id` int NOT NULL AUTO_INCREMENT,
  `task_type` varchar(50) NOT NULL,
  `task_data` json NOT NULL,
  `status` enum('pending','in_progress','completed','failed') NOT NULL DEFAULT 'pending',
  `executed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_hash` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`task_data`,_utf8mb4'$.user_hash'))) VIRTUAL,
  `message` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=669 DEFAULT CHARSET=utf8mb3;

--
-- Table structure for table `zk_users`
--

CREATE TABLE `zk_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_hash` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `device_hash` int DEFAULT NULL,
  `que_id` int NOT NULL,
  `finger_print_template` json DEFAULT NULL,
  `face_finger_print_template` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_hash` (`user_hash`),
  KEY `ibfk1_zk_device_id_idx` (`device_hash`),
  KEY `fk_que_id` (`que_id`),
  CONSTRAINT `fk_que_id` FOREIGN KEY (`que_id`) REFERENCES `zk_queue` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ibfk1_zk_device_id` FOREIGN KEY (`device_hash`) REFERENCES `zk_devices` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8mb3;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`redha`@`%`*/ /*!50003 TRIGGER `delete_queue_when_user_deleted` BEFORE DELETE ON `zk_users` FOR EACH ROW BEGIN
    DELETE FROM spaceLight.zk_queue WHERE id = OLD.que_id;
END */;;
DELIMITER ;

--
-- Table structure for table `zones_ftth`
--

CREATE TABLE `zones_ftth` (
  `id` int NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

--
-- Dumping events for database 'spaceLight'
--
/*!50106 DROP EVENT IF EXISTS `daily_fix_missing_clockouts` */;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`redha`@`%`*/ /*!50106 EVENT `daily_fix_missing_clockouts` ON SCHEDULE EVERY 1 DAY STARTS '2024-07-09 04:00:00' ON COMPLETION NOT PRESERVE DISABLE DO CALL fix_missing_clockouts() */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
/*!50106 DROP EVENT IF EXISTS `daily_insert_absences` */;;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`redha`@`%`*/ /*!50106 EVENT `daily_insert_absences` ON SCHEDULE EVERY 1 DAY STARTS '2024-07-09 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL InsertAbsences() */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
/*!50106 DROP EVENT IF EXISTS `daily_vacation_check` */;;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`redha`@`%`*/ /*!50106 EVENT `daily_vacation_check` ON SCHEDULE EVERY 1 DAY STARTS '2024-06-30 23:00:00' ON COMPLETION NOT PRESERVE DISABLE DO CALL InsertActualSchedules() */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
/*!50106 DROP EVENT IF EXISTS `redha3` */;;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection  = utf8mb4_0900_ai_ci */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`redha`@`%`*/ /*!50106 EVENT `redha3` ON SCHEDULE EVERY 1 DAY STARTS '2022-08-25 23:35:00' ON COMPLETION PRESERVE ENABLE DO BEGIN
      
      insert into emp_fingerprint(user_hash,fingerprint_type,fingerprint_time,in_out,hash,user_create_record)
SELECT user_hash,'111',
(SELECT work_to FROM emp_information where user_hash=emp_fingerprint.user_hash) as fingerprint_time,
'out',
(FLOOR( 1 + RAND( ) *UNIX_TIMESTAMP(NOW()) )) as hash,
'5555'
 FROM hr.emp_fingerprint 
 where date(data_entry)=CURDATE() group by user_hash having count(user_hash)<2;

      
      
      
      insert into event_log(message) values('event start');
      END */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
DELIMITER ;

--
-- Dumping routines for database 'spaceLight'
--
/*!50003 DROP FUNCTION IF EXISTS `check_if_in_exist` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` FUNCTION `check_if_in_exist`(user_hash_para bigint(20)) RETURNS int
BEGIN
DECLARE number_of_row int ; 
SELECT count(user_hash) as number_of_in_fingerprint into number_of_row  FROM emp_fingerprint where user_hash=user_hash_para and  date(fingerprint_time)=curdate() and in_out='in'; 

RETURN number_of_row;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `check_in_fingerprint` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` FUNCTION `check_in_fingerprint`(user_hash_para bigint(20),type_fingerprint varchar(20)) RETURNS int
BEGIN
DECLARE number_of_row int ; 
DECLARE in_time time ; 
DECLARE out_time time ; 
SELECT count(user_hash) as number_of_in_fingerprint into number_of_row  FROM emp_fingerprint where user_hash=user_hash_para and  date(fingerprint_time)=curdate() and in_out=type_fingerprint; 


RETURN number_of_row;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `get_vacations_current_month` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` FUNCTION `get_vacations_current_month`(
    vacations_type_v bigint, emp_hash_v bigint
) RETURNS double
    DETERMINISTIC
BEGIN
     DECLARE number_of_hours_v double; 
    DECLARE number_of_day double;
    
    SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) INTO number_of_day 
    FROM emp_information 
    WHERE user_hash = emp_hash_v;

    CASE vacations_type_v
        WHEN '111' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end, date_start)) * number_of_day, 0) INTO number_of_hours_v
            FROM vacations
            WHERE user_hash = emp_hash_v
                AND MONTH(date_start) = MONTH(CURDATE())
                AND YEAR(date_start) = YEAR(CURDATE())
                AND status = '111'
                AND vication_type_hash = '111';
        
        WHEN '222' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end, date_start)) * number_of_day, 0) INTO number_of_hours_v
            FROM vacations
            WHERE user_hash = emp_hash_v
                AND MONTH(date_start) = MONTH(CURDATE())
                AND YEAR(date_start) = YEAR(CURDATE())
                AND status = '111'
                AND vication_type_hash = '222';
        
        WHEN '333' THEN
            SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(date_end, date_start)))), '00:00:00') INTO number_of_hours_v
            FROM vacations
            WHERE user_hash = emp_hash_v
                AND MONTH(date_start) = MONTH(CURDATE())
                AND YEAR(date_start) = YEAR(CURDATE())
                AND status = '111'
                AND vication_type_hash = '333';
        
        WHEN '444' THEN
            SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(date_end, date_start)))), '00:00:00') INTO number_of_hours_v
            FROM vacations
            WHERE user_hash = emp_hash_v
                AND MONTH(date_start) = MONTH(CURDATE())
                AND YEAR(date_start) = YEAR(CURDATE())
                AND status = '111'
                AND vication_type_hash = '444';
        
        WHEN '555' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end, date_start)) * number_of_day, 0) INTO number_of_hours_v
            FROM vacations
            WHERE user_hash = emp_hash_v
                AND MONTH(date_start) = MONTH(CURDATE())
                AND YEAR(date_start) = YEAR(CURDATE())
                AND status = '111'
                AND vication_type_hash = '555';
        
        ELSE
            SET number_of_hours_v = 404;
    END CASE;

    RETURN number_of_hours_v;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `get_vacations_current_year` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` FUNCTION `get_vacations_current_year`(
    vacations_type_v bigint , emp_hash_v bigint
) RETURNS double
    READS SQL DATA
    DETERMINISTIC
BEGIN
    DECLARE number_of_hours_v double ; 
    DECLARE number_of_day double;

    SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) INTO number_of_day FROM emp_information WHERE user_hash = emp_hash_v;

    CASE vacations_type_v
        WHEN '111' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end , date_start)), 0) * number_of_day INTO number_of_hours_v FROM vacations WHERE user_hash = emp_hash_v AND YEAR(date_start) = YEAR(CURDATE()) AND status = '111' AND vication_type_hash = '111';
        WHEN '222' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end , date_start)), 0) * number_of_day INTO number_of_hours_v FROM vacations WHERE user_hash = emp_hash_v AND YEAR(date_start) = YEAR(CURDATE()) AND status = '111' AND vication_type_hash = '222';
        WHEN '333' THEN
            SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(date_end , date_start)))), 0) INTO number_of_hours_v FROM vacations WHERE user_hash = emp_hash_v AND YEAR(date_start) = YEAR(CURDATE()) AND status = '111' AND vication_type_hash = '333';
        WHEN '444' THEN
            SELECT IFNULL(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(date_end , date_start)))), 0) INTO number_of_hours_v FROM vacations WHERE user_hash = emp_hash_v AND YEAR(date_start) = YEAR(CURDATE()) AND status = '111' AND vication_type_hash = '444';
        WHEN '555' THEN
            SELECT IFNULL(SUM(DATEDIFF(date_end , date_start)), 0) * number_of_day INTO number_of_hours_v FROM vacations WHERE user_hash = emp_hash_v AND YEAR(date_start) = YEAR(CURDATE()) AND status = '111' AND vication_type_hash = '555';
        ELSE
            SET number_of_hours_v = 404;
    END CASE;

    RETURN number_of_hours_v;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `get_vacations_days` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` FUNCTION `get_vacations_days`(
date_from_v date , date_to_v date , vacations_type_v bigint , emp_hash_v bigint
) RETURNS double
BEGIN
DECLARE number_of_hours_v double ; 
CASE vacations_type_v
	WHEN  '111' THEN
		   
		   select ifnull(sum(datediff( if(date(date_end) > date_to_v ,(date_to_v + INTERVAL 1 DAY),date(date_end)) , if(date(date_start) < date_from_v ,date_from_v , date(date_start) )) - (select count(*) from holiday where holiday.date >= if(vacations.date_start < date_from_v ,date_from_v , vacations.date_start ) and holiday.date < if(vacations.date_end > date_to_v ,date_to_v,vacations.date_end) )  ),0) into number_of_hours_v from vacations  where  ( user_hash = emp_hash_v and date(date_start) between date_from_v and date_to_v and status = '111' and vication_type_hash = '111' ) or ( user_hash = emp_hash_v and date(date_end) between date_from_v and date_to_v and status = '111' and vication_type_hash = '111' );
	WHEN  '222' THEN
		   
		   select ifnull(sum(datediff( if(date(date_end) > date_to_v ,(date_to_v + INTERVAL 1 DAY),date(date_end)) , if(date(date_start) < date_from_v ,date_from_v , date(date_start) )) - (select count(*) from holiday where holiday.date >= if(vacations.date_start < date_from_v ,date_from_v , vacations.date_start ) and holiday.date < if(vacations.date_end > date_to_v ,date_to_v,vacations.date_end) )  ),0) into number_of_hours_v from vacations  where  ( user_hash = emp_hash_v and date(date_start) between date_from_v and date_to_v and status = '111' and vication_type_hash = '222')  or ( user_hash = emp_hash_v and date(date_end) between date_from_v and date_to_v and status = '111' and vication_type_hash = '222');

	WHEN  '555' THEN
		   
		   select ifnull(sum(datediff( if(date(date_end) > date_to_v ,(date_to_v + INTERVAL 1 DAY),date(date_end)) , if(date(date_start) < date_from_v ,date_from_v , date(date_start) )) - (select count(*) from holiday where holiday.date >= if(vacations.date_start < date_from_v ,date_from_v , vacations.date_start ) and holiday.date < if(vacations.date_end > date_to_v ,date_to_v,vacations.date_end) ) ),0) into number_of_hours_v from vacations  where  ( user_hash = emp_hash_v and date(date_start) between date_from_v and date_to_v and status = '111' and vication_type_hash = '555')  or ( user_hash = emp_hash_v and date(date_end) between date_from_v and date_to_v and status = '111' and vication_type_hash = '555');

	ELSE
		   SET number_of_hours_v = 404;
	END CASE;

RETURN number_of_hours_v;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `get_vacations_hours` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` FUNCTION `get_vacations_hours`(
date_from_v date , date_to_v date , vacations_type_v bigint , emp_hash_v bigint
) RETURNS double
BEGIN
DECLARE number_of_hours_v time default '00:00:00' ; 
CASE vacations_type_v
	when '333'  then
		   select ifnull(sec_to_time(sum(time_to_sec(timediff(date_end , date_start)))),'00:00:00') into number_of_hours_v from vacations  where  user_hash = emp_hash_v and date_start between cast(concat(date_from_v, ' ', '00:00:00') as datetime)  and cast(concat(date_to_v, ' ', '23:59:59') as datetime) and status = '111' and vication_type_hash = '333' ;
	when '444'  then
		   select ifnull(sec_to_time(sum(time_to_sec(timediff(date_end , date_start)))),'00:00:00') into number_of_hours_v from vacations  where  user_hash = emp_hash_v and date_start between cast(concat(date_from_v, ' ', '00:00:00') as datetime)  and cast(concat(date_to_v, ' ', '23:59:59') as datetime) and status = '111' and vication_type_hash = '444' ;
	ELSE
		   SET number_of_hours_v = 404;
	END CASE;

RETURN number_of_hours_v;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `make_fingerprint` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` FUNCTION `make_fingerprint`() RETURNS int
BEGIN
DECLARE number_of_row int ; 
SELECT count(user_hash) as number_of_in_fingerprint into number_of_row  FROM emp_fingerprint where user_hash='16593540552923218' and  date(fingerprint_time)=curdate() and in_out="in"; 
RETURN number_of_row;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `make_in_fingerprint` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` FUNCTION `make_in_fingerprint`() RETURNS int
BEGIN
DECLARE number_of_row int ; 
SELECT count(user_hash) as number_of_in_fingerprint into number_of_row  FROM emp_fingerprint where user_hash='16593540552923218' and  date(fingerprint_time)=curdate() and in_out="in"; 
RETURN number_of_row;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `new_function` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` FUNCTION `new_function`(
date_from_v date , date_to_v date , vacations_type_v bigint , emp_hash_v bigint
) RETURNS double
BEGIN
DECLARE number_of_hours_v time default '00:00:00' ; 
CASE vacations_type_v
	when '333'  then
		   select ifnull(sum((timediff(date_end , date_start))),'00:00:00') into number_of_hours_v from vacations  where  user_hash = emp_hash_v and date_start between date_from_v and date_to_v and status = '111' and vication_type_hash = '333' ;
	when '444'  then
		   select ifnull(sum((timediff(date_end , date_start))),'00:00:00') into number_of_hours_v from vacations  where  user_hash = emp_hash_v and date_start between date_from_v and date_to_v and status = '111' and vication_type_hash = '444' ;
	ELSE
		   SET number_of_hours_v = 404;
	END CASE;

RETURN number_of_hours_v;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CalculateMonthlyVacationDays` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `CalculateMonthlyVacationDays`(IN userHash VARCHAR(255), IN startDateInput VARCHAR(255), IN endDateInput VARCHAR(255))
BEGIN
    SELECT 
        v.id,
        v.user_hash,
        vt.hash AS vacation_type_hash,
        v.date_start AS startDate,
        v.date_end AS endDate,
        SUM(DATEDIFF(v.date_end, v.date_start)) AS difference,
        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash) AS number_of_hours_per_day,
        vt.type AS vacation_type,

        IFNULL(
            CASE 
                WHEN vt.hash IN ('111', '222', '555', '333', '444') THEN  
                
                    CASE
                        WHEN v.date_start < CONCAT(DATE(v.date_start), ' ', ei.work_from) THEN
                            
                            SUM(DATEDIFF(v.date_end, v.date_start)) * 
                                (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        WHEN v.date_end > CONCAT(DATE(v.date_start), ' ', ei.work_to) THEN
                            
                            SUM(DATEDIFF(v.date_end, v.date_start)) * 
                                (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        ELSE
                            ROUND(TIMESTAMPDIFF(MINUTE, v.date_start, v.date_end) / 60.0)
                    END
                ELSE 0
            END,
        0) AS number_of_hours_v,

        IFNULL(
            CASE 
                WHEN DATEDIFF(v.date_end, v.date_start) = 0 AND vt.hash IN ('111','333','444','222', '555') THEN
                    IF(TIMESTAMPDIFF(HOUR, v.date_start, v.date_end) >= (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash),
                        1, 
                        ROUND(SUM(DATEDIFF(v.date_end, v.date_start)) * 
                            (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash) /
                            (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)) 
                    )
                WHEN v.vication_type_hash IN ('111','333','444','222', '555') THEN
                    Round(SUM(DATEDIFF(v.date_end, v.date_start)) 
                        * 
                        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        /
                        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash))

                WHEN (v.date_start IS NULL OR v.date_end IS NULL) THEN
                    0
                ELSE 
                    'not found'
            END,
        0
        ) AS number_of_days
    FROM 
        vacations_type vt 
    LEFT JOIN 
        vacations v ON vt.hash = v.vication_type_hash AND v.status = '111' 
        AND v.user_hash = userHash 
       
			AND DATE(v.date_start) >= startDateInput 
             AND DATE(v.date_end) <= endDateInput
          
          
    LEFT JOIN 
        emp_information ei ON v.user_hash = ei.user_hash
    GROUP BY 
        v.user_hash, 
        vt.hash;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CalculateMonthlyVacationDays(IN userHash VARCHAR(255))` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `CalculateMonthlyVacationDays(IN userHash VARCHAR(255))`()
BEGIN
    SELECT 
        v.id,
        v.user_hash,
        vt.hash AS vacation_type_hash,
        v.date_start AS startDate,
        v.date_end AS endDate,
        SUM(DATEDIFF(v.date_end, v.date_start)) AS difference,
        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash) AS number_of_hours_per_day,
        vt.type AS vacation_type,

        IFNULL(
            CASE 
                WHEN vt.hash IN ('111', '222', '555', '333', '444') THEN  
                    CASE
                        WHEN v.date_start < CONCAT(DATE(v.date_start), ' ', ei.work_from) THEN
                            
                            SUM(DATEDIFF(v.date_end, v.date_start)) * 
                                (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        WHEN v.date_end > CONCAT(DATE(v.date_start), ' ', ei.work_to) THEN
                            
                            SUM(DATEDIFF(v.date_end, v.date_start)) * 
                                (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        ELSE
                            ROUND(TIMESTAMPDIFF(MINUTE, v.date_start, v.date_end) / 60.0)
                    END
                ELSE 0
            END,
        0) AS number_of_hours_v,

        IFNULL(
            CASE 
                WHEN DATEDIFF(v.date_end, v.date_start) = 0 AND vt.hash IN ('111','333','444','222', '555') THEN
                    IF(TIMESTAMPDIFF(HOUR, v.date_start, v.date_end) >= (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash),
                        1, 
                        ROUND(SUM(DATEDIFF(v.date_end, v.date_start)) * 
                            (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash) /
                            (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)) 
                    )
                WHEN v.vication_type_hash IN ('111','333','444','222', '555') THEN
                    Round(SUM(DATEDIFF(v.date_end, v.date_start)) 
                        * 
                        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash)
                        /
                        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = v.user_hash))

                WHEN (v.date_start IS NULL OR v.date_end IS NULL) THEN
                    0
                ELSE 
                    'not found'
            END,
        0
        ) AS number_of_days
    FROM 
        vacations_type vt 
    LEFT JOIN 
        vacations v ON vt.hash = v.vication_type_hash AND v.status = '111' 
        AND v.user_hash = userHash 
        AND YEAR(v.date_start) = YEAR(CURRENT_DATE())
        AND MONTH(v.date_start) = MONTH(CURDATE())
    LEFT JOIN 
        emp_information ei ON v.user_hash = ei.user_hash
    GROUP BY 
        v.user_hash, 
        vt.hash;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CalculateTodayVacationDays` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `CalculateTodayVacationDays`(IN userHash VARCHAR(255))
BEGIN
    SELECT 
        MIN(v.id) AS id,
        userHash AS user_hash,
        MIN(vt.hash) AS vacation_type_hash,
        MIN(v.date_start) AS startDate,
        MIN(v.date_end) AS endDate,
        DATEDIFF(MIN(v.date_end), MIN(v.date_start)) AS difference,
        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = userHash) AS number_of_hours_per_day,
        MIN(vt.type) AS vacation_type,
        CASE 
            WHEN MIN(vt.hash) IN ('111', '222', '555', '333', '444') THEN
                CASE
                    WHEN MIN(v.date_start) < CONCAT(MIN(DATE(v.date_start)), ' ', ei.work_from) THEN
                        DATEDIFF(MIN(v.date_end), MIN(v.date_start)) *
                            (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = userHash)
                    WHEN MAX(v.date_end) > CONCAT(MAX(DATE(v.date_start)), ' ', ei.work_to) THEN
                        DATEDIFF(MAX(v.date_end), MIN(v.date_start)) *
                            (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = userHash)
                    ELSE
                        ROUND(TIMESTAMPDIFF(MINUTE, MIN(v.date_start), MAX(v.date_end)) / 60.0)
                END
            ELSE 0
        END AS number_of_hours_v,
        CASE 
            WHEN DATEDIFF(MAX(v.date_end), MIN(v.date_start)) = 0 AND MIN(vt.hash) IN ('111','333','444','222', '555') THEN
                IF(TIMESTAMPDIFF(HOUR, MIN(v.date_start), MAX(v.date_end)) >= (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = userHash),
                    1, 
                    ROUND(DATEDIFF(MAX(v.date_end), MIN(v.date_start)) *
                        (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = userHash) /
                        TIMESTAMPDIFF(HOUR, work_from, work_to))
                )
            WHEN MIN(vt.hash) IN ('111','333','444','222', '555') THEN
                ROUND(DATEDIFF(MAX(v.date_end), MIN(v.date_start)) *
                    (SELECT TIMESTAMPDIFF(HOUR, work_from, work_to) FROM emp_information WHERE user_hash = userHash) /
                    TIMESTAMPDIFF(HOUR, work_from, work_to))
            ELSE 0
        END AS number_of_days
    FROM 
        vacations_type vt 
    LEFT JOIN 
        vacations v ON vt.hash = v.vication_type_hash AND v.status = '111' 
        AND v.user_hash = userHash 
        AND DATE(v.date_start) = CURDATE()
    LEFT JOIN 
        emp_information ei ON v.user_hash = ei.user_hash
    WHERE 
        v.user_hash = userHash
    GROUP BY 
        userHash, 
        vt.type;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `finger_print_report` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `finger_print_report`(
in date_from_v date , date_to_v date
)
BEGIN
	DECLARE salary_per_sec,emp_id_v,base_salary_v,sick_leave_v,number_of_holidays_v double DEFAULT 0;
    DECLARE emp_hash_v bigint default 0;
    DECLARE vacations_limit_v,work_from_v , work_to_v , allow_time_v time default '00:00:00' ;
    DECLARE emp_name_v,location_v varchar(255) DEFAULT '' ;
	DECLARE done_v TINYINT DEFAULT FALSE;
	
      
	DECLARE  over_time_after_v time default '00:00:00' ; 
	DECLARE  over_time_allow_v time default '00:15:00' ; 
	

    DECLARE cut_off_amount_v,cut_off_days_v double default 0 ;
    
    declare vacation_limit_out_v,finger_limit_out_v,finger_limit_out_leave_v,cut_off_hours_v,over_time_v,over_time_holiday_v,work_hour_target_v,duty_hour_v,vacation_hour_v,sick_vacation_hour_v,vacation_limit_day_v,work_hour_v,hour_work_day_v,in_allow_diff,absence_hour_v time default '00:00:00';
    
    DECLARE cursor_emp_info CURSOR FOR SELECT emp_info.`emp_id` ,emp_info.`emp_hash`, emp_info.`emp_name` , emp_info.`primary_salary`,   emp_info.`vacations_limit`,    emp_info.`sick_leave`,   emp_info.`work_from`,  emp_info.`work_to`,   emp_info.`allow_time`,  emp_info.`location` FROM `emp_info`  inner join emp_information on emp_information.user_hash = emp_info.emp_hash and is_work = 1 and start_work_date <= date_to_v;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_v = TRUE;
    
	DROP TABLE IF EXISTS  finger_print_report ;
	CREATE TABLE `finger_print_report` (
			  `id` int NOT NULL AUTO_INCREMENT,
			  `name` varchar(255) DEFAULT NULL,
			  `emp_hash` bigint DEFAULT 0,
			  `vacation_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الاجازة',
			  `vacation_limit_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الاجازة المسموح بها ',
			  `duty_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الواجب',
			  `vacation_limit_out` time DEFAULT '00:00:00' COMMENT 'تجاوز الساعات المسموح بها',
			  `work_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الحضور',
			  `finger_limit_out`time DEFAULT '00:00:00' COMMENT 'ساعات التاخير البصمة',
			  `work_hour_target`time DEFAULT '00:00:00' COMMENT 'ساعات الدوام المطلوبة',
			  `cut_off_hours` time DEFAULT '00:00:00' COMMENT 'ساعات الخصم',
			  `over_time` time DEFAULT '00:00:00' COMMENT 'ساعات اضافية',
			  `cut_off_amount` double DEFAULT 0 COMMENT 'مبلغ الخصم',
			  `sick_vacation_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الاجازة المرضية',
			  `cut_off_days` double DEFAULT '0' COMMENT 'ايام  الخصم',
			  `absence_hour` time DEFAULT '00:00:00' COMMENT 'ساعات الغياب',
              `finger_limit_out_leave` time DEFAULT '00:00:00' COMMENT 'ساعات المغادره قبل انتهاء الدوام الرسمي',
	
			  PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;



	
	OPEN cursor_emp_info;
    
	set number_of_holidays_v = (select count(*) from holiday where holiday.date between date_from_v and date_to_v) ; 
		loop_on_each_emp: 
		LOOP
			set vacation_hour_v = 0 ;
            set sick_vacation_hour_v = 0 ;
			set over_time_after_v = '00:00:00' ;
            set in_allow_diff = '00:00:00' ;
			FETCH cursor_emp_info INTO emp_id_v  , emp_hash_v , emp_name_v , base_salary_v,   vacation_limit_day_v,    sick_leave_v,   work_from_v,  work_to_v,   allow_time_v,  location_v ; 
			
			
            
            
			
            set in_allow_diff = timediff(allow_time_v , work_from_v) ;
            
            
			IF done_v THEN 
				LEAVE loop_on_each_emp ; 
			ELSE 
				
				
				
			    
                select TIMEDIFF(work_to , work_from) into hour_work_day_v  from emp_information  where user_hash = emp_hash_v;
			            
                
                set vacation_hour_v =  sec_to_time(( time_to_sec(hour_work_day_v) * ( get_vacations_days(date_from_v,date_to_v,222,emp_hash_v)))) ; 
   			    

				set vacation_hour_v = sec_to_time(time_to_sec(vacation_hour_v) + time_to_sec( get_vacations_hours(date_from_v,date_to_v,333,emp_hash_v))) ; 
			
				set vacations_limit_v = (SEC_TO_TIME((vacation_limit_day_v * time_to_sec(hour_work_day_v))/12)) ;
				set vacation_limit_out_v =if( (time_to_sec(vacation_hour_v)- vacations_limit_v ) > 0,SEC_TO_TIME(time_to_sec(vacation_hour_v)- vacations_limit_v ),0);
				
                
				set absence_hour_v =  sec_to_time(( time_to_sec(hour_work_day_v) * ( get_vacations_days(date_from_v,date_to_v,555,emp_hash_v)))) ; 
                IF(absence_hour_v IS NULL) THEN
					SET absence_hour_v := '00:00:00';
				END IF;
				
				
				set sick_vacation_hour_v =  sec_to_time(( time_to_sec(hour_work_day_v) * ( get_vacations_days(date_from_v,date_to_v,111,emp_hash_v)))) ; 
                
                IF(sick_vacation_hour_v IS NULL) THEN
					SET sick_vacation_hour_v := '00:00:00';
				END IF;
				
  			    

                
				set duty_hour_v =   ( get_vacations_hours(date_from_v,date_to_v,444,emp_hash_v)) ;
			    
				
                
				set work_hour_target_v = sec_to_time((time_to_sec(hour_work_day_v) * (DATEDIFF(date_to_v , date_from_v) - number_of_holidays_v + 1 )) - time_to_sec(vacation_hour_v) - time_to_sec(sick_vacation_hour_v) - time_to_sec(duty_hour_v) ) ;
                
				
                set work_hour_v  = (SELECT 
				
				
				ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(if(time(print_out.fingerprint_time)  > work_to_v,work_to_v,time(print_out.fingerprint_time  )), if(time(print_in.fingerprint_time)  > allow_time_v , timediff( time(print_in.fingerprint_time) , time(in_allow_diff))  , work_from_v)),0)))),'00:00:00') as 'h' 

                FROM emp_fingerprint  as print_in FORCE INDEX (index5) inner join emp_fingerprint as   print_out FORCE INDEX (index5)
				
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and time(print_in.fingerprint_time) <= work_to_v
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
                and print_out.day_print between  date_from_v and date_to_v 
                
                LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL
                group by print_in.user_hash);
                                
				IF(work_hour_v IS NULL) THEN
					SET work_hour_v := '00:00:00';
				END IF;
				
                
				set finger_limit_out_v  = (SELECT 
				ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF( if(time(print_in.fingerprint_time)  > allow_time_v ,time(print_in.fingerprint_time) , allow_time_v),allow_time_v),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5)  inner join emp_fingerprint as   print_out FORCE INDEX (index5)
				
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
                and print_out.day_print between  date_from_v and date_to_v 
                
                LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL 
                group by print_in.user_hash);
                                
				IF(finger_limit_out_v IS NULL) THEN
					SET finger_limit_out_v := '00:00:00';
				END IF;
                
                
                
                
                set finger_limit_out_leave_v = (SELECT 
                ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF( work_to_v,if(time(print_out.fingerprint_time)  < work_to_v ,time(print_out.fingerprint_time) , work_to_v)),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5) inner join emp_fingerprint as   print_out FORCE INDEX (index5)
				
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
                and print_out.day_print between  date_from_v and date_to_v 
                
                LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL
                group by print_in.user_hash);
                                
				IF(finger_limit_out_leave_v IS NULL) THEN
					SET finger_limit_out_leave_v := '00:00:00';
				END IF;
                
				set finger_limit_out_v  = ADDTIME(finger_limit_out_v,finger_limit_out_leave_v);   
                
                
                
                
				set cut_off_hours_v = TIMEDIFF(work_hour_target_v , work_hour_v  ) ;
                
                
                
				
                
                set finger_limit_out_v = (SELECT 
				
                ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF( if(time(print_in.fingerprint_time)  > allow_time_v ,time(print_in.fingerprint_time) , work_from_v),work_from_v),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5)  inner join emp_fingerprint as   print_out FORCE INDEX (index5) 
			
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
				and print_out.day_print between  date_from_v and date_to_v 

				LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL
                
                group by print_in.user_hash);
				IF(finger_limit_out_v IS NULL) THEN
					SET finger_limit_out_v := '00:00:00';
				END IF;
                
                
                
                set finger_limit_out_leave_v = (SELECT 
                ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF( work_to_v,if(time(print_out.fingerprint_time)  < work_to_v ,time(print_out.fingerprint_time) , work_to_v)),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5)  inner join emp_fingerprint as   print_out  FORCE INDEX (index5)
                
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
				and print_out.day_print between  date_from_v and date_to_v 

				LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL
                
                
                group by print_in.user_hash);
                                
				IF(finger_limit_out_leave_v IS NULL) THEN
					SET finger_limit_out_leave_v := '00:00:00';
				END IF;
                
				set finger_limit_out_v  = ADDTIME(finger_limit_out_v,finger_limit_out_leave_v);   

                
                
                
                
				
                set cut_off_hours_v = TIMEDIFF(work_hour_target_v , work_hour_v ) ;
				
                
                
                 
				set cut_off_days_v = TIME_TO_SEC(cut_off_hours_v)/TIME_TO_SEC(hour_work_day_v) ;
                
                
                
                
                
                set over_time_after_v =  ADDTIME(work_to_v,over_time_allow_v);
                
                set over_time_v  = (SELECT 
				
                
                ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(if(time(print_out.fingerprint_time)  > over_time_after_v,time(print_out.fingerprint_time  ),work_to_v), if(time(print_in.fingerprint_time) <= work_to_v  ,work_to_v, time(print_in.fingerprint_time) ) ),0)))),'00:00:00') as 'h' 
                FROM emp_fingerprint  as print_in FORCE INDEX (index5) inner join emp_fingerprint as   print_out FORCE INDEX (index5)
                
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and time(print_out.fingerprint_time) > work_to_v
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
				and print_out.day_print between  date_from_v and date_to_v 

				LEFT JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
				where holiday.id IS NULL
                
                group by print_in.user_hash);
               
                         
				IF(over_time_v IS NULL) THEN
					SET over_time_v := '00:00:00';
				END IF;
            
				   
                set over_time_holiday_v = (SELECT 
				ifnull(SEC_TO_TIME(sum(TIME_TO_SEC(IFNULL(TIMEDIFF(time(print_out.fingerprint_time), time(print_in.fingerprint_time)),0)))),'00:00:00') as 'h' 
				FROM emp_fingerprint  as print_in FORCE INDEX (index5) inner join emp_fingerprint as   print_out FORCE INDEX (index5)
                
                on print_in.user_hash = emp_hash_v and print_out.user_hash = emp_hash_v 
                and print_in.user_hash = print_out.user_hash 
                and print_in.day_print = print_out.day_print 
                and print_in.in_out = 'in' and  print_out.in_out = 'out' 
                and print_in.day_print between  date_from_v and date_to_v 
				and print_out.day_print between  date_from_v and date_to_v 

				left JOIN holiday
				ON DATE(holiday.date) = print_in.day_print
                where holiday.id IS  not NULL
                group by print_in.user_hash);
               
               
               
               IF(over_time_holiday_v IS NULL) THEN
					SET over_time_holiday_v := '00:00:00';
				END IF;
                
			   set over_time_v  = ADDTIME(over_time_v,over_time_holiday_v);   
                         
				
                
                
                
                
				
				 
                set salary_per_sec =  (base_salary_v / TIME_TO_SEC(work_hour_target_v));
                
                set cut_off_amount_v = round((TIME_TO_SEC(cut_off_hours_v) * salary_per_sec),2) ;
				
                
				
				
					INSERT INTO `finger_print_report`(`name`,`emp_hash`,`vacation_hour`,`vacation_limit_hour`,`duty_hour`,`vacation_limit_out`,`work_hour`,`finger_limit_out`,`work_hour_target`,`cut_off_hours`,`over_time`,`cut_off_amount`,`sick_vacation_hour`,`cut_off_days`,`finger_limit_out_leave`, `absence_hour`)
				VALUES 
				(emp_name_v,
                emp_hash_v,
                vacation_hour_v,
                vacations_limit_v,duty_hour_v
                ,vacation_limit_out_v ,
                work_hour_v,
                finger_limit_out_v,
                work_hour_target_v,
                
                cut_off_hours_v ,
                over_time_v,
                cut_off_amount_v,
                sick_vacation_hour_v,
                cut_off_days_v,
                finger_limit_out_leave_v,
                absence_hour_v
                );

			END IF;
		END LOOP;
		
       	SELECT  `name` ,    `vacation_hour` ,    `vacation_limit_hour` ,  `duty_hour` ,`vacation_limit_out` ,  `work_hour` ,  `finger_limit_out` ,  `work_hour_target` , `cut_off_hours` ,   `over_time` , `cut_off_amount`,`sick_vacation_hour` FROM `finger_print_report`;	CLOSE cursor_emp_info;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `FixMissingClockouts` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `FixMissingClockouts`()
BEGIN
  DECLARE old_value INT DEFAULT @@SQL_SAFE_UPDATES;
  SET @@SQL_SAFE_UPDATES = 0;

  -- Your logic here, for example:
  CALL fix_missing_clockouts();

  DELETE e1 FROM emp_fingerprint e1
  INNER JOIN emp_fingerprint e2 
  ON e1.id > e2.id 
  AND e1.user_hash = e2.user_hash 
  AND e1.fingerprint_time = e2.fingerprint_time 
  AND e1.in_out = e2.in_out;

  SET @@SQL_SAFE_UPDATES = old_value;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `fix_missing_clockouts` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `fix_missing_clockouts`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE user_hash BIGINT;
    DECLARE clock_in DATETIME;
    DECLARE schedule_data JSON;
    DECLARE schedule_id VARCHAR(255);
    
    DECLARE cur CURSOR FOR
       SELECT 
    f1.user_hash, 
    f1.fingerprint_time AS clock_in, 
    s.schedule_data, 
    s.id AS schedule_id
FROM 
    emp_fingerprint f1
LEFT JOIN 
    emp_fingerprint f2 
    ON f1.user_hash = f2.user_hash 
    AND f2.in_out = 'out' 
       AND (
            (DATE(f1.fingerprint_time) = DATE(f2.fingerprint_time) AND TIME(f1.fingerprint_time) <= TIME(f2.fingerprint_time)) 
            OR (DATE(f2.fingerprint_time) = DATE_ADD(DATE(f1.fingerprint_time), INTERVAL 1 DAY) AND TIME(f2.fingerprint_time) <= '04:00:00')
        )
JOIN 
    actual_schedules s 
    ON DATE(f1.fingerprint_time) = s.date 
    AND f1.user_hash = s.user_hash
WHERE 
    f1.in_out = 'in' 
    AND f1.is_flexible = '0'
    AND f2.id IS NULL 
    AND DATE(f1.fingerprint_time) = CURDATE() - INTERVAL 1 DAY;


    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO user_hash, clock_in, schedule_data, schedule_id;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Decode schedule_data
        SET @schedules_count = JSON_LENGTH(schedule_data);
        SET @index = 0;

        -- Loop through each schedule in the JSON array
        WHILE @index < @schedules_count DO
            SET @schedule = JSON_UNQUOTE(JSON_EXTRACT(schedule_data, CONCAT('$[', @index, ']')));
            SET @id = JSON_UNQUOTE(JSON_EXTRACT(@schedule, '$.id'));
            SET @end_time = JSON_UNQUOTE(JSON_EXTRACT(@schedule, '$.end'));

            -- Extract hours and minutes from end_time
            SET @hours = FLOOR(@end_time);
            SET @minutes_decimal = @end_time - @hours;
            SET @minutes = FLOOR(@minutes_decimal * 60);

            -- Calculate clock-out time
            SET @clock_out_time = DATE_ADD(DATE(clock_in), INTERVAL @hours HOUR);
            SET @clock_out_time = DATE_ADD(@clock_out_time, INTERVAL @minutes MINUTE);
            
             IF TIME(@clock_out_time) < TIME(clock_in) THEN
                SET @clock_out_time = DATE_ADD(@clock_out_time, INTERVAL 1 DAY);
            END IF;

            INSERT INTO debug_logs (operation, value) VALUES ('Calculated Clock-Out Time', CAST(@clock_out_time AS CHAR));

            -- Check if a clock-out record already exists for this schedule
            SELECT COUNT(*) INTO @existing_clockout_count
            FROM emp_fingerprint
            WHERE user_hash = user_hash
              AND in_out = 'out'
               AND fingerprint_time = @clock_out_time
               AND schedule_id = @id;

            IF @existing_clockout_count = 0 THEN
                -- Insert missing clock-out record for this schedule
                INSERT INTO emp_fingerprint (user_hash, fingerprint_time, in_out, schedule_id)
                VALUES (user_hash, @clock_out_time, 'out', @id);
            END IF;

            SET @index = @index + 1;
        END WHILE;
    END LOOP;

    CLOSE cur;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `fix_missing_flexible_clockouts` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `fix_missing_flexible_clockouts`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE user_hash BIGINT;
    DECLARE latest_clock_in DATETIME;
    DECLARE target_hours DECIMAL(5,2);
    DECLARE expected_clock_out DATETIME;

    -- Cursor to get the latest clock-in for each user without a clock-out
    DECLARE cur CURSOR FOR
        SELECT 
            f1.user_hash, 
            MAX(f1.fingerprint_time) AS latest_clock_in, 
            t.target_hours
        FROM 
            emp_fingerprint f1
        JOIN 
            daily_flexible_schedule_targets t 
            ON f1.user_hash = t.user_hash 
            AND DATE(f1.fingerprint_time) = t.date
        LEFT JOIN 
            emp_fingerprint f2 
            ON f1.user_hash = f2.user_hash 
            AND f2.in_out = 'out' 
            AND DATE(f1.fingerprint_time) = DATE(f2.fingerprint_time)
            AND TIME(f1.fingerprint_time) <= TIME(f2.fingerprint_time)
        WHERE 
            f1.in_out = 'in' 
            AND f1.is_flexible = '1'
            AND f2.id IS NULL
            AND DATE(f1.fingerprint_time) = CURDATE() - INTERVAL 1 DAY
        GROUP BY 
            f1.user_hash, t.target_hours;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO user_hash, latest_clock_in, target_hours;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Calculate the expected clock-out time based on target hours
        SET expected_clock_out = DATE_ADD(latest_clock_in, INTERVAL target_hours HOUR);

        -- Check if a clock-out record already exists for this clock-in
        SELECT COUNT(*) INTO @existing_clockout_count
        FROM emp_fingerprint
        WHERE user_hash = user_hash
          AND in_out = 'out'
          AND DATE(fingerprint_time) = DATE(expected_clock_out)
          AND TIME(fingerprint_time) = TIME(expected_clock_out);

        -- If no clock-out record exists, insert it
        IF @existing_clockout_count = 0 THEN
            INSERT INTO emp_fingerprint (user_hash, fingerprint_time, in_out, schedule_id)
            VALUES (user_hash, expected_clock_out, 'out', NULL);
        END IF;
    END LOOP;

    CLOSE cur;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `fp_report` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `fp_report`(IN start_date DATE,
    IN end_date DATE,
    IN department_id INT,
    IN search VARCHAR(255),
    IN page1 INT,
    IN perPage INT)
BEGIN


call spaceLight.finger_print_report(start_date, end_date);
select 
  finger_print_report.*,
  partments.name as 'department' ,
  image_profile
from 
  finger_print_report 
left join system_users on system_usres.hash = finger_print_report.emp_hash
left join
  emp_information on emp_information.user_hash = finger_print_report.emp_hash
left join
  partments on partments.hash = emp_information.partment
  
   where partments.hash LIKE CONCAT('%', department_id, '%') AND
        finger_print_report.emp_hash LIKE CONCAT('%', search, '%')
        
        limit perPage  offset page1;
   
  

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetAllEmployeesAttendanceSummary` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `GetAllEmployeesAttendanceSummary`(
    IN p_start_date DATETIME,
    IN p_end_date DATETIME
)
BEGIN
    SELECT 
        b.user_hash,
        b.total_worked_hours,
        b.total_target_hours,
        b.total_overtime_hours,
        b.total_missing_hours,
        l.total_sick_leave_days,
        l.total_regular_leave_days,
        l.total_time_leave_days,
        l.total_duty_days,
        l.total_unpaid_leave_days,
        l.total_weekly_off_days,
        l.total_vacation_days
    FROM (
        SELECT 
            ewh.user_hash,
            SUM(ewh.total_worked_hours) AS total_worked_hours,
            SUM(ewh.target_hours) AS total_target_hours,
            SUM(ewh.overtime_hours) AS total_overtime_hours,
            SUM(ewh.total_missed_hours) AS total_missing_hours
        FROM employees_worked_hours ewh
        WHERE ewh.user_hash IN (
            SELECT DISTINCT v.user_hash
            FROM vacations v
            WHERE v.status = 111
                AND v.date_start >= p_start_date
                AND v.date_end <= p_end_date
        )
        GROUP BY ewh.user_hash
    ) b
    JOIN (
        SELECT 
            v.user_hash,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 111 THEN DATEDIFF(v.date_end, v.date_start) + 1 ELSE 0 END), 0) AS total_sick_leave_days,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 222 THEN DATEDIFF(v.date_end, v.date_start) + 1 ELSE 0 END), 0) AS total_regular_leave_days,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 333 THEN DATEDIFF(v.date_end, v.date_start) + 1 ELSE 0 END), 0) AS total_time_leave_days,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 444 THEN DATEDIFF(v.date_end, v.date_start) + 1 ELSE 0 END), 0) AS total_duty_days,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 555 THEN DATEDIFF(v.date_end, v.date_start) + 1 ELSE 0 END), 0) AS total_unpaid_leave_days,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 666 THEN DATEDIFF(v.date_end, v.date_start) + 1 ELSE 0 END), 0) AS total_weekly_off_days,
            COALESCE(SUM(DATEDIFF(v.date_end, v.date_start) + 1), 0) AS total_vacation_days
        FROM vacations v
        WHERE v.status = 111
            AND v.date_start >= p_start_date
            AND v.date_end <= p_end_date
        GROUP BY v.user_hash
    ) l ON b.user_hash = l.user_hash;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetEmployeeAttendanceSummary` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `GetEmployeeAttendanceSummary`(
    IN p_start_date DATETIME,
    IN p_end_date DATETIME,
    IN p_user_hash BIGINT
)
BEGIN
    SELECT 
        b.user_hash,
        b.total_worked_hours,
        b.total_target_hours,
        b.total_overtime_hours,
        b.total_missing_hours,
        l.total_sick_leave_days,
        l.total_regular_leave_days,
        l.total_time_leave_days,
        l.total_duty_days,
        l.total_unpaid_leave_days,
        l.total_weekly_off_days,
        l.total_vacation_days
    FROM (
        SELECT 
            ewh.user_hash,
            SUM(ewh.total_worked_hours) AS total_worked_hours,
            SUM(ewh.target_hours) AS total_target_hours,
            SUM(ewh.overtime_hours) AS total_overtime_hours,
            SUM(ewh.total_missed_hours) AS total_missing_hours
        FROM employees_worked_hours ewh
        WHERE ewh.user_hash IN (
            SELECT DISTINCT v.user_hash
            FROM vacations v
            WHERE v.status = 111
                AND v.date_start >= p_start_date
                AND v.date_end <= p_end_date
                AND (p_user_hash IS NULL OR v.user_hash = p_user_hash)
        )
        GROUP BY ewh.user_hash
    ) b
    JOIN (
        SELECT 
            v.user_hash,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 111 THEN TIMESTAMPDIFF(DAY, v.date_start, v.date_end) ELSE 0 END), 0) AS total_sick_leave_days,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 222 THEN TIMESTAMPDIFF(DAY, v.date_start, v.date_end) ELSE 0 END), 0) AS total_regular_leave_days,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 333 THEN TIMESTAMPDIFF(DAY, v.date_start, v.date_end) ELSE 0 END), 0) AS total_time_leave_days,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 444 THEN TIMESTAMPDIFF(DAY, v.date_start, v.date_end) ELSE 0 END), 0) AS total_duty_days,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 555 THEN TIMESTAMPDIFF(DAY, v.date_start, v.date_end) ELSE 0 END), 0) AS total_unpaid_leave_days,
            COALESCE(SUM(CASE WHEN v.vication_type_hash = 666 THEN TIMESTAMPDIFF(DAY, v.date_start, v.date_end) ELSE 0 END), 0) AS total_weekly_off_days,
            COALESCE(SUM(TIMESTAMPDIFF(DAY, v.date_start, v.date_end)), 0) AS total_vacation_days
        FROM vacations v
        WHERE v.status = 111
            AND v.date_start >= p_start_date
            AND v.date_end <= p_end_date
            AND (p_user_hash IS NULL OR v.user_hash = p_user_hash)
        GROUP BY v.user_hash
    ) l ON b.user_hash = l.user_hash;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `getNotFingerPrint` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `getNotFingerPrint`(dt_start date, dt_end date)
BEGIN
CREATE TEMPORARY TABLE TempTable (dt date, name char(250),data_entey datetime, holiday date); 
WHILE dt_start <= dt_end DO
	Insert into TempTable SELECT date(dt_start) AS date, name,
    (select data_entry from emp_fingerprint where system_users.hash=emp_fingerprint.user_hash and in_out="in" and date(data_entry)=dt_start limit 1) as x,
    holiday.date as holiday
    from system_users 
    left join holiday on holiday.date=dt_start;
    SET dt_start = date_add(dt_start, INTERVAL 1 DAY);
END WHILE;
select * from TempTable where data_entey IS NULL and holiday is null;
DROP TEMPORARY TABLE TempTable;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetTaskCompletionByProject` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `GetTaskCompletionByProject`(
    IN projectHash BIGINT,
    IN user_hash BIGINT,
    IN start_date DATE,
    IN end_date DATE
)
BEGIN
    -- Query to get count of completed tasks
    SELECT 
        'منجز' AS `status`, -- 'Completed' status label
        SUM(CASE WHEN `t`.`status` = '222' THEN 1 ELSE 0 END) AS `count` -- Count of completed tasks
    FROM
        `task` `t`
        LEFT JOIN `task_users` `tu` ON `t`.`id` = `tu`.`task_id`
    WHERE
        `t`.`isaccept` = '1'
        AND (`t`.`project_hash` = projectHash OR projectHash IS NULL OR projectHash = '')
        AND (user_hash IS NULL OR user_hash = 0 OR (user_hash != 0 AND (tu.`user_hash` = user_hash OR t.`accept_user` = user_hash)))
        AND (start_date IS NULL OR t.`start_task` >= start_date)
        AND (end_date IS NULL OR t.`start_task` <= end_date)

    UNION ALL

    -- Query to get count of uncompleted tasks
    SELECT 
        'غير منجز' AS `status`, -- 'Uncompleted' status label
        SUM(CASE WHEN `t`.`status` = '333' THEN 1 ELSE 0 END) AS `count` -- Count of uncompleted tasks
    FROM
        `task` `t`
        LEFT JOIN `task_users` `tu` ON `t`.`id` = `tu`.`task_id`
    WHERE
        `t`.`isaccept` = '1'
        AND (`t`.`project_hash` = projectHash OR projectHash IS NULL OR projectHash = '')
        AND (user_hash IS NULL OR user_hash = 0 OR (user_hash != 0 AND (tu.`user_hash` = user_hash OR t.`accept_user` = user_hash)))
        AND (start_date IS NULL OR t.`start_task` >= start_date)
        AND (end_date IS NULL OR t.`start_task` <= end_date)

    UNION ALL

    -- Query to get count of pending tasks
    SELECT 
        'معلق' AS `status`, -- 'Pending' status label
        SUM(CASE WHEN `t`.`status` = '111' THEN 1 ELSE 0 END) AS `count` -- Count of pending tasks
    FROM
        `task` `t`
        LEFT JOIN `task_users` `tu` ON `t`.`id` = `tu`.`task_id`
    WHERE
        (`t`.`project_hash` = projectHash OR projectHash IS NULL OR projectHash = '') -- Corrected parentheses placement
        AND (user_hash IS NULL OR user_hash = 0 OR (user_hash != 0 AND (tu.`user_hash` = user_hash OR t.`accept_user` = user_hash)))
        AND (start_date IS NULL OR t.`start_task` >= start_date)
        AND (end_date IS NULL OR t.`start_task` <= end_date);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertAbsences` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `InsertAbsences`()
BEGIN
    DECLARE target_date DATE;
    SET target_date = CURDATE() - INTERVAL 1 DAY;

    INSERT INTO employee_absences (user_hash, date, schedule_data)
    SELECT DISTINCT e.user_hash, ash.date, ash.schedule_data
    FROM emp_information e
    JOIN actual_schedules ash ON ash.user_hash = e.user_hash
    LEFT JOIN emp_fingerprint f ON e.user_hash = f.user_hash 
        AND DATE(f.fingerprint_time) = target_date
 WHERE f.user_hash IS NULL
      AND ash.date = target_date
      AND ash.schedule_data IS NOT NULL;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertActualSchedules` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `InsertActualSchedules`()
BEGIN
    -- Insert into vacations table for users with a scheduled vacation day
    INSERT INTO vacations (
        user_hash, 
        vication_type_hash, 
        status, 
        date_start, 
        date_end,
        duration_day
    )
    SELECT 
        user_hash, 
        666 AS vication_type_hash, 
        111 AS status, 
        CURDATE() + INTERVAL 1 DAY AS date_start, 
        CURDATE() + INTERVAL 2 DAY AS date_end,
        1 AS duration_day
    FROM 
        user_vacations
    WHERE 
        day_of_week_id = DAYOFWEEK(CURDATE() + INTERVAL 1 DAY);

    -- Insert into actual_schedules table for users who do not have a scheduled vacation day
    INSERT INTO actual_schedules (
        user_hash, 
        date, 
        schedule_data,
        allowance_time
    )
    SELECT
        ei.user_hash,
        CURDATE() + INTERVAL 1 DAY AS date,
        JSON_UNQUOTE(JSON_EXTRACT(sg.schedule_data, CONCAT('$.', DAYNAME(CURDATE() + INTERVAL 1 DAY)))) AS schedule_data,
        sg.allowance_time
    FROM
        emp_information ei
    JOIN
        schedule_groups sg ON ei.work_schedule = sg.id
    LEFT JOIN
        user_vacations uv ON ei.user_hash = uv.user_hash
                           AND uv.day_of_week_id = DAYOFWEEK(CURDATE() + INTERVAL 1 DAY)
                           
	    LEFT JOIN
        vacations v ON ei.user_hash = v.user_hash
					AND (
                        CURDATE() + INTERVAL 1 DAY  BETWEEN v.date_start AND DATE(v.date_end - INTERVAL 1 DAY)
                        OR (v.date_start = CURDATE() + INTERVAL 1 DAY AND v.date_end = CURDATE() + INTERVAL 1 DAY)
                    )
        
    WHERE 
        uv.user_hash IS NULL
		AND v.user_hash IS NULL
        AND ei.flexible_schedule = '0'
    AND JSON_UNQUOTE(JSON_EXTRACT(sg.schedule_data, CONCAT('$.', DAYNAME(CURDATE() + INTERVAL 1 DAY)))) IS NOT NULL
         AND NOT EXISTS (
         SELECT 1 
         FROM actual_schedules 
         WHERE user_hash = ei.user_hash 
         AND date = CURDATE() + interval 1 DAY
     );
      INSERT INTO daily_flexible_schedule_targets (
        user_hash, 
        date, 
        target_hours
    )
    SELECT
    ei.user_hash,
    CURDATE() + INTERVAL 1 DAY AS date,
    ei.target_hours
FROM
    emp_information ei
LEFT JOIN
    user_vacations uv ON ei.user_hash = uv.user_hash
                       AND uv.day_of_week_id = DAYOFWEEK(CURDATE() + INTERVAL 1 DAY)
LEFT JOIN
    vacations v ON ei.user_hash = v.user_hash
                 AND (
                    CURDATE() + INTERVAL 1 DAY BETWEEN v.date_start AND DATE(v.date_end - INTERVAL 1 DAY)
                    OR (v.date_start = CURDATE() + INTERVAL 1 DAY AND v.date_end = CURDATE() + INTERVAL 1 DAY)
                 )
				AND v.status = '111' -- Add this condition for accepted vacations

WHERE 
    uv.user_hash IS NULL
    AND v.user_hash IS NULL
    AND ei.flexible_schedule = '1'
    AND ei.target_hours IS NOT NULL
    AND 
        (v.user_hash IS NULL OR v.vication_type_hash = 667)
         AND NOT EXISTS (
        SELECT 1 
        FROM actual_schedules 
        WHERE user_hash = ei.user_hash 
        AND date = CURDATE() + interval 1 DAY
    );
    
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `mobile_dashborad` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `mobile_dashborad`(IN user_hash bigint(20),
	OUT current_task json,
    OUT emergency_task json,
    OUT late_task json,
    OUT complete_task json,
	OUT not_complete json)
BEGIN


SELECT * into current_task  FROM task where  receiver=user_hash and status='111' order by id desc limit 3 ;
SELECT * into emergency_task FROM task where emergency='1' and receiver=user_hash;
SELECT * into late_task FROM task where curdate()>date(deadline) and receiver=user_hash and status='111' ;
SELECT count(id) into complete_task  FROM task where month(curdate())=month(date(deadline)) and receiver=user_hash and status='222';
SELECT count(id) into not_complete FROM task where month(curdate())=month(date(deadline)) and receiver=user_hash and status='111';








END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `new_procedure` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `new_procedure`(
in start_date date , end_date date
)
BEGIN


CREATE TABLE `finger_print_report` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `vacation_hour` double DEFAULT NULL COMMENT 'ساعات الاجازة',
  `vacation_limit_hour` double DEFAULT NULL COMMENT 'ساعات الاجازة المسموح بها ',
  `duty_hour` double DEFAULT NULL COMMENT 'ساعات الواجب',
  `vacation_limit_out` double DEFAULT NULL COMMENT 'تجاوز الساعات المسموح بها',
  `work_hour` double DEFAULT NULL COMMENT 'ساعات الحضور',
  `finger_limit_out` double DEFAULT NULL COMMENT 'ساعات التاخير البصمة',
  `work_our_target` double DEFAULT NULL COMMENT 'ساعات الدوام المطلوبة',
  `cut_off_hours` double DEFAULT NULL COMMENT 'ساعات الخصم',
  `over_time` double DEFAULT NULL COMMENT 'ساعات اضافية',
  `cut_off_amount` double DEFAULT NULL COMMENT 'مبلغ الخصم',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `test`()
BEGIN
declare x varchar(255);
select "xxx" into x;
select x;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `updateEmployeeActualSchedule` */;
DELIMITER ;;
CREATE DEFINER=`redha`@`%` PROCEDURE `updateEmployeeActualSchedule`(IN user_hash BIGINT)
BEGIN
    DECLARE vacationExists INT;

    -- Check if the user has a vacation today
    SELECT COUNT(*)
    INTO vacationExists
    FROM vacations v
    WHERE v.user_hash = user_hash
      AND CURDATE() BETWEEN v.date_start AND IFNULL(v.date_end, v.date_start);

    -- If no vacation exists, proceed with updating the actual_schedules table
    IF vacationExists = 0 THEN
        INSERT INTO actual_schedules (user_hash, date, schedule_data, allowance_time)
        SELECT
            ei.user_hash,
            CURDATE() AS date,
            JSON_UNQUOTE(JSON_EXTRACT(sg.schedule_data, CONCAT('$.', DAYNAME(CURDATE())))) AS schedule_data,
            sg.allowance_time
        FROM
            emp_information ei
        JOIN
            schedule_groups sg ON ei.work_schedule = sg.id
	LEFT JOIN
		daily_flexible_schedule_targets dft ON ei.user_hash = dft.user_hash AND CURDATE() = dft.date

        WHERE
            ei.user_hash = user_hash
            AND ei.flexible_schedule = '0'
            AND dft.user_hash IS NULL 
        ON DUPLICATE KEY UPDATE
            schedule_data = VALUES(schedule_data),
            allowance_time = VALUES(allowance_time),
            updated_at = CURRENT_TIMESTAMP;
    END IF;
END ;;
DELIMITER ;

--
-- Current Database: `spaceLight`
--

USE `spaceLight`;

--
-- Final view structure for view `EmployeeAttendanceSummary`
--

/*!50001 DROP VIEW IF EXISTS `EmployeeAttendanceSummary`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `EmployeeAttendanceSummary` AS select `ei`.`user_hash` AS `user_hash`,coalesce(`b`.`total_worked_hours`,0) AS `work_hour`,coalesce(`b`.`total_target_hours`,0) AS `work_hour_target`,coalesce(`b`.`total_overtime_hours`,0) AS `over_time`,coalesce(`b`.`total_adjusted_minutes`,0) AS `cut_off_hours`,coalesce(`b`.`total_adjusted_minutes`,0) AS `total_adjusted_minutes`,coalesce(`b`.`finger_limit_out`,0) AS `finger_limit_out`,coalesce(`b`.`finger_limit_out_leave`,0) AS `finger_limit_out_leave`,coalesce(`b`.`total_daily_penalties`,0) AS `cut_hours_penatly`,`b`.`date` AS `date`,(coalesce(`b`.`zero_work_days`,0) + coalesce(`b`.`missing_hours_days`,0)) AS `cut_off_days`,coalesce(`b`.`absence_hours`,0) AS `absence_hour`,coalesce(`l`.`total_sick_leave_hours`,0) AS `sick_vacation_hour`,coalesce(`l`.`total_regular_leave_hours`,0) AS `total_regular_leave_hours`,coalesce(`l`.`total_time_leave_hours`,0) AS `total_time_leave_hours`,coalesce(`l`.`total_duty_hours`,0) AS `duty_hour`,coalesce(`l`.`total_unpaid_leave_hours`,0) AS `total_unpaid_leave_hours`,coalesce(`l`.`total_weekly_off_hours`,0) AS `total_weekly_off_hours`,coalesce(`l`.`total_vacation_hours`,0) AS `vacation_hour`,coalesce(`vacation_limits`.`vacation_limit_hour`,0) AS `vacation_limit_hour`,(case when ((coalesce(`l`.`total_regular_leave_hours`,0) + coalesce(`l`.`total_time_leave_hours`,0)) > coalesce(`vacation_limits`.`vacation_limit_hour`,0)) then ((coalesce(`l`.`total_regular_leave_hours`,0) + coalesce(`l`.`total_time_leave_hours`,0)) - coalesce(`vacation_limits`.`vacation_limit_hour`,0)) else 0 end) AS `vacation_limit_out`,`ei`.`primary_salary` AS `hourly_hour_price` from (((`emp_information` `ei` left join (select `ewh`.`user_hash` AS `user_hash`,`ewh`.`date` AS `date`,sum(`ewh`.`total_worked_hours`) AS `total_worked_hours`,sum(`ewh`.`target_hours`) AS `total_target_hours`,sum(`ewh`.`overtime_hours`) AS `total_overtime_hours`,sum(`ewh`.`total_missed_hours`) AS `total_missing_hours`,sum(`ewh`.`total_late_arrival_minutes`) AS `finger_limit_out`,sum(`ewh`.`total_early_departure_minutes`) AS `finger_limit_out_leave`,sum((((`ewh`.`total_late_arrival_minutes` / 60) + (`ewh`.`total_early_departure_minutes` / 60)) + (case when (`ewh`.`total_worked_hours` = 0) then `ewh`.`total_missed_hours` else 0 end))) AS `total_adjusted_minutes`,sum((case when (`ewh`.`total_worked_hours` = 0) then ((`ewh`.`total_missed_hours` * 3600) * 3) else (((`ewh`.`total_late_arrival_minutes` * 60) * 3) + ((`ewh`.`total_early_departure_minutes` * 60) * 3)) end)) AS `total_daily_penalties`,sum((case when (`ewh`.`total_worked_hours` = 0) then 1 else 0 end)) AS `zero_work_days`,sum((case when (`ewh`.`total_worked_hours` = 0) then `ewh`.`total_missed_hours` else 0 end)) AS `absence_hours`,round((sum((case when (`ewh`.`total_worked_hours` > 0) then `ewh`.`total_missed_hours` else 0 end)) / nullif(avg((case when (`ewh`.`total_worked_hours` > 0) then `ewh`.`target_hours` else NULL end)),0)),2) AS `missing_hours_days` from `employees_worked_hours` `ewh` group by `ewh`.`user_hash`,`ewh`.`date`) `b` on((`ei`.`user_hash` = `b`.`user_hash`))) left join (select `v`.`user_hash` AS `user_hash`,sum((case when (`v`.`vication_type_hash` = 111) then (((to_days(`v`.`date_end`) - to_days(`v`.`date_start`)) + 1) * (coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].end')),0) - coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].start')),0))) else 0 end)) AS `total_sick_leave_hours`,sum((case when (`v`.`vication_type_hash` = 222) then (((to_days(`v`.`date_end`) - to_days(`v`.`date_start`)) + 1) * (coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].end')),0) - coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].start')),0))) else 0 end)) AS `total_regular_leave_hours`,sum((case when (`v`.`vication_type_hash` = 333) then timestampdiff(HOUR,`v`.`date_start`,`v`.`date_end`) else 0 end)) AS `total_time_leave_hours`,sum((case when (`v`.`vication_type_hash` = 444) then timestampdiff(HOUR,`v`.`date_start`,`v`.`date_end`) else 0 end)) AS `total_duty_hours`,sum((case when (`v`.`vication_type_hash` = 555) then (((to_days(`v`.`date_end`) - to_days(`v`.`date_start`)) + 1) * (coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].end')),0) - coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].start')),0))) else 0 end)) AS `total_unpaid_leave_hours`,sum((case when (`v`.`vication_type_hash` = 666) then (((to_days(`v`.`date_end`) - to_days(`v`.`date_start`)) + 1) * (coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].end')),0) - coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].start')),0))) else 0 end)) AS `total_weekly_off_hours`,sum((case when (`v`.`vication_type_hash` = 667) then timestampdiff(HOUR,`v`.`date_start`,`v`.`date_end`) else 0 end)) AS `total_half_days_work_hours`,sum((case when (`v`.`vication_type_hash` in (333,444)) then timestampdiff(HOUR,`v`.`date_start`,`v`.`date_end`) else (((to_days(`v`.`date_end`) - to_days(`v`.`date_start`)) + 1) * (coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].end')),0) - coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].start')),0))) end)) AS `total_vacation_hours` from ((`vacations` `v` join `emp_information` `ei_v` on((`v`.`user_hash` = `ei_v`.`user_hash`))) join `schedule_groups` `sg` on((`ei_v`.`work_schedule` = `sg`.`id`))) group by `v`.`user_hash`) `l` on((`ei`.`user_hash` = `l`.`user_hash`))) left join (select `ei_inner`.`user_hash` AS `user_hash`,coalesce((`ei_inner`.`vacations_limit` * greatest((coalesce(json_extract(`sg`.`schedule_data`,'$.Monday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Monday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Tuesday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Tuesday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Wednesday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Wednesday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Thursday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Thursday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Friday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Friday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Saturday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Saturday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Sunday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Sunday[0].start'),0)))),0) AS `vacation_limit_hour` from (`emp_information` `ei_inner` left join `schedule_groups` `sg` on((`ei_inner`.`work_schedule` = `sg`.`id`)))) `vacation_limits` on((`ei`.`user_hash` = `vacation_limits`.`user_hash`))) */;

--
-- Final view structure for view `EmployeeAttendanceSummary_backup`
--

/*!50001 DROP VIEW IF EXISTS `EmployeeAttendanceSummary_backup`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `EmployeeAttendanceSummary_backup` AS select `b`.`user_hash` AS `user_hash`,`b`.`total_worked_hours` AS `work_hour`,`b`.`total_target_hours` AS `work_hour_target`,`b`.`total_overtime_hours` AS `over_time`,`b`.`total_adjusted_minutes` AS `cut_off_hours`,`b`.`total_adjusted_minutes` AS `total_adjusted_minutes`,`b`.`finger_limit_out` AS `finger_limit_out`,`b`.`finger_limit_out_leave` AS `finger_limit_out_leave`,`b`.`total_daily_penalties` AS `cut_hours_penatly`,`b`.`date` AS `date`,(`b`.`zero_work_days` + `b`.`missing_hours_days`) AS `cut_off_days`,`b`.`absence_hours` AS `absence_hour`,coalesce(`l`.`total_sick_leave_hours`,0) AS `sick_vacation_hour`,coalesce(`l`.`total_regular_leave_hours`,0) AS `total_regular_leave_hours`,coalesce(`l`.`total_time_leave_hours`,0) AS `total_time_leave_hours`,coalesce(`l`.`total_duty_hours`,0) AS `duty_hour`,coalesce(`l`.`total_unpaid_leave_hours`,0) AS `total_unpaid_leave_hours`,coalesce(`l`.`total_weekly_off_hours`,0) AS `total_weekly_off_hours`,coalesce(`l`.`total_vacation_hours`,0) AS `vacation_hour`,coalesce(`vacation_limits`.`vacation_limit_hour`,0) AS `vacation_limit_hour`,(case when ((coalesce(`l`.`total_regular_leave_hours`,0) + coalesce(`l`.`total_time_leave_hours`,0)) > coalesce(`vacation_limits`.`vacation_limit_hour`,0)) then ((coalesce(`l`.`total_regular_leave_hours`,0) + coalesce(`l`.`total_time_leave_hours`,0)) - coalesce(`vacation_limits`.`vacation_limit_hour`,0)) else 0 end) AS `vacation_limit_out`,`ei`.`primary_salary` AS `hourly_hour_price` from (((((select `ewh`.`user_hash` AS `user_hash`,`ewh`.`date` AS `date`,sum(`ewh`.`total_worked_hours`) AS `total_worked_hours`,sum(`ewh`.`target_hours`) AS `total_target_hours`,sum(`ewh`.`overtime_hours`) AS `total_overtime_hours`,sum(`ewh`.`total_missed_hours`) AS `total_missing_hours`,sum(`ewh`.`total_late_arrival_minutes`) AS `finger_limit_out`,sum(`ewh`.`total_early_departure_minutes`) AS `finger_limit_out_leave`,sum((((`ewh`.`total_late_arrival_minutes` / 60) + (`ewh`.`total_early_departure_minutes` / 60)) + (case when (`ewh`.`total_worked_hours` = 0) then `ewh`.`total_missed_hours` else 0 end))) AS `total_adjusted_minutes`,sum((case when (`ewh`.`total_worked_hours` = 0) then ((`ewh`.`total_missed_hours` * 3600) * 3) else (((`ewh`.`total_late_arrival_minutes` * 60) * 3) + ((`ewh`.`total_early_departure_minutes` * 60) * 3)) end)) AS `total_daily_penalties`,sum((case when (`ewh`.`total_worked_hours` = 0) then 1 else 0 end)) AS `zero_work_days`,sum((case when (`ewh`.`total_worked_hours` = 0) then `ewh`.`total_missed_hours` else 0 end)) AS `absence_hours`,round((sum((case when (`ewh`.`total_worked_hours` > 0) then `ewh`.`total_missed_hours` else 0 end)) / nullif(avg((case when (`ewh`.`total_worked_hours` > 0) then `ewh`.`target_hours` else NULL end)),0)),2) AS `missing_hours_days` from `employees_worked_hours` `ewh` group by `ewh`.`user_hash`,`ewh`.`date`) `b` left join (select `v`.`user_hash` AS `user_hash`,sum((case when (`v`.`vication_type_hash` = 111) then (((to_days(`v`.`date_end`) - to_days(`v`.`date_start`)) + 1) * (coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].end')),0) - coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].start')),0))) else 0 end)) AS `total_sick_leave_hours`,sum((case when (`v`.`vication_type_hash` = 222) then (((to_days(`v`.`date_end`) - to_days(`v`.`date_start`)) + 1) * (coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].end')),0) - coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].start')),0))) else 0 end)) AS `total_regular_leave_hours`,sum((case when (`v`.`vication_type_hash` = 333) then timestampdiff(HOUR,`v`.`date_start`,`v`.`date_end`) else 0 end)) AS `total_time_leave_hours`,sum((case when (`v`.`vication_type_hash` = 444) then timestampdiff(HOUR,`v`.`date_start`,`v`.`date_end`) else 0 end)) AS `total_duty_hours`,sum((case when (`v`.`vication_type_hash` = 555) then (((to_days(`v`.`date_end`) - to_days(`v`.`date_start`)) + 1) * (coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].end')),0) - coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].start')),0))) else 0 end)) AS `total_unpaid_leave_hours`,sum((case when (`v`.`vication_type_hash` = 666) then (((to_days(`v`.`date_end`) - to_days(`v`.`date_start`)) + 1) * (coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].end')),0) - coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].start')),0))) else 0 end)) AS `total_weekly_off_hours`,sum((case when (`v`.`vication_type_hash` = 667) then timestampdiff(HOUR,`v`.`date_start`,`v`.`date_end`) else 0 end)) AS `total_half_days_work_hours`,sum((case when (`v`.`vication_type_hash` in (333,444)) then timestampdiff(HOUR,`v`.`date_start`,`v`.`date_end`) else (((to_days(`v`.`date_end`) - to_days(`v`.`date_start`)) + 1) * (coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].end')),0) - coalesce(json_extract(`sg`.`schedule_data`,concat('$.',dayname(`v`.`date_start`),'[0].start')),0))) end)) AS `total_vacation_hours` from ((`vacations` `v` join `emp_information` `ei` on((`v`.`user_hash` = `ei`.`user_hash`))) join `schedule_groups` `sg` on((`ei`.`work_schedule` = `sg`.`id`))) group by `v`.`user_hash`) `l` on((`b`.`user_hash` = `l`.`user_hash`))) left join `emp_information` `ei` on((`b`.`user_hash` = `ei`.`user_hash`))) left join `schedule_groups` `sg` on((`ei`.`work_schedule` = `sg`.`id`))) left join (select `ei`.`user_hash` AS `user_hash`,coalesce((`ei`.`vacations_limit` * greatest((coalesce(json_extract(`sg`.`schedule_data`,'$.Monday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Monday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Tuesday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Tuesday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Wednesday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Wednesday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Thursday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Thursday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Friday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Friday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Saturday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Saturday[0].start'),0)),(coalesce(json_extract(`sg`.`schedule_data`,'$.Sunday[0].end'),0) - coalesce(json_extract(`sg`.`schedule_data`,'$.Sunday[0].start'),0)))),0) AS `vacation_limit_hour` from (`emp_information` `ei` left join `schedule_groups` `sg` on((`ei`.`work_schedule` = `sg`.`id`)))) `vacation_limits` on((`b`.`user_hash` = `vacation_limits`.`user_hash`))) */;

--
-- Final view structure for view `customer_FTTH_not_working`
--

/*!50001 DROP VIEW IF EXISTS `customer_FTTH_not_working`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `customer_FTTH_not_working` AS select `jt`.`customer_id` AS `customer_id`,`jt`.`customer_displayValue` AS `customer_displayValue`,`jt`.`username` AS `username`,`jt`.`zone_displayValue` AS `zone_displayValue`,`jt`.`expires` AS `expires`,substring_index(`jt`.`username`,'-',1) AS `zone`,substring_index(substring_index(`jt`.`username`,'-',2),'-',-(1)) AS `zoneNumber`,substring_index(`jt`.`username`,'-',-(1)) AS `address`,`subscribers_customers_ftth`.`updated_at` AS `updated_at` from (`subscribers_customers_ftth` join json_table(`subscribers_customers_ftth`.`data`, '$[*]' columns (`customer_displayValue` varchar(255) character set utf8mb4 path '$.customer.displayValue', `customer_id` varchar(55) character set utf8mb4 path '$.customer.id', `username` varchar(255) character set utf8mb4 path '$.username', `zone_displayValue` varchar(255) character set utf8mb4 path '$.zone.displayValue', `expires` varchar(255) character set utf8mb4 path '$.expires', `hasActiveSession` tinyint(1) path '$.hasActiveSession')) `jt`) where ((`jt`.`hasActiveSession` = false) and (`subscribers_customers_ftth`.`id` = 1)) */;

--
-- Final view structure for view `detect_fraud`
--

/*!50001 DROP VIEW IF EXISTS `detect_fraud`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `detect_fraud` AS select `emp_fingerprint`.`id` AS `id`,`emp_fingerprint`.`user_hash` AS `user_hash`,`emp_fingerprint`.`fingerprint_type` AS `fingerprint_type`,`emp_fingerprint`.`fingerprint_time` AS `fingerprint_time`,`emp_fingerprint`.`in_out` AS `in_out`,`emp_fingerprint`.`data_entry` AS `data_entry`,`emp_fingerprint`.`hash` AS `hash`,`emp_fingerprint`.`location` AS `location`,`emp_fingerprint`.`user_create_record` AS `user_create_record`,`emp_fingerprint`.`user_update_record` AS `user_update_record`,`emp_fingerprint`.`mobile_info` AS `mobile_info` from `emp_fingerprint` where `emp_fingerprint`.`user_hash` in (select `t1`.`user_hash` from (select `emp_fingerprint`.`user_hash` AS `user_hash`,`emp_fingerprint`.`mobile_info` AS `mobile_info`,count(`emp_fingerprint`.`mobile_info`) AS `cc` from `emp_fingerprint` group by `emp_fingerprint`.`mobile_info`) `t1` group by `t1`.`user_hash` having (count(`t1`.`mobile_info`) > 1)) */;

--
-- Final view structure for view `emp_info`
--

/*!50001 DROP VIEW IF EXISTS `emp_info`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `emp_info` AS select `system_users`.`id` AS `emp_id`,`emp_information`.`user_hash` AS `emp_hash`,`system_users`.`name` AS `emp_name`,`emp_information`.`primary_salary` AS `primary_salary`,`emp_information`.`vacations_limit` AS `vacations_limit`,`emp_information`.`sick_leave` AS `sick_leave`,`emp_information`.`work_from` AS `work_from`,`emp_information`.`work_to` AS `work_to`,`emp_information`.`allow_time` AS `allow_time`,`office_location`.`name` AS `location` from ((`system_users` join `emp_information` on((`system_users`.`hash` = `emp_information`.`user_hash`))) join `office_location` on((`office_location`.`hash` = `emp_information`.`location_of_work`))) */;

--
-- Final view structure for view `emp_today_status`
--

/*!50001 DROP VIEW IF EXISTS `emp_today_status`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `emp_today_status` AS select `su`.`name` AS `name`,`su`.`hash` AS `hash`,(case when `v`.`date_start` then 'المجازون' when (count(0) = 0) then 'الغياب' when ((count(0) = 1) and (min(`ef`.`fingerprint_time`) is null)) then 'الغياب' when ((count(0) = 1) and (`ef`.`in_out` = 'in') and (`ei`.`allow_time` < cast(min(`ef`.`fingerprint_time`) as time))) then 'المتأخرون' when ((count(0) = 1) and (`ei`.`allow_time` <= cast(min(`ef`.`fingerprint_time`) as time))) then 'المتأخرون' when ((count(0) = 2) and (`ei`.`allow_time` < cast(min(`ef`.`fingerprint_time`) as time))) then 'المتأخرون' else 'الحضور' end) AS `status`,`ei`.`allow_time` AS `time`,min(cast(`ef`.`fingerprint_time` as time)) AS `arrive_time` from (((`system_users` `su` left join `emp_fingerprint` `ef` on(((`ef`.`user_hash` = `su`.`hash`) and (cast(`ef`.`fingerprint_time` as date) = cast(curdate() as date))))) join `emp_information` `ei` on(((`su`.`hash` = `ei`.`user_hash`) and (`ei`.`is_work` = 1)))) left join `vacations` `v` on(((`v`.`user_hash` = `su`.`hash`) and (`v`.`date_start` <= curdate()) and (`v`.`date_end` > curdate()) and (`v`.`status` = '111')))) group by `su`.`hash` */;

--
-- Final view structure for view `emp_vacation_summery`
--

/*!50001 DROP VIEW IF EXISTS `emp_vacation_summery`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `emp_vacation_summery` AS select `system_users`.`name` AS `name`,ifnull(sec_to_time(sum(time_to_sec(timediff(`vacations`.`date_end`,`vacations`.`date_start`)))),0) AS `count`,ifnull(year(`vacations`.`date_start`),'year') AS `year` from (`system_users` left join `vacations` on(((`system_users`.`hash` = `vacations`.`user_hash`) and (`vacations`.`status` = '111') and (`vacations`.`vication_type_hash` in ('222','333'))))) group by `system_users`.`name`,year(`vacations`.`date_start`) */;

--
-- Final view structure for view `finger_print`
--

/*!50001 DROP VIEW IF EXISTS `finger_print`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `finger_print` AS select `print_in`.`user_hash` AS `user_hash`,`print_in`.`in_out` AS `type1`,`print_in`.`fingerprint_time` AS `in_at`,`print_out`.`in_out` AS `type2`,`print_out`.`fingerprint_time` AS `out_at` from (`emp_fingerprint` `print_in` join `emp_fingerprint` `print_out` on(((`print_in`.`user_hash` = `print_out`.`user_hash`) and (cast(`print_in`.`fingerprint_time` as date) = cast(`print_out`.`fingerprint_time` as date)) and (`print_in`.`in_out` = 'in') and (`print_out`.`in_out` = 'out')))) */;

--
-- Final view structure for view `ftth_primaty_informations`
--

/*!50001 DROP VIEW IF EXISTS `ftth_primaty_informations`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `ftth_primaty_informations` AS select `subscribers_customers_ftth`.`updated_at` AS `updated_at`,`jt`.`username` AS `username`,substring_index(substring_index(`jt`.`username`,'-',1),'-',-(1)) AS `zone`,substring_index(substring_index(`jt`.`username`,'-',2),'-',-(1)) AS `fat`,substring_index(`jt`.`username`,'-',-(1)) AS `board`,`jt`.`hasActiveSession` AS `hasActiveSession`,`jt`.`customer_displayValue` AS `customer_displayValue`,`jt`.`customer_id` AS `customer_id`,`jt`.`expires` AS `expires`,`jt`.`ipAddress` AS `ipAddress` from (`subscribers_customers_ftth` join json_table(`subscribers_customers_ftth`.`data`, '$[*]' columns (`username` varchar(255) character set utf8mb4 path '$.username', `hasActiveSession` tinyint(1) path '$.hasActiveSession', `customer_displayValue` varchar(255) character set utf8mb4 path '$.customer.displayValue', `customer_id` varchar(255) character set utf8mb4 path '$.customer.id', `expires` datetime path '$.expires', `ipAddress` varchar(255) character set utf8mb4 path '$.ipAddress')) `jt`) */;

--
-- Final view structure for view `task_completion_view`
--

/*!50001 DROP VIEW IF EXISTS `task_completion_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `task_completion_view` AS select `su`.`name` AS `name`,`ts`.`title` AS `status`,timestampdiff(MINUTE,`t`.`accept_date`,`t`.`action_date`) AS `task_completion_minutes`,`t`.`accept_date` AS `accept_time`,`t`.`action_date` AS `deliver_time`,`t`.`project_hash` AS `project_hash` from ((`task` `t` left join `task_status` `ts` on((`t`.`status` = `ts`.`hash`))) join `system_users` `su` on((`t`.`accept_user` = `su`.`hash`))) where (`t`.`status` in ('111','222','333')) */;

--
-- Final view structure for view `view_customer_status`
--

/*!50001 DROP VIEW IF EXISTS `view_customer_status`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `view_customer_status` AS select `ftth_primaty_informations`.`zone` AS `zone`,`ftth_primaty_informations`.`fat` AS `FAT`,count(0) AS `total_customers`,sum((case when (`ftth_primaty_informations`.`hasActiveSession` = '0') then 1 else 0 end)) AS `customers_not_working_count` from (`ftth_primaty_informations` join `view_zone` on(((`ftth_primaty_informations`.`fat` = `view_zone`.`zoneNumber`) and (`ftth_primaty_informations`.`zone` = `view_zone`.`zone`)))) group by `ftth_primaty_informations`.`zone` */;

--
-- Final view structure for view `view_customers_FTTH_expire_soon`
--

/*!50001 DROP VIEW IF EXISTS `view_customers_FTTH_expire_soon`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `view_customers_FTTH_expire_soon` AS select `jt`.`customer_displayValue` AS `customer_displayValue`,`jt`.`username` AS `username`,`jt`.`zone_displayValue` AS `zone_displayValue`,`jt`.`expires` AS `expires` from (`subscribers_customers_ftth` join json_table(`subscribers_customers_ftth`.`data`, '$[*]' columns (`customer_displayValue` varchar(255) character set utf8mb4 path '$.customer.displayValue', `username` varchar(255) character set utf8mb4 path '$.username', `zone_displayValue` varchar(255) character set utf8mb4 path '$.zone.displayValue', `expires` varchar(255) character set utf8mb4 path '$.expires')) `jt`) where (str_to_date(substr(`jt`.`expires`,1,19),'%Y-%m-%dT%H:%i:%s') <= (now() + interval 48 hour)) */;

--
-- Final view structure for view `view_subscriber_info`
--

/*!50001 DROP VIEW IF EXISTS `view_subscriber_info`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `view_subscriber_info` AS select `jt`.`id` AS `id`,`jt`.`user_name` AS `user_name`,`jt`.`mobile` AS `mobile`,`jt`.`customer_id` AS `customer_id` from (`subscribers_customers_information_ftth` join json_table(`subscribers_customers_information_ftth`.`data`, '$[*]' columns (`id` varchar(255) character set utf8mb4 path '$.self.id', `user_name` varchar(255) character set utf8mb4 path '$.self.displayValue', `mobile` varchar(255) character set utf8mb4 path '$.primaryContact.mobile', `customer_id` varchar(55) character set utf8mb4 path '$.self.id')) `jt`) */;

--
-- Final view structure for view `view_zone`
--

/*!50001 DROP VIEW IF EXISTS `view_zone`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `view_zone` AS select `jt`.`zone` AS `zone`,regexp_substr(`jt_fat`.`fat`,'[0-9]+') AS `zoneNumber` from ((`zones_ftth` join json_table(`zones_ftth`.`data`, '$[*]' columns (`zone` varchar(50) character set utf8mb4 path '$.displayValue', `fats` json path '$.fats')) `jt`) join json_table(`jt`.`fats`, '$[*]' columns (`fat` varchar(255) character set utf8mb4 path '$.displayValue')) `jt_fat`) */;

--
-- Final view structure for view `view_zone_customer_summary`
--

/*!50001 DROP VIEW IF EXISTS `view_zone_customer_summary`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`redha`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `view_zone_customer_summary` AS select `customer_FTTH_not_working`.`zone` AS `zone`,`customer_FTTH_not_working`.`zoneNumber` AS `FAT`,`customer_FTTH_not_working`.`updated_at` AS `updated_at`,count(0) AS `customers_not_working_count` from (`customer_FTTH_not_working` join `view_zone` on(((`customer_FTTH_not_working`.`zoneNumber` = `view_zone`.`zoneNumber`) and (`customer_FTTH_not_working`.`zone` = `view_zone`.`zone`)))) group by `customer_FTTH_not_working`.`zoneNumber` */;


