-- Travel ERP Database Schema
-- Run this SQL to create all tables

-- 000001 & 000014: Agencies (using the more complete version from 000014)
CREATE TABLE IF NOT EXISTS `agencies` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(255) DEFAULT NULL,
  `iata_number` varchar(255) DEFAULT NULL,
  `bsp_country` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `status` enum('active','inactive','suspended') DEFAULT 'active',
  `subscription_amount` decimal(12,2) DEFAULT 0,
  `subscription_start` date DEFAULT NULL,
  `subscription_end` date DEFAULT NULL,
  `settings` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `agencies_code_unique` (`code`),
  KEY `agencies_status_index` (`status`),
  KEY `agencies_iata_number_index` (`iata_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000002 & 000014: Roles (using more complete version from 000014)
CREATE TABLE IF NOT EXISTS `roles` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `level` enum('platform','agency') DEFAULT 'agency',
  `permissions` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000014: Permissions
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `module` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `permissions_name_unique` (`name`),
  KEY `permissions_module_index` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000014: Role Permissions (without FK constraints - will add later)
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` bigint UNSIGNED NOT NULL,
  `permission_id` bigint UNSIGNED NOT NULL,
  `can_view` tinyint(1) DEFAULT 0,
  `can_create` tinyint(1) DEFAULT 0,
  `can_edit` tinyint(1) DEFAULT 0,
  `can_delete` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_permissions_role_id_permission_id_unique` (`role_id`,`permission_id`),
  KEY `role_permissions_role_id_index` (`role_id`),
  KEY `role_permissions_permission_id_index` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000003: Users
CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `role_id` bigint UNSIGNED DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_agency_id_foreign` (`agency_id`),
  KEY `users_agency_id_index` (`agency_id`),
  KEY `users_email_index` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000004: Clients
CREATE TABLE IF NOT EXISTS `clients` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `company` varchar(255) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `type` enum('corporate','sme','individual') DEFAULT 'corporate',
  `status` enum('active','inactive','prospect') DEFAULT 'active',
  `credit_limit` decimal(12,2) DEFAULT 0,
  `total_invoiced` decimal(15,2) DEFAULT 0,
  `total_paid` decimal(15,2) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clients_agency_id_email_index` (`agency_id`,`email`),
  KEY `clients_status_index` (`status`),
  KEY `clients_agency_id_foreign` (`agency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000010: Bookings (required for invoices and expenses)
CREATE TABLE IF NOT EXISTS `bookings` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `client_id` bigint UNSIGNED DEFAULT NULL,
  `booking_reference` varchar(10) NOT NULL,
  `gds_source` enum('amadeus','sabre','travelport','apollo','manual') DEFAULT 'manual',
  `status` enum('pending','confirmed','cancelled','completed','pnr_created','fare_held','ticket_issued','ticket_voided','refunded','reissued','flown') DEFAULT 'pending',
  `booking_date` date NOT NULL,
  `total_amount` decimal(12,2) DEFAULT 0,
  `paid_amount` decimal(12,2) DEFAULT 0,
  `commission` decimal(12,2) DEFAULT 0,
  `currency` varchar(3) DEFAULT 'USD',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bookings_booking_reference_unique` (`booking_reference`),
  KEY `bookings_agency_id_booking_date_index` (`agency_id`,`booking_date`),
  KEY `bookings_gds_source_index` (`gds_source`),
  KEY `bookings_agency_id_foreign` (`agency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000005: Invoices
CREATE TABLE IF NOT EXISTS `invoices` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `client_id` bigint UNSIGNED NOT NULL,
  `booking_id` bigint UNSIGNED DEFAULT NULL,
  `invoice_number` varchar(255) NOT NULL,
  `subtotal` decimal(12,2) DEFAULT 0,
  `tax_amount` decimal(12,2) DEFAULT 0,
  `total` decimal(12,2) DEFAULT 0,
  `paid_amount` decimal(12,2) DEFAULT 0,
  `currency` varchar(3) DEFAULT 'USD',
  `status` enum('draft','pending','paid','overdue','cancelled','refunded') DEFAULT 'draft',
  `issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `paid_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `terms` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoices_invoice_number_unique` (`invoice_number`),
  KEY `invoices_agency_id_status_index` (`agency_id`,`status`),
  KEY `invoices_due_date_index` (`due_date`),
  KEY `invoices_agency_id_foreign` (`agency_id`),
  KEY `invoices_client_id_foreign` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000006: Expenses
CREATE TABLE IF NOT EXISTS `expenses` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `booking_id` bigint UNSIGNED DEFAULT NULL,
  `expense_number` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `vendor` varchar(255) DEFAULT NULL,
  `expense_date` date NOT NULL,
  `status` enum('pending','approved','rejected','reimbursed') DEFAULT 'pending',
  `currency` varchar(3) DEFAULT 'USD',
  `receipt_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `expenses_expense_number_unique` (`expense_number`),
  KEY `expenses_agency_id_status_index` (`agency_id`,`status`),
  KEY `expenses_expense_date_index` (`expense_date`),
  KEY `expenses_agency_id_foreign` (`agency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000006b: Vendor Bills (Accounts Payable)
CREATE TABLE IF NOT EXISTS `vendor_bills` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `supplier_id` bigint UNSIGNED DEFAULT NULL,
  `booking_id` bigint UNSIGNED DEFAULT NULL,
  `bill_number` varchar(255) NOT NULL,
  `bill_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('pending','partial','paid','overdue','cancelled') DEFAULT 'pending',
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0,
  `paid_amount` decimal(12,2) NOT NULL DEFAULT 0,
  `currency` varchar(3) DEFAULT 'USD',
  `description` text,
  `notes` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vendor_bills_bill_number_unique` (`bill_number`),
  KEY `vendor_bills_agency_id_status_index` (`agency_id`,`status`),
  KEY `vendor_bills_supplier_id_index` (`supplier_id`),
  KEY `vendor_bills_due_date_index` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000006c: Vendor Bill Items
CREATE TABLE IF NOT EXISTS `vendor_bill_items` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `bill_id` bigint UNSIGNED NOT NULL,
  `description` varchar(500) NOT NULL,
  `quantity` decimal(10,2) NOT NULL DEFAULT 1,
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0,
  `tax_rate` decimal(5,2) DEFAULT 0,
  `tax_amount` decimal(12,2) DEFAULT 0,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0,
  `booking_id` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `vendor_bill_items_bill_id_index` (`bill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000006d: Credit/Debit Notes
CREATE TABLE IF NOT EXISTS `credit_debit_notes` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `type` enum('credit','debit') NOT NULL,
  `note_number` varchar(255) NOT NULL,
  `reference_type` enum('invoice','bill','booking') DEFAULT NULL,
  `reference_id` bigint UNSIGNED DEFAULT NULL,
  `client_id` bigint UNSIGNED DEFAULT NULL,
  `supplier_id` bigint UNSIGNED DEFAULT NULL,
  `issue_date` date NOT NULL,
  `status` enum('draft','applied','cancelled') DEFAULT 'draft',
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0,
  `currency` varchar(3) DEFAULT 'USD',
  `reason` text,
  `notes` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `credit_debit_notes_note_number_unique` (`note_number`),
  KEY `credit_debit_notes_agency_id_index` (`agency_id`),
  KEY `credit_debit_notes_type_index` (`type`),
  KEY `credit_debit_notes_reference_index` (`reference_type`,`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000007: Bank Accounts
CREATE TABLE IF NOT EXISTS `bank_accounts` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `bank_name` varchar(255) NOT NULL,
  `account_number` varchar(255) NOT NULL,
  `account_name` varchar(255) DEFAULT NULL,
  `type` enum('checking','savings','trust','credit') DEFAULT 'checking',
  `currency` varchar(3) DEFAULT 'USD',
  `balance` decimal(15,2) DEFAULT 0,
  `opening_balance` decimal(15,2) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bank_accounts_agency_id_is_active_index` (`agency_id`,`is_active`),
  KEY `bank_accounts_agency_id_foreign` (`agency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000007: Transactions
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `bank_account_id` bigint UNSIGNED NOT NULL,
  `invoice_id` bigint UNSIGNED DEFAULT NULL,
  `expense_id` bigint UNSIGNED DEFAULT NULL,
  `reference` varchar(255) DEFAULT NULL,
  `type` enum('debit','credit') NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `balance_after` decimal(12,2) DEFAULT NULL,
  `transaction_date` date NOT NULL,
  `description` text DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `status` enum('pending','completed','reconciled','flagged') DEFAULT 'completed',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `transactions_agency_id_transaction_date_index` (`agency_id`,`transaction_date`),
  KEY `transactions_bank_account_id_index` (`bank_account_id`),
  KEY `transactions_agency_id_foreign` (`agency_id`),
  KEY `transactions_bank_account_id_foreign` (`bank_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000008: Vendors
CREATE TABLE IF NOT EXISTS `vendors` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `vendors_agency_id_name_index` (`agency_id`,`name`),
  KEY `vendors_agency_id_foreign` (`agency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000008: Bills
CREATE TABLE IF NOT EXISTS `bills` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `vendor_id` bigint UNSIGNED DEFAULT NULL,
  `bill_number` varchar(255) NOT NULL,
  `vendor` varchar(255) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `paid_amount` decimal(12,2) DEFAULT 0,
  `currency` varchar(3) DEFAULT 'USD',
  `issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `paid_date` date DEFAULT NULL,
  `status` enum('pending','paid','overdue','cancelled') DEFAULT 'pending',
  `priority` enum('low','medium','high') DEFAULT 'medium',
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bills_bill_number_unique` (`bill_number`),
  KEY `bills_agency_id_status_index` (`agency_id`,`status`),
  KEY `bills_due_date_index` (`due_date`),
  KEY `bills_agency_id_foreign` (`agency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000009: CRM Contacts
CREATE TABLE IF NOT EXISTS `crm_contacts` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  `position` varchar(255) DEFAULT NULL,
  `type` enum('corporate','sme','individual') DEFAULT 'individual',
  `status` enum('lead','qualified','negotiation','won','lost') DEFAULT 'lead',
  `potential_value` decimal(12,2) DEFAULT 0,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `crm_contacts_agency_id_status_index` (`agency_id`,`status`),
  KEY `crm_contacts_agency_id_foreign` (`agency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000009: CRM Activities
CREATE TABLE IF NOT EXISTS `crm_activities` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `contact_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED NOT NULL,
  `type` varchar(255) NOT NULL,
  `notes` text NOT NULL,
  `due_date` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `is_completed` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `crm_activities_contact_id_index` (`contact_id`),
  KEY `crm_activities_contact_id_foreign` (`contact_id`),
  KEY `crm_activities_user_id_foreign` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000009: CRM Deals
CREATE TABLE IF NOT EXISTS `crm_deals` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `agency_id` bigint UNSIGNED NOT NULL,
  `contact_id` bigint UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `value` decimal(12,2) DEFAULT 0,
  `stage` enum('prospecting','qualification','proposal','negotiation','closed_won','closed_lost') DEFAULT 'prospecting',
  `expected_close_date` date DEFAULT NULL,
  `actual_close_date` date DEFAULT NULL,
  `assigned_to` bigint UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `crm_deals_agency_id_stage_index` (`agency_id`,`stage`),
  KEY `crm_deals_agency_id_foreign` (`agency_id`),
  KEY `crm_deals_contact_id_foreign` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000010: Passengers
CREATE TABLE IF NOT EXISTS `passengers` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_id` bigint UNSIGNED NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `passport_number` varchar(255) DEFAULT NULL,
  `passport_expiry` varchar(255) DEFAULT NULL,
  `nationality` varchar(255) DEFAULT NULL,
  `ticket_number` varchar(255) DEFAULT NULL,
  `type` enum('adult','child','infant') DEFAULT 'adult',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `passengers_booking_id_index` (`booking_id`),
  KEY `passengers_ticket_number_index` (`ticket_number`),
  KEY `passengers_booking_id_foreign` (`booking_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000010: Segments
CREATE TABLE IF NOT EXISTS `segments` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_id` bigint UNSIGNED NOT NULL,
  `airline` varchar(3) NOT NULL,
  `flight_number` varchar(10) NOT NULL,
  `origin` varchar(3) NOT NULL,
  `destination` varchar(3) NOT NULL,
  `departure_date` datetime NOT NULL,
  `arrival_date` datetime DEFAULT NULL,
  `class` varchar(10) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `segment_number` int DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `segments_booking_id_index` (`booking_id`),
  KEY `segments_airline_flight_number_index` (`airline`,`flight_number`),
  KEY `segments_booking_id_foreign` (`booking_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000010: Booking Services
CREATE TABLE IF NOT EXISTS `booking_services` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_id` bigint UNSIGNED NOT NULL,
  `passenger_id` bigint UNSIGNED DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT 0,
  `currency` varchar(3) DEFAULT 'USD',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `booking_services_booking_id_index` (`booking_id`),
  KEY `booking_services_booking_id_foreign` (`booking_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 000014: Activity Logs
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `agency_id` bigint UNSIGNED DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `module` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `old_values` json DEFAULT NULL,
  `new_values` json DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `activity_logs_user_id_created_at_index` (`user_id`,`created_at`),
  KEY `activity_logs_agency_id_created_at_index` (`agency_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add foreign key constraints for role_permissions
ALTER TABLE `role_permissions`
  ADD CONSTRAINT `role_permissions_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `role_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE;

-- Add foreign key constraints for users
ALTER TABLE `users`
  ADD CONSTRAINT `users_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `users_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE SET NULL;

-- Add foreign key constraints for clients
ALTER TABLE `clients`
  ADD CONSTRAINT `clients_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE;

-- Add foreign key constraints for bookings
ALTER TABLE `bookings`
  ADD CONSTRAINT `bookings_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `bookings_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE SET NULL;

-- Add foreign key constraints for invoices
ALTER TABLE `invoices`
  ADD CONSTRAINT `invoices_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `invoices_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `invoices_booking_id_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE SET NULL;

-- Add foreign key constraints for expenses
ALTER TABLE `expenses`
  ADD CONSTRAINT `expenses_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `expenses_booking_id_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE SET NULL;

-- Add foreign key constraints for bank_accounts
ALTER TABLE `bank_accounts`
  ADD CONSTRAINT `bank_accounts_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE;

-- Add foreign key constraints for transactions
ALTER TABLE `transactions`
  ADD CONSTRAINT `transactions_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `transactions_bank_account_id_foreign` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `transactions_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `transactions_expense_id_foreign` FOREIGN KEY (`expense_id`) REFERENCES `expenses`(`id`) ON DELETE SET NULL;

-- Add foreign key constraints for vendors
ALTER TABLE `vendors`
  ADD CONSTRAINT `vendors_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE;

-- Add foreign key constraints for bills
ALTER TABLE `bills`
  ADD CONSTRAINT `bills_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `bills_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors`(`id`) ON DELETE SET NULL;

-- Add foreign key constraints for crm_contacts
ALTER TABLE `crm_contacts`
  ADD CONSTRAINT `crm_contacts_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `crm_contacts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL;

-- Add foreign key constraints for crm_activities
ALTER TABLE `crm_activities`
  ADD CONSTRAINT `crm_activities_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `crm_activities_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE;

-- Add foreign key constraints for crm_deals
ALTER TABLE `crm_deals`
  ADD CONSTRAINT `crm_deals_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `crm_deals_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `crm_deals_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL;

-- Add foreign key constraints for passengers
ALTER TABLE `passengers`
  ADD CONSTRAINT `passengers_booking_id_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE CASCADE;

-- Add foreign key constraints for segments
ALTER TABLE `segments`
  ADD CONSTRAINT `segments_booking_id_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE CASCADE;

-- Add foreign key constraints for booking_services
ALTER TABLE `booking_services`
  ADD CONSTRAINT `booking_services_booking_id_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `booking_services_passenger_id_foreign` FOREIGN KEY (`passenger_id`) REFERENCES `passengers`(`id`) ON DELETE SET NULL;

-- Add foreign key constraints for activity_logs
ALTER TABLE `activity_logs`
  ADD CONSTRAINT `activity_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `activity_logs_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies`(`id`) ON DELETE CASCADE;