-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- مضيف: localhost:3306
-- وقت الجيل: 16 يوليو 2026 الساعة 20:05
-- إصدار الخادم: 8.0.46
-- نسخة PHP: 8.4.22

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 */;

--
-- قاعدة بيانات: `matchsys_finan`
--

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

--
-- بنية الجدول `accounting_rules`
--

CREATE TABLE `accounting_rules` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `event_code` varchar(100) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `source_module` varchar(80) NOT NULL,
  `version_no` int UNSIGNED NOT NULL DEFAULT '1',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `accounting_rule_lines`
--

CREATE TABLE `accounting_rule_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `accounting_rule_id` bigint UNSIGNED NOT NULL,
  `line_no` smallint UNSIGNED NOT NULL,
  `side` enum('debit','credit') NOT NULL,
  `account_role_id` bigint UNSIGNED NOT NULL,
  `amount_source` varchar(100) NOT NULL,
  `dimension_source` json DEFAULT NULL,
  `condition_expression` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `accounts`
--

CREATE TABLE `accounts` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `parent_id` bigint UNSIGNED DEFAULT NULL,
  `account_type_id` bigint UNSIGNED NOT NULL,
  `code` varchar(40) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `name_en` varchar(190) DEFAULT NULL,
  `is_postable` tinyint(1) NOT NULL DEFAULT '1',
  `control_type` enum('none','customer','supplier','subcontractor','employee','bank','cashbox','tax','retention','advance') NOT NULL DEFAULT 'none',
  `requires_partner` tinyint(1) NOT NULL DEFAULT '0',
  `requires_project` tinyint(1) NOT NULL DEFAULT '0',
  `requires_cost_code` tinyint(1) NOT NULL DEFAULT '0',
  `allows_manual_entries` tinyint(1) NOT NULL DEFAULT '1',
  `currency_id` bigint UNSIGNED DEFAULT NULL,
  `status` enum('active','inactive','blocked') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `account_roles`
--

CREATE TABLE `account_roles` (
  `id` bigint UNSIGNED NOT NULL,
  `role_code` varchar(80) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `description` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- إرجاع أو استيراد بيانات الجدول `account_roles`
--

INSERT INTO `account_roles` (`id`, `role_code`, `name_ar`, `description`) VALUES
(1, 'AR_CONTROL', 'مراقبة العملاء', 'الحساب الإجمالي لذمم العملاء'),
(2, 'AP_CONTROL', 'مراقبة الموردين', 'الحساب الإجمالي لذمم الموردين'),
(3, 'BANK', 'البنك', 'حساب البنك حسب الحساب البنكي'),
(4, 'CASH', 'الصندوق', 'حساب الصندوق حسب الفرع'),
(5, 'VAT_INPUT', 'ضريبة المدخلات', 'الضريبة القابلة للخصم'),
(6, 'VAT_OUTPUT', 'ضريبة المخرجات', 'الضريبة المستحقة على المبيعات'),
(7, 'CONSTRUCTION_REVENUE', 'إيرادات المقاولات', 'إيرادات أعمال العقود'),
(8, 'CONTRACT_ASSET', 'أصل العقد', 'أعمال منفذة غير مفوترة'),
(9, 'CONTRACT_LIABILITY', 'التزام العقد', 'مبالغ مقدمة أو مفوترة قبل الاستحقاق'),
(10, 'CUSTOMER_ADVANCES', 'دفعات مقدمة من العملاء', 'التزام الدفعات المقدمة من العملاء'),
(11, 'SUPPLIER_ADVANCES', 'دفعات مقدمة للموردين', 'أصل الدفعات المقدمة للموردين'),
(12, 'SUBCONTRACTOR_ADVANCES', 'دفعات مقدمة لمقاولي الباطن', 'أصل دفعات مقاولي الباطن'),
(13, 'RETENTION_RECEIVABLE', 'محتجزات مدينة', 'محتجزات لدى العملاء'),
(14, 'RETENTION_PAYABLE', 'محتجزات دائنة', 'محتجزات لمقاولي الباطن والموردين'),
(15, 'INVENTORY', 'المخزون', 'مخزون المواد'),
(16, 'GRNI', 'مستلم غير مفوتر', 'مواد أو خدمات مستلمة وغير مفوترة'),
(17, 'PROJECT_MATERIAL_COST', 'تكلفة مواد المشاريع', 'المواد المصروفة للمشاريع'),
(18, 'PROJECT_SUBCONTRACT_COST', 'تكلفة مقاولي الباطن', 'تكلفة أعمال مقاول الباطن'),
(19, 'PROJECT_LABOR_COST', 'تكلفة عمالة المشاريع', 'العمالة المباشرة'),
(20, 'PROJECT_EQUIPMENT_COST', 'تكلفة معدات المشاريع', 'تشغيل المعدات على المشاريع'),
(21, 'PAYROLL_PAYABLE', 'رواتب مستحقة', 'صافي الرواتب المستحقة'),
(22, 'FIXED_ASSETS', 'الأصول الثابتة', 'تكلفة الأصول الثابتة'),
(23, 'ACCUMULATED_DEPRECIATION', 'مجمع الإهلاك', 'مجمع إهلاك الأصول'),
(24, 'DEPRECIATION_EXPENSE', 'مصروف الإهلاك', 'مصروف إهلاك الفترة'),
(25, 'BANK_FEES', 'رسوم بنكية', 'رسوم وعمولات البنوك'),
(26, 'FX_GAIN', 'أرباح فروق العملة', 'أرباح إعادة التقييم والصرف'),
(27, 'FX_LOSS', 'خسائر فروق العملة', 'خسائر إعادة التقييم والصرف');

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

--
-- بنية الجدول `account_types`
--

CREATE TABLE `account_types` (
  `id` bigint UNSIGNED NOT NULL,
  `code` varchar(40) NOT NULL,
  `name_ar` varchar(100) NOT NULL,
  `statement_group` enum('balance_sheet','income_statement') NOT NULL,
  `normal_balance` enum('debit','credit') NOT NULL,
  `sort_order` smallint UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- إرجاع أو استيراد بيانات الجدول `account_types`
--

INSERT INTO `account_types` (`id`, `code`, `name_ar`, `statement_group`, `normal_balance`, `sort_order`) VALUES
(1, 'ASSET', 'الأصول', 'balance_sheet', 'debit', 10),
(2, 'LIABILITY', 'الالتزامات', 'balance_sheet', 'credit', 20),
(3, 'EQUITY', 'حقوق الملكية', 'balance_sheet', 'credit', 30),
(4, 'REVENUE', 'الإيرادات', 'income_statement', 'credit', 40),
(5, 'COST_OF_REVENUE', 'تكلفة الإيرادات', 'income_statement', 'debit', 50),
(6, 'EXPENSE', 'المصروفات', 'income_statement', 'debit', 60),
(7, 'OTHER_INCOME', 'إيرادات أخرى', 'income_statement', 'credit', 70),
(8, 'OTHER_EXPENSE', 'مصروفات أخرى', 'income_statement', 'debit', 80);

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

--
-- بنية الجدول `api_clients`
--

CREATE TABLE `api_clients` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `client_name` varchar(190) NOT NULL,
  `client_key` varchar(120) NOT NULL,
  `client_secret_hash` varchar(255) NOT NULL,
  `scopes_json` json NOT NULL,
  `allowed_ips_json` json DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `expires_at` datetime DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_used_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `approval_actions`
--

CREATE TABLE `approval_actions` (
  `id` bigint UNSIGNED NOT NULL,
  `approval_instance_id` bigint UNSIGNED NOT NULL,
  `workflow_step_id` bigint UNSIGNED NOT NULL,
  `action` enum('approve','reject','return','delegate','comment','cancel') NOT NULL,
  `acted_by` bigint UNSIGNED NOT NULL,
  `delegated_to` bigint UNSIGNED DEFAULT NULL,
  `comments` varchar(2000) DEFAULT NULL,
  `acted_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `document_snapshot` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `approval_instances`
--

CREATE TABLE `approval_instances` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `workflow_definition_id` bigint UNSIGNED NOT NULL,
  `document_type` varchar(80) NOT NULL,
  `document_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `document_amount` decimal(18,2) DEFAULT NULL,
  `current_step_no` smallint UNSIGNED NOT NULL DEFAULT '1',
  `status` enum('pending','approved','rejected','returned','cancelled') NOT NULL DEFAULT 'pending',
  `submitted_by` bigint UNSIGNED NOT NULL,
  `submitted_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` datetime DEFAULT NULL
) ;

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

--
-- بنية الجدول `assets`
--

CREATE TABLE `assets` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `asset_category_id` bigint UNSIGNED NOT NULL,
  `asset_no` varchar(60) NOT NULL,
  `name_ar` varchar(255) NOT NULL,
  `name_en` varchar(255) DEFAULT NULL,
  `serial_no` varchar(190) DEFAULT NULL,
  `manufacturer` varchar(150) DEFAULT NULL,
  `model` varchar(150) DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `capitalization_date` date DEFAULT NULL,
  `supplier_id` bigint UNSIGNED DEFAULT NULL,
  `supplier_invoice_id` bigint UNSIGNED DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `acquisition_cost` decimal(18,2) NOT NULL DEFAULT '0.00',
  `residual_value` decimal(18,2) NOT NULL DEFAULT '0.00',
  `depreciation_method` enum('straight_line','declining_balance','units_of_production','none') NOT NULL DEFAULT 'straight_line',
  `useful_life_months` int UNSIGNED DEFAULT NULL,
  `accumulated_depreciation` decimal(18,2) NOT NULL DEFAULT '0.00',
  `net_book_value` decimal(18,2) NOT NULL DEFAULT '0.00',
  `current_project_id` bigint UNSIGNED DEFAULT NULL,
  `current_location` varchar(255) DEFAULT NULL,
  `custodian_employee_id` bigint UNSIGNED DEFAULT NULL,
  `asset_status` enum('under_construction','active','idle','under_maintenance','disposed','sold','written_off') NOT NULL DEFAULT 'active',
  `is_equipment` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL
) ;

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

--
-- بنية الجدول `asset_categories`
--

CREATE TABLE `asset_categories` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `code` varchar(40) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `name_en` varchar(190) DEFAULT NULL,
  `asset_account_id` bigint UNSIGNED NOT NULL,
  `accumulated_depreciation_account_id` bigint UNSIGNED NOT NULL,
  `depreciation_expense_account_id` bigint UNSIGNED NOT NULL,
  `default_method` enum('straight_line','declining_balance','units_of_production','none') NOT NULL DEFAULT 'straight_line',
  `default_useful_life_months` int UNSIGNED DEFAULT NULL,
  `default_residual_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `is_active` tinyint(1) NOT NULL DEFAULT '1'
) ;

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

--
-- بنية الجدول `attachments`
--

CREATE TABLE `attachments` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `object_type` varchar(80) NOT NULL,
  `object_id` bigint UNSIGNED NOT NULL,
  `document_category` varchar(80) DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `stored_name` varchar(255) NOT NULL,
  `file_path` varchar(1000) NOT NULL,
  `mime_type` varchar(190) NOT NULL,
  `file_size` bigint UNSIGNED NOT NULL,
  `file_hash` varchar(128) NOT NULL,
  `version_no` int UNSIGNED NOT NULL DEFAULT '1',
  `is_confidential` tinyint(1) NOT NULL DEFAULT '0',
  `uploaded_by` bigint UNSIGNED NOT NULL,
  `uploaded_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `audit_logs`
--

CREATE TABLE `audit_logs` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `event_type` varchar(100) NOT NULL,
  `object_type` varchar(80) NOT NULL,
  `object_id` bigint UNSIGNED DEFAULT NULL,
  `event_description` varchar(1000) DEFAULT NULL,
  `old_values` json DEFAULT NULL,
  `new_values` json DEFAULT NULL,
  `ip_address` varchar(64) DEFAULT NULL,
  `user_agent` varchar(1000) DEFAULT NULL,
  `request_id` varchar(100) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `bank_accounts`
--

CREATE TABLE `bank_accounts` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `bank_partner_id` bigint UNSIGNED DEFAULT NULL,
  `code` varchar(40) NOT NULL,
  `bank_name` varchar(190) NOT NULL,
  `account_name` varchar(190) DEFAULT NULL,
  `iban` varchar(34) DEFAULT NULL,
  `account_no` varchar(80) DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `account_id` bigint UNSIGNED NOT NULL,
  `opening_balance` decimal(18,2) NOT NULL DEFAULT '0.00',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `bank_reconciliations`
--

CREATE TABLE `bank_reconciliations` (
  `id` bigint UNSIGNED NOT NULL,
  `bank_account_id` bigint UNSIGNED NOT NULL,
  `bank_statement_import_id` bigint UNSIGNED NOT NULL,
  `reconciliation_date` date NOT NULL,
  `book_balance` decimal(18,2) NOT NULL,
  `statement_balance` decimal(18,2) NOT NULL,
  `difference_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` enum('draft','reviewed','approved') NOT NULL DEFAULT 'draft',
  `prepared_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `bank_reconciliation_lines`
--

CREATE TABLE `bank_reconciliation_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `bank_reconciliation_id` bigint UNSIGNED NOT NULL,
  `bank_statement_line_id` bigint UNSIGNED NOT NULL,
  `receipt_voucher_id` bigint UNSIGNED DEFAULT NULL,
  `payment_voucher_id` bigint UNSIGNED DEFAULT NULL,
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `matched_amount` decimal(18,2) NOT NULL,
  `match_type` enum('exact','partial','combined','manual') NOT NULL DEFAULT 'exact',
  `matched_by` bigint UNSIGNED NOT NULL,
  `matched_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `bank_statement_imports`
--

CREATE TABLE `bank_statement_imports` (
  `id` bigint UNSIGNED NOT NULL,
  `bank_account_id` bigint UNSIGNED NOT NULL,
  `statement_from` date NOT NULL,
  `statement_to` date NOT NULL,
  `opening_balance` decimal(18,2) NOT NULL,
  `closing_balance` decimal(18,2) NOT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `file_hash` varchar(128) DEFAULT NULL,
  `imported_by` bigint UNSIGNED NOT NULL,
  `imported_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('imported','partially_reconciled','reconciled','cancelled') NOT NULL DEFAULT 'imported'
) ;

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

--
-- بنية الجدول `bank_statement_lines`
--

CREATE TABLE `bank_statement_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `bank_statement_import_id` bigint UNSIGNED NOT NULL,
  `line_no` int UNSIGNED NOT NULL,
  `transaction_date` date NOT NULL,
  `value_date` date DEFAULT NULL,
  `bank_reference` varchar(190) DEFAULT NULL,
  `description` varchar(1000) DEFAULT NULL,
  `debit_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `credit_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `running_balance` decimal(18,2) DEFAULT NULL,
  `reconciliation_status` enum('unmatched','suggested','matched','ignored') NOT NULL DEFAULT 'unmatched'
) ;

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

--
-- بنية الجدول `branches`
--

CREATE TABLE `branches` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `code` varchar(30) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `name_en` varchar(190) DEFAULT NULL,
  `address_line` varchar(500) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `phone` varchar(40) DEFAULT NULL,
  `is_head_office` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `business_partners`
--

CREATE TABLE `business_partners` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `partner_code` varchar(50) NOT NULL,
  `legal_name_ar` varchar(255) NOT NULL,
  `legal_name_en` varchar(255) DEFAULT NULL,
  `trade_name` varchar(255) DEFAULT NULL,
  `partner_kind` enum('organization','individual') NOT NULL DEFAULT 'organization',
  `commercial_registration_no` varchar(50) DEFAULT NULL,
  `vat_registration_no` varchar(30) DEFAULT NULL,
  `national_id` varchar(30) DEFAULT NULL,
  `country_code` char(2) NOT NULL DEFAULT 'SA',
  `city` varchar(100) DEFAULT NULL,
  `address_line` varchar(500) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `phone` varchar(40) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `default_currency_id` bigint UNSIGNED DEFAULT NULL,
  `payment_terms_days` smallint UNSIGNED NOT NULL DEFAULT '0',
  `credit_limit` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` enum('active','inactive','blocked') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `cashboxes`
--

CREATE TABLE `cashboxes` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `code` varchar(40) NOT NULL,
  `name_ar` varchar(150) NOT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `account_id` bigint UNSIGNED NOT NULL,
  `custodian_user_id` bigint UNSIGNED DEFAULT NULL,
  `max_balance` decimal(18,2) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1'
) ;

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

--
-- بنية الجدول `cash_flow_forecasts`
--

CREATE TABLE `cash_flow_forecasts` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `forecast_date` date NOT NULL,
  `horizon_days` int UNSIGNED NOT NULL DEFAULT '90',
  `scenario` enum('base','conservative','optimistic') NOT NULL DEFAULT 'base',
  `status` enum('draft','approved','superseded') NOT NULL DEFAULT 'draft',
  `opening_cash` decimal(18,2) NOT NULL DEFAULT '0.00',
  `prepared_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `cash_flow_forecast_lines`
--

CREATE TABLE `cash_flow_forecast_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `cash_flow_forecast_id` bigint UNSIGNED NOT NULL,
  `flow_date` date NOT NULL,
  `flow_type` enum('inflow','outflow') NOT NULL,
  `source_type` varchar(80) DEFAULT NULL,
  `source_id` bigint UNSIGNED DEFAULT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `description` varchar(500) NOT NULL,
  `amount` decimal(18,2) NOT NULL,
  `probability_pct` decimal(9,4) NOT NULL DEFAULT '100.0000',
  `is_manual` tinyint(1) NOT NULL DEFAULT '0'
) ;

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

--
-- بنية الجدول `cheques`
--

CREATE TABLE `cheques` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `bank_account_id` bigint UNSIGNED NOT NULL,
  `cheque_no` varchar(80) NOT NULL,
  `cheque_type` enum('issued','received') NOT NULL,
  `partner_id` bigint UNSIGNED DEFAULT NULL,
  `amount` decimal(18,2) NOT NULL,
  `issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `receipt_voucher_id` bigint UNSIGNED DEFAULT NULL,
  `payment_voucher_id` bigint UNSIGNED DEFAULT NULL,
  `status` enum('draft','issued','received','deposited','cleared','returned','cancelled','stale') NOT NULL DEFAULT 'draft',
  `cleared_on` date DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `closing_checklists`
--

CREATE TABLE `closing_checklists` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `fiscal_period_id` bigint UNSIGNED NOT NULL,
  `status` enum('draft','in_progress','ready','closed') NOT NULL DEFAULT 'draft',
  `prepared_by` bigint UNSIGNED DEFAULT NULL,
  `reviewed_by` bigint UNSIGNED DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `closing_checklist_items`
--

CREATE TABLE `closing_checklist_items` (
  `id` bigint UNSIGNED NOT NULL,
  `closing_checklist_id` bigint UNSIGNED NOT NULL,
  `item_code` varchar(80) NOT NULL,
  `item_name_ar` varchar(190) NOT NULL,
  `status` enum('pending','completed','waived','blocked') NOT NULL DEFAULT 'pending',
  `completed_by` bigint UNSIGNED DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `notes` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `companies`
--

CREATE TABLE `companies` (
  `id` bigint UNSIGNED NOT NULL,
  `legal_name_ar` varchar(255) NOT NULL,
  `legal_name_en` varchar(255) DEFAULT NULL,
  `trade_name_ar` varchar(255) DEFAULT NULL,
  `trade_name_en` varchar(255) DEFAULT NULL,
  `commercial_registration_no` varchar(50) DEFAULT NULL,
  `vat_registration_no` varchar(30) DEFAULT NULL,
  `unified_national_no` varchar(30) DEFAULT NULL,
  `national_address_short_code` varchar(20) DEFAULT NULL,
  `address_line` varchar(500) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `country_code` char(2) NOT NULL DEFAULT 'SA',
  `phone` varchar(40) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `website` varchar(190) DEFAULT NULL,
  `base_currency_id` bigint UNSIGNED NOT NULL,
  `fiscal_year_start_month` tinyint UNSIGNED NOT NULL DEFAULT '1',
  `timezone` varchar(50) NOT NULL DEFAULT 'Asia/Riyadh',
  `logo_path` varchar(500) DEFAULT NULL,
  `status` enum('active','suspended','closed') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL
) ;

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

--
-- بنية الجدول `company_account_mappings`
--

CREATE TABLE `company_account_mappings` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `account_role_id` bigint UNSIGNED NOT NULL,
  `account_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `contract_boq_items`
--

CREATE TABLE `contract_boq_items` (
  `id` bigint UNSIGNED NOT NULL,
  `contract_id` bigint UNSIGNED NOT NULL,
  `parent_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `item_code` varchar(80) NOT NULL,
  `description_ar` varchar(1000) NOT NULL,
  `description_en` varchar(1000) DEFAULT NULL,
  `unit_code` varchar(30) DEFAULT NULL,
  `quantity` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `unit_rate` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `tax_pct` decimal(9,4) NOT NULL DEFAULT '15.0000',
  `sort_order` int UNSIGNED NOT NULL DEFAULT '0',
  `is_provisional` tinyint(1) NOT NULL DEFAULT '0'
) ;

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

--
-- بنية الجدول `corporate_budget_lines`
--

CREATE TABLE `corporate_budget_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `corporate_budget_version_id` bigint UNSIGNED NOT NULL,
  `fiscal_period_id` bigint UNSIGNED NOT NULL,
  `account_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `department_id` bigint UNSIGNED DEFAULT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `cost_center_id` bigint UNSIGNED DEFAULT NULL,
  `budget_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `notes` varchar(500) DEFAULT NULL
) ;

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

--
-- بنية الجدول `corporate_budget_versions`
--

CREATE TABLE `corporate_budget_versions` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `fiscal_year_id` bigint UNSIGNED NOT NULL,
  `version_no` int UNSIGNED NOT NULL,
  `version_name` varchar(190) NOT NULL,
  `scenario` enum('base','conservative','optimistic','reforecast') NOT NULL DEFAULT 'base',
  `status` enum('draft','submitted','approved','superseded','rejected') NOT NULL DEFAULT 'draft',
  `total_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `cost_centers`
--

CREATE TABLE `cost_centers` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `parent_id` bigint UNSIGNED DEFAULT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `code` varchar(40) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `name_en` varchar(190) DEFAULT NULL,
  `center_type` enum('project','department','administrative','equipment','other') NOT NULL DEFAULT 'other',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `cost_codes`
--

CREATE TABLE `cost_codes` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `parent_id` bigint UNSIGNED DEFAULT NULL,
  `code` varchar(50) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `name_en` varchar(190) DEFAULT NULL,
  `cost_category` enum('material','labor','subcontract','equipment','overhead','other') NOT NULL,
  `is_postable` tinyint(1) NOT NULL DEFAULT '1',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `currencies`
--

