-- System Database Schema Dump
-- Generated: 2026-03-23 21:38:40
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+03:00";

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

DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `event_type` enum('create','update','soft_delete','force_delete','restore','login') NOT NULL,
  `affected_table` varchar(50) NOT NULL,
  `affected_id` bigint(20) NOT NULL,
  `old_values` longtext DEFAULT NULL,
  `new_values` longtext DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `bank_account_group_members`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `bank_account_group_members`;
CREATE TABLE `bank_account_group_members` (
  `group_id` bigint(20) NOT NULL,
  `member_id` bigint(20) NOT NULL,
  `member_type` varchar(20) DEFAULT 'account',
  PRIMARY KEY (`group_id`,`member_id`),
  KEY `group_id` (`group_id`),
  KEY `bank_account_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `bank_account_groups`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `bank_account_groups`;
CREATE TABLE `bank_account_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `status` varchar(20) DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `held_amount` decimal(15,2) DEFAULT 0.00,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `bank_accounts`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `bank_accounts`;
CREATE TABLE `bank_accounts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bank_id` bigint(20) NOT NULL,
  `party_id` bigint(20) DEFAULT NULL COMMENT 'اسم المستفيد من جدول الأطراف',
  `customer_number` varchar(100) DEFAULT NULL,
  `account_number` varchar(100) NOT NULL,
  `iban` varchar(100) DEFAULT NULL,
  `swift_code` varchar(50) DEFAULT NULL,
  `current_balance` decimal(15,2) DEFAULT 0.00,
  `held_amount` decimal(15,2) DEFAULT 0.00,
  `manual_balance` decimal(15,2) DEFAULT 0.00,
  `balance_updated_at` datetime DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `short_name` varchar(100) DEFAULT NULL,
  `account_type` enum('current','financing','stocks','other') NOT NULL DEFAULT 'current',
  `notes` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `classification` enum('private','public') NOT NULL DEFAULT 'private',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `bank_id` (`bank_id`),
  KEY `party_id` (`party_id`),
  CONSTRAINT `bank_accounts_ibfk_1` FOREIGN KEY (`bank_id`) REFERENCES `banks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `bank_accounts_ibfk_2` FOREIGN KEY (`party_id`) REFERENCES `parties` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `banks`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `banks`;
CREATE TABLE `banks` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `website_url` varchar(255) DEFAULT NULL,
  `category` enum('local','international') NOT NULL DEFAULT 'local',
  `country` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table `banks`
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('1', 'البنك الاهلي السعودي', 'SNB', 'https://www.alahli.com/ar/pages/personal-banking', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-02-24 22:48:22', '2026-03-18 20:02:08');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('2', 'بنك البلاد', 'Albilad', 'https://www.bankalbilad.com.sa/', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-02-24 22:48:47', '2026-03-18 20:03:04');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('3', 'مصرف الانماء', 'Alinma', 'https://www.alinma.com/', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-02-24 22:49:01', '2026-03-18 20:07:53');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('4', 'البنك الاهلي المصري', 'nbe', 'https://www.nbe.com.eg/NBE/E/#/EN/Home', 'international', 'مصر', 'active', NULL, '2026-02-24 22:52:01', '2026-03-18 20:08:52');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('6', 'البنك السعودي الفرنسي', 'BSF', 'https://bsf.sa/', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-03-01 17:27:05', '2026-03-01 17:41:53');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('7', 'البنك السعودي للاستثمار', 'SAIB', 'https://www.saib.com.sa/ar', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-03-18 20:09:25', '2026-03-18 20:09:25');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('8', 'مصرف الراجحي', 'Rajhi', 'https://www.alrajhibank.com.sa/', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-03-18 20:10:01', '2026-03-18 20:10:01');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('9', 'بنك الرياض', 'Riyad', 'https://www.riyadbank.com/', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-03-18 20:10:37', '2026-03-18 20:10:37');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('10', 'البنك السعودي الأول', 'SAB', 'https://www.sab.com/en/personal/', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-03-18 20:11:40', '2026-03-18 20:11:40');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('11', 'البنك العربي الوطني', 'ANB', 'https://www.anb.com.sa/', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-03-18 20:12:02', '2026-03-18 20:12:02');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('12', 'بنك الجزيرة', 'aljazira', 'https://www.aljazirabank.com.sa/ar-sa/', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-03-18 20:12:58', '2026-03-18 20:12:58');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('13', 'بنك الخليج الدولي', 'GIB', 'https://www.gib.com/', 'local', 'المملكة العربية السعودية', 'active', NULL, '2026-03-18 20:13:34', '2026-03-18 20:13:34');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('14', 'بيت التمويل الكويتي', 'kfh', 'https://www.bh.kfh.com/personal/', 'international', 'مصر', 'active', NULL, '2026-03-19 04:51:31', '2026-03-19 04:51:31');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('15', 'بنك رأس الخيمة الوطني', 'RAK', 'https://www.rakbank.ae/ar', 'international', 'الإمارات العربية المتحدة', 'active', NULL, '2026-03-19 14:28:07', '2026-03-19 14:28:07');
INSERT INTO `banks` (`id`, `name`, `short_name`, `website_url`, `category`, `country`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('16', 'البنك التجاري الدولي', 'CBI', 'https://www.cbiuae.com/en/about-cbi/', 'international', 'الإمارات العربية المتحدة', 'active', NULL, '2026-03-19 14:30:33', '2026-03-19 14:30:33');

-- --------------------------------------------------------
-- Table structure for table `branches`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `branches`;
CREATE TABLE `branches` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `portfolio_id` bigint(20) NOT NULL,
  `name` varchar(191) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `is_main` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `branches_portfolio_id_foreign` (`portfolio_id`),
  CONSTRAINT `branches_portfolio_id_foreign` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `cost_centers`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `cost_centers`;
CREATE TABLE `cost_centers` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `branch_id` bigint(20) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cc_branch_id_fk` (`branch_id`),
  CONSTRAINT `cc_branch_id_fk` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `document_links`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `document_links`;
CREATE TABLE `document_links` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `document_id` bigint(20) NOT NULL,
  `linkable_type` varchar(50) NOT NULL,
  `linkable_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `document_id` (`document_id`),
  CONSTRAINT `document_links_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `document_types`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `document_types`;
CREATE TABLE `document_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `icon` varchar(50) DEFAULT 'ti ti-file',
  `color` varchar(20) DEFAULT 'blue',
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table `document_types`
INSERT INTO `document_types` (`id`, `name`, `icon`, `color`, `description`, `status`, `deleted_at`) VALUES ('1', 'هوية', 'ti ti-id-badge', 'blue', NULL, 'active', NULL);
INSERT INTO `document_types` (`id`, `name`, `icon`, `color`, `description`, `status`, `deleted_at`) VALUES ('2', 'جواز سفر', 'ti ti-passport', 'indigo', NULL, 'active', NULL);
INSERT INTO `document_types` (`id`, `name`, `icon`, `color`, `description`, `status`, `deleted_at`) VALUES ('3', 'سجل تجاري', 'ti ti-building', 'azure', NULL, 'active', NULL);
INSERT INTO `document_types` (`id`, `name`, `icon`, `color`, `description`, `status`, `deleted_at`) VALUES ('4', 'صك', 'ti ti-certificate', 'orange', NULL, 'active', NULL);
INSERT INTO `document_types` (`id`, `name`, `icon`, `color`, `description`, `status`, `deleted_at`) VALUES ('5', 'وكالة', 'ti ti-gavel', 'purple', NULL, 'active', NULL);
INSERT INTO `document_types` (`id`, `name`, `icon`, `color`, `description`, `status`, `deleted_at`) VALUES ('6', 'رخصة', 'ti ti-license', 'red', NULL, 'active', NULL);
INSERT INTO `document_types` (`id`, `name`, `icon`, `color`, `description`, `status`, `deleted_at`) VALUES ('7', 'شهادة', 'ti ti-school', 'teal', NULL, 'active', NULL);
INSERT INTO `document_types` (`id`, `name`, `icon`, `color`, `description`, `status`, `deleted_at`) VALUES ('8', 'تقرير', 'ti ti-report', 'green', NULL, 'active', NULL);
INSERT INTO `document_types` (`id`, `name`, `icon`, `color`, `description`, `status`, `deleted_at`) VALUES ('9', 'عقد', 'ti ti-file-description', 'cyan', NULL, 'active', NULL);
INSERT INTO `document_types` (`id`, `name`, `icon`, `color`, `description`, `status`, `deleted_at`) VALUES ('10', 'أخرى', 'ti ti-file', 'gray', NULL, 'active', NULL);

-- --------------------------------------------------------
-- Table structure for table `documents`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `documents`;
CREATE TABLE `documents` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL,
  `title` varchar(191) NOT NULL,
  `issuing_authority_id` bigint(20) unsigned DEFAULT NULL,
  `document_number` varchar(100) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `external_url` text DEFAULT NULL,
  `content_text` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('active','inactive','expired','archived') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type_id` (`type_id`),
  CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `document_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `financial_claim_bank_accounts`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financial_claim_bank_accounts`;
CREATE TABLE `financial_claim_bank_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `claim_id` int(11) NOT NULL,
  `bank_account_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_claim` (`claim_id`),
  CONSTRAINT `fk_claim` FOREIGN KEY (`claim_id`) REFERENCES `financial_claims` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `financial_claim_items`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financial_claim_items`;
CREATE TABLE `financial_claim_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `claim_id` int(11) NOT NULL,
  `description` text NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `claim_id` (`claim_id`),
  CONSTRAINT `financial_claim_items_ibfk_1` FOREIGN KEY (`claim_id`) REFERENCES `financial_claims` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `financial_claims`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financial_claims`;
CREATE TABLE `financial_claims` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `claim_number` varchar(50) NOT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `date` date NOT NULL,
  `party_first_id` int(11) NOT NULL,
  `party_second_id` int(11) NOT NULL,
  `portfolio_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `bank_account_id` int(11) DEFAULT NULL,
  `tax_mode` enum('none','exclusive','inclusive') DEFAULT 'none',
  `tax_rate` decimal(5,2) DEFAULT 0.00,
  `subtotal` decimal(15,2) DEFAULT 0.00,
  `tax_amount` decimal(15,2) DEFAULT 0.00,
  `total_amount` decimal(15,2) DEFAULT 0.00,
  `status` enum('draft','active','paid','cancelled') DEFAULT 'draft',
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `claim_number` (`claim_number`),
  KEY `party_first_id` (`party_first_id`),
  KEY `party_second_id` (`party_second_id`),
  KEY `bank_account_id` (`bank_account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `financial_obligations`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financial_obligations`;
CREATE TABLE `financial_obligations` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `reference_type` enum('lease','service','property','finance','financing','investment','sales','purchase') NOT NULL,
  `reference_id` bigint(20) NOT NULL,
  `sub_type` varchar(191) DEFAULT NULL,
  `portfolio_id` bigint(20) NOT NULL,
  `branch_id` bigint(20) NOT NULL,
  `center_id` bigint(20) NOT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `unit_id` bigint(20) DEFAULT NULL,
  `due_date` date NOT NULL,
  `type` enum('payable','receivable') NOT NULL,
  `category` varchar(191) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `status` enum('pending','paid','partial','overdue','cancelled') DEFAULT 'pending',
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `paid_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `obligation_type` varchar(50) NOT NULL DEFAULT 'scheduled',
  PRIMARY KEY (`id`),
  KEY `idx_reference` (`reference_type`,`reference_id`),
  KEY `idx_due_date` (`due_date`),
  KEY `idx_status` (`status`),
  KEY `idx_hierarchy` (`portfolio_id`,`branch_id`,`center_id`)
) ENGINE=InnoDB AUTO_INCREMENT=998 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `financing_contract_beneficiaries`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financing_contract_beneficiaries`;
CREATE TABLE `financing_contract_beneficiaries` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `contract_id` bigint(20) NOT NULL,
  `party_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `contract_id` (`contract_id`),
  KEY `party_id` (`party_id`),
  CONSTRAINT `fk_finc_beneficiary_contract` FOREIGN KEY (`contract_id`) REFERENCES `financing_contracts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_finc_beneficiary_party` FOREIGN KEY (`party_id`) REFERENCES `parties` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `financing_contract_branches`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financing_contract_branches`;
CREATE TABLE `financing_contract_branches` (
  `contract_id` bigint(20) NOT NULL,
  `branch_id` bigint(20) NOT NULL,
  PRIMARY KEY (`contract_id`,`branch_id`),
  KEY `branch_id` (`branch_id`),
  CONSTRAINT `financing_contract_branches_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `financing_contracts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `financing_contract_branches_ibfk_2` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `financing_contract_centers`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financing_contract_centers`;
