-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: May 14, 2026 at 01:48 PM
-- Server version: 10.4.28-MariaDB
-- PHP Version: 8.2.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `linkprox`
--

-- --------------------------------------------------------

--
-- Table structure for table `activity_logs`
--

DROP TABLE IF EXISTS `activity_logs`;
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `actor_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `company_id` bigint(20) UNSIGNED DEFAULT NULL,
  `customer_id` bigint(20) UNSIGNED DEFAULT NULL,
  `module_name` varchar(100) NOT NULL,
  `entity_type` varchar(100) NOT NULL,
  `entity_id` bigint(20) UNSIGNED DEFAULT NULL,
  `action_code` varchar(100) NOT NULL,
  `action_label` varchar(150) NOT NULL,
  `metadata_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata_json`)),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_activity_logs_user` (`actor_user_id`),
  KEY `idx_activity_logs_company` (`company_id`),
  KEY `idx_activity_logs_customer` (`customer_id`),
  KEY `idx_activity_logs_entity` (`entity_type`,`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `app_banners`
--

DROP TABLE IF EXISTS `app_banners`;
CREATE TABLE IF NOT EXISTS `app_banners` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `subtitle` text DEFAULT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `action_type` enum('none','url','category','service','request') DEFAULT 'none',
  `action_value` varchar(255) DEFAULT NULL,
  `button_text` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `app_sliders`
--

DROP TABLE IF EXISTS `app_sliders`;
CREATE TABLE IF NOT EXISTS `app_sliders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `subtitle` text DEFAULT NULL,
  `badge_text` varchar(50) DEFAULT NULL,
  `button_text` varchar(50) DEFAULT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `link_url` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `areas`
--

DROP TABLE IF EXISTS `areas`;
CREATE TABLE IF NOT EXISTS `areas` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `emirate_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_emirate_area` (`emirate_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `areas`
--

INSERT INTO `areas` (`id`, `emirate_id`, `name`, `created_at`) VALUES
(2, 1, 'Al Quoz Industrial Area 3', '2026-05-01 02:33:32');

-- --------------------------------------------------------

--
-- Table structure for table `attachments`
--

DROP TABLE IF EXISTS `attachments`;
CREATE TABLE IF NOT EXISTS `attachments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_type` enum('company_document','request','quotation','contract_version','message','payment_submission','execution_stage_update','help_request','execution_stage_extension') NOT NULL,
  `entity_id` bigint(20) UNSIGNED NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `original_name` varchar(255) DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `file_size` bigint(20) UNSIGNED DEFAULT NULL,
  `uploaded_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_attachments_uploaded_by` (`uploaded_by`),
  KEY `idx_attachments_entity` (`entity_type`,`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `audit_logs`
--

DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `actor_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `event_name` varchar(150) NOT NULL,
  `entity_type` varchar(100) DEFAULT NULL,
  `entity_id` bigint(20) UNSIGNED DEFAULT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_audit_logs_user` (`actor_user_id`),
  KEY `idx_audit_logs_entity` (`entity_type`,`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `authorized_agents`
--

DROP TABLE IF EXISTS `authorized_agents`;
CREATE TABLE IF NOT EXISTS `authorized_agents` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `email` varchar(190) DEFAULT NULL,
  `phone_country_code` varchar(10) DEFAULT NULL,
  `phone_number` varchar(30) DEFAULT NULL,
  `company_position` varchar(150) DEFAULT NULL,
  `verification_status` enum('not_verified','pending','verified','rejected') NOT NULL DEFAULT 'not_verified',
  `verification_provider` varchar(100) DEFAULT NULL,
  `invited_at` datetime DEFAULT NULL,
  `accepted_at` datetime DEFAULT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `status` enum('pending','active','inactive','revoked') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_authorized_agents_customer` (`customer_id`),
  KEY `idx_authorized_agents_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `business_types`
--

DROP TABLE IF EXISTS `business_types`;
CREATE TABLE IF NOT EXISTS `business_types` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `business_types`
--

INSERT INTO `business_types` (`id`, `name`) VALUES
(18, 'Cleaning Services'),
(3, 'Construction & Contracting'),
(11, 'Consulting Services'),
(8, 'Education & Training'),
(1, 'Engineering Services'),
(4, 'Facilities Management'),
(15, 'Financial Services'),
(9, 'Food & Beverages'),
(5, 'General Trading'),
(7, 'Healthcare Services'),
(20, 'Interior Design'),
(2, 'IT Solutions'),
(16, 'Legal Services'),
(13, 'Manufacturing'),
(12, 'Marketing & Advertising'),
(6, 'Real Estate'),
(14, 'Retail & E-Commerce'),
(19, 'Security Services'),
(17, 'Tourism & Hospitality'),
(10, 'Transportation & Logistics');

-- --------------------------------------------------------

--
-- Table structure for table `catalog_categories`
--

DROP TABLE IF EXISTS `catalog_categories`;
CREATE TABLE IF NOT EXISTS `catalog_categories` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_type` enum('product','service') NOT NULL,
  `name_en` varchar(190) DEFAULT NULL,
  `name_ar` varchar(190) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `parent_id` bigint(20) UNSIGNED DEFAULT NULL,
  `is_visible` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_catalog_category_type_name` (`category_type`,`name_en`),
  KEY `fk_catalog_categories_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `catalog_import_batches`
--

DROP TABLE IF EXISTS `catalog_import_batches`;
CREATE TABLE IF NOT EXISTS `catalog_import_batches` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `import_type` enum('products','services') NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `total_rows` int(11) NOT NULL DEFAULT 0,
  `valid_rows` int(11) NOT NULL DEFAULT 0,
  `invalid_rows` int(11) NOT NULL DEFAULT 0,
  `status` enum('uploaded','validated','imported','failed') NOT NULL DEFAULT 'uploaded',
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_catalog_import_batches_company` (`company_id`),
  KEY `fk_catalog_import_batches_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `catalog_import_errors`
--

DROP TABLE IF EXISTS `catalog_import_errors`;
CREATE TABLE IF NOT EXISTS `catalog_import_errors` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch_id` bigint(20) UNSIGNED NOT NULL,
  `row_number` int(11) NOT NULL,
  `column_name` varchar(100) DEFAULT NULL,
  `error_message` text NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_catalog_import_errors_batch` (`batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `companies`
--

DROP TABLE IF EXISTS `companies`;
CREATE TABLE IF NOT EXISTS `companies` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner_user_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(190) NOT NULL,
  `slug` varchar(190) NOT NULL,
  `business_type_id` bigint(20) UNSIGNED DEFAULT NULL,
  `company_email` varchar(190) DEFAULT NULL,
  `phone_country_code` varchar(10) DEFAULT NULL,
  `phone_number` varchar(30) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `coverage_areas` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`coverage_areas`)),
  `emirate_id` bigint(20) UNSIGNED DEFAULT NULL,
  `area_id` bigint(20) UNSIGNED DEFAULT NULL,
  `country_id` bigint(20) UNSIGNED DEFAULT NULL,
  `full_address` text DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `industry` varchar(150) DEFAULT NULL,
  `employee_range` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `logo_path` varchar(255) DEFAULT NULL,
  `cover_path` varchar(255) DEFAULT NULL,
  `visibility_status` enum('private','public') NOT NULL DEFAULT 'public',
  `company_status` enum('pending_approval','approved','rejected','suspended','inactive') NOT NULL DEFAULT 'pending_approval',
  `is_verified_supplier` tinyint(1) NOT NULL DEFAULT 0,
  `is_iso_certified` tinyint(1) NOT NULL DEFAULT 0,
  `car_wash_enabled` tinyint(1) DEFAULT 0,
  `approved_at` datetime DEFAULT NULL,
  `rejected_at` datetime DEFAULT NULL,
  `approved_by` bigint(20) UNSIGNED DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `fk_companies_owner` (`owner_user_id`),
  KEY `fk_companies_business_type` (`business_type_id`),
  KEY `fk_companies_emirate` (`emirate_id`),
  KEY `fk_companies_area` (`area_id`),
  KEY `fk_companies_approved_by` (`approved_by`),
  KEY `idx_companies_status` (`company_status`),
  KEY `idx_companies_visibility` (`visibility_status`),
  KEY `fk_companies_country` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_authorized_agents`
--

DROP TABLE IF EXISTS `company_authorized_agents`;
CREATE TABLE IF NOT EXISTS `company_authorized_agents` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `company_staff_id` bigint(20) UNSIGNED DEFAULT NULL,
  `full_name` varchar(150) NOT NULL,
  `email` varchar(190) DEFAULT NULL,
  `phone_country_code` varchar(10) DEFAULT NULL,
  `phone_number` varchar(30) DEFAULT NULL,
  `company_position` varchar(150) DEFAULT NULL,
  `authority_level` varchar(100) DEFAULT NULL,
  `verification_status` enum('pending_kyc','verified','rejected') NOT NULL DEFAULT 'pending_kyc',
  `uae_pass_linked` tinyint(1) NOT NULL DEFAULT 0,
  `permission_can_view_contract` tinyint(1) NOT NULL DEFAULT 1,
  `permission_can_message` tinyint(1) NOT NULL DEFAULT 1,
  `permission_can_upload_progress` tinyint(1) NOT NULL DEFAULT 0,
  `permission_can_add_notes` tinyint(1) NOT NULL DEFAULT 0,
  `permission_can_approve_milestones` tinyint(1) NOT NULL DEFAULT 0,
  `permission_can_acknowledge_delivery` tinyint(1) NOT NULL DEFAULT 1,
  `max_contract_value` decimal(14,2) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_company_authorized_agents_company_staff` (`company_staff_id`),
  KEY `idx_company_authorized_agents_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_business_hours`
--

DROP TABLE IF EXISTS `company_business_hours`;
CREATE TABLE IF NOT EXISTS `company_business_hours` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `weekday` tinyint(3) UNSIGNED NOT NULL COMMENT '1=Mon ... 7=Sun',
  `opens_at` time DEFAULT NULL,
  `closes_at` time DEFAULT NULL,
  `is_closed` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_weekday` (`company_id`,`weekday`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_car_wash_settings`
--

DROP TABLE IF EXISTS `company_car_wash_settings`;
CREATE TABLE IF NOT EXISTS `company_car_wash_settings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `title_en` varchar(255) NOT NULL DEFAULT 'Premium Car Wash',
  `title_ar` varchar(255) NOT NULL DEFAULT 'غسيل سيارات ممتاز',
  `sub_title_en` varchar(255) DEFAULT 'Full exterior & interior detailing',
  `sub_title_ar` varchar(255) DEFAULT 'تفاصيل خارجية وداخلية كاملة',
  `description_en` text DEFAULT NULL,
  `description_ar` text DEFAULT NULL,
  `base_price` decimal(10,2) DEFAULT 25.00,
  `estimated_duration` varchar(100) DEFAULT '45 Minutes',
  `crew_size` varchar(100) DEFAULT '2 Technicians',
  `cleaning_methods_en` text DEFAULT NULL,
  `cleaning_methods_ar` text DEFAULT NULL,
  `vehicle_coverage_en` text DEFAULT NULL,
  `vehicle_coverage_ar` text DEFAULT NULL,
  `quality_control_en` text DEFAULT NULL,
  `quality_control_ar` text DEFAULT NULL,
  `support_text_en` text DEFAULT NULL,
  `support_text_ar` text DEFAULT NULL,
  `media` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`media`)),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_id` (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `company_car_wash_settings`
--

INSERT INTO `company_car_wash_settings` (`id`, `company_id`, `title_en`, `title_ar`, `sub_title_en`, `sub_title_ar`, `description_en`, `description_ar`, `base_price`, `estimated_duration`, `crew_size`, `cleaning_methods_en`, `cleaning_methods_ar`, `vehicle_coverage_en`, `vehicle_coverage_ar`, `quality_control_en`, `quality_control_ar`, `support_text_en`, `support_text_ar`, `media`, `updated_at`) VALUES
(3, 0, 'Premium Car Wash', 'غسيل سيارات ممتاز', 'Full exterior & interior detailing', 'تفاصيل خارجية وداخلية كاملة', 'We provide professional premium car wash services designed to restore your vehicle\'s appearance and protect its exterior finish.', 'نحن نقدم خدمات غسيل سيارات متميزة احترافية مصممة لاستعادة مظهر سيارتك وحماية شكلها الخارجي.', '25.00', '45 Minutes', '2 Technicians', 'Foam wash, pressure rinse, microfiber hand drying', 'غسيل بالرغوة ، شطف بالضغط ، تجفيف يدوي بقطعة قماش ناعمة', 'Sedans, SUVs, and light commercial vehicles', 'سيارات السيدان وسيارات الدفع الرباعي والمركبات التجارية الخفيفة', 'Final inspection to ensure spotless finish and streak-free surfaces', 'فحص نهائي لضمان تشطيب خالي من البقع وأسطح خالية من الخطوط', 'Optional interior detailing and protective coating available', 'تتوفر تفاصيل داخلية اختيارية وطلاء واقي', '[]', '2026-05-10 04:17:22');

-- --------------------------------------------------------

--
-- Table structure for table `company_catalog_categories`
--

DROP TABLE IF EXISTS `company_catalog_categories`;
CREATE TABLE IF NOT EXISTS `company_catalog_categories` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `parent_category_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(190) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_company_catalog_categories_company` (`company_id`),
  KEY `fk_company_catalog_categories_parent` (`parent_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_deactivation_requests`
--

DROP TABLE IF EXISTS `company_deactivation_requests`;
CREATE TABLE IF NOT EXISTS `company_deactivation_requests` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_deactivate_company` (`company_id`),
  KEY `fk_deactivate_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_documents`
--

DROP TABLE IF EXISTS `company_documents`;
CREATE TABLE IF NOT EXISTS `company_documents` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `document_type` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `file_size` bigint(20) UNSIGNED DEFAULT NULL,
  `uploaded_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_company_documents_uploaded_by` (`uploaded_by`),
  KEY `idx_company_documents_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_legal_information`
--

DROP TABLE IF EXISTS `company_legal_information`;
CREATE TABLE IF NOT EXISTS `company_legal_information` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `trade_license_number` varchar(100) DEFAULT NULL,
  `trade_license_expiry_date` date DEFAULT NULL,
  `tax_registration_number` varchar(100) DEFAULT NULL,
  `legal_representative_name` varchar(150) DEFAULT NULL,
  `registered_address` text DEFAULT NULL,
  `identity_verification_status` enum('not_verified','pending','verified','rejected') NOT NULL DEFAULT 'not_verified',
  `identity_verification_provider` varchar(100) DEFAULT NULL,
  `identity_verified_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_notification_settings`
--

DROP TABLE IF EXISTS `company_notification_settings`;
CREATE TABLE IF NOT EXISTS `company_notification_settings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `receive_notifications` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_request_statuses`
--

DROP TABLE IF EXISTS `company_request_statuses`;
CREATE TABLE IF NOT EXISTS `company_request_statuses` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'submitted',
  `reason` varchar(255) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `req_comp_idx` (`request_id`,`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_request_status_history`
--

DROP TABLE IF EXISTS `company_request_status_history`;
CREATE TABLE IF NOT EXISTS `company_request_status_history` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `old_status` varchar(50) DEFAULT NULL,
  `new_status` varchar(50) NOT NULL,
  `changed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_settings`
--

DROP TABLE IF EXISTS `company_settings`;
CREATE TABLE IF NOT EXISTS `company_settings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `language_id` bigint(20) UNSIGNED DEFAULT NULL,
  `timezone` varchar(100) DEFAULT 'Asia/Dubai',
  `date_format` varchar(30) DEFAULT 'd/m/Y',
  `allow_instant_jobs` tinyint(1) NOT NULL DEFAULT 1,
  `account_status` enum('active','locked','termination_requested','terminated') NOT NULL DEFAULT 'active',
  `termination_requested_at` datetime DEFAULT NULL,
  `terminated_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_id` (`company_id`),
  KEY `fk_company_settings_language` (`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_staff`
--

DROP TABLE IF EXISTS `company_staff`;
CREATE TABLE IF NOT EXISTS `company_staff` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `full_name` varchar(150) NOT NULL,
  `email` varchar(190) NOT NULL,
  `phone_country_code` varchar(10) DEFAULT NULL,
  `phone_number` varchar(30) DEFAULT NULL,
  `official_title` varchar(150) DEFAULT NULL,
  `role_label` varchar(100) DEFAULT NULL,
  `app_access_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('pending','active','disabled','offline') NOT NULL DEFAULT 'pending',
  `invited_at` datetime DEFAULT NULL,
  `joined_at` datetime DEFAULT NULL,
  `disabled_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_staff_email` (`company_id`,`email`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `idx_company_staff_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_staff_permissions`
--

DROP TABLE IF EXISTS `company_staff_permissions`;
CREATE TABLE IF NOT EXISTS `company_staff_permissions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_staff_id` bigint(20) UNSIGNED NOT NULL,
  `permission_code` varchar(100) NOT NULL,
  `is_allowed` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_staff_permission` (`company_staff_id`,`permission_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_subscriptions`
--

DROP TABLE IF EXISTS `company_subscriptions`;
CREATE TABLE IF NOT EXISTS `company_subscriptions` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` int(10) UNSIGNED NOT NULL,
  `plan_id` int(10) UNSIGNED NOT NULL,
  `starts_at` date NOT NULL,
  `expires_at` date NOT NULL,
  `status` enum('active','expired','cancelled') NOT NULL DEFAULT 'active',
  `price_paid` decimal(10,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL COMMENT 'Admin user who assigned this plan',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_company` (`company_id`),
  KEY `idx_plan` (`plan_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `company_verification_logs`
--

DROP TABLE IF EXISTS `company_verification_logs`;
CREATE TABLE IF NOT EXISTS `company_verification_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `previous_status` varchar(50) NOT NULL,
  `new_status` varchar(50) NOT NULL,
  `action` varchar(100) NOT NULL,
  `notes` text DEFAULT NULL,
  `performed_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_veriflog_user` (`performed_by`),
  KEY `idx_veriflog_company` (`company_id`),
  KEY `idx_veriflog_status` (`new_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contracts`
--

DROP TABLE IF EXISTS `contracts`;
CREATE TABLE IF NOT EXISTS `contracts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_no` varchar(50) NOT NULL,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `quotation_id` bigint(20) UNSIGNED NOT NULL,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `current_version_no` int(11) NOT NULL DEFAULT 1,
  `status` enum('draft','pending_signature','under_review','revision_requested','active','termination_pending','completed','cancelled','terminated','archived') NOT NULL DEFAULT 'draft',
  `workflow_step` enum('details','roles','review','completed') DEFAULT 'details',
  `signing_representative_id` bigint(20) UNSIGNED DEFAULT NULL,
  `site_engineer_id` bigint(20) UNSIGNED DEFAULT NULL,
  `engineer_notifications_enabled` tinyint(1) DEFAULT 1,
  `project_name` varchar(255) DEFAULT NULL,
  `total_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `currency_id` bigint(20) UNSIGNED DEFAULT NULL,
  `start_execution_trigger` enum('after_contract_signing','after_advance_payment','specific_date') DEFAULT NULL,
  `specific_start_date` date DEFAULT NULL,
  `expected_completion_date` date DEFAULT NULL,
  `signed_at` datetime DEFAULT NULL,
  `activated_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `terminated_at` datetime DEFAULT NULL,
  `archived_at` datetime DEFAULT NULL,
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `contract_no` (`contract_no`),
  KEY `fk_contracts_request` (`request_id`),
  KEY `fk_contracts_quotation` (`quotation_id`),
  KEY `fk_contracts_currency` (`currency_id`),
  KEY `fk_contracts_created_by` (`created_by`),
  KEY `idx_contracts_customer` (`customer_id`),
  KEY `idx_contracts_company` (`company_id`),
  KEY `idx_contracts_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_archives`
--

DROP TABLE IF EXISTS `contract_archives`;
CREATE TABLE IF NOT EXISTS `contract_archives` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_id` bigint(20) UNSIGNED NOT NULL,
  `archived_by` bigint(20) UNSIGNED DEFAULT NULL,
  `archive_notes` text DEFAULT NULL,
  `archived_at` datetime NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `contract_id` (`contract_id`),
  KEY `fk_contract_archives_user` (`archived_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_logs`
--

DROP TABLE IF EXISTS `contract_logs`;
CREATE TABLE IF NOT EXISTS `contract_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_id` bigint(20) UNSIGNED NOT NULL,
  `actor_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `action_code` varchar(100) NOT NULL,
  `action_label` varchar(150) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_contract_logs_contract` (`contract_id`),
  KEY `fk_contract_logs_user` (`actor_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_milestones`
--

DROP TABLE IF EXISTS `contract_milestones`;
CREATE TABLE IF NOT EXISTS `contract_milestones` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_id` bigint(20) UNSIGNED NOT NULL,
  `contract_version_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `percentage` decimal(5,2) NOT NULL,
  `amount` decimal(14,2) NOT NULL,
  `due_date` date DEFAULT NULL,
  `trigger_label` varchar(255) DEFAULT NULL,
  `status` enum('locked','requested','pending_approval','verified','rejected','paid') NOT NULL DEFAULT 'locked',
  `sequence_no` int(11) NOT NULL DEFAULT 1,
  `requested_at` datetime DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `rejected_at` datetime DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_contract_milestones_version` (`contract_version_id`),
  KEY `idx_contract_milestones_contract` (`contract_id`),
  KEY `idx_contract_milestones_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_participants`
--

DROP TABLE IF EXISTS `contract_participants`;
CREATE TABLE IF NOT EXISTS `contract_participants` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_id` bigint(20) UNSIGNED NOT NULL,
  `participant_role` enum('customer','company_signing_representative','site_engineer','company_staff','authorized_agent') NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `company_staff_id` bigint(20) UNSIGNED DEFAULT NULL,
  `site_engineer_id` bigint(20) UNSIGNED DEFAULT NULL,
  `company_authorized_agent_id` bigint(20) UNSIGNED DEFAULT NULL,
  `customer_authorized_agent_id` bigint(20) UNSIGNED DEFAULT NULL,
  `permission_can_view_contract` tinyint(1) NOT NULL DEFAULT 1,
  `permission_can_message` tinyint(1) NOT NULL DEFAULT 1,
  `permission_can_upload_progress` tinyint(1) NOT NULL DEFAULT 0,
  `permission_can_add_notes` tinyint(1) NOT NULL DEFAULT 0,
  `permission_can_approve_milestones` tinyint(1) NOT NULL DEFAULT 0,
  `permission_can_acknowledge_delivery` tinyint(1) NOT NULL DEFAULT 0,
  `assigned_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_contract_participants_contract` (`contract_id`),
  KEY `fk_contract_participants_user` (`user_id`),
  KEY `fk_contract_participants_company_staff` (`company_staff_id`),
  KEY `fk_contract_participants_site_engineer` (`site_engineer_id`),
  KEY `fk_contract_participants_company_agent` (`company_authorized_agent_id`),
  KEY `fk_contract_participants_customer_agent` (`customer_authorized_agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_signatures`
--

DROP TABLE IF EXISTS `contract_signatures`;
CREATE TABLE IF NOT EXISTS `contract_signatures` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_id` bigint(20) UNSIGNED NOT NULL,
  `contract_version_id` bigint(20) UNSIGNED NOT NULL,
  `signer_type` enum('customer','company') NOT NULL,
  `signer_name` varchar(150) NOT NULL,
  `signer_identifier` varchar(150) DEFAULT NULL,
  `provider` varchar(100) DEFAULT NULL,
  `signature_image_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','signed','declined') NOT NULL DEFAULT 'pending',
  `signed_at` datetime DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_contract_signatures_contract` (`contract_id`),
  KEY `fk_contract_signatures_version` (`contract_version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_termination_requests`
--

DROP TABLE IF EXISTS `contract_termination_requests`;
CREATE TABLE IF NOT EXISTS `contract_termination_requests` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_id` bigint(20) UNSIGNED NOT NULL,
  `requested_by_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `requested_by_side` enum('customer','company') NOT NULL,
  `reason_category` varchar(100) DEFAULT NULL,
  `reason` text NOT NULL,
  `system_scenario` enum('work_based','refund_required','outstanding_payment','manual') DEFAULT NULL,
  `suggested_refund_amount` decimal(14,2) DEFAULT NULL,
  `outstanding_amount` decimal(14,2) DEFAULT NULL,
  `adjustment_amount` decimal(14,2) DEFAULT NULL,
  `adjustment_reason` text DEFAULT NULL,
  `dispute_reason` text DEFAULT NULL,
  `status` enum('pending','approved','rejected','finalized','disputed','withdrawn') NOT NULL DEFAULT 'pending',
  `disputed_at` datetime DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `finalized_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_contract_termination_requests_contract` (`contract_id`),
  KEY `fk_contract_termination_requests_user` (`requested_by_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_versions`
--

DROP TABLE IF EXISTS `contract_versions`;
CREATE TABLE IF NOT EXISTS `contract_versions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_id` bigint(20) UNSIGNED NOT NULL,
  `version_no` int(11) NOT NULL,
  `revision_reason` text DEFAULT NULL,
  `change_summary` text DEFAULT NULL,
  `scope_of_work` longtext DEFAULT NULL,
  `excluded_scope` longtext DEFAULT NULL,
  `technical_specifications` longtext DEFAULT NULL,
  `subtotal` decimal(14,2) NOT NULL DEFAULT 0.00,
  `vat_percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `vat_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `grand_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `payment_terms_type` enum('full_completion','advance_completion','milestones') DEFAULT NULL,
  `execution_duration_value` int(11) DEFAULT NULL,
  `execution_duration_unit` enum('days','weeks','months','years') DEFAULT NULL,
  `start_execution_trigger` enum('after_contract_signing','after_advance_payment','specific_date') DEFAULT NULL,
  `specific_start_date` date DEFAULT NULL,
  `expected_completion_date` date DEFAULT NULL,
  `delay_penalty_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `delay_penalty_type` enum('percentage_per_day','fixed_amount_per_day') DEFAULT NULL,
  `delay_penalty_value` decimal(14,2) DEFAULT NULL,
  `grace_period_days` int(11) DEFAULT NULL,
  `max_penalty_cap_percent` decimal(5,2) DEFAULT NULL,
  `warranty_included` tinyint(1) NOT NULL DEFAULT 0,
  `warranty_period_value` int(11) DEFAULT NULL,
  `warranty_period_unit` enum('days','months','years') DEFAULT NULL,
  `review_status` enum('draft','sent_for_review','approved','rejected') NOT NULL DEFAULT 'draft',
  `sent_for_review_at` datetime DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `attachments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attachments`)),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_contract_version` (`contract_id`,`version_no`),
  KEY `fk_contract_versions_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_version_items`
--

DROP TABLE IF EXISTS `contract_version_items`;
CREATE TABLE IF NOT EXISTS `contract_version_items` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_version_id` bigint(20) UNSIGNED NOT NULL,
  `item_title` varchar(255) NOT NULL,
  `pricing_type` enum('standard','measured','lump_sum') NOT NULL DEFAULT 'standard',
  `measurement_type` enum('per_m2','per_linear','custom_per_sub_item') DEFAULT NULL,
  `rate` decimal(14,2) DEFAULT NULL,
  `quantity` decimal(14,2) DEFAULT NULL,
  `waste_allowance_percent` decimal(5,2) DEFAULT NULL,
  `line_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_contract_version_items_version` (`contract_version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_version_item_subitems`
--

DROP TABLE IF EXISTS `contract_version_item_subitems`;
CREATE TABLE IF NOT EXISTS `contract_version_item_subitems` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_version_item_id` bigint(20) UNSIGNED NOT NULL,
  `label` varchar(255) NOT NULL,
  `width` decimal(14,2) DEFAULT NULL,
  `height` decimal(14,2) DEFAULT NULL,
  `qty` decimal(14,2) DEFAULT 1.00,
  `override_rate` decimal(14,2) DEFAULT NULL,
  `area_value` decimal(14,2) DEFAULT NULL,
  `subtotal` decimal(14,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_contract_version_item_subitems_item` (`contract_version_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_version_milestones`
--

DROP TABLE IF EXISTS `contract_version_milestones`;
CREATE TABLE IF NOT EXISTS `contract_version_milestones` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_version_id` bigint(20) UNSIGNED NOT NULL,
  `milestone_title` varchar(255) NOT NULL,
  `percentage` decimal(5,2) NOT NULL,
  `trigger_label` varchar(255) DEFAULT NULL,
  `calculated_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_contract_version_milestones_version` (`contract_version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `conversations`
--

DROP TABLE IF EXISTS `conversations`;
CREATE TABLE IF NOT EXISTS `conversations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `firestore_thread_id` varchar(100) DEFAULT NULL,
  `conversation_type` enum('request','contract','inquiry') NOT NULL,
  `request_id` bigint(20) UNSIGNED DEFAULT NULL,
  `contract_id` bigint(20) UNSIGNED DEFAULT NULL,
  `instant_job_id` bigint(20) UNSIGNED DEFAULT NULL,
  `customer_id` bigint(20) UNSIGNED DEFAULT NULL,
  `company_id` bigint(20) UNSIGNED DEFAULT NULL,
  `last_message_text` text DEFAULT NULL,
  `service_id` bigint(20) UNSIGNED DEFAULT NULL,
  `product_id` bigint(20) UNSIGNED DEFAULT NULL,
  `is_internal_notes_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','archived','closed') NOT NULL DEFAULT 'active',
  `last_message_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_conversations_customer` (`customer_id`),
  KEY `fk_conversations_company` (`company_id`),
  KEY `idx_conversations_request` (`request_id`),
  KEY `idx_conversations_contract` (`contract_id`),
  KEY `idx_conversations_type` (`conversation_type`),
  KEY `fk_conv_service` (`service_id`),
  KEY `fk_conv_product` (`product_id`),
  KEY `idx_firestore_thread` (`firestore_thread_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `conversation_participants`
--

DROP TABLE IF EXISTS `conversation_participants`;
CREATE TABLE IF NOT EXISTS `conversation_participants` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `role` enum('customer','company_owner','company_staff','site_engineer','customer_agent','company_agent') NOT NULL,
  `can_read` tinyint(1) DEFAULT 1,
  `can_send` tinyint(1) DEFAULT 1,
  `joined_at` datetime DEFAULT NULL,
  `left_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_conversation_participants_user` (`user_id`),
  KEY `idx_conversation_participants_conversation` (`conversation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `countries`
--

DROP TABLE IF EXISTS `countries`;
CREATE TABLE IF NOT EXISTS `countries` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `iso2` char(2) NOT NULL,
  `iso3` char(3) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `phone_code` varchar(10) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `iso2` (`iso2`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `iso3` (`iso3`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `countries`
--

INSERT INTO `countries` (`id`, `iso2`, `iso3`, `name`, `phone_code`, `created_at`) VALUES
(1, 'AE', NULL, 'United Arab Emirates', NULL, '2026-04-22 16:04:53'),
(2, 'SA', NULL, 'Saudi Arabia', NULL, '2026-04-22 16:04:53'),
(3, 'QA', NULL, 'Qatar', NULL, '2026-04-22 16:04:53'),
(4, 'BH', NULL, 'Bahrain', NULL, '2026-04-22 16:26:52'),
(5, 'KW', NULL, 'Kuwait', NULL, '2026-04-22 16:26:52'),
(6, 'OM', NULL, 'Oman', NULL, '2026-04-22 16:26:52'),
(7, 'JO', NULL, 'Jordan', NULL, '2026-04-22 16:26:52'),
(8, 'EG', NULL, 'Egypt', NULL, '2026-04-22 16:26:52'),
(9, 'LB', NULL, 'Lebanon', NULL, '2026-04-22 16:26:52'),
(10, 'IQ', NULL, 'Iraq', NULL, '2026-04-22 16:26:52'),
(11, 'MA', NULL, 'Morocco', NULL, '2026-04-22 16:26:52'),
(12, 'TN', NULL, 'Tunisia', NULL, '2026-04-22 16:26:52'),
(13, 'US', NULL, 'United States', NULL, '2026-04-22 16:26:52'),
(14, 'GB', NULL, 'United Kingdom', NULL, '2026-04-22 16:26:52'),
(15, 'DE', NULL, 'Germany', NULL, '2026-04-22 16:26:52'),
(16, 'FR', NULL, 'France', NULL, '2026-04-22 16:26:52'),
(17, 'IN', NULL, 'India', NULL, '2026-04-22 16:26:52'),
(18, 'PK', NULL, 'Pakistan', NULL, '2026-04-22 16:26:52'),
(19, 'CN', NULL, 'China', NULL, '2026-04-22 16:26:52'),
(20, 'TR', NULL, 'Turkey', NULL, '2026-04-22 16:26:52');

-- --------------------------------------------------------

--
-- Table structure for table `currencies`
--

DROP TABLE IF EXISTS `currencies`;
CREATE TABLE IF NOT EXISTS `currencies` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `symbol` varchar(10) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `currencies`
--

INSERT INTO `currencies` (`id`, `code`, `name`, `symbol`, `created_at`) VALUES
(1, 'AED', 'UAE Dirham', 'AED', '2026-04-21 15:21:25'),
(2, 'USD', 'US Dollar', '$', '2026-04-21 15:21:25');

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `default_language_id` bigint(20) UNSIGNED DEFAULT NULL,
  `default_emirate_id` bigint(20) UNSIGNED DEFAULT NULL,
  `identity_verification_status` enum('not_verified','pending','verified','rejected') NOT NULL DEFAULT 'not_verified',
  `identity_verification_provider` varchar(100) DEFAULT NULL,
  `identity_verified_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `fk_customers_language` (`default_language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `customer_addresses`
--

DROP TABLE IF EXISTS `customer_addresses`;
CREATE TABLE IF NOT EXISTS `customer_addresses` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `label` varchar(150) NOT NULL,
  `emirate_id` bigint(20) UNSIGNED NOT NULL,
  `area_id` bigint(20) UNSIGNED DEFAULT NULL,
  `full_address` text NOT NULL,
  `notes` text DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_customer_addresses_emirate` (`emirate_id`),
  KEY `fk_customer_addresses_area` (`area_id`),
  KEY `idx_customer_addresses_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `customer_help_requests`
--

DROP TABLE IF EXISTS `customer_help_requests`;
CREATE TABLE IF NOT EXISTS `customer_help_requests` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) UNSIGNED DEFAULT NULL,
  `company_id` bigint(20) UNSIGNED DEFAULT NULL,
  `sender_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `sender_role` enum('customer','company','staff','site_engineer','authorized_agent') NOT NULL DEFAULT 'customer',
  `category` varchar(100) NOT NULL,
  `related_type` enum('contract','request','payment','account','other') DEFAULT NULL,
  `related_id` bigint(20) UNSIGNED DEFAULT NULL,
  `subject` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` enum('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_customer_help_requests_customer` (`customer_id`),
  KEY `idx_customer_help_requests_status` (`status`),
  KEY `fk_customer_help_requests_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `customer_notification_preferences`
--

DROP TABLE IF EXISTS `customer_notification_preferences`;
CREATE TABLE IF NOT EXISTS `customer_notification_preferences` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `receive_notifications` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emirates`
--

DROP TABLE IF EXISTS `emirates`;
CREATE TABLE IF NOT EXISTS `emirates` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `country_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `fk_emirates_country` (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `emirates`
--

INSERT INTO `emirates` (`id`, `name`, `country_id`, `created_at`) VALUES
(1, 'Abu Dhabi', 1, '2026-05-01 02:29:17'),
(2, 'Dubai', 1, '2026-04-22 16:04:53'),
(3, 'Sharjah', 1, '2026-04-22 16:04:53'),
(4, 'Ajman', 1, '2026-04-22 16:04:53'),
(5, 'Fujairah', 1, '2026-04-22 16:04:53'),
(6, 'Ras Al Khaimah', 1, '2026-04-22 16:04:53'),
(7, 'Umm Al Quwain', 1, '2026-04-22 16:04:53'),
(8, 'Al Ain', 1, '2026-04-22 16:26:57'),
(9, 'Al Dhafra', 1, '2026-04-22 16:26:57'),
(10, 'Hatta', 1, '2026-04-22 16:26:57'),
(11, 'Khor Fakkan', 1, '2026-04-22 16:26:57'),
(12, 'Kalba', 1, '2026-04-22 16:26:57'),
(13, 'Dibba Al-Fujairah', 1, '2026-04-22 16:26:57'),
(14, 'Dibba Al-Hisn', 1, '2026-04-22 16:26:57'),
(15, 'Masfout', 1, '2026-04-22 16:26:57'),
(16, 'Jebel Ali', 1, '2026-04-22 16:26:57'),
(17, 'Silicon Oasis', 1, '2026-04-22 16:26:57'),
(18, 'Riyadh', 2, '2026-04-22 16:30:05'),
(19, 'Jeddah', 2, '2026-04-22 16:30:05'),
(20, 'Dammam', 2, '2026-04-22 16:30:05'),
(21, 'Mecca', 2, '2026-04-22 16:30:05'),
(22, 'Medina', 2, '2026-04-22 16:30:05'),
(23, 'Doha', 3, '2026-04-22 16:30:05'),
(24, 'Al Wakrah', 3, '2026-04-22 16:30:05'),
(25, 'Al Khor', 3, '2026-04-22 16:30:05'),
(26, 'Manama', 4, '2026-04-22 16:30:05'),
(27, 'Muharraq', 4, '2026-04-22 16:30:05'),
(28, 'Riffa', 4, '2026-04-22 16:30:05'),
(29, 'Kuwait City', 5, '2026-04-22 16:30:05'),
(30, 'Hawalli', 5, '2026-04-22 16:30:05'),
(31, 'Salmiya', 5, '2026-04-22 16:30:05'),
(32, 'Muscat', 6, '2026-04-22 16:30:05'),
(33, 'Salalah', 6, '2026-04-22 16:30:05'),
(34, 'Sohar', 6, '2026-04-22 16:30:05'),
(35, 'Amman', 7, '2026-04-22 16:30:05'),
(36, 'Irbid', 7, '2026-04-22 16:30:05'),
(37, 'Aqaba', 7, '2026-04-22 16:30:05'),
(38, 'Cairo', 8, '2026-04-22 16:30:05'),
(39, 'Alexandria', 8, '2026-04-22 16:30:05'),
(40, 'Giza', 8, '2026-04-22 16:30:05'),
(41, 'Beirut', 9, '2026-04-22 16:30:05'),
(42, 'Tripoli', 9, '2026-04-22 16:30:05'),
(43, 'Sidon', 9, '2026-04-22 16:30:05'),
(44, 'Baghdad', 10, '2026-04-22 16:30:05'),
(45, 'Basra', 10, '2026-04-22 16:30:05'),
(46, 'Erbil', 10, '2026-04-22 16:30:05'),
(47, 'Casablanca', 11, '2026-04-22 16:30:05'),
(48, 'Rabat', 11, '2026-04-22 16:30:05'),
(49, 'Marrakech', 11, '2026-04-22 16:30:05'),
(50, 'Tunis', 12, '2026-04-22 16:30:05'),
(51, 'Sfax', 12, '2026-04-22 16:30:05'),
(52, 'Sousse', 12, '2026-04-22 16:30:05'),
(53, 'New York', 13, '2026-04-22 16:30:13'),
(54, 'Los Angeles', 13, '2026-04-22 16:30:13'),
(55, 'Houston', 13, '2026-04-22 16:30:13'),
(56, 'London', 14, '2026-04-22 16:30:13'),
(57, 'Manchester', 14, '2026-04-22 16:30:13'),
(58, 'Birmingham', 14, '2026-04-22 16:30:13'),
(59, 'Berlin', 15, '2026-04-22 16:30:13'),
(60, 'Munich', 15, '2026-04-22 16:30:13'),
(61, 'Hamburg', 15, '2026-04-22 16:30:13'),
(62, 'Paris', 16, '2026-04-22 16:30:13'),
(63, 'Marseille', 16, '2026-04-22 16:30:13'),
(64, 'Lyon', 16, '2026-04-22 16:30:13'),
(65, 'Mumbai', 17, '2026-04-22 16:30:13'),
(66, 'Delhi', 17, '2026-04-22 16:30:13'),
(67, 'Bangalore', 17, '2026-04-22 16:30:13'),
(68, 'Karachi', 18, '2026-04-22 16:30:13'),
(69, 'Lahore', 18, '2026-04-22 16:30:13'),
(70, 'Islamabad', 18, '2026-04-22 16:30:13'),
(71, 'Beijing', 19, '2026-04-22 16:30:13'),
(72, 'Shanghai', 19, '2026-04-22 16:30:13'),
(73, 'Guangzhou', 19, '2026-04-22 16:30:13'),
(74, 'Istanbul', 20, '2026-04-22 16:30:13'),
(75, 'Ankara', 20, '2026-04-22 16:30:13'),
(76, 'Izmir', 20, '2026-04-22 16:30:13');

-- --------------------------------------------------------

--
-- Table structure for table `engineer_locations`
--

DROP TABLE IF EXISTS `engineer_locations`;
CREATE TABLE IF NOT EXISTS `engineer_locations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `engineer_id` bigint(20) UNSIGNED NOT NULL,
  `latitude` decimal(10,7) NOT NULL,
  `longitude` decimal(10,7) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `engineer_id` (`engineer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `execution_delays`
--

DROP TABLE IF EXISTS `execution_delays`;
CREATE TABLE IF NOT EXISTS `execution_delays` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stage_id` bigint(20) UNSIGNED NOT NULL,
  `recorded_by` bigint(20) UNSIGNED DEFAULT NULL,
  `delay_type` varchar(100) NOT NULL,
  `status` enum('under_review','acknowledged','resolved') DEFAULT 'under_review',
  `number_of_days` int(11) NOT NULL,
  `note` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_execution_delays_stage` (`stage_id`),
  KEY `fk_execution_delays_user` (`recorded_by`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `execution_stages`
--

DROP TABLE IF EXISTS `execution_stages`;
CREATE TABLE IF NOT EXISTS `execution_stages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `contract_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `sequence_no` int(11) NOT NULL,
  `milestone_id` bigint(20) UNSIGNED DEFAULT NULL,
  `assigned_site_engineer_id` bigint(20) UNSIGNED DEFAULT NULL,
  `assigned_company_staff_id` bigint(20) UNSIGNED DEFAULT NULL,
  `status` enum('pending','in_progress','awaiting_approval','approved','rejected','delayed','completed') NOT NULL DEFAULT 'pending',
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `rejected_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_execution_stage_contract_seq` (`contract_id`,`sequence_no`),
  KEY `fk_execution_stages_milestone` (`milestone_id`),
  KEY `fk_execution_stages_engineer` (`assigned_site_engineer_id`),
  KEY `fk_execution_stages_company_staff` (`assigned_company_staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `execution_stage_extension_requests`
--

DROP TABLE IF EXISTS `execution_stage_extension_requests`;
CREATE TABLE IF NOT EXISTS `execution_stage_extension_requests` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stage_id` bigint(20) UNSIGNED NOT NULL,
  `current_deadline_date` date DEFAULT NULL,
  `proposed_deadline_date` date NOT NULL,
  `extension_days` int(11) NOT NULL DEFAULT 0,
  `reason_code` varchar(100) NOT NULL,
  `explanation` text NOT NULL,
  `status` enum('pending_customer','approved','rejected') DEFAULT 'pending_customer',
  `customer_response_note` text DEFAULT NULL,
  `customer_responded_at` datetime DEFAULT NULL,
  `created_by` bigint(20) UNSIGNED 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`),
  KEY `stage_id` (`stage_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `execution_stage_reviews`
--

DROP TABLE IF EXISTS `execution_stage_reviews`;
CREATE TABLE IF NOT EXISTS `execution_stage_reviews` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stage_id` bigint(20) UNSIGNED NOT NULL,
  `reviewed_by` bigint(20) UNSIGNED DEFAULT NULL,
  `review_status` enum('approved','rejected') NOT NULL,
  `review_note` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_execution_stage_reviews_stage` (`stage_id`),
  KEY `fk_execution_stage_reviews_user` (`reviewed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `execution_stage_updates`
--

DROP TABLE IF EXISTS `execution_stage_updates`;
CREATE TABLE IF NOT EXISTS `execution_stage_updates` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `stage_id` bigint(20) UNSIGNED NOT NULL,
  `updated_by` bigint(20) UNSIGNED DEFAULT NULL,
  `execution_notes` longtext DEFAULT NULL,
  `stage_notes` longtext DEFAULT NULL,
  `client_notified` tinyint(1) NOT NULL DEFAULT 0,
  `status_after_update` enum('pending','in_progress','awaiting_approval','approved','rejected','delayed','completed') DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_execution_stage_updates_stage` (`stage_id`),
  KEY `fk_execution_stage_updates_user` (`updated_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `faqs`
--

DROP TABLE IF EXISTS `faqs`;
CREATE TABLE IF NOT EXISTS `faqs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `question` varchar(500) NOT NULL,
  `answer` text NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `faqs`
--

INSERT INTO `faqs` (`id`, `category_id`, `question`, `answer`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, 'What is a Broadcast Request?', 'A Broadcast Request allows you to send a structured request to multiple qualified companies at once. Each company can respond with a quotation independently.', 'active', '2026-05-01 03:18:20', '2026-05-01 03:18:20'),
(2, 1, 'How do I cancel a request?', 'You can cancel a request from the request details page before it is accepted by a company.', 'active', '2026-05-01 03:18:20', '2026-05-01 03:18:20'),
(3, 2, 'How do I sign a contract?', 'You need to verify your identity via UAE PASS first, then you can sign the contract directly from the app.', 'active', '2026-05-01 03:18:20', '2026-05-01 03:18:20'),
(4, 3, 'What payment methods are accepted?', 'We accept bank transfers, credit cards, and standard wire transfers depending on the company policies.', 'active', '2026-05-01 03:18:20', '2026-05-01 03:18:20'),
(5, 4, 'How do I change my password?', 'Navigate to Account Settings -> Security to change your password.', 'active', '2026-05-01 03:18:20', '2026-05-01 03:18:20');

-- --------------------------------------------------------

--
-- Table structure for table `faq_categories`
--

DROP TABLE IF EXISTS `faq_categories`;
CREATE TABLE IF NOT EXISTS `faq_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `faq_categories`
--

INSERT INTO `faq_categories` (`id`, `name`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Requests', 'active', '2026-05-01 03:18:20', '2026-05-01 03:18:20'),
(2, 'Quotations & Contracts', 'active', '2026-05-01 03:18:20', '2026-05-01 03:18:20'),
(3, 'Payments', 'active', '2026-05-01 03:18:20', '2026-05-01 03:18:20'),
(4, 'Account & Security', 'active', '2026-05-01 03:18:20', '2026-05-01 03:18:20');

-- --------------------------------------------------------

--
-- Table structure for table `instant_service_offers`
--

DROP TABLE IF EXISTS `instant_service_offers`;
CREATE TABLE IF NOT EXISTS `instant_service_offers` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `eta_minutes` int(11) NOT NULL,
  `status` enum('pending','accepted','rejected','expired') DEFAULT 'pending',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_request_company` (`request_id`,`company_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `instant_service_requests`
--

DROP TABLE IF EXISTS `instant_service_requests`;
CREATE TABLE IF NOT EXISTS `instant_service_requests` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_no` varchar(50) NOT NULL,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `service_type` varchar(50) NOT NULL DEFAULT 'car_wash',
  `car_details` text DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `address_text` text DEFAULT NULL,
  `status` enum('pending','offered','accepted','in_progress','completed','paid','cancelled') DEFAULT 'pending',
  `accepted_offer_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_no` (`request_no`),
  KEY `customer_id` (`customer_id`),
  KEY `fk_accepted_offer` (`accepted_offer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `instant_service_tracking`
--

DROP TABLE IF EXISTS `instant_service_tracking`;
CREATE TABLE IF NOT EXISTS `instant_service_tracking` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `stage_code` varchar(50) NOT NULL,
  `stage_label_en` varchar(100) DEFAULT NULL,
  `stage_label_ar` varchar(100) DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `request_id` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `languages`
--

DROP TABLE IF EXISTS `languages`;
CREATE TABLE IF NOT EXISTS `languages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `languages`
--

INSERT INTO `languages` (`id`, `code`, `name`, `created_at`) VALUES
(1, 'ar', 'Arabic', '2026-04-22 17:50:19'),
(2, 'en', 'English', '2026-04-22 17:50:27');

-- --------------------------------------------------------

--
-- Table structure for table `measurement_units`
--

DROP TABLE IF EXISTS `measurement_units`;
CREATE TABLE IF NOT EXISTS `measurement_units` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `measurement_units`
--

INSERT INTO `measurement_units` (`id`, `code`, `name`) VALUES
(1, 'piece', 'Piece'),
(2, 'm2', 'Square Meter'),
(3, 'linear_meter', 'Linear Meter'),
(4, 'kg', 'Kilogram'),
(5, 'liter', 'Liter'),
(6, 'hour', 'Hour'),
(7, 'day', 'Day'),
(8, 'unit', 'Unit');

-- --------------------------------------------------------

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

DROP TABLE IF EXISTS `messages`;
CREATE TABLE IF NOT EXISTS `messages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(20) UNSIGNED NOT NULL,
  `sender_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `sender_type` enum('customer','company','staff','engineer','customer_agent','company_agent','system') NOT NULL,
  `message_type` enum('text','system','note') NOT NULL DEFAULT 'text',
  `body` longtext DEFAULT NULL,
  `is_internal_note` tinyint(1) NOT NULL DEFAULT 0,
  `read_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_messages_sender_user` (`sender_user_id`),
  KEY `idx_messages_conversation` (`conversation_id`),
  KEY `idx_messages_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

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

DROP TABLE IF EXISTS `migrations`;
CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `applied_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `migration` (`migration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `milestone_payment_submissions`
--

DROP TABLE IF EXISTS `milestone_payment_submissions`;
CREATE TABLE IF NOT EXISTS `milestone_payment_submissions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `milestone_id` bigint(20) UNSIGNED NOT NULL,
  `submitted_by_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `amount` decimal(14,2) NOT NULL,
  `status` enum('submitted','pending_approval','approved','rejected') NOT NULL DEFAULT 'submitted',
  `supplier_review_note` text DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `submitted_at` datetime NOT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `reviewed_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_milestone_payment_submissions_submitted_by` (`submitted_by_user_id`),
  KEY `fk_milestone_payment_submissions_reviewed_by` (`reviewed_by`),
  KEY `idx_milestone_payment_submissions_milestone` (`milestone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `notification_type` varchar(100) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text DEFAULT NULL,
  `related_type` varchar(100) DEFAULT NULL,
  `related_id` bigint(20) UNSIGNED DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `read_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_notifications_user_read` (`user_id`,`is_read`),
  KEY `idx_notifications_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `otp_codes`
--

DROP TABLE IF EXISTS `otp_codes`;
CREATE TABLE IF NOT EXISTS `otp_codes` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `contact_type` enum('email','phone') NOT NULL,
  `contact_value` varchar(190) NOT NULL,
  `purpose` enum('signup','login','password_reset','verify_phone','verify_email') NOT NULL,
  `otp_code` varchar(20) NOT NULL,
  `attempts` int(11) NOT NULL DEFAULT 0,
  `max_attempts` int(11) NOT NULL DEFAULT 5,
  `verified_at` datetime DEFAULT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_otp_codes_user` (`user_id`),
  KEY `idx_otp_contact` (`contact_type`,`contact_value`),
  KEY `idx_otp_purpose` (`purpose`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `password_reset_tokens`
--

DROP TABLE IF EXISTS `password_reset_tokens`;
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `contact_value` varchar(190) NOT NULL,
  `token` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_password_reset_token` (`token`),
  KEY `fk_password_reset_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `permissions`
--

DROP TABLE IF EXISTS `permissions`;
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(100) NOT NULL,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `module_name` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `pricing_models`
--

DROP TABLE IF EXISTS `pricing_models`;
CREATE TABLE IF NOT EXISTS `pricing_models` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `pricing_models`
--

INSERT INTO `pricing_models` (`id`, `code`, `name`) VALUES
(1, 'fixed_price', 'Fixed Price'),
(2, 'hourly_rate', 'Hourly Rate'),
(3, 'custom_quote', 'Custom Quote');

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

DROP TABLE IF EXISTS `products`;
CREATE TABLE IF NOT EXISTS `products` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `category_id` bigint(20) UNSIGNED DEFAULT NULL,
  `company_category_id` bigint(20) UNSIGNED DEFAULT NULL,
  `name_en` varchar(190) DEFAULT NULL,
  `name_ar` varchar(190) DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `unit` varchar(100) DEFAULT NULL,
  `material_grade` varchar(100) DEFAULT NULL,
  `unit_price` decimal(14,2) DEFAULT NULL,
  `currency_id` bigint(20) UNSIGNED DEFAULT NULL,
  `stock_status` enum('in_stock','low_stock','out_of_stock') NOT NULL DEFAULT 'in_stock',
  `visibility_status` enum('active','disabled','draft') NOT NULL DEFAULT 'active',
  `is_visible_in_catalog` tinyint(1) NOT NULL DEFAULT 1,
  `is_featured` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `stock_quantity` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku_unique` (`company_id`,`sku`),
  KEY `fk_products_currency` (`currency_id`),
  KEY `idx_products_company` (`company_id`),
  KEY `idx_products_category` (`category_id`),
  KEY `idx_products_visibility` (`visibility_status`),
  KEY `fk_products_company_category` (`company_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_media`
--

DROP TABLE IF EXISTS `product_media`;
CREATE TABLE IF NOT EXISTS `product_media` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` enum('image','video') NOT NULL DEFAULT 'image',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_product_media_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_specifications`
--

DROP TABLE IF EXISTS `product_specifications`;
CREATE TABLE IF NOT EXISTS `product_specifications` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `spec_label` varchar(100) NOT NULL,
  `spec_value` varchar(255) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_product_specifications_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `quotations`
--

DROP TABLE IF EXISTS `quotations`;
CREATE TABLE IF NOT EXISTS `quotations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `quotation_no` varchar(50) NOT NULL,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `version_no` int(11) NOT NULL DEFAULT 1,
  `quotation_type` enum('system_generated','uploaded_pdf') NOT NULL DEFAULT 'system_generated',
  `status` enum('draft','sent','pending','viewed','accepted','rejected','expired','withdrawn') NOT NULL DEFAULT 'draft',
  `project_name` varchar(255) DEFAULT NULL,
  `request_summary` text DEFAULT NULL,
  `scope_description` longtext DEFAULT NULL,
  `technical_specifications` longtext DEFAULT NULL,
  `subtotal` decimal(14,2) NOT NULL DEFAULT 0.00,
  `vat_percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `vat_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `currency_id` bigint(20) UNSIGNED DEFAULT NULL,
  `execution_duration_value` int(11) DEFAULT NULL,
  `execution_duration_unit` enum('days','weeks','months','years') DEFAULT NULL,
  `validity_days` int(11) DEFAULT NULL,
  `valid_until` date DEFAULT NULL,
  `payment_terms_type` enum('full_completion','advance_completion','milestones') DEFAULT NULL,
  `payment_terms_details` varchar(255) DEFAULT NULL,
  `warranty_included` tinyint(1) NOT NULL DEFAULT 0,
  `warranty_period_value` int(11) DEFAULT NULL,
  `warranty_period_unit` enum('days','months','years') DEFAULT NULL,
  `supplier_notes` text DEFAULT NULL,
  `special_conditions` text DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `viewed_at` datetime DEFAULT NULL,
  `accepted_at` datetime DEFAULT NULL,
  `rejected_at` datetime DEFAULT NULL,
  `expired_at` datetime DEFAULT NULL,
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `quotation_no` (`quotation_no`),
  KEY `fk_quotations_customer` (`customer_id`),
  KEY `fk_quotations_currency` (`currency_id`),
  KEY `fk_quotations_created_by` (`created_by`),
  KEY `idx_quotations_request` (`request_id`),
  KEY `idx_quotations_company` (`company_id`),
  KEY `idx_quotations_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `quotation_attachments`
--

DROP TABLE IF EXISTS `quotation_attachments`;
CREATE TABLE IF NOT EXISTS `quotation_attachments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint(20) UNSIGNED NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `file_size` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_quotation_attachments_quotation` (`quotation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `quotation_items`
--

DROP TABLE IF EXISTS `quotation_items`;
CREATE TABLE IF NOT EXISTS `quotation_items` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint(20) UNSIGNED NOT NULL,
  `item_title` varchar(255) NOT NULL,
  `pricing_type` enum('standard','measured','lump_sum') NOT NULL DEFAULT 'standard',
  `measurement_type` enum('per_m2','per_linear','custom_per_sub_item') DEFAULT NULL,
  `rate` decimal(14,2) DEFAULT NULL,
  `quantity` decimal(14,2) DEFAULT NULL,
  `waste_allowance_percent` decimal(5,2) DEFAULT 0.00,
  `line_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_quotation_items_quotation` (`quotation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `quotation_item_subitems`
--

DROP TABLE IF EXISTS `quotation_item_subitems`;
CREATE TABLE IF NOT EXISTS `quotation_item_subitems` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `quotation_item_id` bigint(20) UNSIGNED NOT NULL,
  `label` varchar(255) NOT NULL,
  `width` decimal(14,2) DEFAULT NULL,
  `height` decimal(14,2) DEFAULT NULL,
  `qty` decimal(14,2) DEFAULT 1.00,
  `override_rate` decimal(14,2) DEFAULT NULL,
  `area_value` decimal(14,2) DEFAULT NULL,
  `subtotal` decimal(14,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_quotation_item_subitems_item` (`quotation_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `quotation_logs`
--

DROP TABLE IF EXISTS `quotation_logs`;
CREATE TABLE IF NOT EXISTS `quotation_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint(20) UNSIGNED NOT NULL,
  `actor_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `action_code` varchar(100) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_quotation_logs_quotation` (`quotation_id`),
  KEY `fk_quotation_logs_user` (`actor_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `quotation_milestones`
--

DROP TABLE IF EXISTS `quotation_milestones`;
CREATE TABLE IF NOT EXISTS `quotation_milestones` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `quotation_id` bigint(20) UNSIGNED NOT NULL,
  `milestone_title` varchar(255) NOT NULL,
  `percentage` decimal(5,2) NOT NULL,
  `trigger_label` varchar(255) DEFAULT NULL,
  `calculated_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_quotation_milestones_quotation` (`quotation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `requests`
--

DROP TABLE IF EXISTS `requests`;
CREATE TABLE IF NOT EXISTS `requests` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_no` varchar(50) NOT NULL,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'for direct request / inquiry',
  `request_type` enum('inquiry','direct_request','broadcast_request') NOT NULL,
  `source_type` enum('company','service','product','manual') NOT NULL DEFAULT 'manual',
  `source_id` bigint(20) UNSIGNED DEFAULT NULL,
  `category_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `estimated_quantity` decimal(14,2) DEFAULT NULL,
  `unit_id` bigint(20) UNSIGNED DEFAULT NULL,
  `urgency_level_id` bigint(20) UNSIGNED DEFAULT NULL,
  `budget` decimal(14,2) DEFAULT NULL,
  `currency_id` bigint(20) UNSIGNED DEFAULT NULL,
  `budget_flexible` tinyint(1) NOT NULL DEFAULT 0,
  `response_time_hours` int(11) DEFAULT NULL,
  `requested_start_date` date DEFAULT NULL,
  `requested_end_date` date DEFAULT NULL,
  `planned_duration_value` int(11) DEFAULT NULL,
  `planned_duration_unit` enum('days','weeks','months') DEFAULT NULL,
  `customer_address_id` bigint(20) UNSIGNED DEFAULT NULL,
  `emirate_id` bigint(20) UNSIGNED DEFAULT NULL,
  `area_id` bigint(20) UNSIGNED DEFAULT NULL,
  `status` enum('draft','submitted','viewed','in_review','quotation_received','quoted','accepted','rejected','expired','completed','closed') NOT NULL DEFAULT 'draft',
  `submitted_at` datetime DEFAULT NULL,
  `viewed_at` datetime DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_no` (`request_no`),
  KEY `fk_requests_unit` (`unit_id`),
  KEY `fk_requests_urgency` (`urgency_level_id`),
  KEY `fk_requests_currency` (`currency_id`),
  KEY `fk_requests_customer_address` (`customer_address_id`),
  KEY `fk_requests_emirate` (`emirate_id`),
  KEY `fk_requests_area` (`area_id`),
  KEY `idx_requests_customer` (`customer_id`),
  KEY `idx_requests_company` (`company_id`),
  KEY `idx_requests_status` (`status`),
  KEY `idx_requests_type` (`request_type`),
  KEY `fk_requests_category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `request_attachments`
--

DROP TABLE IF EXISTS `request_attachments`;
CREATE TABLE IF NOT EXISTS `request_attachments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` varchar(100) DEFAULT NULL,
  `file_size` int(10) UNSIGNED DEFAULT NULL,
  `uploaded_by` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_request_id` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `request_categories`
--

DROP TABLE IF EXISTS `request_categories`;
CREATE TABLE IF NOT EXISTS `request_categories` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `parent_id` bigint(20) UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `fk_request_categories_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `request_category_mappings`
--

DROP TABLE IF EXISTS `request_category_mappings`;
CREATE TABLE IF NOT EXISTS `request_category_mappings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_category_id` bigint(20) UNSIGNED NOT NULL,
  `catalog_category_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `request_category_id` (`request_category_id`),
  KEY `catalog_category_id` (`catalog_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `request_logs`
--

DROP TABLE IF EXISTS `request_logs`;
CREATE TABLE IF NOT EXISTS `request_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `actor_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `actor_type` enum('customer','company','staff','system','admin') NOT NULL DEFAULT 'system',
  `action_code` varchar(100) NOT NULL,
  `action_label` varchar(150) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_request_logs_user` (`actor_user_id`),
  KEY `idx_request_logs_request` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `request_specifications`
--

DROP TABLE IF EXISTS `request_specifications`;
CREATE TABLE IF NOT EXISTS `request_specifications` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `label` varchar(255) NOT NULL,
  `value` text NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_request_specs_request` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `request_target_companies`
--

DROP TABLE IF EXISTS `request_target_companies`;
CREATE TABLE IF NOT EXISTS `request_target_companies` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `invitation_status` enum('targeted','sent','viewed','responded','ignored','declined') NOT NULL DEFAULT 'targeted',
  `decline_reason` varchar(255) DEFAULT NULL,
  `decline_message` text DEFAULT NULL,
  `invited_at` datetime DEFAULT NULL,
  `viewed_at` datetime DEFAULT NULL,
  `responded_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_request_company` (`request_id`,`company_id`),
  KEY `fk_request_target_companies_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `roles`
--

DROP TABLE IF EXISTS `roles`;
CREATE TABLE IF NOT EXISTS `roles` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `code`, `name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'customer', 'Customer', NULL, '2026-04-21 15:20:59', '2026-04-21 15:20:59'),
(2, 'company', 'Company Owner', NULL, '2026-04-21 15:20:59', '2026-04-21 15:20:59'),
(3, 'company_staff', 'Company Staff', NULL, '2026-04-21 15:20:59', '2026-04-21 15:20:59'),
(4, 'site_engineer', 'Site Engineer', NULL, '2026-04-21 15:20:59', '2026-04-21 15:20:59'),
(5, 'authorized_agent', 'Authorized Agent', NULL, '2026-04-21 15:20:59', '2026-04-21 15:20:59'),
(6, 'admin', 'Platform Admin', NULL, '2026-04-21 15:20:59', '2026-04-21 15:20:59');

-- --------------------------------------------------------

--
-- Table structure for table `role_permissions`
--

DROP TABLE IF EXISTS `role_permissions`;
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` bigint(20) UNSIGNED NOT NULL,
  `permission_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_role_permission` (`role_id`,`permission_id`),
  KEY `fk_role_permissions_permission` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `schema_migrations`
--

DROP TABLE IF EXISTS `schema_migrations`;
CREATE TABLE IF NOT EXISTS `schema_migrations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `checksum` char(64) NOT NULL,
  `applied_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `filename` (`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `services`
--

DROP TABLE IF EXISTS `services`;
CREATE TABLE IF NOT EXISTS `services` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `category_id` bigint(20) UNSIGNED NOT NULL,
  `company_category_id` bigint(20) UNSIGNED DEFAULT NULL,
  `name_en` varchar(255) NOT NULL,
  `name_ar` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `media` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`media`)),
  `unit` varchar(50) DEFAULT 'Session',
  `sku` varchar(100) DEFAULT NULL,
  `base_price` decimal(10,2) DEFAULT 0.00,
  `pricing_model` enum('fixed','hourly','custom') DEFAULT 'fixed',
  `skill_level` varchar(100) DEFAULT NULL,
  `execution_mode` enum('instant','contract') DEFAULT 'instant',
  `visibility_status` enum('active','disabled') DEFAULT 'active',
  `is_featured` tinyint(1) DEFAULT 0,
  `public_portal` tinyint(1) DEFAULT 0,
  `specifications` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`specifications`)),
  `estimated_duration_value` int(11) DEFAULT NULL,
  `estimated_duration_unit` enum('minutes','hours','days','weeks','months') DEFAULT NULL,
  `crew_size` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_id` (`company_id`,`sku`),
  KEY `fk_services_company_category` (`company_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `service_media`
--

DROP TABLE IF EXISTS `service_media`;
CREATE TABLE IF NOT EXISTS `service_media` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `service_id` bigint(20) UNSIGNED NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` enum('image','video') NOT NULL DEFAULT 'image',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_service_media_service` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `service_specifications`
--

DROP TABLE IF EXISTS `service_specifications`;
CREATE TABLE IF NOT EXISTS `service_specifications` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `service_id` bigint(20) UNSIGNED NOT NULL,
  `spec_label` varchar(100) NOT NULL,
  `spec_value` varchar(255) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_service_specifications_service` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `site_engineers`
--

DROP TABLE IF EXISTS `site_engineers`;
CREATE TABLE IF NOT EXISTS `site_engineers` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) UNSIGNED NOT NULL,
  `company_staff_id` bigint(20) UNSIGNED DEFAULT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `engineer_code` varchar(50) DEFAULT NULL,
  `full_name` varchar(150) NOT NULL,
  `email` varchar(190) DEFAULT NULL,
  `phone_country_code` varchar(10) DEFAULT NULL,
  `phone_number` varchar(30) DEFAULT NULL,
  `system_role` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `engineer_code` (`engineer_code`),
  KEY `fk_site_engineers_company_staff` (`company_staff_id`),
  KEY `fk_site_engineers_user` (`user_id`),
  KEY `idx_site_engineers_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `staff_locations`
--

DROP TABLE IF EXISTS `staff_locations`;
CREATE TABLE IF NOT EXISTS `staff_locations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `staff_id` bigint(20) UNSIGNED NOT NULL,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_staff_request` (`staff_id`,`request_id`),
  KEY `request_id` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `subscription_plans`
--

DROP TABLE IF EXISTS `subscription_plans`;
CREATE TABLE IF NOT EXISTS `subscription_plans` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `validity_days` int(10) UNSIGNED NOT NULL DEFAULT 365,
  `contract_availability_days` int(10) UNSIGNED DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(10) 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(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `system_settings`
--

DROP TABLE IF EXISTS `system_settings`;
CREATE TABLE IF NOT EXISTS `system_settings` (
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `system_settings`
--

INSERT INTO `system_settings` (`setting_key`, `setting_value`, `updated_at`) VALUES
('accent_color', '#22cac3', '2026-04-23 02:11:33'),
('api_debugging', '1', '2026-04-23 02:06:14'),
('maintenance_mode', '1', '2026-04-23 02:10:38'),
('platform_name', 'LinkProx Enterprise', '2026-04-23 02:10:09'),
('platform_url', 'http://localhost/linkprox_new', '2026-04-23 02:06:14'),
('primary_color', '#01203f', '2026-04-23 02:06:14'),
('support_business_days', 'Sunday - Monday', '2026-05-01 03:21:30'),
('support_business_hours', '8:00 AM - 5:00 AM', '2026-05-01 03:21:30'),
('support_email', 'support@linkprox.com', '2026-04-23 02:06:14');

-- --------------------------------------------------------

--
-- Table structure for table `urgency_levels`
--

DROP TABLE IF EXISTS `urgency_levels`;
CREATE TABLE IF NOT EXISTS `urgency_levels` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `urgency_levels`
--

INSERT INTO `urgency_levels` (`id`, `code`, `name`, `sort_order`) VALUES
(1, 'low', 'Low', 1),
(2, 'medium', 'Medium', 2),
(3, 'high', 'High', 3);

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL,
  `firebase_uid` varchar(128) DEFAULT NULL,
  `full_name` varchar(150) NOT NULL,
  `email` varchar(190) DEFAULT NULL,
  `phone_country_code` varchar(10) DEFAULT NULL,
  `phone_number` varchar(30) DEFAULT NULL,
  `password_hash` varchar(255) NOT NULL,
  `avatar_path` varchar(255) DEFAULT NULL,
  `preferred_language_id` bigint(20) UNSIGNED DEFAULT NULL,
  `timezone` varchar(100) DEFAULT 'Asia/Dubai',
  `status` enum('pending_verification','active','inactive','suspended','deleted') NOT NULL DEFAULT 'pending_verification',
  `email_verified_at` datetime DEFAULT NULL,
  `phone_verified_at` datetime DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `token_version` int(11) NOT NULL DEFAULT 1,
  `otp_code` varchar(10) DEFAULT NULL,
  `otp_expires_at` datetime DEFAULT NULL,
  `reset_token` varchar(100) DEFAULT NULL,
  `reset_expires_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `firebase_uid` (`firebase_uid`),
  KEY `fk_users_language` (`preferred_language_id`),
  KEY `idx_users_status` (`status`),
  KEY `idx_users_phone` (`phone_country_code`,`phone_number`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `uuid`, `firebase_uid`, `full_name`, `email`, `phone_country_code`, `phone_number`, `password_hash`, `avatar_path`, `preferred_language_id`, `timezone`, `status`, `email_verified_at`, `phone_verified_at`, `last_login_at`, `remember_token`, `created_at`, `updated_at`, `deleted_at`, `token_version`, `otp_code`, `otp_expires_at`, `reset_token`, `reset_expires_at`) VALUES
(1, 'db7b3321-1e7d-4030-9725-25e38aa5a959', NULL, 'Admin User', 'admin@linkprox.test', '+971', '500000000', '$2y$10$X89vtv5nvyGfn/ORg5Lrw.J8wNAnLk7PQPM5Qaptp6k3mR3/5AWXa', NULL, NULL, 'Asia/Dubai', 'active', '2026-04-21 17:48:36', '2026-04-21 17:48:36', NULL, NULL, '2026-04-21 15:48:36', '2026-05-05 18:25:55', NULL, 1, NULL, NULL, NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `user_roles`
--

DROP TABLE IF EXISTS `user_roles`;
CREATE TABLE IF NOT EXISTS `user_roles` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `role_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_role` (`user_id`,`role_id`),
  KEY `fk_user_roles_role` (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_roles`
--

INSERT INTO `user_roles` (`id`, `user_id`, `role_id`, `created_at`) VALUES
(1, 1, 6, '2026-05-14 13:39:48');

-- --------------------------------------------------------

--
-- Table structure for table `user_sessions`
--

DROP TABLE IF EXISTS `user_sessions`;
CREATE TABLE IF NOT EXISTS `user_sessions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `token_hash` varchar(255) NOT NULL,
  `device_name` varchar(150) DEFAULT NULL,
  `device_type` varchar(50) DEFAULT NULL,
  `app_version` varchar(50) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `last_used_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_token_hash` (`token_hash`),
  KEY `idx_user_sessions_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `activity_logs`
--
ALTER TABLE `activity_logs`
  ADD CONSTRAINT `fk_activity_logs_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_activity_logs_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_activity_logs_user` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `areas`
--
ALTER TABLE `areas`
  ADD CONSTRAINT `fk_areas_emirate` FOREIGN KEY (`emirate_id`) REFERENCES `emirates` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `attachments`
--
ALTER TABLE `attachments`
  ADD CONSTRAINT `fk_attachments_uploaded_by` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD CONSTRAINT `fk_audit_logs_user` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `authorized_agents`
--
ALTER TABLE `authorized_agents`
  ADD CONSTRAINT `fk_authorized_agents_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `catalog_categories`
--
ALTER TABLE `catalog_categories`
  ADD CONSTRAINT `fk_catalog_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `catalog_categories` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `catalog_import_batches`
--
ALTER TABLE `catalog_import_batches`
  ADD CONSTRAINT `fk_catalog_import_batches_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_catalog_import_batches_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `catalog_import_errors`
--
ALTER TABLE `catalog_import_errors`
  ADD CONSTRAINT `fk_catalog_import_errors_batch` FOREIGN KEY (`batch_id`) REFERENCES `catalog_import_batches` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `companies`
--
ALTER TABLE `companies`
  ADD CONSTRAINT `fk_companies_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_companies_area` FOREIGN KEY (`area_id`) REFERENCES `areas` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_companies_business_type` FOREIGN KEY (`business_type_id`) REFERENCES `business_types` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_companies_country` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_companies_emirate` FOREIGN KEY (`emirate_id`) REFERENCES `emirates` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_companies_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `company_authorized_agents`
--
ALTER TABLE `company_authorized_agents`
  ADD CONSTRAINT `fk_company_authorized_agents_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_company_authorized_agents_company_staff` FOREIGN KEY (`company_staff_id`) REFERENCES `company_staff` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `company_business_hours`
--
ALTER TABLE `company_business_hours`
  ADD CONSTRAINT `fk_company_business_hours_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `company_catalog_categories`
--
ALTER TABLE `company_catalog_categories`
  ADD CONSTRAINT `fk_company_catalog_categories_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_company_catalog_categories_parent` FOREIGN KEY (`parent_category_id`) REFERENCES `catalog_categories` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `company_deactivation_requests`
--
ALTER TABLE `company_deactivation_requests`
  ADD CONSTRAINT `fk_deactivate_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_deactivate_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `company_documents`
--
ALTER TABLE `company_documents`
  ADD CONSTRAINT `fk_company_documents_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_company_documents_uploaded_by` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `company_legal_information`
--
ALTER TABLE `company_legal_information`
  ADD CONSTRAINT `fk_company_legal_information_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `company_notification_settings`
--
ALTER TABLE `company_notification_settings`
  ADD CONSTRAINT `fk_company_notification_settings_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `company_settings`
--
ALTER TABLE `company_settings`
  ADD CONSTRAINT `fk_company_settings_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_company_settings_language` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `company_staff`
--
ALTER TABLE `company_staff`
  ADD CONSTRAINT `fk_company_staff_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_company_staff_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `company_staff_permissions`
--
ALTER TABLE `company_staff_permissions`
  ADD CONSTRAINT `fk_company_staff_permissions_staff` FOREIGN KEY (`company_staff_id`) REFERENCES `company_staff` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `company_verification_logs`
--
ALTER TABLE `company_verification_logs`
  ADD CONSTRAINT `fk_veriflog_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_veriflog_user` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `contracts`
--
ALTER TABLE `contracts`
  ADD CONSTRAINT `fk_contracts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_contracts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_contracts_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_contracts_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_contracts_quotation` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`),
  ADD CONSTRAINT `fk_contracts_request` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`);

--
-- Constraints for table `contract_archives`
--
ALTER TABLE `contract_archives`
  ADD CONSTRAINT `fk_contract_archives_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_contract_archives_user` FOREIGN KEY (`archived_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `contract_logs`
--
ALTER TABLE `contract_logs`
  ADD CONSTRAINT `fk_contract_logs_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_contract_logs_user` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `contract_milestones`
--
ALTER TABLE `contract_milestones`
  ADD CONSTRAINT `fk_contract_milestones_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_contract_milestones_version` FOREIGN KEY (`contract_version_id`) REFERENCES `contract_versions` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `contract_participants`
--
ALTER TABLE `contract_participants`
  ADD CONSTRAINT `fk_contract_participants_company_agent` FOREIGN KEY (`company_authorized_agent_id`) REFERENCES `company_authorized_agents` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_contract_participants_company_staff` FOREIGN KEY (`company_staff_id`) REFERENCES `company_staff` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_contract_participants_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_contract_participants_customer_agent` FOREIGN KEY (`customer_authorized_agent_id`) REFERENCES `authorized_agents` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_contract_participants_site_engineer` FOREIGN KEY (`site_engineer_id`) REFERENCES `site_engineers` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_contract_participants_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `contract_signatures`
--
ALTER TABLE `contract_signatures`
  ADD CONSTRAINT `fk_contract_signatures_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_contract_signatures_version` FOREIGN KEY (`contract_version_id`) REFERENCES `contract_versions` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `contract_termination_requests`
--
ALTER TABLE `contract_termination_requests`
  ADD CONSTRAINT `fk_contract_termination_requests_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_contract_termination_requests_user` FOREIGN KEY (`requested_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `contract_versions`
--
ALTER TABLE `contract_versions`
  ADD CONSTRAINT `fk_contract_versions_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_contract_versions_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `contract_version_items`
--
ALTER TABLE `contract_version_items`
  ADD CONSTRAINT `fk_contract_version_items_version` FOREIGN KEY (`contract_version_id`) REFERENCES `contract_versions` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `contract_version_item_subitems`
--
ALTER TABLE `contract_version_item_subitems`
  ADD CONSTRAINT `fk_contract_version_item_subitems_item` FOREIGN KEY (`contract_version_item_id`) REFERENCES `contract_version_items` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `contract_version_milestones`
--
ALTER TABLE `contract_version_milestones`
  ADD CONSTRAINT `fk_contract_version_milestones_version` FOREIGN KEY (`contract_version_id`) REFERENCES `contract_versions` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `conversations`
--
ALTER TABLE `conversations`
  ADD CONSTRAINT `fk_conv_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_conv_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_conversations_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_conversations_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_conversations_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_conversations_request` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `conversation_participants`
--
ALTER TABLE `conversation_participants`
  ADD CONSTRAINT `fk_conversation_participants_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_conversation_participants_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `customers`
--
ALTER TABLE `customers`
  ADD CONSTRAINT `fk_customers_language` FOREIGN KEY (`default_language_id`) REFERENCES `languages` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_customers_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `customer_addresses`
--
ALTER TABLE `customer_addresses`
  ADD CONSTRAINT `fk_customer_addresses_area` FOREIGN KEY (`area_id`) REFERENCES `areas` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_customer_addresses_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_customer_addresses_emirate` FOREIGN KEY (`emirate_id`) REFERENCES `emirates` (`id`);

--
-- Constraints for table `customer_help_requests`
--
ALTER TABLE `customer_help_requests`
  ADD CONSTRAINT `fk_customer_help_requests_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_customer_help_requests_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `customer_notification_preferences`
--
ALTER TABLE `customer_notification_preferences`
  ADD CONSTRAINT `fk_customer_notification_preferences_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `emirates`
--
ALTER TABLE `emirates`
  ADD CONSTRAINT `fk_emirates_country` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `engineer_locations`
--
ALTER TABLE `engineer_locations`
  ADD CONSTRAINT `engineer_locations_ibfk_1` FOREIGN KEY (`engineer_id`) REFERENCES `site_engineers` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `execution_delays`
--
ALTER TABLE `execution_delays`
  ADD CONSTRAINT `fk_execution_delays_stage` FOREIGN KEY (`stage_id`) REFERENCES `execution_stages` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_execution_delays_user` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `execution_stages`
--
ALTER TABLE `execution_stages`
  ADD CONSTRAINT `fk_execution_stages_company_staff` FOREIGN KEY (`assigned_company_staff_id`) REFERENCES `company_staff` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_execution_stages_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_execution_stages_engineer` FOREIGN KEY (`assigned_site_engineer_id`) REFERENCES `site_engineers` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_execution_stages_milestone` FOREIGN KEY (`milestone_id`) REFERENCES `contract_milestones` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `execution_stage_extension_requests`
--
ALTER TABLE `execution_stage_extension_requests`
  ADD CONSTRAINT `execution_stage_extension_requests_ibfk_1` FOREIGN KEY (`stage_id`) REFERENCES `execution_stages` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `execution_stage_reviews`
--
ALTER TABLE `execution_stage_reviews`
  ADD CONSTRAINT `fk_execution_stage_reviews_stage` FOREIGN KEY (`stage_id`) REFERENCES `execution_stages` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_execution_stage_reviews_user` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `execution_stage_updates`
--
ALTER TABLE `execution_stage_updates`
  ADD CONSTRAINT `fk_execution_stage_updates_stage` FOREIGN KEY (`stage_id`) REFERENCES `execution_stages` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_execution_stage_updates_user` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `faqs`
--
ALTER TABLE `faqs`
  ADD CONSTRAINT `faqs_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `faq_categories` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `instant_service_offers`
--
ALTER TABLE `instant_service_offers`
  ADD CONSTRAINT `instant_service_offers_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `instant_service_requests` (`id`),
  ADD CONSTRAINT `instant_service_offers_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`);

--
-- Constraints for table `instant_service_requests`
--
ALTER TABLE `instant_service_requests`
  ADD CONSTRAINT `fk_accepted_offer` FOREIGN KEY (`accepted_offer_id`) REFERENCES `instant_service_offers` (`id`),
  ADD CONSTRAINT `instant_service_requests_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`);

--
-- Constraints for table `instant_service_tracking`
--
ALTER TABLE `instant_service_tracking`
  ADD CONSTRAINT `instant_service_tracking_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `instant_service_requests` (`id`);

--
-- Constraints for table `messages`
--
ALTER TABLE `messages`
  ADD CONSTRAINT `fk_messages_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_messages_sender_user` FOREIGN KEY (`sender_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `milestone_payment_submissions`
--
ALTER TABLE `milestone_payment_submissions`
  ADD CONSTRAINT `fk_milestone_payment_submissions_milestone` FOREIGN KEY (`milestone_id`) REFERENCES `contract_milestones` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_milestone_payment_submissions_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_milestone_payment_submissions_submitted_by` FOREIGN KEY (`submitted_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `fk_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `otp_codes`
--
ALTER TABLE `otp_codes`
  ADD CONSTRAINT `fk_otp_codes_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `password_reset_tokens`
--
ALTER TABLE `password_reset_tokens`
  ADD CONSTRAINT `fk_password_reset_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `catalog_categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_products_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_products_company_category` FOREIGN KEY (`company_category_id`) REFERENCES `company_catalog_categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_products_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `product_media`
--
ALTER TABLE `product_media`
  ADD CONSTRAINT `fk_product_media_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `product_specifications`
--
ALTER TABLE `product_specifications`
  ADD CONSTRAINT `fk_product_specifications_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `quotations`
--
ALTER TABLE `quotations`
  ADD CONSTRAINT `fk_quotations_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_quotations_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_quotations_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_quotations_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_quotations_request` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `quotation_attachments`
--
ALTER TABLE `quotation_attachments`
  ADD CONSTRAINT `fk_quotation_attachments_quotation` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `quotation_items`
--
ALTER TABLE `quotation_items`
  ADD CONSTRAINT `fk_quotation_items_quotation` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `quotation_item_subitems`
--
ALTER TABLE `quotation_item_subitems`
  ADD CONSTRAINT `fk_quotation_item_subitems_item` FOREIGN KEY (`quotation_item_id`) REFERENCES `quotation_items` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `quotation_logs`
--
ALTER TABLE `quotation_logs`
  ADD CONSTRAINT `fk_quotation_logs_quotation` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_quotation_logs_user` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `quotation_milestones`
--
ALTER TABLE `quotation_milestones`
  ADD CONSTRAINT `fk_quotation_milestones_quotation` FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `requests`
--
ALTER TABLE `requests`
  ADD CONSTRAINT `fk_requests_area` FOREIGN KEY (`area_id`) REFERENCES `areas` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_requests_category` FOREIGN KEY (`category_id`) REFERENCES `business_types` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_requests_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_requests_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_requests_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_requests_customer_address` FOREIGN KEY (`customer_address_id`) REFERENCES `customer_addresses` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_requests_emirate` FOREIGN KEY (`emirate_id`) REFERENCES `emirates` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_requests_unit` FOREIGN KEY (`unit_id`) REFERENCES `measurement_units` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_requests_urgency` FOREIGN KEY (`urgency_level_id`) REFERENCES `urgency_levels` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `request_categories`
--
ALTER TABLE `request_categories`
  ADD CONSTRAINT `fk_request_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `request_categories` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `request_category_mappings`
--
ALTER TABLE `request_category_mappings`
  ADD CONSTRAINT `request_category_mappings_ibfk_1` FOREIGN KEY (`request_category_id`) REFERENCES `request_categories` (`id`),
  ADD CONSTRAINT `request_category_mappings_ibfk_2` FOREIGN KEY (`catalog_category_id`) REFERENCES `catalog_categories` (`id`);

--
-- Constraints for table `request_logs`
--
ALTER TABLE `request_logs`
  ADD CONSTRAINT `fk_request_logs_request` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_request_logs_user` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `request_specifications`
--
ALTER TABLE `request_specifications`
  ADD CONSTRAINT `fk_request_specs_request` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `request_target_companies`
--
ALTER TABLE `request_target_companies`
  ADD CONSTRAINT `fk_request_target_companies_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_request_target_companies_request` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD CONSTRAINT `fk_role_permissions_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `services`
--
ALTER TABLE `services`
  ADD CONSTRAINT `fk_services_company_category` FOREIGN KEY (`company_category_id`) REFERENCES `company_catalog_categories` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `service_media`
--
ALTER TABLE `service_media`
  ADD CONSTRAINT `fk_service_media_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `service_specifications`
--
ALTER TABLE `service_specifications`
  ADD CONSTRAINT `fk_service_specifications_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `site_engineers`
--
ALTER TABLE `site_engineers`
  ADD CONSTRAINT `fk_site_engineers_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_site_engineers_company_staff` FOREIGN KEY (`company_staff_id`) REFERENCES `company_staff` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_site_engineers_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `staff_locations`
--
ALTER TABLE `staff_locations`
  ADD CONSTRAINT `staff_locations_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `instant_service_requests` (`id`);

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_language` FOREIGN KEY (`preferred_language_id`) REFERENCES `languages` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `user_roles`
--
ALTER TABLE `user_roles`
  ADD CONSTRAINT `fk_user_roles_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_user_roles_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `user_sessions`
--
ALTER TABLE `user_sessions`
  ADD CONSTRAINT `fk_user_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;