CREATE TABLE `currencies` (
  `id` bigint UNSIGNED NOT NULL,
  `code` char(3) NOT NULL,
  `name_ar` varchar(100) NOT NULL,
  `name_en` varchar(100) DEFAULT NULL,
  `symbol` varchar(12) DEFAULT NULL,
  `decimal_places` tinyint UNSIGNED NOT NULL DEFAULT '2',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

--
-- إرجاع أو استيراد بيانات الجدول `currencies`
--

INSERT INTO `currencies` (`id`, `code`, `name_ar`, `name_en`, `symbol`, `decimal_places`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'SAR', 'الريال السعودي', 'Saudi Riyal', 'ر.س', 2, 1, '2026-07-16 18:54:39', '2026-07-16 18:54:39'),
(2, 'USD', 'الدولار الأمريكي', 'US Dollar', '$', 2, 1, '2026-07-16 18:54:39', '2026-07-16 18:54:39'),
(3, 'EUR', 'اليورو', 'Euro', '€', 2, 1, '2026-07-16 18:54:39', '2026-07-16 18:54:39'),
(4, 'AED', 'الدرهم الإماراتي', 'UAE Dirham', 'د.إ', 2, 1, '2026-07-16 18:54:39', '2026-07-16 18:54:39');

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

--
-- بنية الجدول `custodies`
--

CREATE TABLE `custodies` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `employee_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `custody_no` varchar(60) NOT NULL,
  `custody_type` enum('permanent','temporary','travel','project') NOT NULL DEFAULT 'temporary',
  `currency_id` bigint UNSIGNED NOT NULL,
  `approved_limit` decimal(18,2) NOT NULL,
  `issued_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `settled_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `outstanding_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `issued_on` date NOT NULL,
  `due_on` date DEFAULT NULL,
  `status` enum('draft','submitted','approved','open','partially_settled','settled','overdue','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `custody_settlements`
--

CREATE TABLE `custody_settlements` (
  `id` bigint UNSIGNED NOT NULL,
  `custody_id` bigint UNSIGNED NOT NULL,
  `settlement_no` varchar(60) NOT NULL,
  `settlement_date` date NOT NULL,
  `total_expense_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `amount_returned` decimal(18,2) NOT NULL DEFAULT '0.00',
  `amount_reimbursed` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` enum('draft','submitted','approved','posted','rejected','cancelled') NOT NULL DEFAULT 'draft',
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `prepared_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `custody_settlement_lines`
--

CREATE TABLE `custody_settlement_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `custody_settlement_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `expense_account_id` bigint UNSIGNED NOT NULL,
  `business_partner_id` bigint UNSIGNED DEFAULT NULL,
  `expense_date` date NOT NULL,
  `description` varchar(1000) NOT NULL,
  `receipt_no` varchar(100) DEFAULT NULL,
  `amount_before_tax` decimal(18,2) NOT NULL,
  `tax_code_id` bigint UNSIGNED DEFAULT NULL,
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(18,2) NOT NULL
) ;

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

--
-- بنية الجدول `customers`
--

CREATE TABLE `customers` (
  `id` bigint UNSIGNED NOT NULL,
  `partner_id` bigint UNSIGNED NOT NULL,
  `customer_type` enum('private','government','semi_government','individual') NOT NULL DEFAULT 'private',
  `receivable_account_id` bigint UNSIGNED DEFAULT NULL,
  `retention_account_id` bigint UNSIGNED DEFAULT NULL,
  `advance_account_id` bigint UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `customer_certificates`
--

CREATE TABLE `customer_certificates` (
  `id` bigint UNSIGNED NOT NULL,
  `contract_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED NOT NULL,
  `certificate_no` varchar(80) NOT NULL,
  `certificate_type` enum('interim','advance','final','retention_release') NOT NULL DEFAULT 'interim',
  `period_from` date DEFAULT NULL,
  `period_to` date NOT NULL,
  `internal_date` date NOT NULL,
  `submitted_on` date DEFAULT NULL,
  `certified_on` date DEFAULT NULL,
  `previous_work_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `current_work_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `materials_on_site_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `variation_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `claimed_gross_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `certified_gross_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `advance_recovery_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `retention_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `penalty_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `other_deductions_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `taxable_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `net_due_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` enum('draft','internal_review','submitted','under_certification','partially_certified','certified','invoiced','partially_collected','collected','rejected','cancelled') NOT NULL DEFAULT 'draft',
  `prepared_by` bigint UNSIGNED NOT NULL,
  `reviewed_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `customer_certificate_adjustments`
--

CREATE TABLE `customer_certificate_adjustments` (
  `id` bigint UNSIGNED NOT NULL,
  `customer_certificate_id` bigint UNSIGNED NOT NULL,
  `adjustment_type` enum('advance_recovery','retention','penalty','materials_deduction','other') NOT NULL,
  `description` varchar(500) NOT NULL,
  `amount` decimal(18,2) NOT NULL,
  `account_id` bigint UNSIGNED DEFAULT NULL
) ;

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

--
-- بنية الجدول `customer_certificate_items`
--

CREATE TABLE `customer_certificate_items` (
  `id` bigint UNSIGNED NOT NULL,
  `customer_certificate_id` bigint UNSIGNED NOT NULL,
  `contract_boq_item_id` bigint UNSIGNED NOT NULL,
  `previous_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `current_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `cumulative_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `unit_rate` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `claimed_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `certified_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `progress_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `notes` varchar(500) DEFAULT NULL
) ;

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

--
-- بنية الجدول `departments`
--

CREATE TABLE `departments` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `parent_id` bigint UNSIGNED DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `name_en` varchar(190) DEFAULT NULL,
  `manager_user_id` bigint UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `depreciation_runs`
--

CREATE TABLE `depreciation_runs` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `fiscal_period_id` bigint UNSIGNED NOT NULL,
  `run_no` varchar(60) NOT NULL,
  `run_date` date NOT NULL,
  `status` enum('draft','calculated','reviewed','posted','cancelled') NOT NULL DEFAULT 'draft',
  `total_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `prepared_by` bigint UNSIGNED NOT NULL,
  `reviewed_by` bigint UNSIGNED DEFAULT NULL,
  `posted_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `depreciation_run_lines`
--

CREATE TABLE `depreciation_run_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `depreciation_run_id` bigint UNSIGNED NOT NULL,
  `asset_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `opening_net_book_value` decimal(18,2) NOT NULL,
  `depreciation_amount` decimal(18,2) NOT NULL,
  `closing_net_book_value` decimal(18,2) NOT NULL
) ;

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

--
-- بنية الجدول `document_sequences`
--

CREATE TABLE `document_sequences` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `document_type` varchar(80) NOT NULL,
  `fiscal_year_id` bigint UNSIGNED DEFAULT NULL,
  `prefix` varchar(40) DEFAULT NULL,
  `next_number` bigint UNSIGNED NOT NULL DEFAULT '1',
  `padding` tinyint UNSIGNED NOT NULL DEFAULT '6',
  `suffix` varchar(40) DEFAULT NULL,
  `reset_policy` enum('never','yearly','monthly') NOT NULL DEFAULT 'yearly',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `electronic_invoices`
--

CREATE TABLE `electronic_invoices` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `sales_invoice_id` bigint UNSIGNED NOT NULL,
  `uuid` char(36) NOT NULL,
  `invoice_counter_value` bigint UNSIGNED DEFAULT NULL,
  `previous_invoice_hash` varchar(500) DEFAULT NULL,
  `invoice_hash` varchar(500) DEFAULT NULL,
  `qr_code_base64` longtext,
  `xml_path` varchar(500) DEFAULT NULL,
  `pdfa3_path` varchar(500) DEFAULT NULL,
  `cryptographic_stamp` varchar(2000) DEFAULT NULL,
  `clearance_status` enum('not_started','pending','cleared','reported','warning','rejected','failed') NOT NULL DEFAULT 'not_started',
  `cleared_or_reported_at` datetime DEFAULT NULL,
  `last_error_code` varchar(100) DEFAULT NULL,
  `last_error_message` varchar(2000) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `electronic_invoice_submissions`
--

CREATE TABLE `electronic_invoice_submissions` (
  `id` bigint UNSIGNED NOT NULL,
  `electronic_invoice_id` bigint UNSIGNED NOT NULL,
  `submission_type` enum('clearance','reporting','compliance','retry') NOT NULL,
  `request_id` varchar(190) DEFAULT NULL,
  `request_payload_path` varchar(500) DEFAULT NULL,
  `response_status_code` int DEFAULT NULL,
  `response_payload` json DEFAULT NULL,
  `warnings_json` json DEFAULT NULL,
  `errors_json` json DEFAULT NULL,
  `submitted_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` datetime DEFAULT NULL,
  `is_success` tinyint(1) NOT NULL DEFAULT '0',
  `retry_no` int UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `employees`
--

CREATE TABLE `employees` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `partner_id` bigint UNSIGNED DEFAULT NULL,
  `employee_no` varchar(50) NOT NULL,
  `full_name` varchar(190) NOT NULL,
  `job_title` varchar(150) DEFAULT NULL,
  `department_id` bigint UNSIGNED DEFAULT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `employment_status` enum('active','on_leave','suspended','terminated') NOT NULL DEFAULT 'active',
  `default_project_id` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `equipment_fuel_logs`
--

CREATE TABLE `equipment_fuel_logs` (
  `id` bigint UNSIGNED NOT NULL,
  `asset_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `fuel_date` date NOT NULL,
  `fuel_type` varchar(60) NOT NULL,
  `quantity` decimal(18,4) NOT NULL,
  `unit_cost` decimal(18,4) NOT NULL,
  `total_cost` decimal(18,2) NOT NULL,
  `meter_reading` decimal(18,2) DEFAULT NULL,
  `supplier_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `equipment_maintenance`
--

CREATE TABLE `equipment_maintenance` (
  `id` bigint UNSIGNED NOT NULL,
  `asset_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `maintenance_no` varchar(60) NOT NULL,
  `maintenance_type` enum('preventive','corrective','inspection','accident','other') NOT NULL,
  `opened_on` date NOT NULL,
  `completed_on` date DEFAULT NULL,
  `vendor_id` bigint UNSIGNED DEFAULT NULL,
  `description` varchar(1000) NOT NULL,
  `parts_cost` decimal(18,2) NOT NULL DEFAULT '0.00',
  `labor_cost` decimal(18,2) NOT NULL DEFAULT '0.00',
  `other_cost` decimal(18,2) NOT NULL DEFAULT '0.00',
  `downtime_hours` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` enum('open','in_progress','completed','cancelled') NOT NULL DEFAULT 'open',
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `equipment_usage_logs`
--

CREATE TABLE `equipment_usage_logs` (
  `id` bigint UNSIGNED NOT NULL,
  `asset_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED NOT NULL,
  `usage_date` date NOT NULL,
  `start_meter` decimal(18,2) DEFAULT NULL,
  `end_meter` decimal(18,2) DEFAULT NULL,
  `usage_hours` decimal(18,2) NOT NULL DEFAULT '0.00',
  `idle_hours` decimal(18,2) NOT NULL DEFAULT '0.00',
  `internal_rate` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `cost_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `operator_employee_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `exchange_rates`
--

CREATE TABLE `exchange_rates` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `rate_date` date NOT NULL,
  `rate_to_base` decimal(18,8) NOT NULL,
  `source` varchar(100) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `fiscal_periods`
--

CREATE TABLE `fiscal_periods` (
  `id` bigint UNSIGNED NOT NULL,
  `fiscal_year_id` bigint UNSIGNED NOT NULL,
  `period_no` tinyint UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `starts_on` date NOT NULL,
  `ends_on` date NOT NULL,
  `status` enum('open','soft_closed','closed') NOT NULL DEFAULT 'open',
  `closed_at` datetime DEFAULT NULL,
  `closed_by` bigint UNSIGNED DEFAULT NULL
) ;

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

--
-- بنية الجدول `fiscal_years`
--

CREATE TABLE `fiscal_years` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `code` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `starts_on` date NOT NULL,
  `ends_on` date NOT NULL,
  `status` enum('planned','open','soft_closed','closed') NOT NULL DEFAULT 'planned',
  `closed_at` datetime DEFAULT NULL,
  `closed_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `goods_receipts`
--

CREATE TABLE `goods_receipts` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `warehouse_id` bigint UNSIGNED DEFAULT NULL,
  `supplier_id` bigint UNSIGNED NOT NULL,
  `purchase_order_id` bigint UNSIGNED DEFAULT NULL,
  `receipt_no` varchar(60) NOT NULL,
  `receipt_date` date NOT NULL,
  `supplier_delivery_note` varchar(100) DEFAULT NULL,
  `inspection_status` enum('pending','accepted','partially_accepted','rejected','not_required') NOT NULL DEFAULT 'pending',
  `status` enum('draft','submitted','approved','posted','cancelled') NOT NULL DEFAULT 'draft',
  `received_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `goods_receipt_items`
--

CREATE TABLE `goods_receipt_items` (
  `id` bigint UNSIGNED NOT NULL,
  `goods_receipt_id` bigint UNSIGNED NOT NULL,
  `purchase_order_item_id` bigint UNSIGNED DEFAULT NULL,
  `item_id` bigint UNSIGNED DEFAULT NULL,
  `description` varchar(1000) NOT NULL,
  `received_qty` decimal(18,4) NOT NULL,
  `accepted_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `rejected_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `unit_code` varchar(30) NOT NULL,
  `unit_cost` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `lot_no` varchar(100) DEFAULT NULL,
  `serial_no` varchar(190) DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL
) ;

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

--
-- بنية الجدول `guarantees`
--

CREATE TABLE `guarantees` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `tender_id` bigint UNSIGNED DEFAULT NULL,
  `partner_id` bigint UNSIGNED DEFAULT NULL,
  `guarantee_no` varchar(100) NOT NULL,
  `guarantee_type` enum('bid_bond','advance_payment','performance','maintenance','payment','other') NOT NULL,
  `direction` enum('issued','received') NOT NULL,
  `bank_partner_id` bigint UNSIGNED DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `original_amount` decimal(18,2) NOT NULL,
  `current_amount` decimal(18,2) NOT NULL,
  `coverage_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `issued_on` date NOT NULL,
  `expires_on` date DEFAULT NULL,
  `status` enum('draft','active','extended','reduced','released','called','expired','cancelled') NOT NULL DEFAULT 'active',
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `insurance_policies`
--

CREATE TABLE `insurance_policies` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `insurer_partner_id` bigint UNSIGNED NOT NULL,
  `policy_no` varchar(100) NOT NULL,
  `policy_type` varchar(100) NOT NULL,
  `coverage_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `premium_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `starts_on` date NOT NULL,
  `expires_on` date NOT NULL,
  `status` enum('draft','active','expired','cancelled','claimed') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `integration_logs`
--

CREATE TABLE `integration_logs` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `integration_code` varchar(80) NOT NULL,
  `direction` enum('inbound','outbound') NOT NULL,
  `operation` varchar(100) NOT NULL,
  `object_type` varchar(80) DEFAULT NULL,
  `object_id` bigint UNSIGNED DEFAULT NULL,
  `request_id` varchar(190) DEFAULT NULL,
  `request_payload` json DEFAULT NULL,
  `response_status` int DEFAULT NULL,
  `response_payload` json DEFAULT NULL,
  `status` enum('started','success','warning','failed','retrying') NOT NULL DEFAULT 'started',
  `error_message` varchar(2000) DEFAULT NULL,
  `started_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `items`
--

CREATE TABLE `items` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `category_id` bigint UNSIGNED DEFAULT NULL,
  `item_code` varchar(60) NOT NULL,
  `name_ar` varchar(255) NOT NULL,
  `name_en` varchar(255) DEFAULT NULL,
  `item_type` enum('inventory','non_inventory','service','asset','fuel','spare_part') NOT NULL DEFAULT 'inventory',
  `base_unit_code` varchar(30) NOT NULL,
  `purchase_unit_code` varchar(30) DEFAULT NULL,
  `purchase_to_base_factor` decimal(18,6) NOT NULL DEFAULT '1.000000',
  `valuation_method` enum('weighted_average','fifo','specific') NOT NULL DEFAULT 'weighted_average',
  `default_cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `default_tax_code_id` bigint UNSIGNED DEFAULT NULL,
  `reorder_level` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `minimum_stock` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `maximum_stock` decimal(18,4) DEFAULT NULL,
  `track_lot` tinyint(1) NOT NULL DEFAULT '0',
  `track_serial` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL
) ;

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

--
-- بنية الجدول `item_categories`
--

CREATE TABLE `item_categories` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `parent_id` bigint UNSIGNED DEFAULT NULL,
  `code` varchar(40) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `name_en` varchar(190) DEFAULT NULL,
  `inventory_account_id` bigint UNSIGNED DEFAULT NULL,
  `consumption_account_id` bigint UNSIGNED DEFAULT NULL,
  `variance_account_id` bigint UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `journal_books`
--

CREATE TABLE `journal_books` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `code` varchar(30) NOT NULL,
  `name_ar` varchar(150) NOT NULL,
  `name_en` varchar(150) DEFAULT NULL,
  `book_type` enum('general','sales','purchases','cash','bank','payroll','inventory','assets','projects','tax') NOT NULL DEFAULT 'general',
  `sequence_id` bigint UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `journal_entries`
--

CREATE TABLE `journal_entries` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `fiscal_period_id` bigint UNSIGNED NOT NULL,
  `journal_book_id` bigint UNSIGNED NOT NULL,
  `entry_no` varchar(60) NOT NULL,
  `entry_date` date NOT NULL,
  `description` varchar(500) NOT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `exchange_rate` decimal(18,8) NOT NULL DEFAULT '1.00000000',
  `source_type` varchar(80) DEFAULT NULL,
  `source_id` bigint UNSIGNED DEFAULT NULL,
  `idempotency_key` varchar(120) DEFAULT NULL,
  `status` enum('draft','submitted','approved','posted','reversed','cancelled') NOT NULL DEFAULT 'draft',
  `reversing_entry_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `posted_by` bigint UNSIGNED DEFAULT NULL,
  `posted_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `journal_lines`
--

CREATE TABLE `journal_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `journal_entry_id` bigint UNSIGNED NOT NULL,
  `line_no` smallint UNSIGNED NOT NULL,
  `account_id` bigint UNSIGNED NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `debit_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `credit_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `foreign_debit_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `foreign_credit_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `business_partner_id` bigint UNSIGNED DEFAULT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `cost_center_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `department_id` bigint UNSIGNED DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `labor_allocations`
--

CREATE TABLE `labor_allocations` (
  `id` bigint UNSIGNED NOT NULL,
  `payroll_item_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `department_id` bigint UNSIGNED DEFAULT NULL,
  `allocation_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `allocated_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `work_hours` decimal(18,2) NOT NULL DEFAULT '0.00'
) ;

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

--
-- بنية الجدول `notifications`
--

CREATE TABLE `notifications` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED NOT NULL,
  `notification_type` varchar(80) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` varchar(2000) NOT NULL,
  `object_type` varchar(80) DEFAULT NULL,
  `object_id` bigint UNSIGNED DEFAULT NULL,
  `priority` enum('low','normal','high','critical') NOT NULL DEFAULT 'normal',
  `read_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `partner_bank_accounts`
--

CREATE TABLE `partner_bank_accounts` (
  `id` bigint UNSIGNED NOT NULL,
  `partner_id` bigint UNSIGNED NOT NULL,
  `bank_name` varchar(190) NOT NULL,
  `account_name` varchar(190) DEFAULT NULL,
  `iban` varchar(34) DEFAULT NULL,
  `account_no` varchar(80) DEFAULT NULL,
  `currency_id` bigint UNSIGNED DEFAULT NULL,
  `is_verified` tinyint(1) NOT NULL DEFAULT '0',
  `verified_by` bigint UNSIGNED DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  `is_primary` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `partner_contacts`
--

CREATE TABLE `partner_contacts` (
  `id` bigint UNSIGNED NOT NULL,
  `partner_id` bigint UNSIGNED NOT NULL,
  `full_name` varchar(190) NOT NULL,
  `job_title` varchar(150) DEFAULT NULL,
  `mobile` varchar(40) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `is_primary` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `payment_allocations`
--

CREATE TABLE `payment_allocations` (
  `id` bigint UNSIGNED NOT NULL,
  `payment_voucher_id` bigint UNSIGNED NOT NULL,
  `supplier_invoice_id` bigint UNSIGNED DEFAULT NULL,
  `subcontract_certificate_id` bigint UNSIGNED DEFAULT NULL,
  `allocation_type` enum('supplier_invoice','subcontract_certificate','advance','retention','custody','unallocated') NOT NULL,
  `allocated_amount` decimal(18,2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `payment_vouchers`
--

CREATE TABLE `payment_vouchers` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `partner_id` bigint UNSIGNED DEFAULT NULL,
  `payment_no` varchar(60) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method` enum('bank_transfer','cash','cheque','card','other') NOT NULL,
  `bank_account_id` bigint UNSIGNED DEFAULT NULL,
  `cashbox_id` bigint UNSIGNED DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `exchange_rate` decimal(18,8) NOT NULL DEFAULT '1.00000000',
  `amount` decimal(18,2) NOT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `status` enum('draft','submitted','approved','posted','cancelled','reversed') NOT NULL DEFAULT 'draft',
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `payroll_items`
--

CREATE TABLE `payroll_items` (
  `id` bigint UNSIGNED NOT NULL,
  `payroll_run_id` bigint UNSIGNED NOT NULL,
  `employee_id` bigint UNSIGNED NOT NULL,
  `basic_salary` decimal(18,2) NOT NULL DEFAULT '0.00',
  `allowances_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `overtime_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `deductions_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `employer_contributions` decimal(18,2) NOT NULL DEFAULT '0.00',
  `net_salary` decimal(18,2) NOT NULL DEFAULT '0.00',
  `payment_status` enum('pending','paid','held') NOT NULL DEFAULT 'pending'
) ;

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

--
-- بنية الجدول `payroll_runs`
--

CREATE TABLE `payroll_runs` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `fiscal_period_id` bigint UNSIGNED NOT NULL,
  `payroll_no` varchar(60) NOT NULL,
  `period_start` date NOT NULL,
  `period_end` date NOT NULL,
  `payment_date` date DEFAULT NULL,
  `status` enum('draft','calculated','reviewed','approved','posted','paid','cancelled') NOT NULL DEFAULT 'draft',
  `gross_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `deductions_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `employer_cost_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `net_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `prepared_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `permissions`
--

CREATE TABLE `permissions` (
  `id` bigint UNSIGNED NOT NULL,
  `code` varchar(120) NOT NULL,
  `module_code` varchar(60) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `description` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- إرجاع أو استيراد بيانات الجدول `permissions`
--

INSERT INTO `permissions` (`id`, `code`, `module_code`, `name_ar`, `description`) VALUES
(1, 'dashboard.view_executive', 'dashboard', 'عرض لوحة الإدارة العليا', NULL),
(2, 'finance.accounts.manage', 'finance', 'إدارة شجرة الحسابات', NULL),
(3, 'finance.journals.create', 'finance', 'إنشاء القيود', NULL),
(4, 'finance.journals.approve', 'finance', 'اعتماد القيود', NULL),
(5, 'finance.journals.post', 'finance', 'ترحيل القيود', NULL),
(6, 'finance.periods.close', 'finance', 'إقفال الفترات', NULL),
(7, 'projects.view_financials', 'projects', 'عرض ماليات المشاريع', NULL),
(8, 'projects.budgets.manage', 'projects', 'إدارة موازنات المشاريع', NULL),
(9, 'projects.budgets.override', 'projects', 'تجاوز الموازنة', NULL),
(10, 'contracts.manage', 'contracts', 'إدارة العقود', NULL),
(11, 'certificates.customer.manage', 'certificates', 'إدارة مستخلصات العملاء', NULL),
(12, 'certificates.subcontract.manage', 'certificates', 'إدارة مستخلصات مقاولي الباطن', NULL),
(13, 'procurement.requests.create', 'procurement', 'إنشاء طلب شراء', NULL),
(14, 'procurement.orders.approve', 'procurement', 'اعتماد أوامر الشراء', NULL),
(15, 'inventory.transactions.manage', 'inventory', 'إدارة حركات المخزون', NULL),
(16, 'inventory.counts.approve', 'inventory', 'اعتماد الجرد', NULL),
(17, 'treasury.receipts.manage', 'treasury', 'إدارة سندات القبض', NULL),
(18, 'treasury.payments.approve', 'treasury', 'اعتماد سندات الصرف', NULL),
(19, 'treasury.bank_reconcile', 'treasury', 'إجراء التسويات البنكية', NULL),
(20, 'assets.manage', 'assets', 'إدارة الأصول', NULL),
(21, 'payroll.view', 'payroll', 'عرض الرواتب', NULL),
(22, 'payroll.approve', 'payroll', 'اعتماد مسير الرواتب', NULL),
(23, 'tax.manage', 'tax', 'إدارة الضريبة', NULL),
(24, 'tax.einvoice.submit', 'tax', 'إرسال الفواتير الإلكترونية', NULL),
(25, 'reports.financial.view', 'reports', 'عرض التقارير المالية', NULL),
(26, 'reports.export', 'reports', 'تصدير التقارير', NULL),
(27, 'audit.view', 'audit', 'عرض سجل التدقيق', NULL),
(28, 'system.users.manage', 'system', 'إدارة المستخدمين', NULL),
(29, 'system.roles.manage', 'system', 'إدارة الصلاحيات', NULL),
(30, 'system.settings.manage', 'system', 'إدارة إعدادات النظام', NULL);

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

--
-- بنية الجدول `projects`
--

CREATE TABLE `projects` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `tender_id` bigint UNSIGNED DEFAULT NULL,
  `customer_id` bigint UNSIGNED NOT NULL,
  `consultant_partner_id` bigint UNSIGNED DEFAULT NULL,
  `cost_center_id` bigint UNSIGNED NOT NULL,
  `project_no` varchar(60) NOT NULL,
  `name_ar` varchar(255) NOT NULL,
  `name_en` varchar(255) DEFAULT NULL,
  `project_type` varchar(100) DEFAULT NULL,
  `location_name` varchar(255) DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `project_manager_id` bigint UNSIGNED DEFAULT NULL,
  `planned_start_date` date DEFAULT NULL,
  `planned_end_date` date DEFAULT NULL,
  `actual_start_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `completion_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `status` enum('planned','active','on_hold','substantially_completed','warranty','closed','cancelled') NOT NULL DEFAULT 'planned',
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL
) ;

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

--
-- بنية الجدول `project_budget_lines`
--

CREATE TABLE `project_budget_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `budget_version_id` bigint UNSIGNED NOT NULL,
  `contract_boq_item_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED NOT NULL,
  `account_id` bigint UNSIGNED DEFAULT NULL,
  `budget_month` date DEFAULT NULL,
  `quantity` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `unit_cost` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `budget_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `contingency_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `notes` varchar(500) DEFAULT NULL
) ;

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

--
-- بنية الجدول `project_budget_versions`
--

CREATE TABLE `project_budget_versions` (
  `id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED NOT NULL,
  `version_no` int UNSIGNED NOT NULL,
  `version_name` varchar(190) NOT NULL,
  `version_type` enum('baseline','revised','forecast') NOT NULL DEFAULT 'baseline',
  `status` enum('draft','submitted','approved','superseded','rejected') NOT NULL DEFAULT 'draft',
  `total_budget` decimal(18,2) NOT NULL DEFAULT '0.00',
  `effective_from` date DEFAULT NULL,
  `submitted_by` bigint UNSIGNED DEFAULT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `notes` varchar(1000) DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `project_claims`
--

CREATE TABLE `project_claims` (
  `id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED NOT NULL,
  `contract_id` bigint UNSIGNED DEFAULT NULL,
  `claim_no` varchar(60) NOT NULL,
  `title` varchar(255) NOT NULL,
  `claim_type` enum('cost','time','cost_and_time','other') NOT NULL,
  `claimed_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `approved_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `claimed_days` int NOT NULL DEFAULT '0',
  `approved_days` int NOT NULL DEFAULT '0',
  `submitted_on` date DEFAULT NULL,
  `status` enum('draft','submitted','under_review','negotiation','approved','partially_approved','rejected','withdrawn') NOT NULL DEFAULT 'draft',
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `project_contracts`
--

CREATE TABLE `project_contracts` (
  `id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED NOT NULL,
  `contract_no` varchar(80) NOT NULL,
  `contract_title` varchar(255) NOT NULL,
  `contract_type` enum('lump_sum','unit_rate','cost_plus','design_build','epc','framework','other') NOT NULL DEFAULT 'lump_sum',
  `currency_id` bigint UNSIGNED NOT NULL,
  `exchange_rate` decimal(18,8) NOT NULL DEFAULT '1.00000000',
  `original_value` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_pct` decimal(9,4) NOT NULL DEFAULT '15.0000',
  `advance_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `advance_recovery_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `retention_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `retention_limit_pct` decimal(9,4) DEFAULT NULL,
  `delay_penalty_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `delay_penalty_limit_pct` decimal(9,4) DEFAULT NULL,
  `signed_on` date DEFAULT NULL,
  `notice_to_proceed_on` date DEFAULT NULL,
  `starts_on` date DEFAULT NULL,
  `ends_on` date DEFAULT NULL,
  `warranty_ends_on` date DEFAULT NULL,
  `payment_terms_days` smallint UNSIGNED NOT NULL DEFAULT '30',
  `revenue_policy` enum('invoice_based','input_method','output_method','manual') NOT NULL DEFAULT 'invoice_based',
  `status` enum('draft','active','suspended','completed','terminated','closed') NOT NULL DEFAULT 'draft',
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `project_forecasts`
--

CREATE TABLE `project_forecasts` (
  `id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED NOT NULL,
  `forecast_date` date NOT NULL,
  `version_no` int UNSIGNED NOT NULL,
  `status` enum('draft','submitted','approved','superseded') NOT NULL DEFAULT 'draft',
  `estimated_cost_at_completion` decimal(18,2) NOT NULL DEFAULT '0.00',
  `estimated_revenue_at_completion` decimal(18,2) NOT NULL DEFAULT '0.00',
  `notes` varchar(1000) DEFAULT NULL,
  `prepared_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `project_forecast_lines`
--

CREATE TABLE `project_forecast_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `project_forecast_id` bigint UNSIGNED NOT NULL,
  `cost_code_id` bigint UNSIGNED NOT NULL,
  `actual_cost_to_date` decimal(18,2) NOT NULL DEFAULT '0.00',
  `committed_cost` decimal(18,2) NOT NULL DEFAULT '0.00',
  `estimated_cost_to_complete` decimal(18,2) NOT NULL DEFAULT '0.00',
  `explanation` varchar(1000) DEFAULT NULL
) ;

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

--
-- بنية الجدول `project_members`
--

CREATE TABLE `project_members` (
  `project_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED NOT NULL,
  `project_role` varchar(100) DEFAULT NULL,
  `starts_on` date DEFAULT NULL,
  `ends_on` date DEFAULT NULL,
  `can_view_financials` tinyint(1) NOT NULL DEFAULT '0',
  `can_approve_costs` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `purchase_orders`
--

CREATE TABLE `purchase_orders` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `supplier_id` bigint UNSIGNED NOT NULL,
  `supplier_quotation_id` bigint UNSIGNED DEFAULT NULL,
  `warehouse_id` bigint UNSIGNED DEFAULT NULL,
  `po_no` varchar(60) NOT NULL,
  `order_date` date NOT NULL,
  `expected_delivery_date` date DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `exchange_rate` decimal(18,8) NOT NULL DEFAULT '1.00000000',
  `subtotal` decimal(18,2) NOT NULL DEFAULT '0.00',
  `discount_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `payment_terms` varchar(500) DEFAULT NULL,
  `status` enum('draft','submitted','approved','sent','partially_received','received','partially_invoiced','invoiced','closed','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `purchase_order_items`
--

CREATE TABLE `purchase_order_items` (
  `id` bigint UNSIGNED NOT NULL,
  `purchase_order_id` bigint UNSIGNED NOT NULL,
  `purchase_request_item_id` bigint UNSIGNED DEFAULT NULL,
  `item_id` bigint UNSIGNED DEFAULT NULL,
  `description` varchar(1000) NOT NULL,
  `unit_code` varchar(30) NOT NULL,
  `ordered_qty` decimal(18,4) NOT NULL,
  `received_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `invoiced_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `unit_price` decimal(18,4) NOT NULL,
  `discount_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_code_id` bigint UNSIGNED DEFAULT NULL,
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(18,2) NOT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `budget_line_id` bigint UNSIGNED DEFAULT NULL,
  `contract_boq_item_id` bigint UNSIGNED DEFAULT NULL
) ;

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

--
-- بنية الجدول `purchase_requests`
--

CREATE TABLE `purchase_requests` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `warehouse_id` bigint UNSIGNED DEFAULT NULL,
  `request_no` varchar(60) NOT NULL,
  `request_date` date NOT NULL,
  `required_by_date` date DEFAULT NULL,
  `purpose` varchar(1000) DEFAULT NULL,
  `priority` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `status` enum('draft','submitted','approved','partially_ordered','ordered','rejected','cancelled','closed') NOT NULL DEFAULT 'draft',
  `requested_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `purchase_request_items`
--

CREATE TABLE `purchase_request_items` (
  `id` bigint UNSIGNED NOT NULL,
  `purchase_request_id` bigint UNSIGNED NOT NULL,
  `item_id` bigint UNSIGNED DEFAULT NULL,
  `description` varchar(1000) NOT NULL,
  `unit_code` varchar(30) NOT NULL,
  `requested_qty` decimal(18,4) NOT NULL,
  `approved_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `estimated_unit_cost` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `contract_boq_item_id` bigint UNSIGNED DEFAULT NULL,
  `budget_line_id` bigint UNSIGNED DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL
) ;

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

--
-- بنية الجدول `receipt_allocations`
--

CREATE TABLE `receipt_allocations` (
  `id` bigint UNSIGNED NOT NULL,
  `receipt_voucher_id` bigint UNSIGNED NOT NULL,
  `sales_invoice_id` bigint UNSIGNED DEFAULT NULL,
  `customer_certificate_id` bigint UNSIGNED DEFAULT NULL,
  `allocation_type` enum('invoice','certificate','advance','retention','unallocated') NOT NULL,
  `allocated_amount` decimal(18,2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `receipt_vouchers`
--

CREATE TABLE `receipt_vouchers` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `customer_id` bigint UNSIGNED DEFAULT NULL,
  `partner_id` bigint UNSIGNED DEFAULT NULL,
  `receipt_no` varchar(60) NOT NULL,
  `receipt_date` date NOT NULL,
  `payment_method` enum('bank_transfer','cash','cheque','card','other') NOT NULL,
  `bank_account_id` bigint UNSIGNED DEFAULT NULL,
  `cashbox_id` bigint UNSIGNED DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `exchange_rate` decimal(18,8) NOT NULL DEFAULT '1.00000000',
  `amount` decimal(18,2) NOT NULL,
  `reference_no` varchar(100) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `status` enum('draft','submitted','approved','posted','cancelled','reversed') NOT NULL DEFAULT 'draft',
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `recurring_journals`
--

CREATE TABLE `recurring_journals` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `journal_book_id` bigint UNSIGNED NOT NULL,
  `frequency` enum('monthly','quarterly','yearly','custom') NOT NULL,
  `next_run_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `template_json` json NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `requests_for_quotation`
--

CREATE TABLE `requests_for_quotation` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `rfq_no` varchar(60) NOT NULL,
  `issue_date` date NOT NULL,
  `closing_date` date DEFAULT NULL,
  `currency_id` bigint UNSIGNED DEFAULT NULL,
  `status` enum('draft','issued','closed','evaluated','awarded','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `revenue_recognition_lines`
--

CREATE TABLE `revenue_recognition_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `revenue_recognition_run_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED NOT NULL,
  `contract_id` bigint UNSIGNED NOT NULL,
  `measurement_method` enum('input_cost','output_progress','milestone','manual') NOT NULL,
  `progress_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `contract_value` decimal(18,2) NOT NULL DEFAULT '0.00',
  `revenue_to_date` decimal(18,2) NOT NULL DEFAULT '0.00',
  `revenue_previously_recognized` decimal(18,2) NOT NULL DEFAULT '0.00',
  `current_period_revenue` decimal(18,2) NOT NULL DEFAULT '0.00',
  `billed_to_date` decimal(18,2) NOT NULL DEFAULT '0.00',
  `contract_asset_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `contract_liability_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `explanation` varchar(1000) DEFAULT NULL
) ;

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

--
-- بنية الجدول `revenue_recognition_runs`
--

CREATE TABLE `revenue_recognition_runs` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `fiscal_period_id` bigint UNSIGNED NOT NULL,
  `run_no` varchar(60) NOT NULL,
  `run_date` date NOT NULL,
  `status` enum('draft','calculated','reviewed','approved','posted','cancelled') NOT NULL DEFAULT 'draft',
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `prepared_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `rfq_items`
--

CREATE TABLE `rfq_items` (
  `id` bigint UNSIGNED NOT NULL,
  `rfq_id` bigint UNSIGNED NOT NULL,
  `purchase_request_item_id` bigint UNSIGNED DEFAULT NULL,
  `item_id` bigint UNSIGNED DEFAULT NULL,
  `description` varchar(1000) NOT NULL,
  `unit_code` varchar(30) NOT NULL,
  `quantity` decimal(18,4) NOT NULL,
  `required_by_date` date DEFAULT NULL
) ;

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

--
-- بنية الجدول `rfq_suppliers`
--

CREATE TABLE `rfq_suppliers` (
  `rfq_id` bigint UNSIGNED NOT NULL,
  `supplier_id` bigint UNSIGNED NOT NULL,
  `invited_at` datetime DEFAULT NULL,
  `responded_at` datetime DEFAULT NULL,
  `response_status` enum('invited','viewed','responded','declined','no_response') NOT NULL DEFAULT 'invited'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `roles`
--

CREATE TABLE `roles` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `code` varchar(60) NOT NULL,
  `name_ar` varchar(150) NOT NULL,
  `name_en` varchar(150) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `role_permissions`
--

CREATE TABLE `role_permissions` (
  `role_id` bigint UNSIGNED NOT NULL,
  `permission_id` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `sales_invoices`
--

CREATE TABLE `sales_invoices` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `customer_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `contract_id` bigint UNSIGNED DEFAULT NULL,
  `customer_certificate_id` bigint UNSIGNED DEFAULT NULL,
  `invoice_no` varchar(80) NOT NULL,
  `invoice_type` enum('tax_invoice','simplified_tax_invoice','advance_invoice','debit_note','credit_note') NOT NULL DEFAULT 'tax_invoice',
  `original_invoice_id` bigint UNSIGNED DEFAULT NULL,
  `invoice_date` date NOT NULL,
  `supply_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `exchange_rate` decimal(18,8) NOT NULL DEFAULT '1.00000000',
  `subtotal` decimal(18,2) NOT NULL DEFAULT '0.00',
  `discount_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `gross_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `retention_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `advance_applied_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `net_due_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `collected_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `zatca_status` enum('not_required','pending','cleared','reported','warning','rejected') NOT NULL DEFAULT 'pending',
  `status` enum('draft','submitted','approved','issued','partially_collected','collected','overdue','cancelled') NOT NULL DEFAULT 'draft',
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `issued_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `sales_invoice_items`
--

CREATE TABLE `sales_invoice_items` (
  `id` bigint UNSIGNED NOT NULL,
  `sales_invoice_id` bigint UNSIGNED NOT NULL,
  `contract_boq_item_id` bigint UNSIGNED DEFAULT NULL,
  `description` varchar(1000) NOT NULL,
  `quantity` decimal(18,4) NOT NULL,
  `unit_code` varchar(30) NOT NULL,
  `unit_price` decimal(18,4) NOT NULL,
  `discount_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_code_id` bigint UNSIGNED DEFAULT NULL,
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `line_total` decimal(18,2) NOT NULL,
  `revenue_account_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL
) ;

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

--
-- بنية الجدول `schema_migrations`
--

CREATE TABLE `schema_migrations` (
  `id` bigint UNSIGNED NOT NULL,
  `version` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `applied_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- إرجاع أو استيراد بيانات الجدول `schema_migrations`
--

INSERT INTO `schema_migrations` (`id`, `version`, `description`, `applied_at`) VALUES
(1, '0.1.0', 'Initial comprehensive schema for contracting finance system', '2026-07-16 18:54:39');

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

--
-- بنية الجدول `stock_balances`
--

CREATE TABLE `stock_balances` (
  `warehouse_id` bigint UNSIGNED NOT NULL,
  `item_id` bigint UNSIGNED NOT NULL,
  `lot_no` varchar(100) NOT NULL DEFAULT '',
  `quantity_on_hand` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `quantity_reserved` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `average_unit_cost` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `stock_counts`
--

CREATE TABLE `stock_counts` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `warehouse_id` bigint UNSIGNED NOT NULL,
  `count_no` varchar(60) NOT NULL,
  `count_date` date NOT NULL,
  `status` enum('planned','in_progress','submitted','approved','posted','cancelled') NOT NULL DEFAULT 'planned',
  `freeze_movements` tinyint(1) NOT NULL DEFAULT '0',
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `stock_count_lines`
--

CREATE TABLE `stock_count_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `stock_count_id` bigint UNSIGNED NOT NULL,
  `item_id` bigint UNSIGNED NOT NULL,
  `lot_no` varchar(100) NOT NULL DEFAULT '',
  `system_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `counted_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `variance_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `unit_cost` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `notes` varchar(500) DEFAULT NULL
) ;

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

--
-- بنية الجدول `stock_transactions`
--

CREATE TABLE `stock_transactions` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `transaction_no` varchar(60) NOT NULL,
  `transaction_type` enum('opening','receipt','issue','transfer','return_to_supplier','return_from_project','adjustment_in','adjustment_out','count') NOT NULL,
  `transaction_date` date NOT NULL,
  `from_warehouse_id` bigint UNSIGNED DEFAULT NULL,
  `to_warehouse_id` bigint UNSIGNED DEFAULT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `source_type` varchar(80) DEFAULT NULL,
  `source_id` bigint UNSIGNED DEFAULT NULL,
  `status` enum('draft','submitted','approved','posted','cancelled') NOT NULL DEFAULT 'draft',
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `stock_transaction_lines`
--

CREATE TABLE `stock_transaction_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `stock_transaction_id` bigint UNSIGNED NOT NULL,
  `item_id` bigint UNSIGNED NOT NULL,
  `quantity` decimal(18,4) NOT NULL,
  `unit_cost` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `total_cost` decimal(18,2) NOT NULL DEFAULT '0.00',
  `lot_no` varchar(100) DEFAULT NULL,
  `serial_no` varchar(190) DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `contract_boq_item_id` bigint UNSIGNED DEFAULT NULL
) ;

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

--
-- بنية الجدول `subcontractors`
--

CREATE TABLE `subcontractors` (
  `id` bigint UNSIGNED NOT NULL,
  `partner_id` bigint UNSIGNED NOT NULL,
  `classification` varchar(150) DEFAULT NULL,
  `payable_account_id` bigint UNSIGNED DEFAULT NULL,
  `advance_account_id` bigint UNSIGNED DEFAULT NULL,
  `retention_account_id` bigint UNSIGNED DEFAULT NULL,
  `qualification_status` enum('pending','qualified','suspended','rejected') NOT NULL DEFAULT 'pending',
  `qualification_expires_on` date DEFAULT NULL,
  `rating` decimal(5,2) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1'
) ;

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

--
-- بنية الجدول `subcontracts`
--

CREATE TABLE `subcontracts` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED NOT NULL,
  `subcontractor_id` bigint UNSIGNED NOT NULL,
  `subcontract_no` varchar(80) NOT NULL,
  `title` varchar(255) NOT NULL,
  `subcontract_type` enum('lump_sum','unit_rate','labor_only','supply_install','other') NOT NULL DEFAULT 'lump_sum',
  `currency_id` bigint UNSIGNED NOT NULL,
  `exchange_rate` decimal(18,8) NOT NULL DEFAULT '1.00000000',
  `original_value` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_pct` decimal(9,4) NOT NULL DEFAULT '15.0000',
  `advance_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `advance_recovery_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `retention_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `retention_limit_pct` decimal(9,4) DEFAULT NULL,
  `delay_penalty_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `starts_on` date DEFAULT NULL,
  `ends_on` date DEFAULT NULL,
  `warranty_ends_on` date DEFAULT NULL,
  `status` enum('draft','active','suspended','completed','terminated','closed') NOT NULL DEFAULT 'draft',
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `subcontract_certificates`
--

CREATE TABLE `subcontract_certificates` (
  `id` bigint UNSIGNED NOT NULL,
  `subcontract_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED NOT NULL,
  `certificate_no` varchar(80) NOT NULL,
  `period_from` date DEFAULT NULL,
  `period_to` date NOT NULL,
  `certificate_date` date NOT NULL,
  `previous_work_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `current_work_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `materials_on_site_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `variation_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `gross_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `advance_recovery_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `retention_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `penalty_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `other_deductions_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `taxable_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `net_payable_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` enum('draft','submitted','project_approved','finance_approved','approved','invoiced','partially_paid','paid','rejected','cancelled') NOT NULL DEFAULT 'draft',
  `submitted_by` bigint UNSIGNED DEFAULT NULL,
  `project_approved_by` bigint UNSIGNED DEFAULT NULL,
  `finance_approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `subcontract_certificate_adjustments`
--

CREATE TABLE `subcontract_certificate_adjustments` (
  `id` bigint UNSIGNED NOT NULL,
  `subcontract_certificate_id` bigint UNSIGNED NOT NULL,
  `adjustment_type` enum('advance_recovery','retention','penalty','backcharge','insurance','tax_withholding','other') NOT NULL,
  `description` varchar(500) NOT NULL,
  `amount` decimal(18,2) NOT NULL,
  `account_id` bigint UNSIGNED DEFAULT NULL
) ;

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

--
-- بنية الجدول `subcontract_certificate_items`
--

CREATE TABLE `subcontract_certificate_items` (
  `id` bigint UNSIGNED NOT NULL,
  `subcontract_certificate_id` bigint UNSIGNED NOT NULL,
  `subcontract_item_id` bigint UNSIGNED NOT NULL,
  `previous_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `current_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `cumulative_qty` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `unit_rate` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `current_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `certified_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `notes` varchar(500) DEFAULT NULL
) ;

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

--
-- بنية الجدول `subcontract_items`
--

CREATE TABLE `subcontract_items` (
  `id` bigint UNSIGNED NOT NULL,
  `subcontract_id` bigint UNSIGNED NOT NULL,
  `contract_boq_item_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED NOT NULL,
  `item_code` varchar(80) NOT NULL,
  `description` varchar(1000) NOT NULL,
  `unit_code` varchar(30) DEFAULT NULL,
  `quantity` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `unit_rate` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `sort_order` int UNSIGNED NOT NULL DEFAULT '0'
) ;

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

--
-- بنية الجدول `suppliers`
--

CREATE TABLE `suppliers` (
  `id` bigint UNSIGNED NOT NULL,
  `partner_id` bigint UNSIGNED NOT NULL,
  `supplier_category` varchar(100) DEFAULT NULL,
  `payable_account_id` bigint UNSIGNED DEFAULT NULL,
  `advance_account_id` bigint UNSIGNED DEFAULT NULL,
  `qualification_status` enum('pending','qualified','suspended','rejected') NOT NULL DEFAULT 'pending',
  `qualification_expires_on` date DEFAULT NULL,
  `rating` decimal(5,2) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1'
) ;

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

--
-- بنية الجدول `supplier_invoices`
--

CREATE TABLE `supplier_invoices` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `supplier_id` bigint UNSIGNED NOT NULL,
  `purchase_order_id` bigint UNSIGNED DEFAULT NULL,
  `invoice_no` varchar(100) NOT NULL,
  `internal_no` varchar(60) NOT NULL,
  `invoice_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `exchange_rate` decimal(18,8) NOT NULL DEFAULT '1.00000000',
  `subtotal` decimal(18,2) NOT NULL DEFAULT '0.00',
  `discount_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `retention_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `advance_recovery_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `paid_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` enum('draft','matched','submitted','approved','posted','partially_paid','paid','disputed','cancelled') NOT NULL DEFAULT 'draft',
  `matching_status` enum('not_required','pending','matched','exception') NOT NULL DEFAULT 'pending',
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `supplier_invoice_items`
--

CREATE TABLE `supplier_invoice_items` (
  `id` bigint UNSIGNED NOT NULL,
  `supplier_invoice_id` bigint UNSIGNED NOT NULL,
  `purchase_order_item_id` bigint UNSIGNED DEFAULT NULL,
  `goods_receipt_item_id` bigint UNSIGNED DEFAULT NULL,
  `item_id` bigint UNSIGNED DEFAULT NULL,
  `description` varchar(1000) NOT NULL,
  `quantity` decimal(18,4) NOT NULL,
  `unit_code` varchar(30) NOT NULL,
  `unit_price` decimal(18,4) NOT NULL,
  `discount_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_code_id` bigint UNSIGNED DEFAULT NULL,
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `line_total` decimal(18,2) NOT NULL,
  `expense_account_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `contract_boq_item_id` bigint UNSIGNED DEFAULT NULL
) ;

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

--
-- بنية الجدول `supplier_quotations`
--

CREATE TABLE `supplier_quotations` (
  `id` bigint UNSIGNED NOT NULL,
  `rfq_id` bigint UNSIGNED DEFAULT NULL,
  `supplier_id` bigint UNSIGNED NOT NULL,
  `quotation_no` varchar(100) DEFAULT NULL,
  `quotation_date` date NOT NULL,
  `valid_until` date DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `exchange_rate` decimal(18,8) NOT NULL DEFAULT '1.00000000',
  `subtotal` decimal(18,2) NOT NULL DEFAULT '0.00',
  `discount_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `delivery_days` int UNSIGNED DEFAULT NULL,
  `payment_terms` varchar(500) DEFAULT NULL,
  `status` enum('draft','received','evaluated','selected','rejected','expired') NOT NULL DEFAULT 'received',
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `supplier_quotation_items`
--

CREATE TABLE `supplier_quotation_items` (
  `id` bigint UNSIGNED NOT NULL,
  `supplier_quotation_id` bigint UNSIGNED NOT NULL,
  `rfq_item_id` bigint UNSIGNED DEFAULT NULL,
  `item_id` bigint UNSIGNED DEFAULT NULL,
  `description` varchar(1000) NOT NULL,
  `quantity` decimal(18,4) NOT NULL,
  `unit_code` varchar(30) NOT NULL,
  `unit_price` decimal(18,4) NOT NULL,
  `discount_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_code_id` bigint UNSIGNED DEFAULT NULL,
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(18,2) NOT NULL
) ;

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

--
-- بنية الجدول `system_settings`
--

CREATE TABLE `system_settings` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `setting_group` varchar(80) NOT NULL,
  `setting_key` varchar(120) NOT NULL,
  `value_type` enum('string','number','boolean','date','json') NOT NULL DEFAULT 'string',
  `setting_value` json DEFAULT NULL,
  `is_sensitive` tinyint(1) NOT NULL DEFAULT '0',
  `updated_by` bigint UNSIGNED DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `tax_codes`
--

CREATE TABLE `tax_codes` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `code` varchar(40) NOT NULL,
  `name_ar` varchar(150) NOT NULL,
  `tax_type` enum('standard','zero','exempt','out_of_scope','reverse_charge','withholding') NOT NULL DEFAULT 'standard',
  `rate_pct` decimal(9,4) NOT NULL DEFAULT '15.0000',
  `recoverable_pct` decimal(9,4) NOT NULL DEFAULT '100.0000',
  `input_account_id` bigint UNSIGNED DEFAULT NULL,
  `output_account_id` bigint UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL
) ;

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

--
-- بنية الجدول `tax_periods`
--

CREATE TABLE `tax_periods` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `tax_type` enum('vat','withholding','zakat','other') NOT NULL DEFAULT 'vat',
  `period_from` date NOT NULL,
  `period_to` date NOT NULL,
  `filing_due_date` date DEFAULT NULL,
  `payment_due_date` date DEFAULT NULL,
  `status` enum('open','prepared','filed','paid','closed') NOT NULL DEFAULT 'open'
) ;

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

--
-- بنية الجدول `tax_returns`
--

CREATE TABLE `tax_returns` (
  `id` bigint UNSIGNED NOT NULL,
  `tax_period_id` bigint UNSIGNED NOT NULL,
  `return_no` varchar(60) DEFAULT NULL,
  `output_tax` decimal(18,2) NOT NULL DEFAULT '0.00',
  `input_tax` decimal(18,2) NOT NULL DEFAULT '0.00',
  `adjustments_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `net_tax_due` decimal(18,2) NOT NULL DEFAULT '0.00',
  `filed_reference` varchar(190) DEFAULT NULL,
  `filed_at` datetime DEFAULT NULL,
  `status` enum('draft','reviewed','approved','filed','amended','paid') NOT NULL DEFAULT 'draft',
  `prepared_by` bigint UNSIGNED NOT NULL,
  `reviewed_by` bigint UNSIGNED DEFAULT NULL,
  `approved_by` bigint UNSIGNED DEFAULT NULL,
  `journal_entry_id` bigint UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `tax_return_lines`
--

CREATE TABLE `tax_return_lines` (
  `id` bigint UNSIGNED NOT NULL,
  `tax_return_id` bigint UNSIGNED NOT NULL,
  `line_code` varchar(60) NOT NULL,
  `description` varchar(500) NOT NULL,
  `taxable_base` decimal(18,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `adjustment_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `source_count` int UNSIGNED NOT NULL DEFAULT '0'
) ;

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

--
-- بنية الجدول `tenders`
--

CREATE TABLE `tenders` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `tender_no` varchar(60) NOT NULL,
  `title` varchar(255) NOT NULL,
  `customer_id` bigint UNSIGNED DEFAULT NULL,
  `consultant_partner_id` bigint UNSIGNED DEFAULT NULL,
  `tender_type` enum('public','private','invitation','direct') NOT NULL DEFAULT 'private',
  `submission_deadline` datetime DEFAULT NULL,
  `bid_valid_until` date DEFAULT NULL,
  `estimated_value` decimal(18,2) DEFAULT NULL,
  `currency_id` bigint UNSIGNED NOT NULL,
  `bid_bond_required` tinyint(1) NOT NULL DEFAULT '0',
  `bid_bond_amount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `status` enum('draft','studying','priced','submitted','awarded','lost','cancelled') NOT NULL DEFAULT 'draft',
  `owner_user_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `tender_boq_items`
--

CREATE TABLE `tender_boq_items` (
  `id` bigint UNSIGNED NOT NULL,
  `tender_id` bigint UNSIGNED NOT NULL,
  `parent_id` bigint UNSIGNED DEFAULT NULL,
  `item_code` varchar(80) NOT NULL,
  `description_ar` varchar(1000) NOT NULL,
  `description_en` varchar(1000) DEFAULT NULL,
  `unit_code` varchar(30) DEFAULT NULL,
  `quantity` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `client_unit_rate` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `estimated_unit_cost` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `overhead_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `risk_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `profit_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `tax_pct` decimal(9,4) NOT NULL DEFAULT '15.0000',
  `sort_order` int UNSIGNED NOT NULL DEFAULT '0'
) ;

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

--
-- بنية الجدول `tender_estimate_components`
--

CREATE TABLE `tender_estimate_components` (
  `id` bigint UNSIGNED NOT NULL,
  `tender_boq_item_id` bigint UNSIGNED NOT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `component_type` enum('material','labor','subcontract','equipment','overhead','other') NOT NULL,
  `description` varchar(500) NOT NULL,
  `unit_code` varchar(30) DEFAULT NULL,
  `quantity` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `unit_cost` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `waste_pct` decimal(9,4) NOT NULL DEFAULT '0.0000',
  `notes` varchar(1000) DEFAULT NULL
) ;

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

--
-- بنية الجدول `users`
--

CREATE TABLE `users` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `department_id` bigint UNSIGNED DEFAULT NULL,
  `employee_no` varchar(50) DEFAULT NULL,
  `full_name` varchar(190) NOT NULL,
  `email` varchar(190) NOT NULL,
  `mobile` varchar(40) DEFAULT NULL,
  `password_hash` varchar(255) NOT NULL,
  `locale` varchar(10) NOT NULL DEFAULT 'ar',
  `timezone` varchar(50) NOT NULL DEFAULT 'Asia/Riyadh',
  `must_change_password` tinyint(1) NOT NULL DEFAULT '1',
  `two_factor_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `status` enum('active','inactive','locked') NOT NULL DEFAULT 'active',
  `last_login_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `user_roles`
--

CREATE TABLE `user_roles` (
  `user_id` bigint UNSIGNED NOT NULL,
  `role_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED DEFAULT NULL,
  `project_scope` enum('all','assigned','none') NOT NULL DEFAULT 'assigned',
  `starts_at` datetime DEFAULT NULL,
  `ends_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `variation_orders`
--

CREATE TABLE `variation_orders` (
  `id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED NOT NULL,
  `contract_id` bigint UNSIGNED NOT NULL,
  `variation_no` varchar(60) NOT NULL,
  `title` varchar(255) NOT NULL,
  `variation_type` enum('addition','omission','substitution','time_extension','claim_conversion') NOT NULL,
  `requested_by` enum('client','contractor','consultant','other') NOT NULL,
  `submitted_value` decimal(18,2) NOT NULL DEFAULT '0.00',
  `approved_value` decimal(18,2) NOT NULL DEFAULT '0.00',
  `cost_impact` decimal(18,2) NOT NULL DEFAULT '0.00',
  `days_impact` int NOT NULL DEFAULT '0',
  `submitted_on` date DEFAULT NULL,
  `approved_on` date DEFAULT NULL,
  `status` enum('draft','under_review','submitted','approved','partially_approved','rejected','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `variation_order_items`
--

CREATE TABLE `variation_order_items` (
  `id` bigint UNSIGNED NOT NULL,
  `variation_order_id` bigint UNSIGNED NOT NULL,
  `contract_boq_item_id` bigint UNSIGNED DEFAULT NULL,
  `cost_code_id` bigint UNSIGNED DEFAULT NULL,
  `description` varchar(1000) NOT NULL,
  `unit_code` varchar(30) DEFAULT NULL,
  `quantity` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `unit_rate` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `approved_quantity` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `approved_unit_rate` decimal(18,4) NOT NULL DEFAULT '0.0000'
) ;

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

--
-- Stand-in structure for view `vw_ap_open_items`
-- (See below for the actual view)
--
CREATE TABLE `vw_ap_open_items` (
`source_type` varchar(23)
,`source_id` bigint unsigned
,`company_id` bigint unsigned
,`supplier_or_subcontractor_id` bigint unsigned
,`project_id` bigint unsigned
,`source_no` varchar(80)
,`source_date` date
,`due_date` date
,`total_amount` decimal(18,2)
,`paid_amount` decimal(40,2)
,`outstanding_amount` decimal(41,2)
,`status` varchar(16)
);

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

--
-- Stand-in structure for view `vw_ar_open_items`
-- (See below for the actual view)
--
CREATE TABLE `vw_ar_open_items` (
`sales_invoice_id` bigint unsigned
,`company_id` bigint unsigned
,`customer_id` bigint unsigned
,`project_id` bigint unsigned
,`invoice_no` varchar(80)
,`invoice_date` date
,`due_date` date
,`net_due_amount` decimal(18,2)
,`collected_amount` decimal(18,2)
,`outstanding_amount` decimal(19,2)
,`days_overdue` int
,`status` enum('draft','submitted','approved','issued','partially_collected','collected','overdue','cancelled')
);

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

--
-- Stand-in structure for view `vw_inventory_value`
-- (See below for the actual view)
--
CREATE TABLE `vw_inventory_value` (
`warehouse_id` bigint unsigned
,`company_id` bigint unsigned
,`project_id` bigint unsigned
,`item_id` bigint unsigned
,`item_code` varchar(60)
,`item_name` varchar(255)
,`quantity_on_hand` decimal(40,4)
,`average_unit_cost` decimal(18,4)
,`inventory_value` decimal(58,8)
);

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

--
-- Stand-in structure for view `vw_journal_entry_balance`
-- (See below for the actual view)
--
CREATE TABLE `vw_journal_entry_balance` (
`journal_entry_id` bigint unsigned
,`company_id` bigint unsigned
,`entry_no` varchar(60)
,`entry_date` date
,`status` enum('draft','submitted','approved','posted','reversed','cancelled')
,`total_debit` decimal(40,2)
,`total_credit` decimal(40,2)
,`difference_amount` decimal(41,2)
,`is_balanced` int
);

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

--
-- Stand-in structure for view `vw_project_commitments`
-- (See below for the actual view)
--
CREATE TABLE `vw_project_commitments` (
`project_id` bigint unsigned
,`commitment_type` varchar(14)
,`source_id` bigint unsigned
,`source_no` varchar(80)
,`committed_amount` decimal(59,8)
);

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

--
-- Stand-in structure for view `vw_project_cost_summary`
-- (See below for the actual view)
--
CREATE TABLE `vw_project_cost_summary` (
`project_id` bigint unsigned
,`cost_code_id` bigint unsigned
,`cost_code` varchar(50)
,`cost_code_name` varchar(190)
,`actual_cost` decimal(41,2)
);

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

--
-- Stand-in structure for view `vw_project_financial_summary`
-- (See below for the actual view)
--
CREATE TABLE `vw_project_financial_summary` (
`project_id` bigint unsigned
,`company_id` bigint unsigned
,`project_no` varchar(60)
,`project_name` varchar(255)
,`status` enum('planned','active','on_hold','substantially_completed','warranty','closed','cancelled')
,`original_contract_value` decimal(40,2)
,`approved_variations` decimal(40,2)
,`certified_revenue` decimal(40,2)
,`invoiced_amount` decimal(40,2)
,`collected_amount` decimal(40,2)
,`actual_cost` decimal(63,2)
,`committed_cost` decimal(65,8)
);

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

--
-- Stand-in structure for view `vw_trial_balance`
-- (See below for the actual view)
--
CREATE TABLE `vw_trial_balance` (
`company_id` bigint unsigned
,`branch_id` bigint unsigned
,`fiscal_period_id` bigint unsigned
,`account_id` bigint unsigned
,`account_code` varchar(40)
,`account_name` varchar(190)
,`total_debit` decimal(40,2)
,`total_credit` decimal(40,2)
,`net_balance` decimal(41,2)
);

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

--
-- بنية الجدول `warehouses`
--

CREATE TABLE `warehouses` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `branch_id` bigint UNSIGNED NOT NULL,
  `project_id` bigint UNSIGNED DEFAULT NULL,
  `code` varchar(40) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `name_en` varchar(190) DEFAULT NULL,
  `warehouse_type` enum('central','project','transit','quarantine','virtual') NOT NULL DEFAULT 'central',
  `location_text` varchar(500) DEFAULT NULL,
  `custodian_user_id` bigint UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- بنية الجدول `workflow_definitions`
--

CREATE TABLE `workflow_definitions` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `workflow_code` varchar(100) NOT NULL,
  `name_ar` varchar(190) NOT NULL,
  `module_code` varchar(80) NOT NULL,
  `document_type` varchar(80) NOT NULL,
  `version_no` int UNSIGNED NOT NULL DEFAULT '1',
  `condition_json` json DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

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

--
-- بنية الجدول `workflow_steps`
--

CREATE TABLE `workflow_steps` (
  `id` bigint UNSIGNED NOT NULL,
  `workflow_definition_id` bigint UNSIGNED NOT NULL,
  `step_no` smallint UNSIGNED NOT NULL,
  `step_name_ar` varchar(190) NOT NULL,
  `approver_type` enum('user','role','department_manager','project_manager','financial_manager','general_manager') NOT NULL,
  `approver_user_id` bigint UNSIGNED DEFAULT NULL,
  `approver_role_id` bigint UNSIGNED DEFAULT NULL,
  `min_amount` decimal(18,2) DEFAULT NULL,
  `max_amount` decimal(18,2) DEFAULT NULL,
  `approval_mode` enum('any','all','sequential') NOT NULL DEFAULT 'any',
  `escalation_hours` int UNSIGNED DEFAULT NULL,
  `allow_reject` tinyint(1) NOT NULL DEFAULT '1',
  `allow_return` tinyint(1) NOT NULL DEFAULT '1'
) ;

--
-- Indexes for dumped tables
--

--
-- فهارس للجدول `accounting_rules`
--
ALTER TABLE `accounting_rules`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_accounting_rules_company_event_version` (`company_id`,`event_code`,`version_no`),
  ADD KEY `fk_accounting_rules_created_by` (`created_by`);

--
-- فهارس للجدول `accounting_rule_lines`
--
ALTER TABLE `accounting_rule_lines`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_accounting_rule_lines_rule_line` (`accounting_rule_id`,`line_no`),
  ADD KEY `fk_accounting_rule_lines_role` (`account_role_id`);

--
-- فهارس للجدول `accounts`
--
ALTER TABLE `accounts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_accounts_company_code` (`company_id`,`code`),
  ADD KEY `idx_accounts_parent` (`parent_id`),
  ADD KEY `fk_accounts_type` (`account_type_id`),
  ADD KEY `fk_accounts_currency` (`currency_id`);

--
-- فهارس للجدول `account_roles`
--
ALTER TABLE `account_roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_account_roles_code` (`role_code`);

--
-- فهارس للجدول `account_types`
--
ALTER TABLE `account_types`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_account_types_code` (`code`);

--
-- فهارس للجدول `api_clients`
--
ALTER TABLE `api_clients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_api_clients_key` (`client_key`),
  ADD KEY `fk_api_clients_company` (`company_id`),
  ADD KEY `fk_api_clients_created_by` (`created_by`);

--
-- فهارس للجدول `approval_actions`
--
ALTER TABLE `approval_actions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_approval_actions_instance` (`approval_instance_id`),
  ADD KEY `fk_approval_actions_step` (`workflow_step_id`),
  ADD KEY `fk_approval_actions_acted_by` (`acted_by`),
  ADD KEY `fk_approval_actions_delegated_to` (`delegated_to`);

--
-- فهارس للجدول `approval_instances`
--
ALTER TABLE `approval_instances`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_approval_instances_document` (`company_id`,`document_type`,`document_id`),
  ADD KEY `idx_approval_instances_status_step` (`status`,`current_step_no`),
  ADD KEY `fk_approval_instances_workflow` (`workflow_definition_id`),
  ADD KEY `fk_approval_instances_project` (`project_id`),
  ADD KEY `fk_approval_instances_submitted_by` (`submitted_by`);

--
-- فهارس للجدول `assets`
--
ALTER TABLE `assets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_assets_company_no` (`company_id`,`asset_no`),
  ADD UNIQUE KEY `uq_assets_company_serial` (`company_id`,`serial_no`),
  ADD KEY `fk_assets_branch` (`branch_id`),
  ADD KEY `fk_assets_category` (`asset_category_id`),
  ADD KEY `fk_assets_supplier` (`supplier_id`),
  ADD KEY `fk_assets_supplier_invoice` (`supplier_invoice_id`),
  ADD KEY `fk_assets_currency` (`currency_id`),
  ADD KEY `fk_assets_project` (`current_project_id`),
  ADD KEY `fk_assets_custodian` (`custodian_employee_id`);

--
-- فهارس للجدول `asset_categories`
--
ALTER TABLE `asset_categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_asset_categories_company_code` (`company_id`,`code`),
  ADD KEY `fk_asset_categories_asset_account` (`asset_account_id`),
  ADD KEY `fk_asset_categories_accumulated_account` (`accumulated_depreciation_account_id`),
  ADD KEY `fk_asset_categories_expense_account` (`depreciation_expense_account_id`);

--
-- فهارس للجدول `attachments`
--
ALTER TABLE `attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_attachments_object` (`object_type`,`object_id`),
  ADD KEY `fk_attachments_company` (`company_id`),
  ADD KEY `fk_attachments_uploaded_by` (`uploaded_by`);

--
-- فهارس للجدول `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_audit_logs_object` (`object_type`,`object_id`,`created_at`),
  ADD KEY `idx_audit_logs_user_date` (`user_id`,`created_at`),
  ADD KEY `fk_audit_logs_company` (`company_id`);

--
-- فهارس للجدول `bank_accounts`
--
ALTER TABLE `bank_accounts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_bank_accounts_company_code` (`company_id`,`code`),
  ADD UNIQUE KEY `uq_bank_accounts_company_iban` (`company_id`,`iban`),
  ADD KEY `fk_bank_accounts_branch` (`branch_id`),
  ADD KEY `fk_bank_accounts_bank_partner` (`bank_partner_id`),
  ADD KEY `fk_bank_accounts_currency` (`currency_id`),
  ADD KEY `fk_bank_accounts_account` (`account_id`);

--
-- فهارس للجدول `bank_reconciliations`
--
ALTER TABLE `bank_reconciliations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_bank_reconciliations_account` (`bank_account_id`),
  ADD KEY `fk_bank_reconciliations_import` (`bank_statement_import_id`),
  ADD KEY `fk_bank_reconciliations_prepared_by` (`prepared_by`),
  ADD KEY `fk_bank_reconciliations_approved_by` (`approved_by`);

--
-- فهارس للجدول `bank_reconciliation_lines`
--
ALTER TABLE `bank_reconciliation_lines`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_bank_reconciliation_lines_reconciliation` (`bank_reconciliation_id`),
  ADD KEY `fk_bank_reconciliation_lines_statement_line` (`bank_statement_line_id`),
  ADD KEY `fk_bank_reconciliation_lines_receipt` (`receipt_voucher_id`),
  ADD KEY `fk_bank_reconciliation_lines_payment` (`payment_voucher_id`),
  ADD KEY `fk_bank_reconciliation_lines_journal` (`journal_entry_id`),
  ADD KEY `fk_bank_reconciliation_lines_matched_by` (`matched_by`);

--
-- فهارس للجدول `bank_statement_imports`
--
ALTER TABLE `bank_statement_imports`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_bank_statement_imports_file_hash` (`bank_account_id`,`file_hash`),
  ADD KEY `fk_bank_statement_imports_imported_by` (`imported_by`);

--
-- فهارس للجدول `bank_statement_lines`
--
ALTER TABLE `bank_statement_lines`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_bank_statement_lines_import_line` (`bank_statement_import_id`,`line_no`);

--
-- فهارس للجدول `branches`
--
ALTER TABLE `branches`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_branches_company_code` (`company_id`,`code`);

--
-- فهارس للجدول `business_partners`
--
ALTER TABLE `business_partners`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_business_partners_company_code` (`company_id`,`partner_code`),
  ADD KEY `idx_business_partners_vat` (`vat_registration_no`),
  ADD KEY `fk_business_partners_currency` (`default_currency_id`);

--
-- فهارس للجدول `cashboxes`
--
ALTER TABLE `cashboxes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_cashboxes_company_code` (`company_id`,`code`),
  ADD KEY `fk_cashboxes_branch` (`branch_id`),
  ADD KEY `fk_cashboxes_currency` (`currency_id`),
  ADD KEY `fk_cashboxes_account` (`account_id`),
  ADD KEY `fk_cashboxes_custodian` (`custodian_user_id`);

--
-- فهارس للجدول `cash_flow_forecasts`
--
ALTER TABLE `cash_flow_forecasts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_cash_flow_forecasts` (`company_id`,`forecast_date`,`scenario`),
  ADD KEY `fk_cash_flow_forecasts_prepared_by` (`prepared_by`),
  ADD KEY `fk_cash_flow_forecasts_approved_by` (`approved_by`);

--
-- فهارس للجدول `cash_flow_forecast_lines`
--
ALTER TABLE `cash_flow_forecast_lines`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_cash_flow_forecast_lines_forecast` (`cash_flow_forecast_id`),
  ADD KEY `fk_cash_flow_forecast_lines_project` (`project_id`);

--
-- فهارس للجدول `cheques`
--
ALTER TABLE `cheques`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_cheques_bank_no` (`bank_account_id`,`cheque_no`),
  ADD KEY `idx_cheques_due_status` (`due_date`,`status`),
  ADD KEY `fk_cheques_company` (`company_id`),
  ADD KEY `fk_cheques_partner` (`partner_id`),
  ADD KEY `fk_cheques_receipt` (`receipt_voucher_id`),
  ADD KEY `fk_cheques_payment` (`payment_voucher_id`);

--
-- فهارس للجدول `closing_checklists`
--
ALTER TABLE `closing_checklists`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_closing_checklists_period` (`company_id`,`fiscal_period_id`),
  ADD KEY `fk_closing_checklists_period` (`fiscal_period_id`),
  ADD KEY `fk_closing_checklists_prepared_by` (`prepared_by`),
  ADD KEY `fk_closing_checklists_reviewed_by` (`reviewed_by`);

--
-- فهارس للجدول `closing_checklist_items`
--
ALTER TABLE `closing_checklist_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_closing_checklist_items_code` (`closing_checklist_id`,`item_code`),
  ADD KEY `fk_closing_checklist_items_completed_by` (`completed_by`);

--
-- فهارس للجدول `companies`
--
ALTER TABLE `companies`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_companies_base_currency` (`base_currency_id`);

--
-- فهارس للجدول `company_account_mappings`
--
ALTER TABLE `company_account_mappings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_company_account_mappings_lookup` (`company_id`,`account_role_id`,`branch_id`,`project_id`,`effective_from`),
  ADD KEY `fk_company_account_mappings_role` (`account_role_id`),
  ADD KEY `fk_company_account_mappings_account` (`account_id`),
  ADD KEY `fk_company_account_mappings_branch` (`branch_id`),
  ADD KEY `fk_company_account_mappings_created_by` (`created_by`),
  ADD KEY `fk_company_account_mappings_project` (`project_id`);

--
-- فهارس للجدول `contract_boq_items`
--
ALTER TABLE `contract_boq_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_contract_boq_items_code` (`contract_id`,`item_code`),
  ADD KEY `fk_contract_boq_items_parent` (`parent_id`),
  ADD KEY `fk_contract_boq_items_cost_code` (`cost_code_id`);

--
-- فهارس للجدول `corporate_budget_lines`
--
ALTER TABLE `corporate_budget_lines`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_corporate_budget_lines_dimensions` (`corporate_budget_version_id`,`fiscal_period_id`,`account_id`,`branch_id`,`project_id`),
  ADD KEY `fk_corporate_budget_lines_period` (`fiscal_period_id`),
  ADD KEY `fk_corporate_budget_lines_account` (`account_id`),
  ADD KEY `fk_corporate_budget_lines_branch` (`branch_id`),
  ADD KEY `fk_corporate_budget_lines_department` (`department_id`),
  ADD KEY `fk_corporate_budget_lines_project` (`project_id`),
  ADD KEY `fk_corporate_budget_lines_cost_center` (`cost_center_id`);

--
-- فهارس للجدول `corporate_budget_versions`
--
ALTER TABLE `corporate_budget_versions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_corporate_budget_versions` (`company_id`,`fiscal_year_id`,`version_no`),
  ADD KEY `fk_corporate_budget_versions_year` (`fiscal_year_id`),
  ADD KEY `fk_corporate_budget_versions_created_by` (`created_by`),
  ADD KEY `fk_corporate_budget_versions_approved_by` (`approved_by`);

--
-- فهارس للجدول `cost_centers`
--
ALTER TABLE `cost_centers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_cost_centers_company_code` (`company_id`,`code`),
  ADD KEY `fk_cost_centers_parent` (`parent_id`),
  ADD KEY `fk_cost_centers_branch` (`branch_id`);

--
-- فهارس للجدول `cost_codes`
--
ALTER TABLE `cost_codes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_cost_codes_company_code` (`company_id`,`code`),
  ADD KEY `fk_cost_codes_parent` (`parent_id`);

--
-- فهارس للجدول `currencies`
--
ALTER TABLE `currencies`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_currencies_code` (`code`);

--
-- فهارس للجدول `custodies`
--
ALTER TABLE `custodies`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_custodies_company_no` (`company_id`,`custody_no`),
  ADD KEY `fk_custodies_branch` (`branch_id`),
  ADD KEY `fk_custodies_employee` (`employee_id`),
  ADD KEY `fk_custodies_project` (`project_id`),
  ADD KEY `fk_custodies_currency` (`currency_id`),
  ADD KEY `fk_custodies_created_by` (`created_by`),
  ADD KEY `fk_custodies_approved_by` (`approved_by`);

--
-- فهارس للجدول `custody_settlements`
--
ALTER TABLE `custody_settlements`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_custody_settlements_no` (`custody_id`,`settlement_no`),
  ADD KEY `fk_custody_settlements_journal` (`journal_entry_id`),
  ADD KEY `fk_custody_settlements_prepared_by` (`prepared_by`),
  ADD KEY `fk_custody_settlements_approved_by` (`approved_by`);

--
-- فهارس للجدول `custody_settlement_lines`
--
ALTER TABLE `custody_settlement_lines`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_custody_settlement_lines_settlement` (`custody_settlement_id`),
  ADD KEY `fk_custody_settlement_lines_project` (`project_id`),
  ADD KEY `fk_custody_settlement_lines_cost_code` (`cost_code_id`),
  ADD KEY `fk_custody_settlement_lines_account` (`expense_account_id`),
  ADD KEY `fk_custody_settlement_lines_partner` (`business_partner_id`),
  ADD KEY `fk_custody_settlement_lines_tax` (`tax_code_id`);

--
-- فهارس للجدول `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_customers_partner` (`partner_id`),
  ADD KEY `fk_customers_receivable_account` (`receivable_account_id`),
  ADD KEY `fk_customers_retention_account` (`retention_account_id`),
  ADD KEY `fk_customers_advance_account` (`advance_account_id`);

--
-- فهارس للجدول `customer_certificates`
--
ALTER TABLE `customer_certificates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_customer_certificates_contract_no` (`contract_id`,`certificate_no`),
  ADD KEY `idx_customer_certificates_project_status` (`project_id`,`status`),
  ADD KEY `fk_customer_certificates_prepared_by` (`prepared_by`),
  ADD KEY `fk_customer_certificates_reviewed_by` (`reviewed_by`);

--
-- فهارس للجدول `customer_certificate_adjustments`
--
ALTER TABLE `customer_certificate_adjustments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_customer_certificate_adjustments_certificate` (`customer_certificate_id`),
  ADD KEY `fk_customer_certificate_adjustments_account` (`account_id`);

--
-- فهارس للجدول `customer_certificate_items`
--
ALTER TABLE `customer_certificate_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_customer_certificate_items_boq` (`customer_certificate_id`,`contract_boq_item_id`),
  ADD KEY `fk_customer_certificate_items_boq` (`contract_boq_item_id`);

--
-- فهارس للجدول `departments`
--
ALTER TABLE `departments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_departments_company_code` (`company_id`,`code`),
  ADD KEY `idx_departments_parent` (`parent_id`),
  ADD KEY `fk_departments_branch` (`branch_id`),
  ADD KEY `fk_departments_manager` (`manager_user_id`);

--
-- فهارس للجدول `depreciation_runs`
--
ALTER TABLE `depreciation_runs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_depreciation_runs_company_no` (`company_id`,`run_no`),
  ADD UNIQUE KEY `uq_depreciation_runs_period` (`company_id`,`fiscal_period_id`),
  ADD KEY `fk_depreciation_runs_period` (`fiscal_period_id`),
  ADD KEY `fk_depreciation_runs_journal` (`journal_entry_id`),
  ADD KEY `fk_depreciation_runs_prepared_by` (`prepared_by`),
  ADD KEY `fk_depreciation_runs_reviewed_by` (`reviewed_by`);

--
-- فهارس للجدول `depreciation_run_lines`
--
ALTER TABLE `depreciation_run_lines`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_depreciation_run_lines_asset` (`depreciation_run_id`,`asset_id`),
  ADD KEY `fk_depreciation_run_lines_asset` (`asset_id`),
  ADD KEY `fk_depreciation_run_lines_project` (`project_id`),
  ADD KEY `fk_depreciation_run_lines_cost_code` (`cost_code_id`);

--
-- فهارس للجدول `document_sequences`
--
ALTER TABLE `document_sequences`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_document_sequences_scope` (`company_id`,`branch_id`,`document_type`,`fiscal_year_id`),
  ADD KEY `fk_document_sequences_branch` (`branch_id`),
  ADD KEY `fk_document_sequences_year` (`fiscal_year_id`);

--
-- فهارس للجدول `electronic_invoices`
--
ALTER TABLE `electronic_invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_electronic_invoices_sales_invoice` (`sales_invoice_id`),
  ADD UNIQUE KEY `uq_electronic_invoices_uuid` (`company_id`,`uuid`);

--
-- فهارس للجدول `electronic_invoice_submissions`
--
ALTER TABLE `electronic_invoice_submissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_electronic_invoice_submissions_invoice` (`electronic_invoice_id`);

--
-- فهارس للجدول `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_employees_company_no` (`company_id`,`employee_no`),
  ADD UNIQUE KEY `uq_employees_user` (`user_id`),
  ADD KEY `fk_employees_partner` (`partner_id`),
  ADD KEY `fk_employees_department` (`department_id`),
  ADD KEY `fk_employees_branch` (`branch_id`),
  ADD KEY `fk_employees_default_project` (`default_project_id`);

--
-- فهارس للجدول `equipment_fuel_logs`
--
ALTER TABLE `equipment_fuel_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_equipment_fuel_asset` (`asset_id`),
  ADD KEY `fk_equipment_fuel_project` (`project_id`),
  ADD KEY `fk_equipment_fuel_supplier` (`supplier_id`),
  ADD KEY `fk_equipment_fuel_created_by` (`created_by`);

--
-- فهارس للجدول `equipment_maintenance`
--
ALTER TABLE `equipment_maintenance`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_equipment_maintenance_asset_no` (`asset_id`,`maintenance_no`),
  ADD KEY `fk_equipment_maintenance_project` (`project_id`),
  ADD KEY `fk_equipment_maintenance_vendor` (`vendor_id`),
  ADD KEY `fk_equipment_maintenance_created_by` (`created_by`);

--
-- فهارس للجدول `equipment_usage_logs`
--
ALTER TABLE `equipment_usage_logs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_equipment_usage_asset_project_date` (`asset_id`,`project_id`,`usage_date`),
  ADD KEY `fk_equipment_usage_project` (`project_id`),
  ADD KEY `fk_equipment_usage_operator` (`operator_employee_id`),
  ADD KEY `fk_equipment_usage_cost_code` (`cost_code_id`),
  ADD KEY `fk_equipment_usage_approved_by` (`approved_by`);

--
-- فهارس للجدول `exchange_rates`
--
ALTER TABLE `exchange_rates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_exchange_rates_company_currency_date` (`company_id`,`currency_id`,`rate_date`),
  ADD KEY `fk_exchange_rates_currency` (`currency_id`);

--
-- فهارس للجدول `fiscal_periods`
--
ALTER TABLE `fiscal_periods`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_fiscal_periods_year_no` (`fiscal_year_id`,`period_no`),
  ADD KEY `fk_fiscal_periods_closed_by` (`closed_by`);

--
-- فهارس للجدول `fiscal_years`
--
ALTER TABLE `fiscal_years`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_fiscal_years_company_code` (`company_id`,`code`),
  ADD KEY `fk_fiscal_years_closed_by` (`closed_by`);

--
-- فهارس للجدول `goods_receipts`
--
ALTER TABLE `goods_receipts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_goods_receipts_company_no` (`company_id`,`receipt_no`),
  ADD KEY `fk_goods_receipts_branch` (`branch_id`),
  ADD KEY `fk_goods_receipts_project` (`project_id`),
  ADD KEY `fk_goods_receipts_warehouse` (`warehouse_id`),
  ADD KEY `fk_goods_receipts_supplier` (`supplier_id`),
  ADD KEY `fk_goods_receipts_po` (`purchase_order_id`),
  ADD KEY `fk_goods_receipts_received_by` (`received_by`),
  ADD KEY `fk_goods_receipts_approved_by` (`approved_by`),
  ADD KEY `fk_goods_receipts_journal` (`journal_entry_id`);

--
-- فهارس للجدول `goods_receipt_items`
--
ALTER TABLE `goods_receipt_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_goods_receipt_items_receipt` (`goods_receipt_id`),
  ADD KEY `fk_goods_receipt_items_po_item` (`purchase_order_item_id`),
  ADD KEY `fk_goods_receipt_items_item` (`item_id`),
  ADD KEY `fk_goods_receipt_items_cost_code` (`cost_code_id`);

--
-- فهارس للجدول `guarantees`
--
ALTER TABLE `guarantees`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_guarantees_company_no` (`company_id`,`guarantee_no`),
  ADD KEY `idx_guarantees_expiry` (`status`,`expires_on`),
  ADD KEY `fk_guarantees_project` (`project_id`),
  ADD KEY `fk_guarantees_tender` (`tender_id`),
  ADD KEY `fk_guarantees_partner` (`partner_id`),
  ADD KEY `fk_guarantees_bank_partner` (`bank_partner_id`),
  ADD KEY `fk_guarantees_currency` (`currency_id`),
  ADD KEY `fk_guarantees_created_by` (`created_by`);

--
-- فهارس للجدول `insurance_policies`
--
ALTER TABLE `insurance_policies`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_insurance_policies_company_no` (`company_id`,`policy_no`),
  ADD KEY `idx_insurance_policies_expiry` (`status`,`expires_on`),
  ADD KEY `fk_insurance_policies_project` (`project_id`),
  ADD KEY `fk_insurance_policies_insurer` (`insurer_partner_id`);

--
-- فهارس للجدول `integration_logs`
--
ALTER TABLE `integration_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_integration_logs_code_status` (`integration_code`,`status`,`started_at`),
  ADD KEY `fk_integration_logs_company` (`company_id`);

--
-- فهارس للجدول `items`
--
ALTER TABLE `items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_items_company_code` (`company_id`,`item_code`),
  ADD KEY `fk_items_category` (`category_id`),
  ADD KEY `fk_items_cost_code` (`default_cost_code_id`),
  ADD KEY `fk_items_tax_code` (`default_tax_code_id`);

--
-- فهارس للجدول `item_categories`
--
ALTER TABLE `item_categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_item_categories_company_code` (`company_id`,`code`),
  ADD KEY `fk_item_categories_parent` (`parent_id`),
  ADD KEY `fk_item_categories_inventory_account` (`inventory_account_id`),
  ADD KEY `fk_item_categories_consumption_account` (`consumption_account_id`),
  ADD KEY `fk_item_categories_variance_account` (`variance_account_id`);

--
-- فهارس للجدول `journal_books`
--
ALTER TABLE `journal_books`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_journal_books_company_code` (`company_id`,`code`),
  ADD KEY `fk_journal_books_sequence` (`sequence_id`);

--
-- فهارس للجدول `journal_entries`
--
ALTER TABLE `journal_entries`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_journal_entries_company_no` (`company_id`,`entry_no`),
  ADD UNIQUE KEY `uq_journal_entries_idempotency` (`company_id`,`idempotency_key`),
  ADD KEY `idx_journal_entries_date_status` (`entry_date`,`status`),
  ADD KEY `idx_journal_entries_source` (`source_type`,`source_id`),
  ADD KEY `fk_journal_entries_branch` (`branch_id`),
  ADD KEY `fk_journal_entries_period` (`fiscal_period_id`),
  ADD KEY `fk_journal_entries_book` (`journal_book_id`),
  ADD KEY `fk_journal_entries_currency` (`currency_id`),
  ADD KEY `fk_journal_entries_reversing` (`reversing_entry_id`),
  ADD KEY `fk_journal_entries_created_by` (`created_by`),
  ADD KEY `fk_journal_entries_approved_by` (`approved_by`),
  ADD KEY `fk_journal_entries_posted_by` (`posted_by`);

--
-- فهارس للجدول `journal_lines`
--
ALTER TABLE `journal_lines`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_journal_lines_entry_line` (`journal_entry_id`,`line_no`),
  ADD KEY `idx_journal_lines_account` (`account_id`),
  ADD KEY `idx_journal_lines_partner` (`business_partner_id`),
  ADD KEY `idx_journal_lines_project_cost` (`project_id`,`cost_code_id`),
  ADD KEY `fk_journal_lines_cost_center` (`cost_center_id`),
  ADD KEY `fk_journal_lines_cost_code` (`cost_code_id`),
  ADD KEY `fk_journal_lines_department` (`department_id`);

--
-- فهارس للجدول `labor_allocations`
--
ALTER TABLE `labor_allocations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_labor_allocations_payroll_item` (`payroll_item_id`),
  ADD KEY `fk_labor_allocations_project` (`project_id`),
  ADD KEY `fk_labor_allocations_cost_code` (`cost_code_id`),
  ADD KEY `fk_labor_allocations_department` (`department_id`);

--
-- فهارس للجدول `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_notifications_user_read` (`user_id`,`read_at`,`created_at`),
  ADD KEY `fk_notifications_company` (`company_id`);

--
-- فهارس للجدول `partner_bank_accounts`
--
ALTER TABLE `partner_bank_accounts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_partner_bank_accounts_iban` (`partner_id`,`iban`),
  ADD KEY `fk_partner_bank_accounts_currency` (`currency_id`),
  ADD KEY `fk_partner_bank_accounts_verified_by` (`verified_by`);

--
-- فهارس للجدول `partner_contacts`
--
ALTER TABLE `partner_contacts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_partner_contacts_partner` (`partner_id`);

--
-- فهارس للجدول `payment_allocations`
--
ALTER TABLE `payment_allocations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_payment_allocations_voucher` (`payment_voucher_id`),
  ADD KEY `fk_payment_allocations_supplier_invoice` (`supplier_invoice_id`),
  ADD KEY `fk_payment_allocations_subcontract_certificate` (`subcontract_certificate_id`);

--
-- فهارس للجدول `payment_vouchers`
--
ALTER TABLE `payment_vouchers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_payment_vouchers_company_no` (`company_id`,`payment_no`),
  ADD KEY `fk_payment_vouchers_branch` (`branch_id`),
  ADD KEY `fk_payment_vouchers_partner` (`partner_id`),
  ADD KEY `fk_payment_vouchers_bank` (`bank_account_id`),
  ADD KEY `fk_payment_vouchers_cashbox` (`cashbox_id`),
  ADD KEY `fk_payment_vouchers_currency` (`currency_id`),
  ADD KEY `fk_payment_vouchers_journal` (`journal_entry_id`),
  ADD KEY `fk_payment_vouchers_created_by` (`created_by`),
  ADD KEY `fk_payment_vouchers_approved_by` (`approved_by`);

--
-- فهارس للجدول `payroll_items`
--
ALTER TABLE `payroll_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_payroll_items_employee` (`payroll_run_id`,`employee_id`),
  ADD KEY `fk_payroll_items_employee` (`employee_id`);

--
-- فهارس للجدول `payroll_runs`
--
ALTER TABLE `payroll_runs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_payroll_runs_company_no` (`company_id`,`payroll_no`),
  ADD KEY `fk_payroll_runs_branch` (`branch_id`),
  ADD KEY `fk_payroll_runs_period` (`fiscal_period_id`),
  ADD KEY `fk_payroll_runs_journal` (`journal_entry_id`),
  ADD KEY `fk_payroll_runs_prepared_by` (`prepared_by`),
  ADD KEY `fk_payroll_runs_approved_by` (`approved_by`);

--
-- فهارس للجدول `permissions`
--
ALTER TABLE `permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_permissions_code` (`code`),
  ADD KEY `idx_permissions_module` (`module_code`);

--
-- فهارس للجدول `projects`
--
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_projects_company_no` (`company_id`,`project_no`),
  ADD KEY `idx_projects_status_manager` (`status`,`project_manager_id`),
  ADD KEY `fk_projects_branch` (`branch_id`),
  ADD KEY `fk_projects_tender` (`tender_id`),
  ADD KEY `fk_projects_customer` (`customer_id`),
  ADD KEY `fk_projects_consultant` (`consultant_partner_id`),
  ADD KEY `fk_projects_cost_center` (`cost_center_id`),
  ADD KEY `fk_projects_manager` (`project_manager_id`),
  ADD KEY `fk_projects_created_by` (`created_by`);

--
-- فهارس للجدول `project_budget_lines`
--
ALTER TABLE `project_budget_lines`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_project_budget_lines_cost` (`budget_version_id`,`cost_code_id`),
  ADD KEY `fk_project_budget_lines_boq` (`contract_boq_item_id`),
  ADD KEY `fk_project_budget_lines_cost_code` (`cost_code_id`),
  ADD KEY `fk_project_budget_lines_account` (`account_id`);

--
-- فهارس للجدول `project_budget_versions`
--
ALTER TABLE `project_budget_versions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_project_budget_versions_project_version` (`project_id`,`version_no`),
  ADD KEY `fk_project_budget_versions_submitted_by` (`submitted_by`),
  ADD KEY `fk_project_budget_versions_approved_by` (`approved_by`),
  ADD KEY `fk_project_budget_versions_created_by` (`created_by`);

--
-- فهارس للجدول `project_claims`
--
ALTER TABLE `project_claims`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_project_claims_project_no` (`project_id`,`claim_no`),
  ADD KEY `fk_project_claims_contract` (`contract_id`),
  ADD KEY `fk_project_claims_created_by` (`created_by`);

--
-- فهارس للجدول `project_contracts`
--
ALTER TABLE `project_contracts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_project_contracts_project_no` (`project_id`,`contract_no`),
  ADD KEY `fk_project_contracts_currency` (`currency_id`),
  ADD KEY `fk_project_contracts_created_by` (`created_by`);

--
-- فهارس للجدول `project_forecasts`
--
ALTER TABLE `project_forecasts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_project_forecasts_project_version` (`project_id`,`version_no`),
  ADD KEY `fk_project_forecasts_prepared_by` (`prepared_by`),
  ADD KEY `fk_project_forecasts_approved_by` (`approved_by`);

--
-- فهارس للجدول `project_forecast_lines`
--
ALTER TABLE `project_forecast_lines`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_project_forecast_lines_cost` (`project_forecast_id`,`cost_code_id`),
  ADD KEY `fk_project_forecast_lines_cost_code` (`cost_code_id`);

--
-- فهارس للجدول `project_members`
--
ALTER TABLE `project_members`
  ADD PRIMARY KEY (`project_id`,`user_id`),
  ADD KEY `fk_project_members_user` (`user_id`);

--
-- فهارس للجدول `purchase_orders`
--
ALTER TABLE `purchase_orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_purchase_orders_company_no` (`company_id`,`po_no`),
  ADD KEY `idx_purchase_orders_project_status` (`project_id`,`status`),
  ADD KEY `idx_purchase_orders_supplier_status` (`supplier_id`,`status`),
  ADD KEY `fk_purchase_orders_branch` (`branch_id`),
  ADD KEY `fk_purchase_orders_quotation` (`supplier_quotation_id`),
  ADD KEY `fk_purchase_orders_warehouse` (`warehouse_id`),
  ADD KEY `fk_purchase_orders_currency` (`currency_id`),
  ADD KEY `fk_purchase_orders_created_by` (`created_by`),
  ADD KEY `fk_purchase_orders_approved_by` (`approved_by`);

--
-- فهارس للجدول `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_purchase_order_items_order` (`purchase_order_id`),
  ADD KEY `fk_purchase_order_items_request_item` (`purchase_request_item_id`),
  ADD KEY `fk_purchase_order_items_item` (`item_id`),
  ADD KEY `fk_purchase_order_items_tax` (`tax_code_id`),
  ADD KEY `fk_purchase_order_items_cost_code` (`cost_code_id`),
  ADD KEY `fk_purchase_order_items_budget` (`budget_line_id`),
  ADD KEY `fk_purchase_order_items_boq` (`contract_boq_item_id`);

--
-- فهارس للجدول `purchase_requests`
--
ALTER TABLE `purchase_requests`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_purchase_requests_company_no` (`company_id`,`request_no`),
  ADD KEY `idx_purchase_requests_project_status` (`project_id`,`status`),
  ADD KEY `fk_purchase_requests_branch` (`branch_id`),
  ADD KEY `fk_purchase_requests_warehouse` (`warehouse_id`),
  ADD KEY `fk_purchase_requests_requested_by` (`requested_by`),
  ADD KEY `fk_purchase_requests_approved_by` (`approved_by`);

--
-- فهارس للجدول `purchase_request_items`
--
ALTER TABLE `purchase_request_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_purchase_request_items_request` (`purchase_request_id`),
  ADD KEY `fk_purchase_request_items_item` (`item_id`),
  ADD KEY `fk_purchase_request_items_cost_code` (`cost_code_id`),
  ADD KEY `fk_purchase_request_items_boq` (`contract_boq_item_id`),
  ADD KEY `fk_purchase_request_items_budget` (`budget_line_id`);

--
-- فهارس للجدول `receipt_allocations`
--
ALTER TABLE `receipt_allocations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_receipt_allocations_voucher` (`receipt_voucher_id`),
  ADD KEY `fk_receipt_allocations_invoice` (`sales_invoice_id`),
  ADD KEY `fk_receipt_allocations_certificate` (`customer_certificate_id`);

--
-- فهارس للجدول `receipt_vouchers`
--
ALTER TABLE `receipt_vouchers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_receipt_vouchers_company_no` (`company_id`,`receipt_no`),
  ADD KEY `fk_receipt_vouchers_branch` (`branch_id`),
  ADD KEY `fk_receipt_vouchers_customer` (`customer_id`),
  ADD KEY `fk_receipt_vouchers_partner` (`partner_id`),
  ADD KEY `fk_receipt_vouchers_bank` (`bank_account_id`),
  ADD KEY `fk_receipt_vouchers_cashbox` (`cashbox_id`),
  ADD KEY `fk_receipt_vouchers_currency` (`currency_id`),
  ADD KEY `fk_receipt_vouchers_journal` (`journal_entry_id`),
  ADD KEY `fk_receipt_vouchers_created_by` (`created_by`),
  ADD KEY `fk_receipt_vouchers_approved_by` (`approved_by`);

--
-- فهارس للجدول `recurring_journals`
--
ALTER TABLE `recurring_journals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_recurring_journals_company` (`company_id`),
  ADD KEY `fk_recurring_journals_book` (`journal_book_id`),
  ADD KEY `fk_recurring_journals_created_by` (`created_by`);

--
-- فهارس للجدول `requests_for_quotation`
--
ALTER TABLE `requests_for_quotation`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_requests_for_quotation_company_no` (`company_id`,`rfq_no`),
  ADD KEY `fk_rfq_project` (`project_id`),
  ADD KEY `fk_rfq_currency` (`currency_id`),
  ADD KEY `fk_rfq_created_by` (`created_by`);

--
-- فهارس للجدول `revenue_recognition_lines`
--
ALTER TABLE `revenue_recognition_lines`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_revenue_recognition_lines_contract` (`revenue_recognition_run_id`,`contract_id`),
  ADD KEY `fk_revenue_recognition_lines_project` (`project_id`),
  ADD KEY `fk_revenue_recognition_lines_contract` (`contract_id`);

--
-- فهارس للجدول `revenue_recognition_runs`
--
ALTER TABLE `revenue_recognition_runs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_revenue_recognition_runs_company_no` (`company_id`,`run_no`),
  ADD UNIQUE KEY `uq_revenue_recognition_runs_period` (`company_id`,`fiscal_period_id`),
  ADD KEY `fk_revenue_recognition_runs_period` (`fiscal_period_id`),
  ADD KEY `fk_revenue_recognition_runs_journal` (`journal_entry_id`),
  ADD KEY `fk_revenue_recognition_runs_prepared_by` (`prepared_by`),
  ADD KEY `fk_revenue_recognition_runs_approved_by` (`approved_by`);

--
-- فهارس للجدول `rfq_items`
--
ALTER TABLE `rfq_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_rfq_items_rfq` (`rfq_id`),
  ADD KEY `fk_rfq_items_request_item` (`purchase_request_item_id`),
  ADD KEY `fk_rfq_items_item` (`item_id`);

--
-- فهارس للجدول `rfq_suppliers`
--
ALTER TABLE `rfq_suppliers`
  ADD PRIMARY KEY (`rfq_id`,`supplier_id`),
  ADD KEY `fk_rfq_suppliers_supplier` (`supplier_id`);

--
-- فهارس للجدول `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_roles_company_code` (`company_id`,`code`);

--
-- فهارس للجدول `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD PRIMARY KEY (`role_id`,`permission_id`),
  ADD KEY `fk_role_permissions_permission` (`permission_id`);

--
-- فهارس للجدول `sales_invoices`
--
ALTER TABLE `sales_invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_sales_invoices_company_no` (`company_id`,`invoice_no`),
  ADD KEY `idx_sales_invoices_customer_due` (`customer_id`,`due_date`,`status`),
  ADD KEY `idx_sales_invoices_project_status` (`project_id`,`status`),
  ADD KEY `fk_sales_invoices_branch` (`branch_id`),
  ADD KEY `fk_sales_invoices_contract` (`contract_id`),
  ADD KEY `fk_sales_invoices_certificate` (`customer_certificate_id`),
  ADD KEY `fk_sales_invoices_original` (`original_invoice_id`),
  ADD KEY `fk_sales_invoices_currency` (`currency_id`),
  ADD KEY `fk_sales_invoices_journal` (`journal_entry_id`),
  ADD KEY `fk_sales_invoices_created_by` (`created_by`),
  ADD KEY `fk_sales_invoices_approved_by` (`approved_by`);

--
-- فهارس للجدول `sales_invoice_items`
--
ALTER TABLE `sales_invoice_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_sales_invoice_items_invoice` (`sales_invoice_id`),
  ADD KEY `fk_sales_invoice_items_boq` (`contract_boq_item_id`),
  ADD KEY `fk_sales_invoice_items_tax` (`tax_code_id`),
  ADD KEY `fk_sales_invoice_items_revenue_account` (`revenue_account_id`),
  ADD KEY `fk_sales_invoice_items_cost_code` (`cost_code_id`);

--
-- فهارس للجدول `schema_migrations`
--
ALTER TABLE `schema_migrations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_schema_migrations_version` (`version`);

--
-- فهارس للجدول `stock_balances`
--
ALTER TABLE `stock_balances`
  ADD PRIMARY KEY (`warehouse_id`,`item_id`,`lot_no`),
  ADD KEY `fk_stock_balances_item` (`item_id`);

--
-- فهارس للجدول `stock_counts`
--
ALTER TABLE `stock_counts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_stock_counts_company_no` (`company_id`,`count_no`),
  ADD KEY `fk_stock_counts_warehouse` (`warehouse_id`),
  ADD KEY `fk_stock_counts_created_by` (`created_by`),
  ADD KEY `fk_stock_counts_approved_by` (`approved_by`),
  ADD KEY `fk_stock_counts_journal` (`journal_entry_id`);

--
-- فهارس للجدول `stock_count_lines`
--
ALTER TABLE `stock_count_lines`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_stock_count_lines_item_lot` (`stock_count_id`,`item_id`,`lot_no`),
  ADD KEY `fk_stock_count_lines_item` (`item_id`);

--
-- فهارس للجدول `stock_transactions`
--
ALTER TABLE `stock_transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_stock_transactions_company_no` (`company_id`,`transaction_no`),
  ADD KEY `idx_stock_transactions_date_type` (`transaction_date`,`transaction_type`),
  ADD KEY `fk_stock_transactions_branch` (`branch_id`),
  ADD KEY `fk_stock_transactions_from_warehouse` (`from_warehouse_id`),
  ADD KEY `fk_stock_transactions_to_warehouse` (`to_warehouse_id`),
  ADD KEY `fk_stock_transactions_project` (`project_id`),
  ADD KEY `fk_stock_transactions_journal` (`journal_entry_id`),
  ADD KEY `fk_stock_transactions_created_by` (`created_by`),
  ADD KEY `fk_stock_transactions_approved_by` (`approved_by`);

--
-- فهارس للجدول `stock_transaction_lines`
--
ALTER TABLE `stock_transaction_lines`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_stock_transaction_lines_transaction` (`stock_transaction_id`),
  ADD KEY `fk_stock_transaction_lines_item` (`item_id`),
  ADD KEY `fk_stock_transaction_lines_cost_code` (`cost_code_id`),
  ADD KEY `fk_stock_transaction_lines_boq` (`contract_boq_item_id`);

--
-- فهارس للجدول `subcontractors`
--
ALTER TABLE `subcontractors`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_subcontractors_partner` (`partner_id`),
  ADD KEY `fk_subcontractors_payable_account` (`payable_account_id`),
  ADD KEY `fk_subcontractors_advance_account` (`advance_account_id`),
  ADD KEY `fk_subcontractors_retention_account` (`retention_account_id`);

--
-- فهارس للجدول `subcontracts`
--
ALTER TABLE `subcontracts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_subcontracts_project_no` (`project_id`,`subcontract_no`),
  ADD KEY `idx_subcontracts_subcontractor_status` (`subcontractor_id`,`status`),
  ADD KEY `fk_subcontracts_company` (`company_id`),
  ADD KEY `fk_subcontracts_currency` (`currency_id`),
  ADD KEY `fk_subcontracts_created_by` (`created_by`),
  ADD KEY `fk_subcontracts_approved_by` (`approved_by`);

--
-- فهارس للجدول `subcontract_certificates`
--
ALTER TABLE `subcontract_certificates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_subcontract_certificates_no` (`subcontract_id`,`certificate_no`),
  ADD KEY `fk_subcontract_certificates_project` (`project_id`),
  ADD KEY `fk_subcontract_certificates_submitted_by` (`submitted_by`),
  ADD KEY `fk_subcontract_certificates_project_approved_by` (`project_approved_by`),
  ADD KEY `fk_subcontract_certificates_finance_approved_by` (`finance_approved_by`),
  ADD KEY `fk_subcontract_certificates_created_by` (`created_by`);

--
-- فهارس للجدول `subcontract_certificate_adjustments`
--
ALTER TABLE `subcontract_certificate_adjustments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_subcontract_certificate_adjustments_certificate` (`subcontract_certificate_id`),
  ADD KEY `fk_subcontract_certificate_adjustments_account` (`account_id`);

--
-- فهارس للجدول `subcontract_certificate_items`
--
ALTER TABLE `subcontract_certificate_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_subcontract_certificate_items_item` (`subcontract_certificate_id`,`subcontract_item_id`),
  ADD KEY `fk_subcontract_certificate_items_subcontract_item` (`subcontract_item_id`);

--
-- فهارس للجدول `subcontract_items`
--
ALTER TABLE `subcontract_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_subcontract_items_code` (`subcontract_id`,`item_code`),
  ADD KEY `fk_subcontract_items_boq` (`contract_boq_item_id`),
  ADD KEY `fk_subcontract_items_cost_code` (`cost_code_id`);

--
-- فهارس للجدول `suppliers`
--
ALTER TABLE `suppliers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_suppliers_partner` (`partner_id`),
  ADD KEY `fk_suppliers_payable_account` (`payable_account_id`),
  ADD KEY `fk_suppliers_advance_account` (`advance_account_id`);

--
-- فهارس للجدول `supplier_invoices`
--
ALTER TABLE `supplier_invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_supplier_invoices_supplier_no` (`supplier_id`,`invoice_no`),
  ADD UNIQUE KEY `uq_supplier_invoices_company_internal` (`company_id`,`internal_no`),
  ADD KEY `idx_supplier_invoices_due_status` (`due_date`,`status`),
  ADD KEY `fk_supplier_invoices_branch` (`branch_id`),
  ADD KEY `fk_supplier_invoices_project` (`project_id`),
  ADD KEY `fk_supplier_invoices_po` (`purchase_order_id`),
  ADD KEY `fk_supplier_invoices_currency` (`currency_id`),
  ADD KEY `fk_supplier_invoices_journal` (`journal_entry_id`),
  ADD KEY `fk_supplier_invoices_created_by` (`created_by`),
  ADD KEY `fk_supplier_invoices_approved_by` (`approved_by`);

--
-- فهارس للجدول `supplier_invoice_items`
--
ALTER TABLE `supplier_invoice_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_supplier_invoice_items_invoice` (`supplier_invoice_id`),
  ADD KEY `fk_supplier_invoice_items_po_item` (`purchase_order_item_id`),
  ADD KEY `fk_supplier_invoice_items_receipt_item` (`goods_receipt_item_id`),
  ADD KEY `fk_supplier_invoice_items_item` (`item_id`),
  ADD KEY `fk_supplier_invoice_items_tax` (`tax_code_id`),
  ADD KEY `fk_supplier_invoice_items_account` (`expense_account_id`),
  ADD KEY `fk_supplier_invoice_items_cost_code` (`cost_code_id`),
  ADD KEY `fk_supplier_invoice_items_boq` (`contract_boq_item_id`);

--
-- فهارس للجدول `supplier_quotations`
--
ALTER TABLE `supplier_quotations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_supplier_quotations_rfq` (`rfq_id`),
  ADD KEY `fk_supplier_quotations_supplier` (`supplier_id`),
  ADD KEY `fk_supplier_quotations_currency` (`currency_id`),
  ADD KEY `fk_supplier_quotations_created_by` (`created_by`);

--
-- فهارس للجدول `supplier_quotation_items`
--
ALTER TABLE `supplier_quotation_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_supplier_quotation_items_quotation` (`supplier_quotation_id`),
  ADD KEY `fk_supplier_quotation_items_rfq_item` (`rfq_item_id`),
  ADD KEY `fk_supplier_quotation_items_item` (`item_id`),
  ADD KEY `fk_supplier_quotation_items_tax` (`tax_code_id`);

--
-- فهارس للجدول `system_settings`
--
ALTER TABLE `system_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_system_settings_company_key` (`company_id`,`setting_group`,`setting_key`),
  ADD KEY `fk_system_settings_updated_by` (`updated_by`);

--
-- فهارس للجدول `tax_codes`
--
ALTER TABLE `tax_codes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_tax_codes_company_code` (`company_id`,`code`),
  ADD KEY `fk_tax_codes_input_account` (`input_account_id`),
  ADD KEY `fk_tax_codes_output_account` (`output_account_id`);

--
-- فهارس للجدول `tax_periods`
--
ALTER TABLE `tax_periods`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_tax_periods_company_type_dates` (`company_id`,`tax_type`,`period_from`,`period_to`);

--
-- فهارس للجدول `tax_returns`
--
ALTER TABLE `tax_returns`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_tax_returns_period` (`tax_period_id`),
  ADD KEY `fk_tax_returns_prepared_by` (`prepared_by`),
  ADD KEY `fk_tax_returns_reviewed_by` (`reviewed_by`),
  ADD KEY `fk_tax_returns_approved_by` (`approved_by`),
  ADD KEY `fk_tax_returns_journal` (`journal_entry_id`);

--
-- فهارس للجدول `tax_return_lines`
--
ALTER TABLE `tax_return_lines`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_tax_return_lines_code` (`tax_return_id`,`line_code`);

--
-- فهارس للجدول `tenders`
--
ALTER TABLE `tenders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_tenders_company_no` (`company_id`,`tender_no`),
  ADD KEY `idx_tenders_status_deadline` (`status`,`submission_deadline`),
  ADD KEY `fk_tenders_branch` (`branch_id`),
  ADD KEY `fk_tenders_customer` (`customer_id`),
  ADD KEY `fk_tenders_consultant` (`consultant_partner_id`),
  ADD KEY `fk_tenders_currency` (`currency_id`),
  ADD KEY `fk_tenders_owner` (`owner_user_id`),
  ADD KEY `fk_tenders_created_by` (`created_by`);

--
-- فهارس للجدول `tender_boq_items`
--
ALTER TABLE `tender_boq_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_tender_boq_items_code` (`tender_id`,`item_code`),
  ADD KEY `fk_tender_boq_items_parent` (`parent_id`);

--
-- فهارس للجدول `tender_estimate_components`
--
ALTER TABLE `tender_estimate_components`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_tender_estimate_components_boq` (`tender_boq_item_id`),
  ADD KEY `fk_tender_estimate_components_cost_code` (`cost_code_id`);

--
-- فهارس للجدول `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_users_company_email` (`company_id`,`email`),
  ADD UNIQUE KEY `uq_users_company_employee` (`company_id`,`employee_no`),
  ADD KEY `fk_users_branch` (`branch_id`),
  ADD KEY `fk_users_department` (`department_id`);

--
-- فهارس للجدول `user_roles`
--
ALTER TABLE `user_roles`
  ADD PRIMARY KEY (`user_id`,`role_id`),
  ADD KEY `fk_user_roles_role` (`role_id`),
  ADD KEY `fk_user_roles_branch` (`branch_id`);

--
-- فهارس للجدول `variation_orders`
--
ALTER TABLE `variation_orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_variation_orders_contract_no` (`contract_id`,`variation_no`),
  ADD KEY `fk_variation_orders_project` (`project_id`),
  ADD KEY `fk_variation_orders_created_by` (`created_by`);

--
-- فهارس للجدول `variation_order_items`
--
ALTER TABLE `variation_order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_variation_order_items_order` (`variation_order_id`),
  ADD KEY `fk_variation_order_items_boq` (`contract_boq_item_id`),
  ADD KEY `fk_variation_order_items_cost_code` (`cost_code_id`);

--
-- فهارس للجدول `warehouses`
--
ALTER TABLE `warehouses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_warehouses_company_code` (`company_id`,`code`),
  ADD KEY `fk_warehouses_branch` (`branch_id`),
  ADD KEY `fk_warehouses_project` (`project_id`),
  ADD KEY `fk_warehouses_custodian` (`custodian_user_id`);

--
-- فهارس للجدول `workflow_definitions`
--
ALTER TABLE `workflow_definitions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_workflow_definitions_code_version` (`company_id`,`workflow_code`,`version_no`),
  ADD KEY `fk_workflow_definitions_created_by` (`created_by`);

--
-- فهارس للجدول `workflow_steps`
--
ALTER TABLE `workflow_steps`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_workflow_steps_definition_no` (`workflow_definition_id`,`step_no`),
  ADD KEY `fk_workflow_steps_user` (`approver_user_id`),
  ADD KEY `fk_workflow_steps_role` (`approver_role_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `accounting_rules`
--
ALTER TABLE `accounting_rules`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `accounting_rule_lines`
--
ALTER TABLE `accounting_rule_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `accounts`
--
ALTER TABLE `accounts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `account_roles`
--
ALTER TABLE `account_roles`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `account_types`
--
ALTER TABLE `account_types`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `api_clients`
--
ALTER TABLE `api_clients`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `approval_actions`
--
ALTER TABLE `approval_actions`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `approval_instances`
--
ALTER TABLE `approval_instances`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `assets`
--
ALTER TABLE `assets`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `asset_categories`
--
ALTER TABLE `asset_categories`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `attachments`
--
ALTER TABLE `attachments`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `audit_logs`
--
ALTER TABLE `audit_logs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bank_accounts`
--
ALTER TABLE `bank_accounts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bank_reconciliations`
--
ALTER TABLE `bank_reconciliations`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bank_reconciliation_lines`
--
ALTER TABLE `bank_reconciliation_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bank_statement_imports`
--
ALTER TABLE `bank_statement_imports`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bank_statement_lines`
--
ALTER TABLE `bank_statement_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `branches`
--
ALTER TABLE `branches`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `business_partners`
--
ALTER TABLE `business_partners`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `cashboxes`
--
ALTER TABLE `cashboxes`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `cash_flow_forecasts`
--
ALTER TABLE `cash_flow_forecasts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `cash_flow_forecast_lines`
--
ALTER TABLE `cash_flow_forecast_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `cheques`
--
ALTER TABLE `cheques`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `closing_checklists`
--
ALTER TABLE `closing_checklists`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `closing_checklist_items`
--
ALTER TABLE `closing_checklist_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `companies`
--
ALTER TABLE `companies`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `company_account_mappings`
--
ALTER TABLE `company_account_mappings`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `contract_boq_items`
--
ALTER TABLE `contract_boq_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `corporate_budget_lines`
--
ALTER TABLE `corporate_budget_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `corporate_budget_versions`
--
ALTER TABLE `corporate_budget_versions`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `cost_centers`
--
ALTER TABLE `cost_centers`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `cost_codes`
--
ALTER TABLE `cost_codes`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `currencies`
--
ALTER TABLE `currencies`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `custodies`
--
ALTER TABLE `custodies`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `custody_settlements`
--
ALTER TABLE `custody_settlements`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `custody_settlement_lines`
--
ALTER TABLE `custody_settlement_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `customer_certificates`
--
ALTER TABLE `customer_certificates`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `customer_certificate_adjustments`
--
ALTER TABLE `customer_certificate_adjustments`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `customer_certificate_items`
--
ALTER TABLE `customer_certificate_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `departments`
--
ALTER TABLE `departments`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `depreciation_runs`
--
ALTER TABLE `depreciation_runs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `depreciation_run_lines`
--
ALTER TABLE `depreciation_run_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `document_sequences`
--
ALTER TABLE `document_sequences`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `electronic_invoices`
--
ALTER TABLE `electronic_invoices`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `electronic_invoice_submissions`
--
ALTER TABLE `electronic_invoice_submissions`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `equipment_fuel_logs`
--
ALTER TABLE `equipment_fuel_logs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `equipment_maintenance`
--
ALTER TABLE `equipment_maintenance`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `equipment_usage_logs`
--
ALTER TABLE `equipment_usage_logs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `exchange_rates`
--
ALTER TABLE `exchange_rates`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fiscal_periods`
--
ALTER TABLE `fiscal_periods`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fiscal_years`
--
ALTER TABLE `fiscal_years`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `goods_receipts`
--
ALTER TABLE `goods_receipts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `goods_receipt_items`
--
ALTER TABLE `goods_receipt_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `guarantees`
--
ALTER TABLE `guarantees`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `insurance_policies`
--
ALTER TABLE `insurance_policies`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `integration_logs`
--
ALTER TABLE `integration_logs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `items`
--
ALTER TABLE `items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `item_categories`
--
ALTER TABLE `item_categories`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `journal_books`
--
ALTER TABLE `journal_books`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `journal_entries`
--
ALTER TABLE `journal_entries`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `journal_lines`
--
ALTER TABLE `journal_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `labor_allocations`
--
ALTER TABLE `labor_allocations`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `partner_bank_accounts`
--
ALTER TABLE `partner_bank_accounts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `partner_contacts`
--
ALTER TABLE `partner_contacts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payment_allocations`
--
ALTER TABLE `payment_allocations`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payment_vouchers`
--
ALTER TABLE `payment_vouchers`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payroll_items`
--
ALTER TABLE `payroll_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payroll_runs`
--
ALTER TABLE `payroll_runs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `permissions`
--
ALTER TABLE `permissions`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT for table `projects`
--
ALTER TABLE `projects`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_budget_lines`
--
ALTER TABLE `project_budget_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_budget_versions`
--
ALTER TABLE `project_budget_versions`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_claims`
--
ALTER TABLE `project_claims`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_contracts`
--
ALTER TABLE `project_contracts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_forecasts`
--
ALTER TABLE `project_forecasts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_forecast_lines`
--
ALTER TABLE `project_forecast_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchase_requests`
--
ALTER TABLE `purchase_requests`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchase_request_items`
--
ALTER TABLE `purchase_request_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `receipt_allocations`
--
ALTER TABLE `receipt_allocations`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `receipt_vouchers`
--
ALTER TABLE `receipt_vouchers`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `recurring_journals`
--
ALTER TABLE `recurring_journals`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `requests_for_quotation`
--
ALTER TABLE `requests_for_quotation`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `revenue_recognition_lines`
--
ALTER TABLE `revenue_recognition_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `revenue_recognition_runs`
--
ALTER TABLE `revenue_recognition_runs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `rfq_items`
--
ALTER TABLE `rfq_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sales_invoices`
--
ALTER TABLE `sales_invoices`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sales_invoice_items`
--
ALTER TABLE `sales_invoice_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `schema_migrations`
--
ALTER TABLE `schema_migrations`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `stock_counts`
--
ALTER TABLE `stock_counts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `stock_count_lines`
--
ALTER TABLE `stock_count_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `stock_transactions`
--
ALTER TABLE `stock_transactions`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `stock_transaction_lines`
--
ALTER TABLE `stock_transaction_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `subcontractors`
--
ALTER TABLE `subcontractors`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `subcontracts`
--
ALTER TABLE `subcontracts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `subcontract_certificates`
--
ALTER TABLE `subcontract_certificates`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `subcontract_certificate_adjustments`
--
ALTER TABLE `subcontract_certificate_adjustments`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `subcontract_certificate_items`
--
ALTER TABLE `subcontract_certificate_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `subcontract_items`
--
ALTER TABLE `subcontract_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `suppliers`
--
ALTER TABLE `suppliers`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_invoices`
--
ALTER TABLE `supplier_invoices`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_invoice_items`
--
ALTER TABLE `supplier_invoice_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_quotations`
--
ALTER TABLE `supplier_quotations`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_quotation_items`
--
ALTER TABLE `supplier_quotation_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `system_settings`
--
ALTER TABLE `system_settings`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tax_codes`
--
ALTER TABLE `tax_codes`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tax_periods`
--
ALTER TABLE `tax_periods`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tax_returns`
--
ALTER TABLE `tax_returns`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tax_return_lines`
--
ALTER TABLE `tax_return_lines`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tenders`
--
ALTER TABLE `tenders`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tender_boq_items`
--
ALTER TABLE `tender_boq_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tender_estimate_components`
--
ALTER TABLE `tender_estimate_components`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `variation_orders`
--
ALTER TABLE `variation_orders`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `variation_order_items`
--
ALTER TABLE `variation_order_items`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `warehouses`
--
ALTER TABLE `warehouses`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `workflow_definitions`
--
ALTER TABLE `workflow_definitions`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `workflow_steps`
--
ALTER TABLE `workflow_steps`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

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

--
-- Structure for view `vw_ap_open_items`
--
DROP TABLE IF EXISTS `vw_ap_open_items`;

CREATE ALGORITHM=UNDEFINED DEFINER=`cpses_ma0wr2ocj0`@`localhost` SQL SECURITY DEFINER VIEW `vw_ap_open_items`  AS SELECT 'SUPPLIER_INVOICE' AS `source_type`, `spi`.`id` AS `source_id`, `spi`.`company_id` AS `company_id`, `spi`.`supplier_id` AS `supplier_or_subcontractor_id`, `spi`.`project_id` AS `project_id`, `spi`.`internal_no` AS `source_no`, `spi`.`invoice_date` AS `source_date`, `spi`.`due_date` AS `due_date`, `spi`.`total_amount` AS `total_amount`, `spi`.`paid_amount` AS `paid_amount`, greatest((`spi`.`total_amount` - `spi`.`paid_amount`),0) AS `outstanding_amount`, `spi`.`status` AS `status` FROM `supplier_invoices` AS `spi` WHERE ((`spi`.`status` not in ('draft','cancelled','paid')) AND (`spi`.`total_amount` > `spi`.`paid_amount`))union all select 'SUBCONTRACT_CERTIFICATE' AS `source_type`,`scc`.`id` AS `source_id`,`sc`.`company_id` AS `company_id`,`sc`.`subcontractor_id` AS `supplier_or_subcontractor_id`,`scc`.`project_id` AS `project_id`,`scc`.`certificate_no` AS `source_no`,`scc`.`certificate_date` AS `source_date`,NULL AS `due_date`,`scc`.`net_payable_amount` AS `total_amount`,coalesce((select sum(`pa`.`allocated_amount`) from `payment_allocations` `pa` where (`pa`.`subcontract_certificate_id` = `scc`.`id`)),0) AS `paid_amount`,greatest((`scc`.`net_payable_amount` - coalesce((select sum(`pa2`.`allocated_amount`) from `payment_allocations` `pa2` where (`pa2`.`subcontract_certificate_id` = `scc`.`id`)),0)),0) AS `outstanding_amount`,`scc`.`status` AS `status` from (`subcontract_certificates` `scc` join `subcontracts` `sc` on((`sc`.`id` = `scc`.`subcontract_id`))) where (`scc`.`status` not in ('draft','rejected','cancelled','paid'))  ;

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

--
-- Structure for view `vw_ar_open_items`
--
DROP TABLE IF EXISTS `vw_ar_open_items`;

CREATE ALGORITHM=UNDEFINED DEFINER=`cpses_ma0wr2ocj0`@`localhost` SQL SECURITY DEFINER VIEW `vw_ar_open_items`  AS SELECT `si`.`id` AS `sales_invoice_id`, `si`.`company_id` AS `company_id`, `si`.`customer_id` AS `customer_id`, `si`.`project_id` AS `project_id`, `si`.`invoice_no` AS `invoice_no`, `si`.`invoice_date` AS `invoice_date`, `si`.`due_date` AS `due_date`, `si`.`net_due_amount` AS `net_due_amount`, `si`.`collected_amount` AS `collected_amount`, greatest((`si`.`net_due_amount` - `si`.`collected_amount`),0) AS `outstanding_amount`, greatest((to_days(curdate()) - to_days(`si`.`due_date`)),0) AS `days_overdue`, `si`.`status` AS `status` FROM `sales_invoices` AS `si` WHERE ((`si`.`status` not in ('draft','cancelled','collected')) AND (`si`.`net_due_amount` > `si`.`collected_amount`)) ;

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

--
-- Structure for view `vw_inventory_value`
--
DROP TABLE IF EXISTS `vw_inventory_value`;

CREATE ALGORITHM=UNDEFINED DEFINER=`cpses_ma0wr2ocj0`@`localhost` SQL SECURITY DEFINER VIEW `vw_inventory_value`  AS SELECT `sb`.`warehouse_id` AS `warehouse_id`, `w`.`company_id` AS `company_id`, `w`.`project_id` AS `project_id`, `sb`.`item_id` AS `item_id`, `i`.`item_code` AS `item_code`, `i`.`name_ar` AS `item_name`, sum(`sb`.`quantity_on_hand`) AS `quantity_on_hand`, max(`sb`.`average_unit_cost`) AS `average_unit_cost`, sum((`sb`.`quantity_on_hand` * `sb`.`average_unit_cost`)) AS `inventory_value` FROM ((`stock_balances` `sb` join `warehouses` `w` on((`w`.`id` = `sb`.`warehouse_id`))) join `items` `i` on((`i`.`id` = `sb`.`item_id`))) GROUP BY `sb`.`warehouse_id`, `w`.`company_id`, `w`.`project_id`, `sb`.`item_id`, `i`.`item_code`, `i`.`name_ar` ;

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

--
-- Structure for view `vw_journal_entry_balance`
--
DROP TABLE IF EXISTS `vw_journal_entry_balance`;

CREATE ALGORITHM=UNDEFINED DEFINER=`cpses_ma0wr2ocj0`@`localhost` SQL SECURITY DEFINER VIEW `vw_journal_entry_balance`  AS SELECT `je`.`id` AS `journal_entry_id`, `je`.`company_id` AS `company_id`, `je`.`entry_no` AS `entry_no`, `je`.`entry_date` AS `entry_date`, `je`.`status` AS `status`, round(sum(`jl`.`debit_amount`),2) AS `total_debit`, round(sum(`jl`.`credit_amount`),2) AS `total_credit`, round((sum(`jl`.`debit_amount`) - sum(`jl`.`credit_amount`)),2) AS `difference_amount`, (case when (round((sum(`jl`.`debit_amount`) - sum(`jl`.`credit_amount`)),2) = 0) then 1 else 0 end) AS `is_balanced` FROM (`journal_entries` `je` join `journal_lines` `jl` on((`jl`.`journal_entry_id` = `je`.`id`))) GROUP BY `je`.`id`, `je`.`company_id`, `je`.`entry_no`, `je`.`entry_date`, `je`.`status` ;

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

--
-- Structure for view `vw_project_commitments`
--
DROP TABLE IF EXISTS `vw_project_commitments`;

CREATE ALGORITHM=UNDEFINED DEFINER=`cpses_ma0wr2ocj0`@`localhost` SQL SECURITY DEFINER VIEW `vw_project_commitments`  AS SELECT `po`.`project_id` AS `project_id`, 'PURCHASE_ORDER' AS `commitment_type`, `po`.`id` AS `source_id`, `po`.`po_no` AS `source_no`, sum((greatest((`poi`.`ordered_qty` - `poi`.`invoiced_qty`),0) * `poi`.`unit_price`)) AS `committed_amount` FROM (`purchase_orders` `po` join `purchase_order_items` `poi` on((`poi`.`purchase_order_id` = `po`.`id`))) WHERE ((`po`.`project_id` is not null) AND (`po`.`status` not in ('draft','closed','cancelled','invoiced'))) GROUP BY `po`.`project_id`, `po`.`id`, `po`.`po_no`union all select `sc`.`project_id` AS `project_id`,'SUBCONTRACT' AS `commitment_type`,`sc`.`id` AS `source_id`,`sc`.`subcontract_no` AS `source_no`,greatest((`sc`.`original_value` - coalesce(sum(`scc`.`gross_amount`),0)),0) AS `committed_amount` from (`subcontracts` `sc` left join `subcontract_certificates` `scc` on(((`scc`.`subcontract_id` = `sc`.`id`) and (`scc`.`status` not in ('draft','rejected','cancelled'))))) where (`sc`.`status` in ('active','suspended','completed')) group by `sc`.`project_id`,`sc`.`id`,`sc`.`subcontract_no`,`sc`.`original_value`  ;

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

--
-- Structure for view `vw_project_cost_summary`
--
DROP TABLE IF EXISTS `vw_project_cost_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`cpses_ma0wr2ocj0`@`localhost` SQL SECURITY DEFINER VIEW `vw_project_cost_summary`  AS SELECT `jl`.`project_id` AS `project_id`, `jl`.`cost_code_id` AS `cost_code_id`, `cc`.`code` AS `cost_code`, `cc`.`name_ar` AS `cost_code_name`, sum((`jl`.`debit_amount` - `jl`.`credit_amount`)) AS `actual_cost` FROM ((((`journal_lines` `jl` join `journal_entries` `je` on(((`je`.`id` = `jl`.`journal_entry_id`) and (`je`.`status` = 'posted')))) join `accounts` `a` on((`a`.`id` = `jl`.`account_id`))) join `account_types` `atp` on((`atp`.`id` = `a`.`account_type_id`))) left join `cost_codes` `cc` on((`cc`.`id` = `jl`.`cost_code_id`))) WHERE ((`jl`.`project_id` is not null) AND (`atp`.`code` in ('COST_OF_REVENUE','EXPENSE'))) GROUP BY `jl`.`project_id`, `jl`.`cost_code_id`, `cc`.`code`, `cc`.`name_ar` ;

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

--
-- Structure for view `vw_project_financial_summary`
--
DROP TABLE IF EXISTS `vw_project_financial_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`cpses_ma0wr2ocj0`@`localhost` SQL SECURITY DEFINER VIEW `vw_project_financial_summary`  AS SELECT `p`.`id` AS `project_id`, `p`.`company_id` AS `company_id`, `p`.`project_no` AS `project_no`, `p`.`name_ar` AS `project_name`, `p`.`status` AS `status`, coalesce((select sum(`pc`.`original_value`) from `project_contracts` `pc` where ((`pc`.`project_id` = `p`.`id`) and (`pc`.`status` <> 'terminated'))),0) AS `original_contract_value`, coalesce((select sum(`vo`.`approved_value`) from `variation_orders` `vo` where ((`vo`.`project_id` = `p`.`id`) and (`vo`.`status` in ('approved','partially_approved')))),0) AS `approved_variations`, coalesce((select sum(`ccert`.`certified_gross_amount`) from `customer_certificates` `ccert` where ((`ccert`.`project_id` = `p`.`id`) and (`ccert`.`status` not in ('draft','rejected','cancelled')))),0) AS `certified_revenue`, coalesce((select sum(`si`.`gross_amount`) from `sales_invoices` `si` where ((`si`.`project_id` = `p`.`id`) and (`si`.`status` not in ('draft','cancelled')))),0) AS `invoiced_amount`, coalesce((select sum(`ra`.`allocated_amount`) from (`receipt_allocations` `ra` join `sales_invoices` `si2` on((`si2`.`id` = `ra`.`sales_invoice_id`))) where (`si2`.`project_id` = `p`.`id`)),0) AS `collected_amount`, coalesce((select sum(`pcs`.`actual_cost`) from `vw_project_cost_summary` `pcs` where (`pcs`.`project_id` = `p`.`id`)),0) AS `actual_cost`, coalesce((select sum(`pcmt`.`committed_amount`) from `vw_project_commitments` `pcmt` where (`pcmt`.`project_id` = `p`.`id`)),0) AS `committed_cost` FROM `projects` AS `p` ;

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

--
-- Structure for view `vw_trial_balance`
--
DROP TABLE IF EXISTS `vw_trial_balance`;

CREATE ALGORITHM=UNDEFINED DEFINER=`cpses_ma0wr2ocj0`@`localhost` SQL SECURITY DEFINER VIEW `vw_trial_balance`  AS SELECT `je`.`company_id` AS `company_id`, `je`.`branch_id` AS `branch_id`, `je`.`fiscal_period_id` AS `fiscal_period_id`, `jl`.`account_id` AS `account_id`, `a`.`code` AS `account_code`, `a`.`name_ar` AS `account_name`, sum(`jl`.`debit_amount`) AS `total_debit`, sum(`jl`.`credit_amount`) AS `total_credit`, sum((`jl`.`debit_amount` - `jl`.`credit_amount`)) AS `net_balance` FROM ((`journal_entries` `je` join `journal_lines` `jl` on((`jl`.`journal_entry_id` = `je`.`id`))) join `accounts` `a` on((`a`.`id` = `jl`.`account_id`))) WHERE (`je`.`status` = 'posted') GROUP BY `je`.`company_id`, `je`.`branch_id`, `je`.`fiscal_period_id`, `jl`.`account_id`, `a`.`code`, `a`.`name_ar` ;

--
-- القيود المفروضة على الجداول الملقاة
--

--
-- قيود الجداول `accounting_rules`
--
ALTER TABLE `accounting_rules`
  ADD CONSTRAINT `fk_accounting_rules_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_accounting_rules_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `accounting_rule_lines`
--
ALTER TABLE `accounting_rule_lines`
  ADD CONSTRAINT `fk_accounting_rule_lines_role` FOREIGN KEY (`account_role_id`) REFERENCES `account_roles` (`id`),
  ADD CONSTRAINT `fk_accounting_rule_lines_rule` FOREIGN KEY (`accounting_rule_id`) REFERENCES `accounting_rules` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `accounts`
--
ALTER TABLE `accounts`
  ADD CONSTRAINT `fk_accounts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_accounts_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_accounts_parent` FOREIGN KEY (`parent_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_accounts_type` FOREIGN KEY (`account_type_id`) REFERENCES `account_types` (`id`);

--
-- قيود الجداول `api_clients`
--
ALTER TABLE `api_clients`
  ADD CONSTRAINT `fk_api_clients_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_api_clients_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `approval_actions`
--
ALTER TABLE `approval_actions`
  ADD CONSTRAINT `fk_approval_actions_acted_by` FOREIGN KEY (`acted_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_approval_actions_delegated_to` FOREIGN KEY (`delegated_to`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_approval_actions_instance` FOREIGN KEY (`approval_instance_id`) REFERENCES `approval_instances` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_approval_actions_step` FOREIGN KEY (`workflow_step_id`) REFERENCES `workflow_steps` (`id`);

--
-- قيود الجداول `approval_instances`
--
ALTER TABLE `approval_instances`
  ADD CONSTRAINT `fk_approval_instances_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_approval_instances_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_approval_instances_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_approval_instances_workflow` FOREIGN KEY (`workflow_definition_id`) REFERENCES `workflow_definitions` (`id`);

--
-- قيود الجداول `assets`
--
ALTER TABLE `assets`
  ADD CONSTRAINT `fk_assets_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_assets_category` FOREIGN KEY (`asset_category_id`) REFERENCES `asset_categories` (`id`),
  ADD CONSTRAINT `fk_assets_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_assets_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_assets_custodian` FOREIGN KEY (`custodian_employee_id`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `fk_assets_project` FOREIGN KEY (`current_project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_assets_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  ADD CONSTRAINT `fk_assets_supplier_invoice` FOREIGN KEY (`supplier_invoice_id`) REFERENCES `supplier_invoices` (`id`);

--
-- قيود الجداول `asset_categories`
--
ALTER TABLE `asset_categories`
  ADD CONSTRAINT `fk_asset_categories_accumulated_account` FOREIGN KEY (`accumulated_depreciation_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_asset_categories_asset_account` FOREIGN KEY (`asset_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_asset_categories_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_asset_categories_expense_account` FOREIGN KEY (`depreciation_expense_account_id`) REFERENCES `accounts` (`id`);

--
-- قيود الجداول `attachments`
--
ALTER TABLE `attachments`
  ADD CONSTRAINT `fk_attachments_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_attachments_uploaded_by` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD CONSTRAINT `fk_audit_logs_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_audit_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- قيود الجداول `bank_accounts`
--
ALTER TABLE `bank_accounts`
  ADD CONSTRAINT `fk_bank_accounts_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_bank_accounts_bank_partner` FOREIGN KEY (`bank_partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_bank_accounts_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_bank_accounts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_bank_accounts_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`);

--
-- قيود الجداول `bank_reconciliations`
--
ALTER TABLE `bank_reconciliations`
  ADD CONSTRAINT `fk_bank_reconciliations_account` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`),
  ADD CONSTRAINT `fk_bank_reconciliations_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_bank_reconciliations_import` FOREIGN KEY (`bank_statement_import_id`) REFERENCES `bank_statement_imports` (`id`),
  ADD CONSTRAINT `fk_bank_reconciliations_prepared_by` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `bank_reconciliation_lines`
--
ALTER TABLE `bank_reconciliation_lines`
  ADD CONSTRAINT `fk_bank_reconciliation_lines_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_bank_reconciliation_lines_matched_by` FOREIGN KEY (`matched_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_bank_reconciliation_lines_payment` FOREIGN KEY (`payment_voucher_id`) REFERENCES `payment_vouchers` (`id`),
  ADD CONSTRAINT `fk_bank_reconciliation_lines_receipt` FOREIGN KEY (`receipt_voucher_id`) REFERENCES `receipt_vouchers` (`id`),
  ADD CONSTRAINT `fk_bank_reconciliation_lines_reconciliation` FOREIGN KEY (`bank_reconciliation_id`) REFERENCES `bank_reconciliations` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_bank_reconciliation_lines_statement_line` FOREIGN KEY (`bank_statement_line_id`) REFERENCES `bank_statement_lines` (`id`);

--
-- قيود الجداول `bank_statement_imports`
--
ALTER TABLE `bank_statement_imports`
  ADD CONSTRAINT `fk_bank_statement_imports_account` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`),
  ADD CONSTRAINT `fk_bank_statement_imports_imported_by` FOREIGN KEY (`imported_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `bank_statement_lines`
--
ALTER TABLE `bank_statement_lines`
  ADD CONSTRAINT `fk_bank_statement_lines_import` FOREIGN KEY (`bank_statement_import_id`) REFERENCES `bank_statement_imports` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `branches`
--
ALTER TABLE `branches`
  ADD CONSTRAINT `fk_branches_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`);

--
-- قيود الجداول `business_partners`
--
ALTER TABLE `business_partners`
  ADD CONSTRAINT `fk_business_partners_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_business_partners_currency` FOREIGN KEY (`default_currency_id`) REFERENCES `currencies` (`id`);

--
-- قيود الجداول `cashboxes`
--
ALTER TABLE `cashboxes`
  ADD CONSTRAINT `fk_cashboxes_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_cashboxes_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_cashboxes_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_cashboxes_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_cashboxes_custodian` FOREIGN KEY (`custodian_user_id`) REFERENCES `users` (`id`);

--
-- قيود الجداول `cash_flow_forecasts`
--
ALTER TABLE `cash_flow_forecasts`
  ADD CONSTRAINT `fk_cash_flow_forecasts_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_cash_flow_forecasts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_cash_flow_forecasts_prepared_by` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `cash_flow_forecast_lines`
--
ALTER TABLE `cash_flow_forecast_lines`
  ADD CONSTRAINT `fk_cash_flow_forecast_lines_forecast` FOREIGN KEY (`cash_flow_forecast_id`) REFERENCES `cash_flow_forecasts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_cash_flow_forecast_lines_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `cheques`
--
ALTER TABLE `cheques`
  ADD CONSTRAINT `fk_cheques_bank` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`),
  ADD CONSTRAINT `fk_cheques_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_cheques_partner` FOREIGN KEY (`partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_cheques_payment` FOREIGN KEY (`payment_voucher_id`) REFERENCES `payment_vouchers` (`id`),
  ADD CONSTRAINT `fk_cheques_receipt` FOREIGN KEY (`receipt_voucher_id`) REFERENCES `receipt_vouchers` (`id`);

--
-- قيود الجداول `closing_checklists`
--
ALTER TABLE `closing_checklists`
  ADD CONSTRAINT `fk_closing_checklists_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_closing_checklists_period` FOREIGN KEY (`fiscal_period_id`) REFERENCES `fiscal_periods` (`id`),
  ADD CONSTRAINT `fk_closing_checklists_prepared_by` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_closing_checklists_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `closing_checklist_items`
--
ALTER TABLE `closing_checklist_items`
  ADD CONSTRAINT `fk_closing_checklist_items_checklist` FOREIGN KEY (`closing_checklist_id`) REFERENCES `closing_checklists` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_closing_checklist_items_completed_by` FOREIGN KEY (`completed_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `companies`
--
ALTER TABLE `companies`
  ADD CONSTRAINT `fk_companies_base_currency` FOREIGN KEY (`base_currency_id`) REFERENCES `currencies` (`id`);

--
-- قيود الجداول `company_account_mappings`
--
ALTER TABLE `company_account_mappings`
  ADD CONSTRAINT `fk_company_account_mappings_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_company_account_mappings_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_company_account_mappings_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_company_account_mappings_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_company_account_mappings_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_company_account_mappings_role` FOREIGN KEY (`account_role_id`) REFERENCES `account_roles` (`id`);

--
-- قيود الجداول `contract_boq_items`
--
ALTER TABLE `contract_boq_items`
  ADD CONSTRAINT `fk_contract_boq_items_contract` FOREIGN KEY (`contract_id`) REFERENCES `project_contracts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_contract_boq_items_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_contract_boq_items_parent` FOREIGN KEY (`parent_id`) REFERENCES `contract_boq_items` (`id`);

--
-- قيود الجداول `corporate_budget_lines`
--
ALTER TABLE `corporate_budget_lines`
  ADD CONSTRAINT `fk_corporate_budget_lines_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_corporate_budget_lines_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_corporate_budget_lines_cost_center` FOREIGN KEY (`cost_center_id`) REFERENCES `cost_centers` (`id`),
  ADD CONSTRAINT `fk_corporate_budget_lines_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`),
  ADD CONSTRAINT `fk_corporate_budget_lines_period` FOREIGN KEY (`fiscal_period_id`) REFERENCES `fiscal_periods` (`id`),
  ADD CONSTRAINT `fk_corporate_budget_lines_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_corporate_budget_lines_version` FOREIGN KEY (`corporate_budget_version_id`) REFERENCES `corporate_budget_versions` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `corporate_budget_versions`
--
ALTER TABLE `corporate_budget_versions`
  ADD CONSTRAINT `fk_corporate_budget_versions_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_corporate_budget_versions_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_corporate_budget_versions_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_corporate_budget_versions_year` FOREIGN KEY (`fiscal_year_id`) REFERENCES `fiscal_years` (`id`);

--
-- قيود الجداول `cost_centers`
--
ALTER TABLE `cost_centers`
  ADD CONSTRAINT `fk_cost_centers_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_cost_centers_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_cost_centers_parent` FOREIGN KEY (`parent_id`) REFERENCES `cost_centers` (`id`);

--
-- قيود الجداول `cost_codes`
--
ALTER TABLE `cost_codes`
  ADD CONSTRAINT `fk_cost_codes_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_cost_codes_parent` FOREIGN KEY (`parent_id`) REFERENCES `cost_codes` (`id`);

--
-- قيود الجداول `custodies`
--
ALTER TABLE `custodies`
  ADD CONSTRAINT `fk_custodies_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_custodies_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_custodies_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_custodies_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_custodies_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_custodies_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `fk_custodies_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `custody_settlements`
--
ALTER TABLE `custody_settlements`
  ADD CONSTRAINT `fk_custody_settlements_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_custody_settlements_custody` FOREIGN KEY (`custody_id`) REFERENCES `custodies` (`id`),
  ADD CONSTRAINT `fk_custody_settlements_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_custody_settlements_prepared_by` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `custody_settlement_lines`
--
ALTER TABLE `custody_settlement_lines`
  ADD CONSTRAINT `fk_custody_settlement_lines_account` FOREIGN KEY (`expense_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_custody_settlement_lines_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_custody_settlement_lines_partner` FOREIGN KEY (`business_partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_custody_settlement_lines_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_custody_settlement_lines_settlement` FOREIGN KEY (`custody_settlement_id`) REFERENCES `custody_settlements` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_custody_settlement_lines_tax` FOREIGN KEY (`tax_code_id`) REFERENCES `tax_codes` (`id`);

--
-- قيود الجداول `customers`
--
ALTER TABLE `customers`
  ADD CONSTRAINT `fk_customers_advance_account` FOREIGN KEY (`advance_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_customers_partner` FOREIGN KEY (`partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_customers_receivable_account` FOREIGN KEY (`receivable_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_customers_retention_account` FOREIGN KEY (`retention_account_id`) REFERENCES `accounts` (`id`);

--
-- قيود الجداول `customer_certificates`
--
ALTER TABLE `customer_certificates`
  ADD CONSTRAINT `fk_customer_certificates_contract` FOREIGN KEY (`contract_id`) REFERENCES `project_contracts` (`id`),
  ADD CONSTRAINT `fk_customer_certificates_prepared_by` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_customer_certificates_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_customer_certificates_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `customer_certificate_adjustments`
--
ALTER TABLE `customer_certificate_adjustments`
  ADD CONSTRAINT `fk_customer_certificate_adjustments_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_customer_certificate_adjustments_certificate` FOREIGN KEY (`customer_certificate_id`) REFERENCES `customer_certificates` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `customer_certificate_items`
--
ALTER TABLE `customer_certificate_items`
  ADD CONSTRAINT `fk_customer_certificate_items_boq` FOREIGN KEY (`contract_boq_item_id`) REFERENCES `contract_boq_items` (`id`),
  ADD CONSTRAINT `fk_customer_certificate_items_certificate` FOREIGN KEY (`customer_certificate_id`) REFERENCES `customer_certificates` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `departments`
--
ALTER TABLE `departments`
  ADD CONSTRAINT `fk_departments_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_departments_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_departments_manager` FOREIGN KEY (`manager_user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_departments_parent` FOREIGN KEY (`parent_id`) REFERENCES `departments` (`id`);

--
-- قيود الجداول `depreciation_runs`
--
ALTER TABLE `depreciation_runs`
  ADD CONSTRAINT `fk_depreciation_runs_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_depreciation_runs_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_depreciation_runs_period` FOREIGN KEY (`fiscal_period_id`) REFERENCES `fiscal_periods` (`id`),
  ADD CONSTRAINT `fk_depreciation_runs_prepared_by` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_depreciation_runs_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `depreciation_run_lines`
--
ALTER TABLE `depreciation_run_lines`
  ADD CONSTRAINT `fk_depreciation_run_lines_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`),
  ADD CONSTRAINT `fk_depreciation_run_lines_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_depreciation_run_lines_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_depreciation_run_lines_run` FOREIGN KEY (`depreciation_run_id`) REFERENCES `depreciation_runs` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `document_sequences`
--
ALTER TABLE `document_sequences`
  ADD CONSTRAINT `fk_document_sequences_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_document_sequences_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_document_sequences_year` FOREIGN KEY (`fiscal_year_id`) REFERENCES `fiscal_years` (`id`);

--
-- قيود الجداول `electronic_invoices`
--
ALTER TABLE `electronic_invoices`
  ADD CONSTRAINT `fk_electronic_invoices_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_electronic_invoices_sales_invoice` FOREIGN KEY (`sales_invoice_id`) REFERENCES `sales_invoices` (`id`);

--
-- قيود الجداول `electronic_invoice_submissions`
--
ALTER TABLE `electronic_invoice_submissions`
  ADD CONSTRAINT `fk_electronic_invoice_submissions_invoice` FOREIGN KEY (`electronic_invoice_id`) REFERENCES `electronic_invoices` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `employees`
--
ALTER TABLE `employees`
  ADD CONSTRAINT `fk_employees_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_employees_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_employees_default_project` FOREIGN KEY (`default_project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_employees_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`),
  ADD CONSTRAINT `fk_employees_partner` FOREIGN KEY (`partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_employees_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- قيود الجداول `equipment_fuel_logs`
--
ALTER TABLE `equipment_fuel_logs`
  ADD CONSTRAINT `fk_equipment_fuel_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`),
  ADD CONSTRAINT `fk_equipment_fuel_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_equipment_fuel_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_equipment_fuel_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`);

--
-- قيود الجداول `equipment_maintenance`
--
ALTER TABLE `equipment_maintenance`
  ADD CONSTRAINT `fk_equipment_maintenance_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`),
  ADD CONSTRAINT `fk_equipment_maintenance_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_equipment_maintenance_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_equipment_maintenance_vendor` FOREIGN KEY (`vendor_id`) REFERENCES `suppliers` (`id`);

--
-- قيود الجداول `equipment_usage_logs`
--
ALTER TABLE `equipment_usage_logs`
  ADD CONSTRAINT `fk_equipment_usage_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_equipment_usage_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`),
  ADD CONSTRAINT `fk_equipment_usage_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_equipment_usage_operator` FOREIGN KEY (`operator_employee_id`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `fk_equipment_usage_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `exchange_rates`
--
ALTER TABLE `exchange_rates`
  ADD CONSTRAINT `fk_exchange_rates_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_exchange_rates_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`);

--
-- قيود الجداول `fiscal_periods`
--
ALTER TABLE `fiscal_periods`
  ADD CONSTRAINT `fk_fiscal_periods_closed_by` FOREIGN KEY (`closed_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_fiscal_periods_year` FOREIGN KEY (`fiscal_year_id`) REFERENCES `fiscal_years` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `fiscal_years`
--
ALTER TABLE `fiscal_years`
  ADD CONSTRAINT `fk_fiscal_years_closed_by` FOREIGN KEY (`closed_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_fiscal_years_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`);

--
-- قيود الجداول `goods_receipts`
--
ALTER TABLE `goods_receipts`
  ADD CONSTRAINT `fk_goods_receipts_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_goods_receipts_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_goods_receipts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_goods_receipts_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_goods_receipts_po` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`),
  ADD CONSTRAINT `fk_goods_receipts_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_goods_receipts_received_by` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_goods_receipts_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  ADD CONSTRAINT `fk_goods_receipts_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`);

--
-- قيود الجداول `goods_receipt_items`
--
ALTER TABLE `goods_receipt_items`
  ADD CONSTRAINT `fk_goods_receipt_items_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_goods_receipt_items_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `fk_goods_receipt_items_po_item` FOREIGN KEY (`purchase_order_item_id`) REFERENCES `purchase_order_items` (`id`),
  ADD CONSTRAINT `fk_goods_receipt_items_receipt` FOREIGN KEY (`goods_receipt_id`) REFERENCES `goods_receipts` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `guarantees`
--
ALTER TABLE `guarantees`
  ADD CONSTRAINT `fk_guarantees_bank_partner` FOREIGN KEY (`bank_partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_guarantees_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_guarantees_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_guarantees_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_guarantees_partner` FOREIGN KEY (`partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_guarantees_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_guarantees_tender` FOREIGN KEY (`tender_id`) REFERENCES `tenders` (`id`);

--
-- قيود الجداول `insurance_policies`
--
ALTER TABLE `insurance_policies`
  ADD CONSTRAINT `fk_insurance_policies_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_insurance_policies_insurer` FOREIGN KEY (`insurer_partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_insurance_policies_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `integration_logs`
--
ALTER TABLE `integration_logs`
  ADD CONSTRAINT `fk_integration_logs_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`);

--
-- قيود الجداول `items`
--
ALTER TABLE `items`
  ADD CONSTRAINT `fk_items_category` FOREIGN KEY (`category_id`) REFERENCES `item_categories` (`id`),
  ADD CONSTRAINT `fk_items_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_items_cost_code` FOREIGN KEY (`default_cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_items_tax_code` FOREIGN KEY (`default_tax_code_id`) REFERENCES `tax_codes` (`id`);

--
-- قيود الجداول `item_categories`
--
ALTER TABLE `item_categories`
  ADD CONSTRAINT `fk_item_categories_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_item_categories_consumption_account` FOREIGN KEY (`consumption_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_item_categories_inventory_account` FOREIGN KEY (`inventory_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_item_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `item_categories` (`id`),
  ADD CONSTRAINT `fk_item_categories_variance_account` FOREIGN KEY (`variance_account_id`) REFERENCES `accounts` (`id`);

--
-- قيود الجداول `journal_books`
--
ALTER TABLE `journal_books`
  ADD CONSTRAINT `fk_journal_books_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_journal_books_sequence` FOREIGN KEY (`sequence_id`) REFERENCES `document_sequences` (`id`);

--
-- قيود الجداول `journal_entries`
--
ALTER TABLE `journal_entries`
  ADD CONSTRAINT `fk_journal_entries_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_journal_entries_book` FOREIGN KEY (`journal_book_id`) REFERENCES `journal_books` (`id`),
  ADD CONSTRAINT `fk_journal_entries_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_journal_entries_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_journal_entries_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_journal_entries_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_journal_entries_period` FOREIGN KEY (`fiscal_period_id`) REFERENCES `fiscal_periods` (`id`),
  ADD CONSTRAINT `fk_journal_entries_posted_by` FOREIGN KEY (`posted_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_journal_entries_reversing` FOREIGN KEY (`reversing_entry_id`) REFERENCES `journal_entries` (`id`);

--
-- قيود الجداول `journal_lines`
--
ALTER TABLE `journal_lines`
  ADD CONSTRAINT `fk_journal_lines_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_journal_lines_cost_center` FOREIGN KEY (`cost_center_id`) REFERENCES `cost_centers` (`id`),
  ADD CONSTRAINT `fk_journal_lines_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_journal_lines_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`),
  ADD CONSTRAINT `fk_journal_lines_entry` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_journal_lines_partner` FOREIGN KEY (`business_partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_journal_lines_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `labor_allocations`
--
ALTER TABLE `labor_allocations`
  ADD CONSTRAINT `fk_labor_allocations_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_labor_allocations_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`),
  ADD CONSTRAINT `fk_labor_allocations_payroll_item` FOREIGN KEY (`payroll_item_id`) REFERENCES `payroll_items` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_labor_allocations_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `fk_notifications_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- قيود الجداول `partner_bank_accounts`
--
ALTER TABLE `partner_bank_accounts`
  ADD CONSTRAINT `fk_partner_bank_accounts_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_partner_bank_accounts_partner` FOREIGN KEY (`partner_id`) REFERENCES `business_partners` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_partner_bank_accounts_verified_by` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `partner_contacts`
--
ALTER TABLE `partner_contacts`
  ADD CONSTRAINT `fk_partner_contacts_partner` FOREIGN KEY (`partner_id`) REFERENCES `business_partners` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `payment_allocations`
--
ALTER TABLE `payment_allocations`
  ADD CONSTRAINT `fk_payment_allocations_subcontract_certificate` FOREIGN KEY (`subcontract_certificate_id`) REFERENCES `subcontract_certificates` (`id`),
  ADD CONSTRAINT `fk_payment_allocations_supplier_invoice` FOREIGN KEY (`supplier_invoice_id`) REFERENCES `supplier_invoices` (`id`),
  ADD CONSTRAINT `fk_payment_allocations_voucher` FOREIGN KEY (`payment_voucher_id`) REFERENCES `payment_vouchers` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `payment_vouchers`
--
ALTER TABLE `payment_vouchers`
  ADD CONSTRAINT `fk_payment_vouchers_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_payment_vouchers_bank` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`),
  ADD CONSTRAINT `fk_payment_vouchers_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_payment_vouchers_cashbox` FOREIGN KEY (`cashbox_id`) REFERENCES `cashboxes` (`id`),
  ADD CONSTRAINT `fk_payment_vouchers_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_payment_vouchers_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_payment_vouchers_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_payment_vouchers_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_payment_vouchers_partner` FOREIGN KEY (`partner_id`) REFERENCES `business_partners` (`id`);

--
-- قيود الجداول `payroll_items`
--
ALTER TABLE `payroll_items`
  ADD CONSTRAINT `fk_payroll_items_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `fk_payroll_items_run` FOREIGN KEY (`payroll_run_id`) REFERENCES `payroll_runs` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `payroll_runs`
--
ALTER TABLE `payroll_runs`
  ADD CONSTRAINT `fk_payroll_runs_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_payroll_runs_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_payroll_runs_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_payroll_runs_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_payroll_runs_period` FOREIGN KEY (`fiscal_period_id`) REFERENCES `fiscal_periods` (`id`),
  ADD CONSTRAINT `fk_payroll_runs_prepared_by` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `projects`
--
ALTER TABLE `projects`
  ADD CONSTRAINT `fk_projects_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_projects_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_projects_consultant` FOREIGN KEY (`consultant_partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_projects_cost_center` FOREIGN KEY (`cost_center_id`) REFERENCES `cost_centers` (`id`),
  ADD CONSTRAINT `fk_projects_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_projects_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  ADD CONSTRAINT `fk_projects_manager` FOREIGN KEY (`project_manager_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_projects_tender` FOREIGN KEY (`tender_id`) REFERENCES `tenders` (`id`);

--
-- قيود الجداول `project_budget_lines`
--
ALTER TABLE `project_budget_lines`
  ADD CONSTRAINT `fk_project_budget_lines_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_project_budget_lines_boq` FOREIGN KEY (`contract_boq_item_id`) REFERENCES `contract_boq_items` (`id`),
  ADD CONSTRAINT `fk_project_budget_lines_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_project_budget_lines_version` FOREIGN KEY (`budget_version_id`) REFERENCES `project_budget_versions` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `project_budget_versions`
--
ALTER TABLE `project_budget_versions`
  ADD CONSTRAINT `fk_project_budget_versions_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_project_budget_versions_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_project_budget_versions_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_project_budget_versions_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `project_claims`
--
ALTER TABLE `project_claims`
  ADD CONSTRAINT `fk_project_claims_contract` FOREIGN KEY (`contract_id`) REFERENCES `project_contracts` (`id`),
  ADD CONSTRAINT `fk_project_claims_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_project_claims_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `project_contracts`
--
ALTER TABLE `project_contracts`
  ADD CONSTRAINT `fk_project_contracts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_project_contracts_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_project_contracts_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `project_forecasts`
--
ALTER TABLE `project_forecasts`
  ADD CONSTRAINT `fk_project_forecasts_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_project_forecasts_prepared_by` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_project_forecasts_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `project_forecast_lines`
--
ALTER TABLE `project_forecast_lines`
  ADD CONSTRAINT `fk_project_forecast_lines_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_project_forecast_lines_forecast` FOREIGN KEY (`project_forecast_id`) REFERENCES `project_forecasts` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `project_members`
--
ALTER TABLE `project_members`
  ADD CONSTRAINT `fk_project_members_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_project_members_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- قيود الجداول `purchase_orders`
--
ALTER TABLE `purchase_orders`
  ADD CONSTRAINT `fk_purchase_orders_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_purchase_orders_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_purchase_orders_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_purchase_orders_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_purchase_orders_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_purchase_orders_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_purchase_orders_quotation` FOREIGN KEY (`supplier_quotation_id`) REFERENCES `supplier_quotations` (`id`),
  ADD CONSTRAINT `fk_purchase_orders_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  ADD CONSTRAINT `fk_purchase_orders_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`);

--
-- قيود الجداول `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  ADD CONSTRAINT `fk_purchase_order_items_boq` FOREIGN KEY (`contract_boq_item_id`) REFERENCES `contract_boq_items` (`id`),
  ADD CONSTRAINT `fk_purchase_order_items_budget` FOREIGN KEY (`budget_line_id`) REFERENCES `project_budget_lines` (`id`),
  ADD CONSTRAINT `fk_purchase_order_items_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_purchase_order_items_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `fk_purchase_order_items_order` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_purchase_order_items_request_item` FOREIGN KEY (`purchase_request_item_id`) REFERENCES `purchase_request_items` (`id`),
  ADD CONSTRAINT `fk_purchase_order_items_tax` FOREIGN KEY (`tax_code_id`) REFERENCES `tax_codes` (`id`);

--
-- قيود الجداول `purchase_requests`
--
ALTER TABLE `purchase_requests`
  ADD CONSTRAINT `fk_purchase_requests_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_purchase_requests_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_purchase_requests_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_purchase_requests_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_purchase_requests_requested_by` FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_purchase_requests_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`);

--
-- قيود الجداول `purchase_request_items`
--
ALTER TABLE `purchase_request_items`
  ADD CONSTRAINT `fk_purchase_request_items_boq` FOREIGN KEY (`contract_boq_item_id`) REFERENCES `contract_boq_items` (`id`),
  ADD CONSTRAINT `fk_purchase_request_items_budget` FOREIGN KEY (`budget_line_id`) REFERENCES `project_budget_lines` (`id`),
  ADD CONSTRAINT `fk_purchase_request_items_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_purchase_request_items_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `fk_purchase_request_items_request` FOREIGN KEY (`purchase_request_id`) REFERENCES `purchase_requests` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `receipt_allocations`
--
ALTER TABLE `receipt_allocations`
  ADD CONSTRAINT `fk_receipt_allocations_certificate` FOREIGN KEY (`customer_certificate_id`) REFERENCES `customer_certificates` (`id`),
  ADD CONSTRAINT `fk_receipt_allocations_invoice` FOREIGN KEY (`sales_invoice_id`) REFERENCES `sales_invoices` (`id`),
  ADD CONSTRAINT `fk_receipt_allocations_voucher` FOREIGN KEY (`receipt_voucher_id`) REFERENCES `receipt_vouchers` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `receipt_vouchers`
--
ALTER TABLE `receipt_vouchers`
  ADD CONSTRAINT `fk_receipt_vouchers_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_receipt_vouchers_bank` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`),
  ADD CONSTRAINT `fk_receipt_vouchers_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_receipt_vouchers_cashbox` FOREIGN KEY (`cashbox_id`) REFERENCES `cashboxes` (`id`),
  ADD CONSTRAINT `fk_receipt_vouchers_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_receipt_vouchers_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_receipt_vouchers_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_receipt_vouchers_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  ADD CONSTRAINT `fk_receipt_vouchers_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_receipt_vouchers_partner` FOREIGN KEY (`partner_id`) REFERENCES `business_partners` (`id`);

--
-- قيود الجداول `recurring_journals`
--
ALTER TABLE `recurring_journals`
  ADD CONSTRAINT `fk_recurring_journals_book` FOREIGN KEY (`journal_book_id`) REFERENCES `journal_books` (`id`),
  ADD CONSTRAINT `fk_recurring_journals_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_recurring_journals_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `requests_for_quotation`
--
ALTER TABLE `requests_for_quotation`
  ADD CONSTRAINT `fk_rfq_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_rfq_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_rfq_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_rfq_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `revenue_recognition_lines`
--
ALTER TABLE `revenue_recognition_lines`
  ADD CONSTRAINT `fk_revenue_recognition_lines_contract` FOREIGN KEY (`contract_id`) REFERENCES `project_contracts` (`id`),
  ADD CONSTRAINT `fk_revenue_recognition_lines_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_revenue_recognition_lines_run` FOREIGN KEY (`revenue_recognition_run_id`) REFERENCES `revenue_recognition_runs` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `revenue_recognition_runs`
--
ALTER TABLE `revenue_recognition_runs`
  ADD CONSTRAINT `fk_revenue_recognition_runs_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_revenue_recognition_runs_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_revenue_recognition_runs_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_revenue_recognition_runs_period` FOREIGN KEY (`fiscal_period_id`) REFERENCES `fiscal_periods` (`id`),
  ADD CONSTRAINT `fk_revenue_recognition_runs_prepared_by` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `rfq_items`
--
ALTER TABLE `rfq_items`
  ADD CONSTRAINT `fk_rfq_items_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `fk_rfq_items_request_item` FOREIGN KEY (`purchase_request_item_id`) REFERENCES `purchase_request_items` (`id`),
  ADD CONSTRAINT `fk_rfq_items_rfq` FOREIGN KEY (`rfq_id`) REFERENCES `requests_for_quotation` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `rfq_suppliers`
--
ALTER TABLE `rfq_suppliers`
  ADD CONSTRAINT `fk_rfq_suppliers_rfq` FOREIGN KEY (`rfq_id`) REFERENCES `requests_for_quotation` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_rfq_suppliers_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`);

--
-- قيود الجداول `roles`
--
ALTER TABLE `roles`
  ADD CONSTRAINT `fk_roles_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`);

--
-- قيود الجداول `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;

--
-- قيود الجداول `sales_invoices`
--
ALTER TABLE `sales_invoices`
  ADD CONSTRAINT `fk_sales_invoices_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_sales_invoices_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_sales_invoices_certificate` FOREIGN KEY (`customer_certificate_id`) REFERENCES `customer_certificates` (`id`),
  ADD CONSTRAINT `fk_sales_invoices_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_sales_invoices_contract` FOREIGN KEY (`contract_id`) REFERENCES `project_contracts` (`id`),
  ADD CONSTRAINT `fk_sales_invoices_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_sales_invoices_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_sales_invoices_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  ADD CONSTRAINT `fk_sales_invoices_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_sales_invoices_original` FOREIGN KEY (`original_invoice_id`) REFERENCES `sales_invoices` (`id`),
  ADD CONSTRAINT `fk_sales_invoices_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `sales_invoice_items`
--
ALTER TABLE `sales_invoice_items`
  ADD CONSTRAINT `fk_sales_invoice_items_boq` FOREIGN KEY (`contract_boq_item_id`) REFERENCES `contract_boq_items` (`id`),
  ADD CONSTRAINT `fk_sales_invoice_items_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_sales_invoice_items_invoice` FOREIGN KEY (`sales_invoice_id`) REFERENCES `sales_invoices` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sales_invoice_items_revenue_account` FOREIGN KEY (`revenue_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_sales_invoice_items_tax` FOREIGN KEY (`tax_code_id`) REFERENCES `tax_codes` (`id`);

--
-- قيود الجداول `stock_balances`
--
ALTER TABLE `stock_balances`
  ADD CONSTRAINT `fk_stock_balances_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `fk_stock_balances_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`);

--
-- قيود الجداول `stock_counts`
--
ALTER TABLE `stock_counts`
  ADD CONSTRAINT `fk_stock_counts_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_stock_counts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_stock_counts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_stock_counts_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_stock_counts_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`);

--
-- قيود الجداول `stock_count_lines`
--
ALTER TABLE `stock_count_lines`
  ADD CONSTRAINT `fk_stock_count_lines_count` FOREIGN KEY (`stock_count_id`) REFERENCES `stock_counts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_stock_count_lines_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`);

--
-- قيود الجداول `stock_transactions`
--
ALTER TABLE `stock_transactions`
  ADD CONSTRAINT `fk_stock_transactions_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_stock_transactions_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_stock_transactions_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_stock_transactions_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_stock_transactions_from_warehouse` FOREIGN KEY (`from_warehouse_id`) REFERENCES `warehouses` (`id`),
  ADD CONSTRAINT `fk_stock_transactions_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_stock_transactions_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_stock_transactions_to_warehouse` FOREIGN KEY (`to_warehouse_id`) REFERENCES `warehouses` (`id`);

--
-- قيود الجداول `stock_transaction_lines`
--
ALTER TABLE `stock_transaction_lines`
  ADD CONSTRAINT `fk_stock_transaction_lines_boq` FOREIGN KEY (`contract_boq_item_id`) REFERENCES `contract_boq_items` (`id`),
  ADD CONSTRAINT `fk_stock_transaction_lines_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_stock_transaction_lines_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `fk_stock_transaction_lines_transaction` FOREIGN KEY (`stock_transaction_id`) REFERENCES `stock_transactions` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `subcontractors`
--
ALTER TABLE `subcontractors`
  ADD CONSTRAINT `fk_subcontractors_advance_account` FOREIGN KEY (`advance_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_subcontractors_partner` FOREIGN KEY (`partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_subcontractors_payable_account` FOREIGN KEY (`payable_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_subcontractors_retention_account` FOREIGN KEY (`retention_account_id`) REFERENCES `accounts` (`id`);

--
-- قيود الجداول `subcontracts`
--
ALTER TABLE `subcontracts`
  ADD CONSTRAINT `fk_subcontracts_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_subcontracts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_subcontracts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_subcontracts_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_subcontracts_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_subcontracts_subcontractor` FOREIGN KEY (`subcontractor_id`) REFERENCES `subcontractors` (`id`);

--
-- قيود الجداول `subcontract_certificates`
--
ALTER TABLE `subcontract_certificates`
  ADD CONSTRAINT `fk_subcontract_certificates_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_subcontract_certificates_finance_approved_by` FOREIGN KEY (`finance_approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_subcontract_certificates_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_subcontract_certificates_project_approved_by` FOREIGN KEY (`project_approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_subcontract_certificates_subcontract` FOREIGN KEY (`subcontract_id`) REFERENCES `subcontracts` (`id`),
  ADD CONSTRAINT `fk_subcontract_certificates_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `subcontract_certificate_adjustments`
--
ALTER TABLE `subcontract_certificate_adjustments`
  ADD CONSTRAINT `fk_subcontract_certificate_adjustments_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_subcontract_certificate_adjustments_certificate` FOREIGN KEY (`subcontract_certificate_id`) REFERENCES `subcontract_certificates` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `subcontract_certificate_items`
--
ALTER TABLE `subcontract_certificate_items`
  ADD CONSTRAINT `fk_subcontract_certificate_items_certificate` FOREIGN KEY (`subcontract_certificate_id`) REFERENCES `subcontract_certificates` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_subcontract_certificate_items_subcontract_item` FOREIGN KEY (`subcontract_item_id`) REFERENCES `subcontract_items` (`id`);

--
-- قيود الجداول `subcontract_items`
--
ALTER TABLE `subcontract_items`
  ADD CONSTRAINT `fk_subcontract_items_boq` FOREIGN KEY (`contract_boq_item_id`) REFERENCES `contract_boq_items` (`id`),
  ADD CONSTRAINT `fk_subcontract_items_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_subcontract_items_subcontract` FOREIGN KEY (`subcontract_id`) REFERENCES `subcontracts` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `suppliers`
--
ALTER TABLE `suppliers`
  ADD CONSTRAINT `fk_suppliers_advance_account` FOREIGN KEY (`advance_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_suppliers_partner` FOREIGN KEY (`partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_suppliers_payable_account` FOREIGN KEY (`payable_account_id`) REFERENCES `accounts` (`id`);

--
-- قيود الجداول `supplier_invoices`
--
ALTER TABLE `supplier_invoices`
  ADD CONSTRAINT `fk_supplier_invoices_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_supplier_invoices_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_supplier_invoices_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_supplier_invoices_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_supplier_invoices_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_supplier_invoices_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_supplier_invoices_po` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`),
  ADD CONSTRAINT `fk_supplier_invoices_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_supplier_invoices_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`);

--
-- قيود الجداول `supplier_invoice_items`
--
ALTER TABLE `supplier_invoice_items`
  ADD CONSTRAINT `fk_supplier_invoice_items_account` FOREIGN KEY (`expense_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_supplier_invoice_items_boq` FOREIGN KEY (`contract_boq_item_id`) REFERENCES `contract_boq_items` (`id`),
  ADD CONSTRAINT `fk_supplier_invoice_items_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_supplier_invoice_items_invoice` FOREIGN KEY (`supplier_invoice_id`) REFERENCES `supplier_invoices` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_supplier_invoice_items_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `fk_supplier_invoice_items_po_item` FOREIGN KEY (`purchase_order_item_id`) REFERENCES `purchase_order_items` (`id`),
  ADD CONSTRAINT `fk_supplier_invoice_items_receipt_item` FOREIGN KEY (`goods_receipt_item_id`) REFERENCES `goods_receipt_items` (`id`),
  ADD CONSTRAINT `fk_supplier_invoice_items_tax` FOREIGN KEY (`tax_code_id`) REFERENCES `tax_codes` (`id`);

--
-- قيود الجداول `supplier_quotations`
--
ALTER TABLE `supplier_quotations`
  ADD CONSTRAINT `fk_supplier_quotations_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_supplier_quotations_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_supplier_quotations_rfq` FOREIGN KEY (`rfq_id`) REFERENCES `requests_for_quotation` (`id`),
  ADD CONSTRAINT `fk_supplier_quotations_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`);

--
-- قيود الجداول `supplier_quotation_items`
--
ALTER TABLE `supplier_quotation_items`
  ADD CONSTRAINT `fk_supplier_quotation_items_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  ADD CONSTRAINT `fk_supplier_quotation_items_quotation` FOREIGN KEY (`supplier_quotation_id`) REFERENCES `supplier_quotations` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_supplier_quotation_items_rfq_item` FOREIGN KEY (`rfq_item_id`) REFERENCES `rfq_items` (`id`),
  ADD CONSTRAINT `fk_supplier_quotation_items_tax` FOREIGN KEY (`tax_code_id`) REFERENCES `tax_codes` (`id`);

--
-- قيود الجداول `system_settings`
--
ALTER TABLE `system_settings`
  ADD CONSTRAINT `fk_system_settings_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_system_settings_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `tax_codes`
--
ALTER TABLE `tax_codes`
  ADD CONSTRAINT `fk_tax_codes_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_tax_codes_input_account` FOREIGN KEY (`input_account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `fk_tax_codes_output_account` FOREIGN KEY (`output_account_id`) REFERENCES `accounts` (`id`);

--
-- قيود الجداول `tax_periods`
--
ALTER TABLE `tax_periods`
  ADD CONSTRAINT `fk_tax_periods_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`);

--
-- قيود الجداول `tax_returns`
--
ALTER TABLE `tax_returns`
  ADD CONSTRAINT `fk_tax_returns_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_tax_returns_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  ADD CONSTRAINT `fk_tax_returns_period` FOREIGN KEY (`tax_period_id`) REFERENCES `tax_periods` (`id`),
  ADD CONSTRAINT `fk_tax_returns_prepared_by` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_tax_returns_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `tax_return_lines`
--
ALTER TABLE `tax_return_lines`
  ADD CONSTRAINT `fk_tax_return_lines_return` FOREIGN KEY (`tax_return_id`) REFERENCES `tax_returns` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `tenders`
--
ALTER TABLE `tenders`
  ADD CONSTRAINT `fk_tenders_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_tenders_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_tenders_consultant` FOREIGN KEY (`consultant_partner_id`) REFERENCES `business_partners` (`id`),
  ADD CONSTRAINT `fk_tenders_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_tenders_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  ADD CONSTRAINT `fk_tenders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  ADD CONSTRAINT `fk_tenders_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`id`);

--
-- قيود الجداول `tender_boq_items`
--
ALTER TABLE `tender_boq_items`
  ADD CONSTRAINT `fk_tender_boq_items_parent` FOREIGN KEY (`parent_id`) REFERENCES `tender_boq_items` (`id`),
  ADD CONSTRAINT `fk_tender_boq_items_tender` FOREIGN KEY (`tender_id`) REFERENCES `tenders` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `tender_estimate_components`
--
ALTER TABLE `tender_estimate_components`
  ADD CONSTRAINT `fk_tender_estimate_components_boq` FOREIGN KEY (`tender_boq_item_id`) REFERENCES `tender_boq_items` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_tender_estimate_components_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`);

--
-- قيود الجداول `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_users_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_users_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`);

--
-- قيود الجداول `user_roles`
--
ALTER TABLE `user_roles`
  ADD CONSTRAINT `fk_user_roles_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  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;

--
-- قيود الجداول `variation_orders`
--
ALTER TABLE `variation_orders`
  ADD CONSTRAINT `fk_variation_orders_contract` FOREIGN KEY (`contract_id`) REFERENCES `project_contracts` (`id`),
  ADD CONSTRAINT `fk_variation_orders_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_variation_orders_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `variation_order_items`
--
ALTER TABLE `variation_order_items`
  ADD CONSTRAINT `fk_variation_order_items_boq` FOREIGN KEY (`contract_boq_item_id`) REFERENCES `contract_boq_items` (`id`),
  ADD CONSTRAINT `fk_variation_order_items_cost_code` FOREIGN KEY (`cost_code_id`) REFERENCES `cost_codes` (`id`),
  ADD CONSTRAINT `fk_variation_order_items_order` FOREIGN KEY (`variation_order_id`) REFERENCES `variation_orders` (`id`) ON DELETE CASCADE;

--
-- قيود الجداول `warehouses`
--
ALTER TABLE `warehouses`
  ADD CONSTRAINT `fk_warehouses_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  ADD CONSTRAINT `fk_warehouses_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_warehouses_custodian` FOREIGN KEY (`custodian_user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_warehouses_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- قيود الجداول `workflow_definitions`
--
ALTER TABLE `workflow_definitions`
  ADD CONSTRAINT `fk_workflow_definitions_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_workflow_definitions_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- قيود الجداول `workflow_steps`
--
ALTER TABLE `workflow_steps`
  ADD CONSTRAINT `fk_workflow_steps_definition` FOREIGN KEY (`workflow_definition_id`) REFERENCES `workflow_definitions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_workflow_steps_role` FOREIGN KEY (`approver_role_id`) REFERENCES `roles` (`id`),
  ADD CONSTRAINT `fk_workflow_steps_user` FOREIGN KEY (`approver_user_id`) REFERENCES `users` (`id`);
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 */;