CREATE TABLE `financing_contract_centers` (
  `contract_id` bigint(20) NOT NULL,
  `center_id` bigint(20) NOT NULL,
  PRIMARY KEY (`contract_id`,`center_id`),
  KEY `center_id` (`center_id`),
  CONSTRAINT `financing_contract_centers_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `financing_contracts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `financing_contract_centers_ibfk_2` FOREIGN KEY (`center_id`) REFERENCES `cost_centers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `financing_contract_portfolios`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financing_contract_portfolios`;
CREATE TABLE `financing_contract_portfolios` (
  `contract_id` bigint(20) NOT NULL,
  `portfolio_id` bigint(20) NOT NULL,
  PRIMARY KEY (`contract_id`,`portfolio_id`),
  KEY `portfolio_id` (`portfolio_id`),
  CONSTRAINT `financing_contract_portfolios_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `financing_contracts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `financing_contract_portfolios_ibfk_2` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `financing_contract_projects`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financing_contract_projects`;
CREATE TABLE `financing_contract_projects` (
  `contract_id` bigint(20) NOT NULL,
  `project_id` bigint(20) NOT NULL,
  PRIMARY KEY (`contract_id`,`project_id`),
  KEY `project_id` (`project_id`),
  CONSTRAINT `financing_contract_projects_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `financing_contracts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `financing_contract_projects_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `financing_contract_units`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financing_contract_units`;
CREATE TABLE `financing_contract_units` (
  `contract_id` bigint(20) NOT NULL,
  `unit_id` bigint(20) NOT NULL,
  PRIMARY KEY (`contract_id`,`unit_id`),
  KEY `unit_id` (`unit_id`),
  CONSTRAINT `financing_contract_units_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `financing_contracts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `financing_contract_units_ibfk_2` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `financing_contracts`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `financing_contracts`;
CREATE TABLE `financing_contracts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `contract_number` varchar(100) NOT NULL,
  `bank_id` bigint(20) NOT NULL,
  `bank_account_id` bigint(20) DEFAULT NULL,
  `portfolio_id` bigint(20) DEFAULT NULL,
  `branch_id` bigint(20) DEFAULT NULL,
  `center_id` bigint(20) DEFAULT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `unit_id` bigint(20) DEFAULT NULL,
  `status` enum('draft','active','expired','archived','renewed','cancelled') DEFAULT 'draft',
  `category` varchar(50) DEFAULT 'term_loan',
  `contract_date` date NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `duration_months` int(11) DEFAULT NULL,
  `duration_days` int(11) DEFAULT NULL,
  `loan_amount` decimal(15,2) NOT NULL,
  `payment_cycle` enum('monthly','quarterly','semi_annual','annual','flexible','one_time') DEFAULT 'monthly',
  `details` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bank_id` (`bank_id`),
  KEY `bank_account_id` (`bank_account_id`),
  CONSTRAINT `financing_contracts_ibfk_1` FOREIGN KEY (`bank_id`) REFERENCES `banks` (`id`),
  CONSTRAINT `financing_contracts_ibfk_2` FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `funds`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `funds`;
CREATE TABLE `funds` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `short_name` varchar(100) DEFAULT NULL,
  `branch_id` bigint(20) DEFAULT NULL,
  `project_id` bigint(20) DEFAULT NULL,
  `center_id` bigint(20) DEFAULT NULL,
  `custodian_id` bigint(20) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `current_balance` decimal(15,2) DEFAULT 0.00,
  `held_amount` decimal(15,2) DEFAULT 0.00,
  `manual_balance` decimal(15,2) DEFAULT 0.00,
  `balance_updated_at` datetime DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_funds_branch` (`branch_id`),
  KEY `fk_funds_project` (`project_id`),
  KEY `fk_funds_center` (`center_id`),
  KEY `fk_funds_custodian` (`custodian_id`),
  CONSTRAINT `fk_funds_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_funds_center` FOREIGN KEY (`center_id`) REFERENCES `cost_centers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_funds_custodian` FOREIGN KEY (`custodian_id`) REFERENCES `parties` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_funds_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `investment_contract_increases`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `investment_contract_increases`;
CREATE TABLE `investment_contract_increases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `year_number` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `contract_id` (`contract_id`),
  CONSTRAINT `investment_contract_increases_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `investment_contracts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=125 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `investment_contracts`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `investment_contracts`;
CREATE TABLE `investment_contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `portfolio_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `center_id` int(11) NOT NULL,
  `project_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) NOT NULL COMMENT 'Investor / Second Party',
  `lessor_ids` text DEFAULT NULL,
  `contract_number` varchar(100) NOT NULL,
  `status` enum('draft','active','expired','archived','cancelled') DEFAULT 'draft',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `contract_date` date NOT NULL COMMENT 'Organization / Documentation Date',
  `duration_months` int(11) DEFAULT NULL,
  `duration_days` int(11) DEFAULT NULL,
  `investment_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `payment_cycle` enum('monthly','quarterly','semi_annual','annual','flexible','one_time') DEFAULT 'annual',
  `details` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `contract_number` (`contract_number`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `invoice_items`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `invoice_items`;
CREATE TABLE `invoice_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) NOT NULL,
  `description` text NOT NULL,
  `quantity` decimal(15,3) DEFAULT 1.000,
  `unit_price` decimal(15,2) DEFAULT 0.00,
  `total_price` decimal(15,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`),
  CONSTRAINT `invoice_items_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `invoices`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `invoices`;
CREATE TABLE `invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(50) NOT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `invoice_title` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `portfolio_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `party_from_id` int(11) DEFAULT NULL,
  `party_id` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `center_id` int(11) DEFAULT NULL,
  `work_category_id` varchar(100) DEFAULT NULL,
  `subtotal` decimal(15,2) DEFAULT 0.00,
  `tax_rate` decimal(5,2) DEFAULT 15.00,
  `tax_amount` decimal(15,2) DEFAULT 0.00,
  `tax_mode` enum('exclusive','inclusive','none') DEFAULT 'exclusive',
  `total_amount` decimal(15,2) DEFAULT 0.00,
  `status` enum('active','draft','cancelled') DEFAULT 'draft',
  `external_link` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_number` (`invoice_number`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `lease_contract_increases`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `lease_contract_increases`;
CREATE TABLE `lease_contract_increases` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `contract_id` bigint(20) NOT NULL,
  `year_number` int(11) NOT NULL,
  `start_date` date DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `contract_id` (`contract_id`),
  CONSTRAINT `lease_contract_increases_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `lease_contracts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `lease_contracts`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `lease_contracts`;
CREATE TABLE `lease_contracts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `contract_number` varchar(191) NOT NULL,
  `tenant_id` bigint(20) NOT NULL,
  `lessor_ids` text DEFAULT NULL,
  `unit_id` bigint(20) NOT NULL,
  `portfolio_id` bigint(20) NOT NULL,
  `branch_id` bigint(20) NOT NULL,
  `center_id` bigint(20) NOT NULL,
  `status` enum('draft','active','expired','archived') NOT NULL DEFAULT 'draft',
  `documentation_date` date DEFAULT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `duration_months` int(11) DEFAULT NULL,
  `duration_days` int(11) DEFAULT NULL,
  `rent_amount` decimal(15,2) NOT NULL,
  `payment_cycle` enum('monthly','quarterly','semi_annual','annual','flexible') NOT NULL DEFAULT 'annual',
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_contract_number` (`contract_number`),
  KEY `idx_tenant` (`tenant_id`),
  KEY `idx_unit` (`unit_id`),
  KEY `idx_center` (`center_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `meter_links`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `meter_links`;
CREATE TABLE `meter_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `meter_id` int(11) NOT NULL,
  `entity_type` enum('center','unit','project') NOT NULL,
  `entity_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `meter_id` (`meter_id`),
  CONSTRAINT `meter_links_ibfk_1` FOREIGN KEY (`meter_id`) REFERENCES `meters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `meters`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `meters`;
CREATE TABLE `meters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('electricity','water') NOT NULL,
  `name` varchar(255) NOT NULL,
  `account_number` varchar(100) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `subscription_number` varchar(100) DEFAULT NULL,
  `serial_number` varchar(100) DEFAULT NULL,
  `location_number` varchar(100) DEFAULT NULL,
  `breaker_capacity` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `parking_assignments`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `parking_assignments`;
CREATE TABLE `parking_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `package_id` int(11) NOT NULL,
  `system_id` varchar(50) NOT NULL,
  `serial_number` varchar(100) DEFAULT NULL,
  `save_number` varchar(100) DEFAULT NULL,
  `beneficiary_name` varchar(255) NOT NULL,
  `vehicle_make` varchar(100) DEFAULT NULL,
  `vehicle_model` varchar(100) DEFAULT NULL,
  `vehicle_color` varchar(50) DEFAULT NULL,
  `vehicle_plate` varchar(50) DEFAULT NULL,
  `assignment_status` varchar(50) DEFAULT 'included',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `system_id` (`system_id`),
  KEY `package_id` (`package_id`),
  CONSTRAINT `parking_assignments_ibfk_1` FOREIGN KEY (`package_id`) REFERENCES `parking_packages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `parking_audit_logs`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `parking_audit_logs`;
CREATE TABLE `parking_audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `assignment_id` int(11) NOT NULL,
  `action_type` varchar(50) NOT NULL,
  `old_beneficiary` varchar(255) DEFAULT NULL,
  `new_beneficiary` varchar(255) DEFAULT NULL,
  `old_vehicle_info` text DEFAULT NULL,
  `new_vehicle_info` text DEFAULT NULL,
  `old_serial_number` varchar(100) DEFAULT NULL,
  `new_serial_number` varchar(100) DEFAULT NULL,
  `save_number` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `action_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `action_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `assignment_id` (`assignment_id`),
  CONSTRAINT `parking_audit_logs_ibfk_1` FOREIGN KEY (`assignment_id`) REFERENCES `parking_assignments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `parking_packages`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `parking_packages`;
CREATE TABLE `parking_packages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `package_number` varchar(100) DEFAULT NULL,
  `package_name` varchar(255) NOT NULL,
  `party_id` int(11) DEFAULT NULL,
  `portfolio_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `center_id` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `contract_id` int(11) DEFAULT NULL,
  `total_slots` int(11) NOT NULL,
  `linked_entity_type` varchar(50) DEFAULT NULL,
  `linked_entity_id` int(11) DEFAULT NULL,
  `category` varchar(50) DEFAULT 'independent',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` varchar(50) DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `parties`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `parties`;
CREATE TABLE `parties` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `category` enum('customer','supplier','employee','general') NOT NULL DEFAULT 'customer',
  `entity_type` enum('individual','company') NOT NULL DEFAULT 'individual',
  `name` varchar(191) NOT NULL,
  `trade_name` varchar(191) DEFAULT NULL COMMENT 'الاسم التجاري للمنشأة',
  `national_address` text DEFAULT NULL COMMENT 'العنوان الوطني',
  `email` varchar(191) DEFAULT NULL,
  `notes` text DEFAULT NULL COMMENT 'ملاحظات',
  `manager_name` varchar(191) DEFAULT NULL COMMENT 'مدير السجل للمنشأة',
  `phone` varchar(50) DEFAULT NULL,
  `id_number` varchar(50) DEFAULT NULL COMMENT 'رقم السجل أو الهوية',
  `unified_number` varchar(50) DEFAULT NULL COMMENT 'الرقم الموحد للمنشأة',
  `tax_number` varchar(50) DEFAULT NULL COMMENT 'الرقم الضريبي',
  `birth_date` date DEFAULT NULL COMMENT 'تاريخ الميلاد للفرد',
  `issue_date` date DEFAULT NULL COMMENT 'تاريخ الإصدار',
  `expiry_date` date DEFAULT NULL COMMENT 'تاريخ الانتهاء',
  `status` enum('active','inactive','draft') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=119 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `portfolios`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `portfolios`;
CREATE TABLE `portfolios` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `code` varchar(20) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `currency` char(3) NOT NULL DEFAULT 'SAR',
  `lock_date` date DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `proforma_invoice_items`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `proforma_invoice_items`;
CREATE TABLE `proforma_invoice_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `quantity` decimal(12,2) DEFAULT 1.00,
  `unit_price` decimal(15,2) DEFAULT 0.00,
  `total_price` decimal(15,2) DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`),
  CONSTRAINT `proforma_invoice_items_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `proforma_invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `proforma_invoices`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `proforma_invoices`;
CREATE TABLE `proforma_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_number` varchar(50) NOT NULL,
  `invoice_title` varchar(255) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `date` date NOT NULL,
  `party_id` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `center_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `portfolio_id` int(11) DEFAULT NULL,
  `work_category_id` varchar(100) DEFAULT NULL,
  `external_link` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `subtotal` decimal(15,2) DEFAULT 0.00,
  `tax_mode` enum('none','exclusive','inclusive') DEFAULT 'none',
  `tax_rate` decimal(5,2) DEFAULT 15.00,
  `tax_amount` decimal(15,2) DEFAULT 0.00,
  `total_amount` decimal(15,2) DEFAULT 0.00,
  `status` enum('draft','active','archived','cancelled') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_number` (`invoice_number`),
  KEY `idx_proforma_party` (`party_id`),
  KEY `idx_proforma_project` (`project_id`),
  KEY `idx_proforma_branch` (`branch_id`),
  KEY `idx_proforma_portfolio` (`portfolio_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `project_categories`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `project_categories`;
CREATE TABLE `project_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `project_work_budgets`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `project_work_budgets`;
CREATE TABLE `project_work_budgets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` bigint(20) NOT NULL,
  `category_id` int(11) NOT NULL,
  `budgeted_amount` decimal(15,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_id` (`project_id`,`category_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `project_work_budgets_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
  CONSTRAINT `project_work_budgets_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `project_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `projects`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `branch_id` bigint(20) NOT NULL,
  `center_id` bigint(20) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  `name` varchar(191) NOT NULL,
  `description` text DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `budget` decimal(19,4) NOT NULL DEFAULT 0.0000,
  `status` enum('active','completed','suspended','cancelled') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `proj_branch_id_fk` (`branch_id`),
  KEY `fk_project_center` (`center_id`),
  CONSTRAINT `fk_project_center` FOREIGN KEY (`center_id`) REFERENCES `cost_centers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `proj_branch_id_fk` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `purchase_contract_branches`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `purchase_contract_branches`;
CREATE TABLE `purchase_contract_branches` (
  `contract_id` int(11) NOT NULL,
  `branch_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `purchase_contract_centers`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `purchase_contract_centers`;
CREATE TABLE `purchase_contract_centers` (
  `contract_id` int(11) NOT NULL,
  `center_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`center_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `purchase_contract_parties`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `purchase_contract_parties`;
CREATE TABLE `purchase_contract_parties` (
  `contract_id` int(11) NOT NULL,
  `party_id` int(11) NOT NULL,
  `side` enum('seller','buyer') NOT NULL,
  PRIMARY KEY (`contract_id`,`party_id`,`side`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `purchase_contract_portfolios`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `purchase_contract_portfolios`;
CREATE TABLE `purchase_contract_portfolios` (
  `contract_id` int(11) NOT NULL,
  `portfolio_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`portfolio_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `purchase_contract_projects`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `purchase_contract_projects`;
CREATE TABLE `purchase_contract_projects` (
  `contract_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `purchase_contract_units`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `purchase_contract_units`;
CREATE TABLE `purchase_contract_units` (
  `contract_id` int(11) NOT NULL,
  `unit_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `purchase_contracts`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `purchase_contracts`;
CREATE TABLE `purchase_contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_number` varchar(50) NOT NULL,
  `category` enum('real_estate','vehicles','software','machinery','goods','other') DEFAULT 'other',
  `purchase_date` date NOT NULL,
  `total_amount` decimal(15,2) NOT NULL,
  `status` enum('draft','active','archived','expired') DEFAULT 'draft',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `role_modules`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `role_modules`;
CREATE TABLE `role_modules` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `role_id` bigint(20) NOT NULL,
  `module_name` varchar(50) NOT NULL COMMENT 'e.g. hierarchy, directory, documents, parking, contracts',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `access_level` varchar(20) NOT NULL DEFAULT 'manager',
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_module_unique` (`role_id`,`module_name`),
  KEY `rm_role_id_foreign` (`role_id`),
  CONSTRAINT `rm_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table `role_modules`
INSERT INTO `role_modules` (`id`, `role_id`, `module_name`, `created_at`, `access_level`) VALUES ('47', '1', 'parking', '2026-03-04 03:04:12', 'view');
INSERT INTO `role_modules` (`id`, `role_id`, `module_name`, `created_at`, `access_level`) VALUES ('48', '1', 'invoices', '2026-03-17 04:02:47', 'manager');
INSERT INTO `role_modules` (`id`, `role_id`, `module_name`, `created_at`, `access_level`) VALUES ('49', '2', 'invoices', '2026-03-17 04:02:47', 'manager');
INSERT INTO `role_modules` (`id`, `role_id`, `module_name`, `created_at`, `access_level`) VALUES ('50', '3', 'invoices', '2026-03-17 04:02:47', 'manager');
INSERT INTO `role_modules` (`id`, `role_id`, `module_name`, `created_at`, `access_level`) VALUES ('51', '4', 'invoices', '2026-03-17 04:02:47', 'manager');

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

DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name_ar` varchar(191) NOT NULL,
  `name_en` varchar(191) NOT NULL,
  `can_view` tinyint(1) NOT NULL DEFAULT 0,
  `can_create` tinyint(1) NOT NULL DEFAULT 0,
  `can_edit` tinyint(1) NOT NULL DEFAULT 0,
  `can_print` tinyint(1) NOT NULL DEFAULT 0,
  `can_archive` tinyint(1) NOT NULL DEFAULT 0,
  `can_restore` tinyint(1) NOT NULL DEFAULT 0,
  `can_delete` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table `roles`
INSERT INTO `roles` (`id`, `name_ar`, `name_en`, `can_view`, `can_create`, `can_edit`, `can_print`, `can_archive`, `can_restore`, `can_delete`, `created_at`) VALUES ('1', 'مشاهد', 'Viewer', '1', '0', '0', '1', '0', '0', '0', '2026-02-22 02:10:24');
INSERT INTO `roles` (`id`, `name_ar`, `name_en`, `can_view`, `can_create`, `can_edit`, `can_print`, `can_archive`, `can_restore`, `can_delete`, `created_at`) VALUES ('2', 'مدخل بيانات1', 'Data Entry', '1', '1', '0', '1', '0', '0', '0', '2026-02-22 02:10:24');
INSERT INTO `roles` (`id`, `name_ar`, `name_en`, `can_view`, `can_create`, `can_edit`, `can_print`, `can_archive`, `can_restore`, `can_delete`, `created_at`) VALUES ('3', 'محرر', 'Editor', '1', '1', '1', '1', '1', '0', '0', '2026-02-22 02:10:24');
INSERT INTO `roles` (`id`, `name_ar`, `name_en`, `can_view`, `can_create`, `can_edit`, `can_print`, `can_archive`, `can_restore`, `can_delete`, `created_at`) VALUES ('4', 'مدير', 'Manager', '1', '1', '1', '1', '1', '1', '1', '2026-02-22 02:10:24');

-- --------------------------------------------------------
-- Table structure for table `sales_contract_branches`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `sales_contract_branches`;
CREATE TABLE `sales_contract_branches` (
  `contract_id` int(11) NOT NULL,
  `branch_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `sales_contract_centers`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `sales_contract_centers`;
CREATE TABLE `sales_contract_centers` (
  `contract_id` int(11) NOT NULL,
  `center_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`center_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `sales_contract_parties`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `sales_contract_parties`;
CREATE TABLE `sales_contract_parties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `party_id` int(11) NOT NULL,
  `side` enum('seller','buyer') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `sales_contract_portfolios`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `sales_contract_portfolios`;
CREATE TABLE `sales_contract_portfolios` (
  `contract_id` int(11) NOT NULL,
  `portfolio_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`portfolio_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `sales_contract_projects`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `sales_contract_projects`;
CREATE TABLE `sales_contract_projects` (
  `contract_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `sales_contract_units`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `sales_contract_units`;
CREATE TABLE `sales_contract_units` (
  `contract_id` int(11) NOT NULL,
  `unit_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `sales_contracts`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `sales_contracts`;
CREATE TABLE `sales_contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_number` varchar(50) NOT NULL,
  `category` varchar(50) NOT NULL,
  `sale_date` date NOT NULL,
  `total_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','draft','archived','expired') DEFAULT 'active',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- Table structure for table `service_contract_centers`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `service_contract_centers`;
CREATE TABLE `service_contract_centers` (
  `contract_id` int(11) NOT NULL,
  `center_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`center_id`),
  CONSTRAINT `service_contract_centers_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `service_contracts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `service_contract_increases`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `service_contract_increases`;
CREATE TABLE `service_contract_increases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `year_number` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `contract_id` (`contract_id`),
  CONSTRAINT `service_contract_increases_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `service_contracts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `service_contract_projects`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `service_contract_projects`;
CREATE TABLE `service_contract_projects` (
  `contract_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`project_id`),
  CONSTRAINT `service_contract_projects_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `service_contracts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `service_contract_units`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `service_contract_units`;
CREATE TABLE `service_contract_units` (
  `contract_id` int(11) NOT NULL,
  `unit_id` int(11) NOT NULL,
  PRIMARY KEY (`contract_id`,`unit_id`),
  CONSTRAINT `service_contract_units_ibfk_1` FOREIGN KEY (`contract_id`) REFERENCES `service_contracts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `service_contracts`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `service_contracts`;
CREATE TABLE `service_contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_number` varchar(100) NOT NULL,
  `status` enum('draft','active','expired','archived','renewed','cancelled') DEFAULT 'draft',
  `category` varchar(50) DEFAULT 'general',
  `portfolio_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `center_id` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `unit_id` int(11) DEFAULT NULL,
  `provider_id` int(11) NOT NULL COMMENT 'Service Provider / Second Party',
  `lessor_id` int(11) DEFAULT NULL COMMENT 'Property Owner / First Party (Optional if internal)',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `contract_date` date NOT NULL,
  `duration_months` int(11) DEFAULT NULL,
  `duration_days` int(11) DEFAULT NULL,
  `contract_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `payment_cycle` enum('monthly','quarterly','semi_annual','annual','flexible','one_time') DEFAULT 'annual',
  `details` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `renewed_from` int(11) DEFAULT NULL COMMENT 'Link to parent contract',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `contract_number` (`contract_number`),
  KEY `renewed_from` (`renewed_from`),
  CONSTRAINT `service_contracts_ibfk_1` FOREIGN KEY (`renewed_from`) REFERENCES `service_contracts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `settings`
-- --------------------------------------------------------

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

-- Dumping data for table `settings`
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('contact_address', 'العنوان', '2026-02-26 01:05:48');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('contact_email', 'name@name.com', '2026-02-26 01:05:48');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('contact_phone', '0', '2026-02-26 01:05:48');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('default_tax_id', '', '2026-02-26 01:11:47');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('footer_center', 'نظام مالي سحابي ذكي. تصميم وتطوير ناجي', '2026-02-26 22:46:18');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('footer_left', '{hijri_date} | {gregorian_date} | {time}', '2026-02-26 01:05:04');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('footer_right', 'جميع الحقوق محفوظة © 2026', '2026-02-26 00:09:04');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('hijri_correction', '0', '2026-02-26 01:07:53');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('print_font', 'Tajawal', '2026-03-19 17:24:25');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('site_favicon', '', '2026-02-26 17:26:06');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('site_font', 'Readex Pro', '2026-03-19 17:16:38');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('site_logo', '', '2026-02-26 17:26:06');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('site_name', 'نظام الادارة المتكامل', '2026-03-17 23:52:46');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('theme_color_branches', '#de35c2', '2026-03-19 16:41:58');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('theme_color_centers', '#06b6d4', '2026-03-19 16:41:58');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('theme_color_claims', '#25d684', '2026-03-21 15:39:31');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('theme_color_invoices', '#e33371', '2026-03-18 00:26:46');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('theme_color_portfolios', '#8b5cf6', '2026-03-19 16:41:58');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('theme_color_proforma', '#cd42e3', '2026-03-18 00:37:50');
INSERT INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES ('theme_color_projects', '#f59e0b', '2026-03-19 16:41:58');

-- --------------------------------------------------------
-- Table structure for table `tax_rates`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `tax_rates`;
CREATE TABLE `tax_rates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `percentage` decimal(5,2) NOT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `transfer_entries`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `transfer_entries`;
CREATE TABLE `transfer_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transfer_id` int(11) NOT NULL,
  `side` enum('source','destination') NOT NULL,
  `account_type` enum('bank','fund') NOT NULL,
  `account_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `item_reference` varchar(100) DEFAULT NULL,
  `memo` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `transfer_id` (`transfer_id`),
  CONSTRAINT `transfer_entries_ibfk_1` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `transfers`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `transfers`;
CREATE TABLE `transfers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transfer_number` varchar(50) NOT NULL,
  `date` date NOT NULL,
  `portfolio_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `total_amount` decimal(15,2) NOT NULL,
  `status` varchar(20) DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transfer_number` (`transfer_number`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `units`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `units`;
CREATE TABLE `units` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `branch_id` bigint(20) NOT NULL,
  `center_id` bigint(20) NOT NULL,
  `type` varchar(50) NOT NULL DEFAULT 'other',
  `name` varchar(191) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `floor` varchar(50) DEFAULT NULL,
  `area` decimal(10,2) DEFAULT NULL,
  `status` enum('active','leased','inactive','cancelled') NOT NULL DEFAULT 'active',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `units_branch_id_fk` (`branch_id`),
  KEY `units_center_id_fk` (`center_id`),
  CONSTRAINT `units_branch_id_fk` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  CONSTRAINT `units_center_id_fk` FOREIGN KEY (`center_id`) REFERENCES `cost_centers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `user_access_scopes`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `user_access_scopes`;
CREATE TABLE `user_access_scopes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `role_id` bigint(20) NOT NULL,
  `entity_type` varchar(50) NOT NULL COMMENT 'portfolio, branch, center, project',
  `entity_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `uas_user_id_foreign` (`user_id`),
  KEY `uas_role_id_foreign` (`role_id`),
  CONSTRAINT `uas_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `uas_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `username` varchar(191) DEFAULT NULL,
  `email` varchar(191) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `permissions_version` int(10) NOT NULL DEFAULT 1,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_login_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_username_unique` (`username`),
  UNIQUE KEY `users_phone_unique` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `vouchers`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `vouchers`;
CREATE TABLE `vouchers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `voucher_number` varchar(100) NOT NULL,
  `type` varchar(50) NOT NULL CHECK (`type` in ('receipt','payment','adjustment')),
  `adjustment_subtype` enum('financial','contract','party') DEFAULT NULL,
  `portfolio_id` int(11) DEFAULT NULL,
  `party_id` int(11) DEFAULT NULL,
  `cost_center_id` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `work_category_id` int(11) DEFAULT NULL,
  `unit_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `bank_id` int(11) DEFAULT NULL,
  `bank_account_id` int(11) DEFAULT NULL,
  `fund_id` bigint(20) DEFAULT NULL,
  `reference_type` varchar(100) NOT NULL,
  `reference_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `payment_method` varchar(100) DEFAULT NULL,
  `document_number` varchar(255) DEFAULT NULL,
  `external_reference` varchar(100) DEFAULT NULL,
  `date` date NOT NULL,
  `description` text DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_vouchers_number` (`voucher_number`),
  KEY `idx_vouchers_reference` (`reference_type`,`reference_id`),
  KEY `idx_vouchers_work_category` (`work_category_id`),
  KEY `idx_vouchers_portfolio` (`portfolio_id`),
  KEY `idx_vouchers_fund` (`fund_id`),
  KEY `idx_adjustment_subtype` (`adjustment_subtype`),
  CONSTRAINT `check_status` CHECK (`status` in ('active','cancelled','draft'))
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;