DROP TABLE IF EXISTS `cargo`;
CREATE TABLE `cargo` (
  `cargo_id` int(11) NOT NULL AUTO_INCREMENT,
  `sender_name` varchar(100) DEFAULT NULL,
  `sender_contact` varchar(50) DEFAULT NULL,
  `receiver_name` varchar(100) DEFAULT NULL,
  `receiver_contact` varchar(50) DEFAULT NULL,
  `pickup_location` varchar(100) DEFAULT NULL,
  `destination` varchar(100) DEFAULT NULL,
  `cargo_type` varchar(100) DEFAULT NULL,
  `weight` decimal(10,2) DEFAULT NULL,
  `cost` decimal(10,2) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'Pending',
  `departure_date` date DEFAULT NULL,
  `arrival_date` date DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`cargo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `cargo` (`cargo_id`, `sender_name`, `sender_contact`, `receiver_name`, `receiver_contact`, `pickup_location`, `destination`, `cargo_type`, `weight`, `cost`, `currency`, `status`, `departure_date`, `arrival_date`, `created_at`, `updated_at`) VALUES ('2', 'Safiullah', '079944', 'Khan', '343243', 'kabul', 'nanagarhar', 'clothes', '5.00', '12000.00', 'USD', 'Delivered', '2025-06-07', '2025-06-26', '2025-06-03 09:43:08', '2025-06-04 07:55:55');

DROP TABLE IF EXISTS `customer_applications`;
CREATE TABLE `customer_applications` (
  `application_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(255) NOT NULL,
  `customer_phone` varchar(20) NOT NULL,
  `application_type` varchar(255) NOT NULL,
  `application_date` date NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `paid_amount` decimal(10,2) DEFAULT 0.00,
  `total_balance` decimal(10,2) GENERATED ALWAYS AS (`total_amount` - `paid_amount`) STORED,
  `net_profit` decimal(10,2) DEFAULT 0.00,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`application_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

INSERT INTO `customer_applications` (`application_id`, `customer_name`, `customer_phone`, `application_type`, `application_date`, `total_amount`, `paid_amount`, `total_balance`, `net_profit`, `remarks`, `created_at`, `updated_at`) VALUES ('4', 'bahadar', '575676', 'dfgdfgf', '2025-01-16', '900.00', '600.00', '300.00', '100.00', 'dgfdg', '2025-01-25 09:39:30', '2025-01-25 09:39:30');
INSERT INTO `customer_applications` (`application_id`, `customer_name`, `customer_phone`, `application_type`, `application_date`, `total_amount`, `paid_amount`, `total_balance`, `net_profit`, `remarks`, `created_at`, `updated_at`) VALUES ('5', 'bahadar', '575676', 'dfgdfgf', '2025-01-16', '900.00', '600.00', '300.00', '100.00', 'dgfdg', '2025-01-25 09:40:47', '2025-01-25 09:40:47');
INSERT INTO `customer_applications` (`application_id`, `customer_name`, `customer_phone`, `application_type`, `application_date`, `total_amount`, `paid_amount`, `total_balance`, `net_profit`, `remarks`, `created_at`, `updated_at`) VALUES ('6', 'babar', '0781113616', 'hay', '2025-01-16', '7000.00', '5000.00', '2000.00', '500.00', 'gdfgf', '2025-01-25 11:33:11', '2025-01-25 11:33:40');
INSERT INTO `customer_applications` (`application_id`, `customer_name`, `customer_phone`, `application_type`, `application_date`, `total_amount`, `paid_amount`, `total_balance`, `net_profit`, `remarks`, `created_at`, `updated_at`) VALUES ('7', 'samiullah', '78797789', 'Dawadt Nanma', '2025-01-24', '9000.00', '6000.00', '3000.00', '2000.00', '', '2025-01-29 18:18:09', '2025-01-29 18:18:09');

DROP TABLE IF EXISTS `external_tickets`;
CREATE TABLE `external_tickets` (
  `ticket_id` int(11) NOT NULL AUTO_INCREMENT,
  `passenger_name` varchar(255) NOT NULL,
  `passenger_phone` varchar(15) NOT NULL,
  `departure_country` varchar(255) NOT NULL,
  `arrival_country` varchar(255) NOT NULL,
  `departure_date` date NOT NULL,
  `return_date` date DEFAULT NULL,
  `ticket_type` varchar(50) NOT NULL,
  `ticket_price` decimal(10,2) NOT NULL,
  `total_paid` decimal(10,2) NOT NULL,
  `total_balance` decimal(10,2) NOT NULL,
  `payment_status` enum('Paid','Partial','Unpaid') NOT NULL,
  `ticket_status` enum('Confirmed','Pending','Cancelled') NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `remarks` text DEFAULT NULL,
  `stay_point_1` varchar(255) DEFAULT NULL,
  `stay_point_2` varchar(255) DEFAULT NULL,
  `ticket_currency` varchar(3) NOT NULL DEFAULT 'AFN',
  PRIMARY KEY (`ticket_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

INSERT INTO `external_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_country`, `arrival_country`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `stay_point_1`, `stay_point_2`, `ticket_currency`) VALUES ('1', 'Salam ', '36646', 'ttt', 'yyyy', '2024-12-19', '0000-00-00', 'One Way', '9000.00', '6000.00', '3000.00', 'Paid', 'Confirmed', '2025-01-11 08:55:42', '2025-01-15 14:00:55', 'ffghg', 'fadsfds', 'sadfds', 'AFN');
INSERT INTO `external_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_country`, `arrival_country`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `stay_point_1`, `stay_point_2`, `ticket_currency`) VALUES ('2', 'Khan jan', '36646', 'khan', 'jan', '2024-12-31', '0000-00-00', 'One Way', '9000.00', '6000.00', '3000.00', 'Paid', 'Confirmed', '2025-01-11 08:57:12', '2025-01-15 14:01:27', 'ffghg', '', '', 'AFN');
INSERT INTO `external_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_country`, `arrival_country`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `stay_point_1`, `stay_point_2`, `ticket_currency`) VALUES ('4', 'saleem', '53454', 'sdfadsf', 'rtyrtytry', '2025-01-10', '2025-01-17', 'One-Way', '6000.00', '600.00', '5400.00', 'Paid', 'Confirmed', '2025-01-14 15:16:24', '2025-01-14 15:16:24', 'gdfgf', '', '', 'AFN');
INSERT INTO `external_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_country`, `arrival_country`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `stay_point_1`, `stay_point_2`, `ticket_currency`) VALUES ('5', 'saleem', '53454', 'Other', 'Other', '2025-01-10', '2025-01-17', 'One-Way', '6000.00', '600.00', '5400.00', 'Paid', 'Confirmed', '2025-01-14 15:25:40', '2025-01-14 15:25:40', 'gdfgf', '', '', 'AFN');
INSERT INTO `external_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_country`, `arrival_country`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `stay_point_1`, `stay_point_2`, `ticket_currency`) VALUES ('6', 'saleem', '53454', 'Other', 'Other', '2025-01-10', '2025-01-17', 'One-Way', '6000.00', '600.00', '5400.00', 'Paid', 'Confirmed', '2025-01-14 15:25:49', '2025-01-14 15:25:49', 'gdfgf', '', '', 'AFN');
INSERT INTO `external_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_country`, `arrival_country`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `stay_point_1`, `stay_point_2`, `ticket_currency`) VALUES ('7', 'saleem', '53454', '', 'fgdsafda', '2025-01-24', '0000-00-00', 'One-way', '6000.00', '7000.00', '-1000.00', 'Paid', 'Confirmed', '2025-01-15 13:39:46', '2025-01-15 13:39:46', 'sdfdasf', 'dfasdf', 'sadfds', 'AFN');
INSERT INTO `external_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_country`, `arrival_country`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `stay_point_1`, `stay_point_2`, `ticket_currency`) VALUES ('8', 'saleem', '53454', '', 'fgdsafda', '2025-01-24', '0000-00-00', 'One-way', '6000.00', '7000.00', '-1000.00', 'Paid', 'Confirmed', '2025-01-15 13:42:01', '2025-01-15 13:42:01', 'sdfdasf', 'dfasdf', 'sadfds', 'AFN');
INSERT INTO `external_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_country`, `arrival_country`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `stay_point_1`, `stay_point_2`, `ticket_currency`) VALUES ('9', 'saleem', '53454', 'jjjj', 'gggg', '2025-01-17', '2025-01-17', 'One Way', '6000.00', '7000.00', '-1000.00', 'Paid', '', '2025-01-15 14:03:35', '2025-01-15 14:03:35', 'gdfgdfg', 'dfasdf', 'sadfds', 'AFN');
INSERT INTO `external_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_country`, `arrival_country`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `stay_point_1`, `stay_point_2`, `ticket_currency`) VALUES ('10', 'Mama', '089899898', 'Afghanistan', 'United States', '2025-06-12', '2025-06-26', 'One Way', '12000.00', '1200.00', '800.00', 'Paid', '', '2025-06-02 09:04:27', '2025-06-02 09:04:27', 'SADKFASJF', 'kABUL', 'nANGARHAR', 'USD');
INSERT INTO `external_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_country`, `arrival_country`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `stay_point_1`, `stay_point_2`, `ticket_currency`) VALUES ('11', 'Mama', '089899898', 'Afghanistan', 'United States', '2025-06-12', '2025-06-26', 'One Way', '12000.00', '1200.00', '800.00', 'Paid', '', '2025-06-02 09:08:18', '2025-06-02 09:08:18', 'SADKFASJF', 'kABUL', 'nANGARHAR', 'USD');

