DROP TABLE IF EXISTS `agents`;
CREATE TABLE `agents` (
  `agent_id` int(11) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) NOT NULL,
  `father_name` varchar(255) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT 'Male',
  `national_id` varchar(50) DEFAULT NULL,
  `jawaz_number` varchar(50) DEFAULT NULL,
  `shirkat_name` varchar(255) DEFAULT NULL,
  `contact_number1` varchar(20) DEFAULT NULL,
  `contact_number2` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`agent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `agents` (`agent_id`, `full_name`, `father_name`, `dob`, `gender`, `national_id`, `jawaz_number`, `shirkat_name`, `contact_number1`, `contact_number2`, `email`, `address`, `image`, `status`, `created_at`, `updated_at`) VALUES ('1', 'Ahmad Khan', 'Mohammad Khan', '1990-05-10', 'Male', '543534534', 'JAW12345', 'Kabul Travels', '0783342345', '0798765432', 'ahmad.khan@gmail.com', 'Kabul, Afghanistan', 'uploads/agent_1767551490.jpg', 'Active', '2026-01-04 21:04:22', '2026-01-04 23:01:30');
INSERT INTO `agents` (`agent_id`, `full_name`, `father_name`, `dob`, `gender`, `national_id`, `jawaz_number`, `shirkat_name`, `contact_number1`, `contact_number2`, `email`, `address`, `image`, `status`, `created_at`, `updated_at`) VALUES ('2', 'Samiullah', 'Abdul Rahim', '1992-08-15', 'Male', '987654321', 'JAW98765', 'Herat Travels', '0783345678', '0798765433', 'samiullah@gmail.com', 'Kandahar, Afghanistan', 'uploads/agent_1767551498.PNG', 'Active', '2026-01-04 21:04:22', '2026-01-04 23:01:38');
INSERT INTO `agents` (`agent_id`, `full_name`, `father_name`, `dob`, `gender`, `national_id`, `jawaz_number`, `shirkat_name`, `contact_number1`, `contact_number2`, `email`, `address`, `image`, `status`, `created_at`, `updated_at`) VALUES ('3', 'Safiullah Khan', 'Mohammad Khan', '1990-05-10', 'Male', '987654321', 'JAW12345', 'Safiullah', '0783332428', '0783423424', 'safiullahfekarmand@gmail.com', 'Jalalabad, Nangarhar Afghanistan', 'uploads/agent_1767551536.jpg', 'Active', '2026-01-04 23:02:09', '2026-01-04 23:02:16');

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,
  `paid` decimal(12,2) DEFAULT 0.00,
  `balance` decimal(12,2) DEFAULT 0.00,
  `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(),
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`cargo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `cargo` (`cargo_id`, `sender_name`, `sender_contact`, `receiver_name`, `receiver_contact`, `pickup_location`, `destination`, `cargo_type`, `weight`, `cost`, `paid`, `balance`, `currency`, `status`, `departure_date`, `arrival_date`, `created_at`, `updated_at`, `created_by`, `updated_by`) VALUES ('1', 'Ahmad jan', '07833423', 'baba jan', '07989898', 'Nangarhar', 'Germany', 'Clothes', '77.00', '6000.00', '300.00', '5700.00', 'USD', 'Cancelled', '2025-12-23', '2025-12-19', '2025-12-17 13:05:57', '2025-12-17 09:40:08', '', '4');

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 DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `cv_applications`;
CREATE TABLE `cv_applications` (
  `cv_id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_name` varchar(255) NOT NULL,
  `contact_number` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `education_level` varchar(100) NOT NULL,
  `experience_years` int(2) DEFAULT 0,
  `job_applied_for` varchar(100) NOT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'AFG',
  `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','Approved','Rejected','Cancelled') NOT NULL DEFAULT 'Pending',
  `remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`cv_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `cv_applications_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `cv_applications` (`cv_id`, `applicant_name`, `contact_number`, `email`, `education_level`, `experience_years`, `job_applied_for`, `photo`, `currency`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_by`) VALUES ('1', 'Saleem', '0783332428', 'safiullahfekarmand@gmail.com', 'High School', '11', 'Developer', '', 'AFG', '3000.00', '900.00', '2100.00', 'Cancelled', 'vdfg', '4', '2025-12-17 12:55:42', '4');

