-- ===================================================================== -- TIMOPDF - Tourism Proposal PDF System -- Database schema (MySQL 5.7+/MariaDB 10.3+) -- ===================================================================== SET FOREIGN_KEY_CHECKS = 0; SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Users -- --------------------------------------------------------------------- DROP TABLE IF EXISTS users; CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, role ENUM('admin','editor') NOT NULL DEFAULT 'admin', avatar VARCHAR(255) NULL, status TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Companies (single-row settings table) -- --------------------------------------------------------------------- DROP TABLE IF EXISTS companies; CREATE TABLE companies ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name_en VARCHAR(190) NOT NULL, name_ar VARCHAR(190) NULL, logo VARCHAR(255) NULL, email VARCHAR(190) NULL, phone VARCHAR(80) NULL, address_en TEXT NULL, address_ar TEXT NULL, website VARCHAR(190) NULL, default_header_en VARCHAR(255) NULL, default_header_ar VARCHAR(255) NULL, default_subheading_en VARCHAR(255) NULL, default_subheading_ar VARCHAR(255) NULL, brand_primary VARCHAR(20) NOT NULL DEFAULT '#0B2A6B', brand_secondary VARCHAR(20) NOT NULL DEFAULT '#1E4FD8', brand_accent VARCHAR(20) NOT NULL DEFAULT '#C9A55C', pdf_theme VARCHAR(50) NOT NULL DEFAULT 'royal-blue', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Currencies -- --------------------------------------------------------------------- DROP TABLE IF EXISTS currencies; CREATE TABLE currencies ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(8) NOT NULL UNIQUE, name_en VARCHAR(80) NOT NULL, name_ar VARCHAR(80) NULL, symbol VARCHAR(10) NULL, status TINYINT(1) NOT NULL DEFAULT 1, sort_order INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Cities -- --------------------------------------------------------------------- DROP TABLE IF EXISTS cities; CREATE TABLE cities ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name_en VARCHAR(150) NOT NULL, name_ar VARCHAR(150) NULL, status TINYINT(1) NOT NULL DEFAULT 1, sort_order INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Hotels -- --------------------------------------------------------------------- DROP TABLE IF EXISTS hotels; CREATE TABLE hotels ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, city_id INT UNSIGNED NOT NULL, name_en VARCHAR(190) NOT NULL, name_ar VARCHAR(190) NULL, address_en TEXT NULL, address_ar TEXT NULL, website VARCHAR(190) NULL, stars TINYINT UNSIGNED NULL DEFAULT NULL, image VARCHAR(255) NULL, status TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_hotels_city (city_id), CONSTRAINT fk_hotels_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Places / Attractions -- --------------------------------------------------------------------- DROP TABLE IF EXISTS places; CREATE TABLE places ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, city_id INT UNSIGNED NOT NULL, name_en VARCHAR(190) NOT NULL, name_ar VARCHAR(190) NULL, description_en TEXT NULL, description_ar TEXT NULL, image VARCHAR(255) NULL, status TINYINT(1) NOT NULL DEFAULT 1, sort_order INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_places_city (city_id), CONSTRAINT fk_places_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Transportation types (master list: Flight, Bus, Train, Other) -- --------------------------------------------------------------------- DROP TABLE IF EXISTS transportation_types; CREATE TABLE transportation_types ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name_en VARCHAR(80) NOT NULL, name_ar VARCHAR(80) NULL, icon VARCHAR(50) NULL, status TINYINT(1) NOT NULL DEFAULT 1, sort_order INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Inclusions master list -- --------------------------------------------------------------------- DROP TABLE IF EXISTS inclusions; CREATE TABLE inclusions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title_en VARCHAR(190) NOT NULL, title_ar VARCHAR(190) NULL, description_en TEXT NULL, description_ar TEXT NULL, status TINYINT(1) NOT NULL DEFAULT 1, sort_order INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Exclusions master list -- --------------------------------------------------------------------- DROP TABLE IF EXISTS exclusions; CREATE TABLE exclusions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title_en VARCHAR(190) NOT NULL, title_ar VARCHAR(190) NULL, description_en TEXT NULL, description_ar TEXT NULL, status TINYINT(1) NOT NULL DEFAULT 1, sort_order INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Cancellation policies -- --------------------------------------------------------------------- DROP TABLE IF EXISTS cancellation_policies; CREATE TABLE cancellation_policies ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title_en VARCHAR(190) NOT NULL, title_ar VARCHAR(190) NULL, description_en TEXT NULL, description_ar TEXT NULL, status TINYINT(1) NOT NULL DEFAULT 1, sort_order INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Payment methods -- --------------------------------------------------------------------- DROP TABLE IF EXISTS payment_methods; CREATE TABLE payment_methods ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title_en VARCHAR(190) NOT NULL, title_ar VARCHAR(190) NULL, description_en TEXT NULL, description_ar TEXT NULL, status TINYINT(1) NOT NULL DEFAULT 1, sort_order INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Proposals (root entity) -- --------------------------------------------------------------------- DROP TABLE IF EXISTS proposals; CREATE TABLE proposals ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, proposal_number VARCHAR(50) NOT NULL UNIQUE, proposal_type ENUM('individual','corporate') NOT NULL DEFAULT 'individual', language_mode ENUM('en','ar','bilingual') NOT NULL DEFAULT 'en', currency_id INT UNSIGNED NULL, company_id INT UNSIGNED NULL, title_en VARCHAR(255) NULL, title_ar VARCHAR(255) NULL, header_en VARCHAR(255) NULL, header_ar VARCHAR(255) NULL, subheading_en VARCHAR(255) NULL, subheading_ar VARCHAR(255) NULL, company_name_en VARCHAR(190) NULL, company_name_ar VARCHAR(190) NULL, trip_from_en VARCHAR(150) NULL, trip_from_ar VARCHAR(150) NULL, trip_to_en VARCHAR(150) NULL, trip_to_ar VARCHAR(150) NULL, days_count INT UNSIGNED NOT NULL DEFAULT 0, nights_count INT UNSIGNED NOT NULL DEFAULT 0, cover_background VARCHAR(255) NULL, total_price DECIMAL(14,2) NOT NULL DEFAULT 0, cancellation_policy_id INT UNSIGNED NULL, payment_method_id INT UNSIGNED NULL, payment_description_en TEXT NULL, payment_description_ar TEXT NULL, additional_notes_en TEXT NULL, additional_notes_ar TEXT NULL, status ENUM('draft','exported','approved','archived') NOT NULL DEFAULT 'draft', version INT UNSIGNED NOT NULL DEFAULT 1, created_by INT UNSIGNED NULL, updated_by INT UNSIGNED NULL, last_exported_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_proposals_status (status), KEY idx_proposals_currency (currency_id), CONSTRAINT fk_proposals_currency FOREIGN KEY (currency_id) REFERENCES currencies(id) ON DELETE SET NULL, CONSTRAINT fk_proposals_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL, CONSTRAINT fk_proposals_policy FOREIGN KEY (cancellation_policy_id) REFERENCES cancellation_policies(id) ON DELETE SET NULL, CONSTRAINT fk_proposals_payment FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE SET NULL, CONSTRAINT fk_proposals_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_proposals_updater FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Proposal client (1-to-1 with proposals) -- --------------------------------------------------------------------- DROP TABLE IF EXISTS proposal_clients; CREATE TABLE proposal_clients ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, proposal_id INT UNSIGNED NOT NULL, client_type ENUM('individual','corporate') NOT NULL, name VARCHAR(190) NULL, corporate_name VARCHAR(190) NULL, contact_person_name VARCHAR(190) NULL, phone VARCHAR(80) NULL, email VARCHAR(190) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_pc_proposal (proposal_id), CONSTRAINT fk_pc_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Proposal hotels by city (selected hotels grouped by city) -- --------------------------------------------------------------------- DROP TABLE IF EXISTS proposal_hotels; CREATE TABLE proposal_hotels ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, proposal_id INT UNSIGNED NOT NULL, city_id INT UNSIGNED NOT NULL, hotel_id INT UNSIGNED NOT NULL, nights INT UNSIGNED NOT NULL DEFAULT 0, sort_order INT UNSIGNED NOT NULL DEFAULT 0, KEY idx_ph_proposal (proposal_id), CONSTRAINT fk_ph_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE, CONSTRAINT fk_ph_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE, CONSTRAINT fk_ph_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Proposal days (itinerary) -- --------------------------------------------------------------------- DROP TABLE IF EXISTS proposal_days; CREATE TABLE proposal_days ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, proposal_id INT UNSIGNED NOT NULL, day_number INT UNSIGNED NOT NULL, city_id INT UNSIGNED NULL, title_en VARCHAR(255) NULL, title_ar VARCHAR(255) NULL, notes_en TEXT NULL, notes_ar TEXT NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_pd_proposal (proposal_id), CONSTRAINT fk_pd_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE, CONSTRAINT fk_pd_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Proposal day places (places visited per day) -- --------------------------------------------------------------------- DROP TABLE IF EXISTS proposal_day_places; CREATE TABLE proposal_day_places ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, proposal_day_id INT UNSIGNED NOT NULL, place_id INT UNSIGNED NOT NULL, description_override_en TEXT NULL, description_override_ar TEXT NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 0, KEY idx_pdp_day (proposal_day_id), CONSTRAINT fk_pdp_day FOREIGN KEY (proposal_day_id) REFERENCES proposal_days(id) ON DELETE CASCADE, CONSTRAINT fk_pdp_place FOREIGN KEY (place_id) REFERENCES places(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Proposal day transportation -- --------------------------------------------------------------------- DROP TABLE IF EXISTS proposal_day_transportations; CREATE TABLE proposal_day_transportations ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, proposal_day_id INT UNSIGNED NOT NULL, transport_type_id INT UNSIGNED NOT NULL, from_location_en VARCHAR(150) NULL, from_location_ar VARCHAR(150) NULL, to_location_en VARCHAR(150) NULL, to_location_ar VARCHAR(150) NULL, price DECIMAL(14,2) NULL, currency_id INT UNSIGNED NULL, notes_en TEXT NULL, notes_ar TEXT NULL, KEY idx_pdt_day (proposal_day_id), CONSTRAINT fk_pdt_day FOREIGN KEY (proposal_day_id) REFERENCES proposal_days(id) ON DELETE CASCADE, CONSTRAINT fk_pdt_type FOREIGN KEY (transport_type_id) REFERENCES transportation_types(id) ON DELETE RESTRICT, CONSTRAINT fk_pdt_currency FOREIGN KEY (currency_id) REFERENCES currencies(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Proposal inclusions -- --------------------------------------------------------------------- DROP TABLE IF EXISTS proposal_inclusions; CREATE TABLE proposal_inclusions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, proposal_id INT UNSIGNED NOT NULL, inclusion_id INT UNSIGNED NOT NULL, custom_title_en VARCHAR(255) NULL, custom_title_ar VARCHAR(255) NULL, description_en TEXT NULL, description_ar TEXT NULL, price DECIMAL(14,2) NULL, currency_id INT UNSIGNED NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 0, KEY idx_pi_proposal (proposal_id), CONSTRAINT fk_pi_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE, CONSTRAINT fk_pi_inclusion FOREIGN KEY (inclusion_id) REFERENCES inclusions(id) ON DELETE CASCADE, CONSTRAINT fk_pi_currency FOREIGN KEY (currency_id) REFERENCES currencies(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Proposal exclusions -- --------------------------------------------------------------------- DROP TABLE IF EXISTS proposal_exclusions; CREATE TABLE proposal_exclusions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, proposal_id INT UNSIGNED NOT NULL, exclusion_id INT UNSIGNED NOT NULL, custom_title_en VARCHAR(255) NULL, custom_title_ar VARCHAR(255) NULL, description_en TEXT NULL, description_ar TEXT NULL, price DECIMAL(14,2) NULL, currency_id INT UNSIGNED NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 0, KEY idx_pe_proposal (proposal_id), CONSTRAINT fk_pe_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE, CONSTRAINT fk_pe_exclusion FOREIGN KEY (exclusion_id) REFERENCES exclusions(id) ON DELETE CASCADE, CONSTRAINT fk_pe_currency FOREIGN KEY (currency_id) REFERENCES currencies(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Proposal versions (history snapshot) -- --------------------------------------------------------------------- DROP TABLE IF EXISTS proposal_versions; CREATE TABLE proposal_versions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, proposal_id INT UNSIGNED NOT NULL, version INT UNSIGNED NOT NULL, snapshot LONGTEXT NOT NULL, pdf_path VARCHAR(255) NULL, action ENUM('saved','exported','approved') NOT NULL DEFAULT 'saved', created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_pv_proposal (proposal_id), CONSTRAINT fk_pv_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE, CONSTRAINT fk_pv_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Activity log (audit) -- --------------------------------------------------------------------- DROP TABLE IF EXISTS activity_log; CREATE TABLE activity_log ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NULL, action VARCHAR(80) NOT NULL, subject_type VARCHAR(80) NULL, subject_id INT UNSIGNED NULL, description TEXT NULL, ip_address VARCHAR(64) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_al_subject (subject_type, subject_id), CONSTRAINT fk_al_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1;