DROP TABLE IF EXISTS `income_application`;
CREATE TABLE `income_application` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `application_type` varchar(100) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `currency` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

INSERT INTO `income_application` (`id`, `name`, `application_type`, `amount`, `remarks`, `created_at`, `currency`) VALUES ('3', 'Bilal', 'Umra', '200.00', 'lksfjkd', '2025-02-09 09:52:13', '');
INSERT INTO `income_application` (`id`, `name`, `application_type`, `amount`, `remarks`, `created_at`, `currency`) VALUES ('4', 'Safiullah', 'Visa', '300.00', 'LKK', '2025-06-14 09:14:16', 'AFN');
INSERT INTO `income_application` (`id`, `name`, `application_type`, `amount`, `remarks`, `created_at`, `currency`) VALUES ('5', 'Shirt', 'External Tickets', '3000.00', 'LSDFDJSKADLF', '2025-06-14 10:46:10', 'USD');

DROP TABLE IF EXISTS `internal_tickets`;
CREATE TABLE `internal_tickets` (
  `ticket_id` int(11) NOT NULL AUTO_INCREMENT,
  `passenger_name` varchar(255) NOT NULL,
  `passenger_phone` varchar(20) NOT NULL,
  `departure_province` varchar(100) NOT NULL,
  `arrival_province` varchar(100) NOT NULL,
  `departure_date` date NOT NULL,
  `return_date` date DEFAULT NULL,
  `ticket_type` varchar(50) NOT NULL,
  `ticket_price` decimal(10,2) NOT NULL,
  `total_paid` decimal(10,2) DEFAULT 0.00,
  `total_balance` decimal(10,2) GENERATED ALWAYS AS (`ticket_price` - `total_paid`) STORED,
  `payment_status` enum('Paid','Partially Paid','Unpaid') DEFAULT 'Unpaid',
  `ticket_status` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `remarks` text DEFAULT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'AFN',
  PRIMARY KEY (`ticket_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

INSERT INTO `internal_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_province`, `arrival_province`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `currency`) VALUES ('1', 'Salam khan', '5675676', 'Mazar-i-Sharif', 'Mazar-i-Sharif', '2025-01-11', '2025-01-18', 'One-way', '8000.00', '6000.00', '2000.00', 'Paid', 'Confirmed', '2025-01-04 09:13:33', '2025-06-02 08:54:35', 'sdfdsf', 'AFN');
INSERT INTO `internal_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_province`, `arrival_province`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `currency`) VALUES ('3', 'kamal', '36646', 'Other', 'Other', '2025-01-15', '2025-01-17', 'One Way', '9000.00', '9000.00', '0.00', 'Unpaid', 'Booked', '2025-01-04 10:51:57', '2025-01-16 09:13:43', 'ffghg', 'AFN');
INSERT INTO `internal_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_province`, `arrival_province`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `currency`) VALUES ('4', 'sabir', '354354', 'baamnya', 'iiii', '2025-01-21', '2025-01-07', 'One-sided', '700.00', '80.00', '620.00', 'Paid', 'Confirmed', '2025-01-16 08:54:09', '2025-01-16 08:54:09', 'sfsdf', 'AFN');
INSERT INTO `internal_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_province`, `arrival_province`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `currency`) VALUES ('5', 'Baram', '45646', 'zadran', 'baghaln', '2025-01-24', '2025-01-22', 'One-sided', '900.00', '600.00', '300.00', 'Paid', 'Confirmed', '2025-01-16 08:57:54', '2025-01-16 08:57:54', 'sdfdsf', 'AFN');
INSERT INTO `internal_tickets` (`ticket_id`, `passenger_name`, `passenger_phone`, `departure_province`, `arrival_province`, `departure_date`, `return_date`, `ticket_type`, `ticket_price`, `total_paid`, `total_balance`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `remarks`, `currency`) VALUES ('6', 'Lala Gul', '079434', 'Kandahar', 'Herat', '2025-06-06', '2025-06-25', 'One-sided', '12000.00', '2000.00', '10000.00', '', 'Pending', '2025-06-02 08:40:49', '2025-06-02 08:40:49', 'FJKAKSDLFK', 'USD');

DROP TABLE IF EXISTS `national_id_applications`;
CREATE TABLE `national_id_applications` (
  `id_application_id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_name` varchar(100) NOT NULL,
  `father_name` varchar(100) NOT NULL,
  `date_of_birth` date NOT NULL,
  `contact_number` varchar(15) DEFAULT NULL,
  `application_type` varchar(255) DEFAULT NULL,
  `application_date` date NOT NULL,
  `total_fee` decimal(10,2) NOT NULL,
  `paid_fee` decimal(10,2) DEFAULT 0.00,
  `balance_fee` decimal(10,2) GENERATED ALWAYS AS (`total_fee` - `paid_fee`) STORED,
  `status` enum('Pending','Submitted','Approved','Rejected') DEFAULT 'Pending',
  `remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id_application_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `national_id_applications_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

INSERT INTO `national_id_applications` (`id_application_id`, `applicant_name`, `father_name`, `date_of_birth`, `contact_number`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('1', 'saeed jan', 'salman', '2025-01-16', '0783332428', 'Renewal', '2025-01-16', '600.00', '300.00', '300.00', 'Pending', 'sdfd', '1', '2025-01-01 12:56:51', '2025-01-01 14:35:32');
INSERT INTO `national_id_applications` (`id_application_id`, `applicant_name`, `father_name`, `date_of_birth`, `contact_number`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('3', 'Sharif jan', 'samiullah', '2025-01-23', '784354234', 'New', '2025-01-23', '12000.00', '6000.00', '6000.00', 'Approved', 'trtertr', '1', '2025-01-14 13:10:00', '2025-02-02 11:26:05');
INSERT INTO `national_id_applications` (`id_application_id`, `applicant_name`, `father_name`, `date_of_birth`, `contact_number`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('4', 'khan', 'Shmal', '2025-01-16', '645645', 'sdfsafd', '2025-01-23', '7000.00', '2900.00', '4100.00', 'Approved', 'fsfsdf', '1', '2025-01-14 13:16:02', '2025-01-14 13:16:02');
INSERT INTO `national_id_applications` (`id_application_id`, `applicant_name`, `father_name`, `date_of_birth`, `contact_number`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('5', 'khan', 'Shmal', '2025-01-22', '645645', 'New', '2025-01-16', '7000.00', '2900.00', '4100.00', 'Approved', 'dgfgf', '1', '2025-01-14 14:00:32', '2025-01-14 14:00:32');
INSERT INTO `national_id_applications` (`id_application_id`, `applicant_name`, `father_name`, `date_of_birth`, `contact_number`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('7', 'Ihsanullah', 'salman kahnm', '2025-01-28', '534543', 'New', '2025-01-22', '900.00', '600.00', '300.00', 'Pending', 'sfsdf', '1', '2025-01-16 09:46:07', '2025-01-16 09:46:07');
INSERT INTO `national_id_applications` (`id_application_id`, `applicant_name`, `father_name`, `date_of_birth`, `contact_number`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('8', 'tertre', 'eertrewt', '2025-01-22', '534543', 'Renewal', '2025-01-15', '6000.00', '4000.00', '2000.00', 'Approved', 'sgsfds', '5', '2025-01-16 12:16:33', '2025-01-16 12:16:33');

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `notification_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `task_assigned_to` int(11) DEFAULT NULL,
  `assigned_by` int(11) DEFAULT NULL,
  `notification_type` varchar(255) NOT NULL,
  `status` enum('unread','read') DEFAULT 'unread',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `due_date` date DEFAULT NULL,
  `priority` enum('low','medium','high') DEFAULT 'medium',
  PRIMARY KEY (`notification_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4;

INSERT INTO `notifications` (`notification_id`, `title`, `message`, `task_assigned_to`, `assigned_by`, `notification_type`, `status`, `created_at`, `updated_at`, `due_date`, `priority`) VALUES ('5', 'fbhfgh', 'sdgsdfg', '3', '4', '', 'read', '2025-01-25 14:23:00', '2025-01-27 14:56:33', '2025-01-22', 'low');
INSERT INTO `notifications` (`notification_id`, `title`, `message`, `task_assigned_to`, `assigned_by`, `notification_type`, `status`, `created_at`, `updated_at`, `due_date`, `priority`) VALUES ('6', 'khan', 'xcvxcvc', '3', '4', '0', 'read', '2025-01-25 14:28:34', '2025-01-27 15:00:50', '2025-01-16', 'low');
INSERT INTO `notifications` (`notification_id`, `title`, `message`, `task_assigned_to`, `assigned_by`, `notification_type`, `status`, `created_at`, `updated_at`, `due_date`, `priority`) VALUES ('7', 'gdgfd', 'dgsdfg', '3', '4', 'message', 'read', '2025-01-25 14:32:47', '2025-01-29 18:07:49', '2025-01-08', 'low');
INSERT INTO `notifications` (`notification_id`, `title`, `message`, `task_assigned_to`, `assigned_by`, `notification_type`, `status`, `created_at`, `updated_at`, `due_date`, `priority`) VALUES ('8', 'JANANAS', 'SFSFD', '5', '4', 'message', 'read', '2025-01-27 14:41:45', '2025-01-28 14:21:51', '2025-01-29', 'medium');
INSERT INTO `notifications` (`notification_id`, `title`, `message`, `task_assigned_to`, `assigned_by`, `notification_type`, `status`, `created_at`, `updated_at`, `due_date`, `priority`) VALUES ('9', 'samiullah', 'kha safasdfadsf', '5', '4', 'message', 'read', '2025-01-27 15:00:25', '2025-01-28 14:21:32', '2025-01-15', 'low');
INSERT INTO `notifications` (`notification_id`, `title`, `message`, `task_assigned_to`, `assigned_by`, `notification_type`, `status`, `created_at`, `updated_at`, `due_date`, `priority`) VALUES ('10', 'Please be Carful 🤣', 'Someone is coming here for learning English Language please allow him to enrol himself.', '5', '4', 'message', 'read', '2025-01-29 18:03:47', '2025-01-29 18:07:50', '2025-01-24', 'low');
INSERT INTO `notifications` (`notification_id`, `title`, `message`, `task_assigned_to`, `assigned_by`, `notification_type`, `status`, `created_at`, `updated_at`, `due_date`, `priority`) VALUES ('11', 'Please be Carful 🤣', 'gfd78y98', '5', '4', 'alert', 'read', '2025-02-01 18:40:39', '2025-02-03 11:36:11', '2025-02-01', 'high');
INSERT INTO `notifications` (`notification_id`, `title`, `message`, `task_assigned_to`, `assigned_by`, `notification_type`, `status`, `created_at`, `updated_at`, `due_date`, `priority`) VALUES ('12', 'Hay', 'ksdfjksaf', '3', '4', 'message', 'read', '2025-02-03 11:35:33', '2025-02-03 11:36:01', '2025-02-14', 'high');
INSERT INTO `notifications` (`notification_id`, `title`, `message`, `task_assigned_to`, `assigned_by`, `notification_type`, `status`, `created_at`, `updated_at`, `due_date`, `priority`) VALUES ('13', 'kljlkjklj', 'kjllkjlkj', '3', '4', 'task', 'read', '2025-08-30 13:00:34', '2025-08-30 13:07:03', '2025-08-30', 'high');

DROP TABLE IF EXISTS `office_expenses`;
CREATE TABLE `office_expenses` (
  `expense_id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_type` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `expense_date` date NOT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`expense_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `office_expenses_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

INSERT INTO `office_expenses` (`expense_id`, `expense_type`, `category`, `amount`, `expense_date`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('1', 'yearly', 'electricity_bill', '800.00', '2025-01-14', '0', '0', '2025-01-02 12:42:37', '2025-01-02 13:28:35');
INSERT INTO `office_expenses` (`expense_id`, `expense_type`, `category`, `amount`, `expense_date`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('2', 'yearly', 'rent', '900.00', '2025-01-24', '0', '0', '2025-01-02 12:49:24', '2025-01-02 13:30:39');
INSERT INTO `office_expenses` (`expense_id`, `expense_type`, `category`, `amount`, `expense_date`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('3', 'daily', 'food', '300.00', '2025-01-29', 'hfhfghg', '0', '2025-01-13 18:30:01', '2025-01-13 18:30:01');
INSERT INTO `office_expenses` (`expense_id`, `expense_type`, `category`, `amount`, `expense_date`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('4', 'daily', 'food', '3000.00', '2025-02-19', '', '0', '2025-02-15 17:10:56', '2025-02-15 17:10:56');
INSERT INTO `office_expenses` (`expense_id`, `expense_type`, `category`, `amount`, `expense_date`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES ('8', 'daily', 'food', '12000.00', '2025-09-07', 'salkesfjklsdfj', '', '2025-09-07 15:55:52', '2025-09-07 15:55:52');

DROP TABLE IF EXISTS `passport_applications`;
CREATE TABLE `passport_applications` (
  `passport_id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_name` varchar(100) NOT NULL,
  `national_id_number` varchar(50) NOT NULL,
  `date_of_birth` date NOT NULL,
  `application_type` varchar(255) NOT NULL,
  `application_date` date NOT NULL,
  `total_fee` decimal(10,2) NOT NULL,
  `paid_fee` decimal(10,2) DEFAULT 0.00,
  `balance_fee` decimal(10,2) GENERATED ALWAYS AS (`total_fee` - `paid_fee`) STORED,
  `status` enum('Pending','Submitted','Approved','Rejected') DEFAULT 'Pending',
  `remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `biometric_date` date DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`passport_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `passport_applications_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4;

INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('4', 'Hiala', '6346546', '2025-01-16', 'Diplomatic', '2025-01-09', '800.00', '400.00', '400.00', 'Pending', 'dgdfg', '1', '2025-01-01 06:54:38', '2025-01-14 15:02:32', '2025-01-30', '');
INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('5', 'Bahar', '4564365', '2025-01-16', '', '2025-01-15', '5000.00', '500.00', '4500.00', 'Approved', 'gdsgfdg', '1', '2025-01-01 08:43:22', '2025-01-01 08:43:22', '', '');
INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('7', 'fff', 'ffgfg', '2025-01-08', '', '2025-01-17', '600.00', '70.00', '530.00', 'Approved', 'sdfsdf', '1', '2025-01-01 08:50:51', '2025-01-01 08:50:51', '', '');
INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('8', 'safi', '534543', '2025-01-09', 'Diplomatic', '2025-01-23', '700.00', '400.00', '300.00', 'Approved', 'sfsfd', '1', '2025-01-01 08:55:26', '2025-01-01 08:55:26', '', '');
INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('9', 'Saleem', '45345435', '2025-01-16', 'Old', '2025-01-10', '5000.00', '800.00', '4200.00', 'Approved', 'adsdfds', '1', '2025-01-14 08:14:16', '2025-01-14 08:14:16', '', '');
INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('10', 'Samiullah jan', '3345', '2025-01-09', 'Ordinary', '2025-01-16', '8000.00', '6000.00', '2000.00', 'Approved', 'gdsgf', '1', '2025-01-14 10:47:59', '2025-01-14 15:02:14', '2025-01-24', '');
INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('11', 'bdam', '345435', '2025-01-17', 'dsafadsfds', '2025-01-24', '8000.00', '6000.00', '2000.00', 'Approved', 'sffsdf', '1', '2025-01-14 10:55:58', '2025-01-14 10:55:58', '2025-01-21', '');
INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('12', 'fadsfasdf', '54354', '2025-01-16', 'sadfasdf', '2025-01-17', '5000.00', '8000.00', '-3000.00', 'Rejected', 'asfdadsfad', '1', '2025-01-14 10:56:42', '2025-01-14 10:56:42', '', '');
INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('13', 'fffff', '5345345', '2025-01-16', 'sdfgfdsgsfdg', '2025-01-23', '600.00', '300.00', '300.00', 'Pending', 'dgdfg', '1', '2025-01-14 10:58:43', '2025-01-14 10:58:43', '2025-01-24', '');
INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('14', 'Badar', '3432432', '2025-05-05', 'Ordinary', '2025-05-22', '1200.00', '400.00', '800.00', 'Pending', 'dlkfsdakf', '4', '2025-05-31 08:57:31', '2025-05-31 08:57:31', '2025-05-20', 'AFN');
INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`) VALUES ('15', 'BaDA,', '3432432', '2025-05-05', 'Official', '2025-05-22', '1200.00', '400.00', '800.00', 'Pending', 'LDFJKALS;KFD', '4', '2025-05-31 09:42:33', '2025-05-31 09:42:33', '2025-05-20', 'USD');

DROP TABLE IF EXISTS `record_logs`;
CREATE TABLE `record_logs` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `action_type` enum('add','update','delete') DEFAULT NULL,
  `action_description` text DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `scholarship_applications`;
CREATE TABLE `scholarship_applications` (
  `scholarship_id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_name` varchar(100) NOT NULL,
  `contact_number` varchar(15) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `scholarship_type` varchar(255) NOT NULL,
  `field_of_study` varchar(100) NOT NULL,
  `country` varchar(100) NOT NULL,
  `application_date` date NOT NULL,
  `total_fee` decimal(10,2) NOT NULL,
  `paid_fee` decimal(10,2) DEFAULT 0.00,
  `balance_fee` decimal(10,2) GENERATED ALWAYS AS (`total_fee` - `paid_fee`) STORED,
  `status` enum('Pending','Submitted','Approved','Rejected') DEFAULT 'Pending',
  `remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `currency` varchar(10) NOT NULL DEFAULT 'AFN',
  PRIMARY KEY (`scholarship_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `scholarship_applications_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;

INSERT INTO `scholarship_applications` (`scholarship_id`, `applicant_name`, `contact_number`, `email`, `scholarship_type`, `field_of_study`, `country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('1', 'Ihsanullah', '0783332428', 'safiullahfekarmand@gmail.com', 'Merit-Based', 'Engineering', 'Afghanistan', '2024-12-31', '4000.00', '2000.00', '2000.00', 'Approved', 'dfgdfg', '1', '2024-12-31 11:57:04', '2025-05-31 10:13:03', 'AFN');
INSERT INTO `scholarship_applications` (`scholarship_id`, `applicant_name`, `contact_number`, `email`, `scholarship_type`, `field_of_study`, `country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('5', 'janullah', '0783332428', 'safiullahfekarmand@gmail.com', '', 'Computer Science', 'India', '2025-01-01', '2000.00', '600.00', '1400.00', 'Pending', 'gfsdgf', '1', '2025-01-01 06:05:59', '2025-01-01 06:05:59', 'AFN');
INSERT INTO `scholarship_applications` (`scholarship_id`, `applicant_name`, `contact_number`, `email`, `scholarship_type`, `field_of_study`, `country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('7', 'Salam', '0783332428', 'safiullahfekarmand@gmail.com', 'working', 'art', 'canada', '2025-01-14', '8000.00', '4000.00', '4000.00', 'Approved', 'gdfgdf', '1', '2025-01-14 06:26:39', '2025-01-14 06:26:39', 'AFN');
INSERT INTO `scholarship_applications` (`scholarship_id`, `applicant_name`, `contact_number`, `email`, `scholarship_type`, `field_of_study`, `country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('8', 'Ihsanullah', '0783332428', 'safiullahfekarmand@gmail.com', 'Merit-Based', 'Medicine', 'Afghanistan', '2025-01-16', '500.00', '800.00', '-300.00', 'Approved', 'frtr', '1', '2025-01-16 06:20:20', '2025-01-16 06:20:20', 'AFN');
INSERT INTO `scholarship_applications` (`scholarship_id`, `applicant_name`, `contact_number`, `email`, `scholarship_type`, `field_of_study`, `country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('9', 'Ihsanullah', '0783332428', 'safiullahfekarmand@gmail.com', 'Need-Based', 'Medicine', 'Afghanistan', '2025-01-25', '500.00', '800.00', '-300.00', 'Approved', 'sdfds', '4', '2025-01-25 08:36:15', '2025-01-25 08:36:15', 'AFN');
INSERT INTO `scholarship_applications` (`scholarship_id`, `applicant_name`, `contact_number`, `email`, `scholarship_type`, `field_of_study`, `country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('10', 'Sanaullah', '0783332428', 'admin@gmail.com', 'Need-Based', 'Engineering', 'Nagira', '2025-05-31', '12100.00', '2000.00', '10100.00', 'Pending', 'lzkflas;dkf', '4', '2025-05-31 06:26:20', '2025-05-31 06:26:20', 'AFN');
INSERT INTO `scholarship_applications` (`scholarship_id`, `applicant_name`, `contact_number`, `email`, `scholarship_type`, `field_of_study`, `country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('11', 'Kaka', '0734234234', 'safiullahkhan.shirzad@gmial.com', 'Sports', 'Medicine', 'USA', '2025-05-31', '12100.00', '2000.00', '10100.00', 'Pending', 'kfl;ksdl;fkgl;dfg', '4', '2025-05-31 06:27:11', '2025-05-31 06:27:11', 'AFN');
INSERT INTO `scholarship_applications` (`scholarship_id`, `applicant_name`, `contact_number`, `email`, `scholarship_type`, `field_of_study`, `country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('12', 'Abdullah jan', '0783332428', 'safiullahfekarmand@gmail.com', 'Merit-Based', 'Medicine', 'Afghanistan', '2025-08-30', '8000.00', '700.00', '7300.00', 'Rejected', 'kjklj', '4', '2025-08-30 10:04:44', '2025-08-30 10:04:44', 'AFN');

DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
  `transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) DEFAULT NULL,
  `amount_given` decimal(10,2) DEFAULT NULL,
  `amount_paid` decimal(10,2) DEFAULT NULL,
  `balance` decimal(10,2) DEFAULT NULL,
  `transaction_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `purchased_by` varchar(255) NOT NULL,
  `remarks` text DEFAULT NULL,
  PRIMARY KEY (`transaction_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

INSERT INTO `transactions` (`transaction_id`, `product_name`, `amount_given`, `amount_paid`, `balance`, `transaction_date`, `purchased_by`, `remarks`) VALUES ('3', 'Happy', '5000.00', '5500.00', '-500.00', '0000-00-00 00:00:00', '', '');
INSERT INTO `transactions` (`transaction_id`, `product_name`, `amount_given`, `amount_paid`, `balance`, `transaction_date`, `purchased_by`, `remarks`) VALUES ('5', 'Alogan', '5000.00', '4800.00', '200.00', '2025-02-20 00:00:00', 'Amrullah', 'lssjflskdjfs');
INSERT INTO `transactions` (`transaction_id`, `product_name`, `amount_given`, `amount_paid`, `balance`, `transaction_date`, `purchased_by`, `remarks`) VALUES ('6', 'Lobaya', '5000.00', '4000.00', '1000.00', '2025-02-27 00:00:00', 'Sadiq', 'hjghj');

DROP TABLE IF EXISTS `umrah_applications`;
CREATE TABLE `umrah_applications` (
  `umrah_id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_name` varchar(100) NOT NULL,
  `passport_number` varchar(50) NOT NULL,
  `contact_number` varchar(15) DEFAULT NULL,
  `package_type` varchar(255) DEFAULT NULL,
  `departure_date` date NOT NULL,
  `return_date` date NOT NULL,
  `total_fee` decimal(10,2) NOT NULL,
  `paid_fee` decimal(10,2) DEFAULT 0.00,
  `balance_fee` decimal(10,2) GENERATED ALWAYS AS (`total_fee` - `paid_fee`) STORED,
  `status` enum('Pending','Submitted','Approved','Rejected') DEFAULT 'Pending',
  `remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `days` int(2) NOT NULL DEFAULT 15,
  `currency` varchar(3) NOT NULL DEFAULT 'AFN',
  PRIMARY KEY (`umrah_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `umrah_applications_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

INSERT INTO `umrah_applications` (`umrah_id`, `applicant_name`, `passport_number`, `contact_number`, `package_type`, `departure_date`, `return_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `days`, `currency`) VALUES ('3', 'sabir', 'p9876554', '343543', 'Premium', '2025-01-05', '2025-01-07', '900.00', '200.00', '700.00', 'Pending', 'fsdf', '1', '2025-01-05 10:05:25', '2025-01-14 15:10:18', '21', 'AFN');
INSERT INTO `umrah_applications` (`umrah_id`, `applicant_name`, `passport_number`, `contact_number`, `package_type`, `departure_date`, `return_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `days`, `currency`) VALUES ('4', 'Mama', '64654', '3453454', 'Standard', '2025-01-17', '2025-01-23', '5000.00', '3000.00', '2000.00', 'Approved', 'sdfdsfdas', '1', '2025-01-14 15:08:25', '2025-01-14 15:08:25', '15', 'AFN');
INSERT INTO `umrah_applications` (`umrah_id`, `applicant_name`, `passport_number`, `contact_number`, `package_type`, `departure_date`, `return_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `days`, `currency`) VALUES ('5', 'Saleem', '7987878', '0783332428', 'Premium', '2025-06-03', '2025-06-11', '1000.00', '200.00', '800.00', 'Pending', 'CZLJCLKzxc', '4', '2025-06-01 07:47:40', '2025-06-01 08:35:00', '21', 'USD');

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('Admin','Staff') NOT NULL,
  `contact` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` (`user_id`, `username`, `password`, `role`, `contact`, `created_at`, `updated_at`) VALUES ('1', 'safiullah khan', '$2y$10$LRcAX9wBP5jBsLuhLN7tSOKX4S4ABwYUNT/1AV0a/xtrjvrvapTYq', 'Admin', '0783332428', '2024-12-31 10:35:01', '2024-12-31 12:30:21');
INSERT INTO `users` (`user_id`, `username`, `password`, `role`, `contact`, `created_at`, `updated_at`) VALUES ('3', 'khan', '$2y$10$qVKjJpQFbU5./M.QIIGCbu7NgaKMSJ7edQUc37EWcxTcDLVUfnFPy', 'Staff', '0783332428', '2025-01-04 08:19:55', '2025-01-04 08:19:55');
INSERT INTO `users` (`user_id`, `username`, `password`, `role`, `contact`, `created_at`, `updated_at`) VALUES ('4', 'sadiq', '$2y$10$.hlK5p3dN/ScMhtbm7hl7O4SHyloTYGt/pxmojhtpVBLlhVnhh6me', 'Admin', '0783332428', '2025-01-11 12:39:53', '2025-01-13 18:05:53');
INSERT INTO `users` (`user_id`, `username`, `password`, `role`, `contact`, `created_at`, `updated_at`) VALUES ('5', 'hikmatullah', '$2y$10$t6dBL5aroiwqiQCRqi.UzOVZxe3k8LURd4nId0PpEp9HODhnkeG7u', 'Staff', '0783332428', '2025-01-13 18:05:11', '2025-01-13 18:05:11');
INSERT INTO `users` (`user_id`, `username`, `password`, `role`, `contact`, `created_at`, `updated_at`) VALUES ('6', 'kkkk', '$2y$10$rPLMRM2fa5ibwc4ryh3nHuL3vAkqpu87ikk9Jj1llvV5lGs7TGiQq', 'Admin', '', '2025-01-22 10:32:04', '2025-01-22 10:32:04');
INSERT INTO `users` (`user_id`, `username`, `password`, `role`, `contact`, `created_at`, `updated_at`) VALUES ('8', 'AHMAD', '$2y$10$wtRZWaTyM/L7o4OQfdW8PeGnXNn5jiLNsF8vbQ0BVIMtMop8MwaiW', 'Admin', '', '2025-02-05 12:08:57', '2025-02-05 12:08:57');

DROP TABLE IF EXISTS `visa_applications`;
CREATE TABLE `visa_applications` (
  `visa_id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_name` varchar(100) NOT NULL,
  `passport_number` varchar(50) NOT NULL,
  `nationality` varchar(50) NOT NULL,
  `visa_type` varchar(255) DEFAULT NULL,
  `destination_country` varchar(50) NOT NULL,
  `application_date` date NOT NULL,
  `total_fee` decimal(10,2) NOT NULL,
  `paid_fee` decimal(10,2) DEFAULT 0.00,
  `balance_fee` decimal(10,2) GENERATED ALWAYS AS (`total_fee` - `paid_fee`) STORED,
  `net_profit` decimal(10,2) DEFAULT NULL,
  `status` enum('Pending','Submitted','Approved','Rejected') DEFAULT 'Pending',
  `remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `currency` varchar(10) DEFAULT 'AFN',
  PRIMARY KEY (`visa_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `visa_applications_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;

INSERT INTO `visa_applications` (`visa_id`, `applicant_name`, `passport_number`, `nationality`, `visa_type`, `destination_country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `net_profit`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('2', 'Ihsanullah', 'p9876554', 'Afghan', 'Student', 'Uzbekistan', '2024-12-31', '5000.00', '4000.00', '1000.00', '', 'Rejected', 'skldafjlaskd', '1', '2024-12-31 09:44:34', '2025-05-31 05:52:24', 'USD');
INSERT INTO `visa_applications` (`visa_id`, `applicant_name`, `passport_number`, `nationality`, `visa_type`, `destination_country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `net_profit`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('6', 'Sameer', 'Salman', 'Afghan', 'Student', 'Pakistan', '2025-01-14', '600.00', '300.00', '300.00', '', 'Approved', 'dgdfsg', '1', '2025-01-14 06:14:05', '2025-09-07 13:14:07', 'AFN');
INSERT INTO `visa_applications` (`visa_id`, `applicant_name`, `passport_number`, `nationality`, `visa_type`, `destination_country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `net_profit`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('7', 'Sameer', 'Salman', 'Afghan', 'Tourist', 'America ', '2025-01-14', '600.00', '300.00', '300.00', '', 'Pending', 'sfasdf', '1', '2025-01-14 06:14:34', '2025-01-14 06:14:34', 'AFN');
INSERT INTO `visa_applications` (`visa_id`, `applicant_name`, `passport_number`, `nationality`, `visa_type`, `destination_country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `net_profit`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('8', 'sadiqullah', '525345', 'Afghan', '', 'japan', '2025-02-02', '5000.00', '2000.00', '3000.00', '-3000.00', 'Pending', 'sfjkdsajf', '4', '2025-02-02 05:54:46', '2025-02-02 05:54:46', 'AFN');
INSERT INTO `visa_applications` (`visa_id`, `applicant_name`, `passport_number`, `nationality`, `visa_type`, `destination_country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `net_profit`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('9', 'Shirzad', '535345', 'Afghan', 'Diplomatic', 'India', '2025-02-02', '4000.00', '2000.00', '2000.00', '-2000.00', 'Pending', '0', '4', '2025-02-02 05:57:05', '2025-02-02 09:30:29', 'AFN');
INSERT INTO `visa_applications` (`visa_id`, `applicant_name`, `passport_number`, `nationality`, `visa_type`, `destination_country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `net_profit`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('10', 'baba', '3534543', 'babba', 'Business', 'Pakistan', '2025-02-02', '6000.00', '700.00', '5300.00', '-5300.00', 'Pending', 'etertr', '4', '2025-02-02 06:01:13', '2025-02-02 06:01:13', 'AFN');
INSERT INTO `visa_applications` (`visa_id`, `applicant_name`, `passport_number`, `nationality`, `visa_type`, `destination_country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `net_profit`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('11', 'Ihsanullah', 'p9876554', 'Afghan', 'Business', 'Pakistan', '2025-05-31', '6000.00', '700.00', '5300.00', '', 'Pending', 'dfjalksdf', '4', '2025-05-31 05:21:45', '2025-05-31 05:21:45', 'AFN');
INSERT INTO `visa_applications` (`visa_id`, `applicant_name`, `passport_number`, `nationality`, `visa_type`, `destination_country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `net_profit`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`) VALUES ('12', 'Safiullah', 'Khan', 'Aghan', 'Tourist', 'India', '2025-05-31', '1200.00', '200.00', '1000.00', '', 'Pending', 'sdksafjkdsf', '4', '2025-05-31 05:34:31', '2025-05-31 05:34:31', 'USD');