DROP TABLE IF EXISTS `dv_lottery_applications`;
CREATE TABLE `dv_lottery_applications` (
  `dv_id` int(11) NOT NULL AUTO_INCREMENT,
  `dv_number` varchar(50) NOT NULL,
  `applicant_name` varchar(255) NOT NULL,
  `birth_date` date NOT NULL,
  `passport_number` varchar(50) NOT NULL,
  `country` varchar(50) NOT NULL,
  `entry_year` year(4) NOT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'AFG',
  `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','Approved','Rejected','Cancelled') DEFAULT 'Pending',
  `remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`dv_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `dv_lottery_applications_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `dv_lottery_applications` (`dv_id`, `dv_number`, `applicant_name`, `birth_date`, `passport_number`, `country`, `entry_year`, `photo`, `currency`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_by`) VALUES ('1', '8978', 'Saleem', '2025-12-08', 'p9876554', 'Afghanistan', '2025', '', 'AFG', '700.00', '80.00', '620.00', 'Cancelled', 'sjfsldkjf', '4', '2025-12-15 09:54:01', '4');

DROP TABLE IF EXISTS `external_tickets`;
CREATE TABLE `external_tickets` (
  `ticket_id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by` int(11) NOT NULL,
  `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,
  `family_type` enum('Single','Family') DEFAULT 'Single',
  `total_members` int(11) DEFAULT NULL,
  `num_adults` int(11) DEFAULT NULL,
  `num_infants` int(11) DEFAULT NULL,
  `ticket_currency` varchar(3) NOT NULL DEFAULT 'AFN',
  PRIMARY KEY (`ticket_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

INSERT INTO `external_tickets` (`ticket_id`, `created_by`, `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`, `family_type`, `total_members`, `num_adults`, `num_infants`, `ticket_currency`) VALUES ('5', '0', 'Salam kahn', 'fsdf', 'Afghanistan', 'Afghanistan', '2025-12-17', '2025-12-26', 'Round Trip', '0.00', '0.00', '0.00', '', 'Cancelled', '2025-12-17 14:09:25', '2025-12-17 14:09:38', 'zczsc', 'vgdfg', 'sdfsa', 'Single', '0', '0', '0', 'AFN');

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 DEFAULT CHARSET=utf8mb4;


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,
  `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(),
  `updated_by` int(11) DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'AFN',
  `total_balance` decimal(10,2) GENERATED ALWAYS AS (`ticket_price` - `total_paid`) STORED,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`ticket_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 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`, `payment_status`, `ticket_status`, `created_at`, `updated_at`, `updated_by`, `remarks`, `currency`, `total_balance`, `created_by`) VALUES ('1', 'Salam ali kahn', '0783332428', 'Khan jan', 'safsdf', '2025-12-24', '2025-12-26', '0', '700.00', '80.00', 'Unpaid', 'Cancelled', '2025-12-17 13:10:53', '2025-12-17 09:44:16', '4', 'xvxdf', 'AFN', '620.00', '4');

DROP TABLE IF EXISTS `job_applications`;
CREATE TABLE `job_applications` (
  `job_id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_name` varchar(255) NOT NULL,
  `contact_number` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `education_level` varchar(100) NOT NULL,
  `position_applied` varchar(100) NOT NULL,
  `experience_years` int(2) DEFAULT 0,
  `skills` text DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `currency` enum('AFG','USD') DEFAULT 'AFG',
  `total_fee` decimal(10,2) DEFAULT 0.00,
  `paid_fee` decimal(10,2) DEFAULT 0.00,
  `balance_fee` decimal(10,2) GENERATED ALWAYS AS (`total_fee` - `paid_fee`) STORED,
  `status` enum('Pending','Approved','Rejected','Cancelled') DEFAULT 'Pending',
  `remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`job_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `job_applications_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `job_applications` (`job_id`, `applicant_name`, `contact_number`, `email`, `education_level`, `position_applied`, `experience_years`, `skills`, `photo`, `currency`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_by`) VALUES ('1', 'Saleem Khan jna', '0783332428', 'safiullahfekarmand@gmail.com', 'High School', 'Developer', '0', 'JavaScript', '', 'AFG', '3000.00', '900.00', '2100.00', 'Cancelled', 'dsfds', '4', '2025-12-17 12:58:32', '4');

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 DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT 'User who did the action',
  `message` text DEFAULT NULL,
  `table_name` varchar(100) NOT NULL COMMENT 'Table on which action was done',
  `record_id` int(11) DEFAULT NULL COMMENT 'ID of affected record',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `status` enum('unread','read') NOT NULL DEFAULT 'unread',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4;

INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('1', '4', 'User sadiq inserted a new DV Lottery application for Applicant: Saleem', 'dv_lottery_applications', '1', '2025-12-15 09:54:01', 'read');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('2', '4', 'User sadiq added visa application: Saleem khan khan', 'visa_applications', '1', '2025-12-15 10:18:59', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('3', '4', 'Visa application updated (Status: Pending) - Saleem khan khan', 'visa_applications', '1', '2025-12-17 12:01:49', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('4', '4', 'Visa application updated (Status: Cancelled) - Saleem khan khan', 'visa_applications', '1', '2025-12-17 12:01:49', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('5', '4', 'User sadiq created Scholarship application for Saleem baba', 'scholarship_applications', '1', '2025-12-17 12:02:16', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('6', '4', 'User sadiq updated Scholarship application for Saleem baba', 'scholarship_applications', '1', '2025-12-17 12:02:21', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('7', '4', 'User sadiq updated the DV Lottery application for Applicant: Saleem', 'dv_lottery_applications', '1', '2025-12-17 12:05:08', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('8', '4', 'User sadiq added SIV application: Ihsanullah Khan', 'siv_applications', '1', '2025-12-17 12:50:53', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('9', '4', 'SIV Application updated (Status: Cancelled) - Ihsanullah Khan', 'siv_applications', '1', '2025-12-17 12:55:10', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('10', '4', 'User sadiq added a CV application: Saleem', 'cv_applications', '1', '2025-12-17 12:55:42', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('11', '4', 'User sadiq updated CV application: Saleem', 'cv_applications', '1', '2025-12-17 12:55:48', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('12', '4', 'User sadiq updated CV application: Saleem', 'cv_applications', '1', '2025-12-17 12:56:45', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('13', '4', 'CV Application updated (Status: Cancelled) - Saleem', 'cv_applications', '1', '2025-12-17 12:57:55', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('14', '4', 'User sadiq inserted a new Job application for Applicant: Saleem Khan jna', 'job_applications', '1', '2025-12-17 12:58:32', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('15', '4', 'Job Application updated (Status: Cancelled) - Saleem Khan jna', 'job_applications', '1', '2025-12-17 12:59:45', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('16', '4', 'Scholarship Application updated (Status: Cancelled) - Saleem baba', 'scholarship_applications', '1', '2025-12-17 13:01:32', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('17', '4', 'Scholarship Application updated (Status: Approved) - Saleem baba', 'scholarship_applications', '1', '2025-12-17 13:01:50', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('18', '4', 'User sadiq submitted Passport Application: Ihsanullah jan', 'passport_applications', '1', '2025-12-17 13:02:23', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('19', '4', 'Passport Application updated (Status: Cancelled) - Ihsanullah jan', 'passport_applications', '1', '2025-12-17 13:03:19', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('20', '4', 'User sadiq inserted a new Umrah application for Applicant: Ihsanullah jan', 'umrah_applications', '1', '2025-12-17 13:03:41', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('21', '4', 'Umrah Application updated (Status: Cancelled) - Ihsanullah jan', 'umrah_applications', '1', '2025-12-17 13:04:38', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('22', '0', 'New cargo added: Ahmad jan -> baba jan', 'cargo', '1', '2025-12-17 13:05:57', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('23', '4', 'Cargo record updated (Status: Cancelled) - Sender: Ahmad jan, Receiver: baba jan', 'cargo', '1', '2025-12-17 13:10:08', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('24', '4', 'User sadiq inserted a new Internal Ticket for Passenger: Salam ali kahn', 'internal_tickets', '1', '2025-12-17 13:10:53', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('25', '4', 'Internal Ticket updated (Status: Cancelled) - Ticket ID: 1, Passenger: Salam ali kahn', 'internal_tickets', '1', '2025-12-17 13:14:16', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('26', '0', '', 'external_tickets', '5', '2025-12-17 14:09:25', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('27', '0', 'External Ticket updated (Status: Cancelled) - Ticket ID: 5, Passenger: Salam kahn', 'external_tickets', '5', '2025-12-17 14:09:38', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('28', '4', 'User sadiq added visa application: Samiullah', 'visa_applications', '2', '2026-02-03 11:28:30', 'unread');
INSERT INTO `notifications` (`id`, `user_id`, `message`, `table_name`, `record_id`, `created_at`, `status`) VALUES ('29', '4', 'Visa application updated (Status: Approved) - Samiullah', 'visa_applications', '2', '2026-02-03 11:29:10', 'unread');

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,
  `currency` varchar(10) DEFAULT 'AFN',
  `expense_date` date NOT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_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 DEFAULT CHARSET=utf8mb4;


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,
  `phone_number1` varchar(20) DEFAULT NULL,
  `phone_number2` varchar(20) DEFAULT 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','Approved','Rejected','Cancelled') NOT NULL 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,
  `updated_by` int(11) 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=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `passport_applications` (`passport_id`, `applicant_name`, `national_id_number`, `phone_number1`, `phone_number2`, `date_of_birth`, `application_type`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `biometric_date`, `currency`, `updated_by`) VALUES ('1', 'Ihsanullah jan', '56546465', '0783332428', '0783332428', '2025-12-26', 'Ordinary', '0000-00-00', '8000.00', '88.00', '7912.00', 'Cancelled', 'kjsksldjf', '4', '2025-12-17 09:32:23', '2025-12-17 09:33:19', '2025-12-27', 'AFN', '4');

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','Approved','Rejected','Cancelled') NOT NULL,
  `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',
  `updated_by` int(11) DEFAULT NULL,
  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=2 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`, `updated_by`) VALUES ('1', 'Saleem baba', '0783332428', 'safiullahfekarmand@gmail.com', 'Need-Based', 'Medicine', 'Afghanistan', '0000-00-00', '3000.00', '900.00', '2100.00', 'Approved', 'vfxdfdsf', '4', '2025-12-17 08:32:16', '2025-12-17 09:31:50', 'AFN', '4');

DROP TABLE IF EXISTS `services`;
CREATE TABLE `services` (
  `service_id` int(11) NOT NULL AUTO_INCREMENT,
  `agent_id` int(11) NOT NULL,
  `service_name` varchar(255) NOT NULL,
  `total_fee` decimal(12,2) NOT NULL,
  `paid_fee` decimal(12,2) DEFAULT 0.00,
  `balance_fee` decimal(12,2) GENERATED ALWAYS AS (`total_fee` - `paid_fee`) STORED,
  `currency` varchar(10) DEFAULT 'AFN',
  `status` enum('Pending','Approved','Cancelled') DEFAULT 'Pending',
  `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 (`service_id`),
  KEY `agent_id` (`agent_id`),
  CONSTRAINT `services_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`agent_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

INSERT INTO `services` (`service_id`, `agent_id`, `service_name`, `total_fee`, `paid_fee`, `balance_fee`, `currency`, `status`, `remarks`, `created_at`, `updated_at`) VALUES ('1', '1', 'Visa Processing', '1200.00', '500.00', '700.00', 'USD', 'Pending', 'Urgent processing', '2026-01-04 21:08:04', '2026-01-04 21:08:04');
INSERT INTO `services` (`service_id`, `agent_id`, `service_name`, `total_fee`, `paid_fee`, `balance_fee`, `currency`, `status`, `remarks`, `created_at`, `updated_at`) VALUES ('2', '2', 'Flight Booking', '800.00', '800.00', '0.00', 'AFG', 'Approved', 'Confirmed ticket', '2026-01-04 21:08:04', '2026-01-04 21:08:04');
INSERT INTO `services` (`service_id`, `agent_id`, `service_name`, `total_fee`, `paid_fee`, `balance_fee`, `currency`, `status`, `remarks`, `created_at`, `updated_at`) VALUES ('3', '1', 'Umrah Package', '3000.00', '1000.00', '2000.00', 'USD', 'Cancelled', 'Family package', '2026-01-04 21:08:04', '2026-01-04 21:08:04');
INSERT INTO `services` (`service_id`, `agent_id`, `service_name`, `total_fee`, `paid_fee`, `balance_fee`, `currency`, `status`, `remarks`, `created_at`, `updated_at`) VALUES ('4', '1', 'External Ticket', '3000.00', '2000.00', '1000.00', '0', 'Approved', 'kjksjfksldf', '2026-01-04 22:28:17', '2026-01-04 22:28:17');
INSERT INTO `services` (`service_id`, `agent_id`, `service_name`, `total_fee`, `paid_fee`, `balance_fee`, `currency`, `status`, `remarks`, `created_at`, `updated_at`) VALUES ('5', '1', 'External Ticket', '20000.00', '2000.00', '18000.00', '0', 'Approved', 'MSDKDFJSKDLF', '2026-01-04 22:29:24', '2026-01-04 22:29:24');
INSERT INTO `services` (`service_id`, `agent_id`, `service_name`, `total_fee`, `paid_fee`, `balance_fee`, `currency`, `status`, `remarks`, `created_at`, `updated_at`) VALUES ('6', '1', 'Umra', '9000.00', '2000.00', '7000.00', 'AFN', 'Approved', 'klljfksdjf', '2026-01-04 22:39:06', '2026-01-04 22:39:06');

DROP TABLE IF EXISTS `siv_applications`;
CREATE TABLE `siv_applications` (
  `siv_id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_name` varchar(255) NOT NULL,
  `birth_date` date NOT NULL,
  `passport_number` varchar(50) NOT NULL,
  `sponsor_name` varchar(255) NOT NULL,
  `sponsor_contact` varchar(50) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'AFG',
  `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','Approved','Rejected','Cancelled') DEFAULT 'Pending',
  `remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`siv_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `siv_applications_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `siv_applications` (`siv_id`, `applicant_name`, `birth_date`, `passport_number`, `sponsor_name`, `sponsor_contact`, `photo`, `currency`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_by`) VALUES ('1', 'Ihsanullah Khan', '2025-12-18', '234324234fdsaf', 'gdfsg', '532544', '', '0', '90000.00', '4000.00', '86000.00', 'Cancelled', 'hjh', '4', '2025-12-17 12:50:53', '4');

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 DEFAULT CHARSET=utf8mb4;


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,
  `application_type` enum('One Person','Family') NOT NULL DEFAULT 'One Person',
  `total_members` int(11) DEFAULT NULL,
  `num_adults` int(11) DEFAULT NULL,
  `num_infants` int(11) DEFAULT NULL,
  `other_details` text 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','Approved','Rejected','Cancelled') NOT NULL 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',
  `updated_by` int(11) DEFAULT NULL,
  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=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `umrah_applications` (`umrah_id`, `applicant_name`, `passport_number`, `contact_number`, `package_type`, `application_type`, `total_members`, `num_adults`, `num_infants`, `other_details`, `departure_date`, `return_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `days`, `currency`, `updated_by`) VALUES ('1', 'Ihsanullah jan', 'p9876554', '0783332428', 'Premium', 'One Person', '0', '0', '0', '', '2025-12-22', '2025-12-18', '900.00', '200.00', '700.00', 'Cancelled', '', '4', '2025-12-17 13:03:41', '2025-12-17 09:34:38', '21', 'USD', '4');

DROP TABLE IF EXISTS `user_permissions`;
CREATE TABLE `user_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `permission` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;

INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('1', '5', 'sidebar_visa');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('2', '5', 'sidebar_scholarship');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('3', '5', 'sidebar_passport');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('4', '5', 'sidebar_umrah');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('5', '5', 'sidebar_cargo');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('6', '5', 'sidebar_internal_ticket');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('7', '5', 'sidebar_external_ticket');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('8', '5', 'sidebar_expense');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('9', '5', 'sidebar_reports');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('10', '5', 'visa_view');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('11', '5', 'scholarship_view');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('12', '5', 'passport_view');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('13', '5', 'umrah_view');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('14', '5', 'cargo_view');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('15', '5', 'internal_ticket_view');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('16', '5', 'external_ticket_view');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('17', '5', 'dv_section');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('18', '5', 'cv_section');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('19', '5', 'job_section');
INSERT INTO `user_permissions` (`id`, `user_id`, `permission`) VALUES ('20', '5', 'siv_section');

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', 'Staff', '0783332428', '2024-12-31 10:35:01', '2025-09-20 17:22:18');
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');

DROP TABLE IF EXISTS `visa_applications`;
CREATE TABLE `visa_applications` (
  `visa_id` int(11) NOT NULL AUTO_INCREMENT,
  `applicant_name` varchar(100) NOT NULL,
  `phone_required` varchar(20) NOT NULL,
  `phone_optional` varchar(20) DEFAULT NULL,
  `passport_number` varchar(50) NOT NULL,
  `nationality` varchar(50) NOT NULL,
  `visa_type` varchar(255) DEFAULT NULL,
  `tracking_id` varchar(100) NOT 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,
  `status` enum('Pending','Approved','Rejected','Cancelled') NOT NULL,
  `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',
  `updated_by` int(11) DEFAULT NULL,
  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=3 DEFAULT CHARSET=utf8mb4;

INSERT INTO `visa_applications` (`visa_id`, `applicant_name`, `phone_required`, `phone_optional`, `passport_number`, `nationality`, `visa_type`, `tracking_id`, `destination_country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`, `updated_by`) VALUES ('1', 'Saleem khan khan', '0783332428', '1200', 'p9876554', 'Pakistan', '', '23434', 'Russia', '2025-12-17', '3000.00', '900.00', '2100.00', 'Cancelled', 'sfjakldjfalkjf', '4', '2025-12-15 06:48:59', '2025-12-17 12:01:49', 'AFN', '4');
INSERT INTO `visa_applications` (`visa_id`, `applicant_name`, `phone_required`, `phone_optional`, `passport_number`, `nationality`, `visa_type`, `tracking_id`, `destination_country`, `application_date`, `total_fee`, `paid_fee`, `balance_fee`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`, `currency`, `updated_by`) VALUES ('2', 'Samiullah', '0783332428', '0353452345', 'p9876554', 'Afghanistan', 'Tourist', '8676', 'Pakistan', '0000-00-00', '10000.00', '10000.00', '0.00', 'Approved', 'fdsfasdfsdfs', '4', '2026-02-03 07:58:30', '2026-02-03 07:59:10', 'AFN', '');