/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; DROP TABLE IF EXISTS `acc_attachments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_attachments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `attachable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `attachable_id` bigint(20) unsigned NOT NULL, `filename` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `mime_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `size` bigint(20) unsigned DEFAULT NULL, `path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_attachments_attachable_type_attachable_id_index` (`attachable_type`,`attachable_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_audit_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_audit_logs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `action` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `module` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `entity_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `entity_id` bigint(20) unsigned DEFAULT NULL, `description` text COLLATE utf8mb4_unicode_ci NOT NULL, `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)), `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)), `ip_address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `method` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_id` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_audit_logs_action_module_index` (`action`,`module`), KEY `acc_audit_logs_entity_type_entity_id_index` (`entity_type`,`entity_id`), KEY `acc_audit_logs_user_id_created_at_index` (`user_id`,`created_at`), KEY `acc_audit_logs_created_at_index` (`created_at`), CONSTRAINT `acc_audit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=153 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_budgets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_budgets` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `period_type` enum('monthly','quarterly','yearly') COLLATE utf8mb4_unicode_ci NOT NULL, `period_year` int(11) NOT NULL, `period_month` int(11) DEFAULT NULL COMMENT 'For monthly budgets', `period_quarter` int(11) DEFAULT NULL COMMENT 'For quarterly budgets', `total_budget` decimal(15,2) NOT NULL, `allocated_amount` decimal(15,2) NOT NULL DEFAULT 0.00, `spent_amount` decimal(15,2) NOT NULL DEFAULT 0.00, `remaining_amount` decimal(15,2) NOT NULL DEFAULT 0.00, `branch_id` bigint(20) unsigned NOT NULL, `department_id` bigint(20) unsigned DEFAULT NULL, `expense_category_id` bigint(20) unsigned DEFAULT NULL, `category_allocations` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'JSON of category-specific allocations' CHECK (json_valid(`category_allocations`)), `status` enum('draft','active','completed','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft', `approved_by` bigint(20) unsigned DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `approval_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_by` bigint(20) unsigned NOT NULL, `updated_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_budgets_branch_id_period_type_period_year_index` (`branch_id`,`period_type`,`period_year`), KEY `acc_budgets_status_period_type_index` (`status`,`period_type`), KEY `acc_budgets_expense_category_id_status_index` (`expense_category_id`,`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_client_credits`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_client_credits` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `client_id` bigint(20) unsigned NOT NULL, `amount` decimal(14,2) NOT NULL, `currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL, `exchange_rate` decimal(10,8) NOT NULL DEFAULT 1.00000000, `credit_date` date NOT NULL, `description` text COLLATE utf8mb4_unicode_ci NOT NULL, `reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` enum('active','used','expired') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_client_credits_client_id_status_index` (`client_id`,`status`), KEY `acc_client_credits_credit_date_index` (`credit_date`), CONSTRAINT `acc_client_credits_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `acc_clients` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_clients`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_clients` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `reference` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `billing_address` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `acc_clients_reference_unique` (`reference`), KEY `acc_clients_email_index` (`email`), KEY `acc_clients_country_id_index` (`country_id`), KEY `acc_clients_added_by_index` (`added_by`), CONSTRAINT `acc_clients_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `acc_clients_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=273 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_expense_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_expense_categories` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Category code for reporting', `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `color` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Hex color for UI display', `icon` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'FontAwesome icon class', `monthly_budget` decimal(15,2) DEFAULT NULL, `yearly_budget` decimal(15,2) DEFAULT NULL, `requires_approval` tinyint(1) NOT NULL DEFAULT 0, `approval_threshold` decimal(15,2) DEFAULT NULL COMMENT 'Amount above which approval is required', `is_active` tinyint(1) NOT NULL DEFAULT 1, `sort_order` int(11) NOT NULL DEFAULT 0, `parent_id` bigint(20) unsigned DEFAULT NULL COMMENT 'For sub-categories', `created_by` bigint(20) unsigned NOT NULL, `updated_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `acc_expense_categories_code_unique` (`code`), KEY `acc_expense_categories_is_active_sort_order_index` (`is_active`,`sort_order`), KEY `acc_expense_categories_parent_id_index` (`parent_id`), KEY `acc_expense_categories_code_index` (`code`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_expenses`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_expenses` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `expense_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Auto-generated expense number', `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `amount` decimal(15,2) NOT NULL, `currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `exchange_rate` decimal(10,4) NOT NULL DEFAULT 1.0000, `amount_usd` decimal(15,2) NOT NULL COMMENT 'Amount converted to USD', `branch_id` bigint(20) unsigned NOT NULL, `department_id` bigint(20) unsigned DEFAULT NULL, `expense_category_id` bigint(20) unsigned NOT NULL, `sub_category` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `payment_method` enum('cash','bank_transfer','check','credit_card','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'bank_transfer', `payment_reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `payment_date` date NOT NULL, `expense_date` date NOT NULL, `vendor_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `vendor_contact` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `vendor_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `vendor_phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` enum('pending','approved','rejected','paid','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `approved_by` bigint(20) unsigned DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `approval_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_recurring` tinyint(1) NOT NULL DEFAULT 0, `recurrence_type` enum('monthly','quarterly','yearly') COLLATE utf8mb4_unicode_ci DEFAULT NULL, `next_due_date` date DEFAULT NULL, `recurrence_count` int(11) DEFAULT NULL COMMENT 'Number of times to repeat', `recurrence_completed` int(11) NOT NULL DEFAULT 0, `budget_id` bigint(20) unsigned DEFAULT NULL, `budget_allocated` decimal(15,2) DEFAULT NULL, `budget_remaining` decimal(15,2) DEFAULT NULL, `created_by` bigint(20) unsigned NOT NULL, `updated_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_expenses_branch_id_expense_date_index` (`branch_id`,`expense_date`), KEY `acc_expenses_expense_category_id_status_index` (`expense_category_id`,`status`), KEY `acc_expenses_payment_date_status_index` (`payment_date`,`status`), KEY `acc_expenses_created_by_status_index` (`created_by`,`status`), KEY `acc_expenses_expense_number_index` (`expense_number`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_invoice_adders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_invoice_adders` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `invoice_id` bigint(20) unsigned NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `type` enum('charge','discount') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'charge', `amount` decimal(14,2) NOT NULL DEFAULT 0.00, `currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `percentage` decimal(5,2) DEFAULT NULL, `is_percentage` tinyint(1) NOT NULL DEFAULT 0, `sort_order` int(11) NOT NULL DEFAULT 0, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_invoice_adders_invoice_id_sort_order_index` (`invoice_id`,`sort_order`), CONSTRAINT `acc_invoice_adders_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `acc_invoices` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_invoice_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_invoice_items` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `invoice_id` bigint(20) unsigned NOT NULL, `acc_sections_id` bigint(20) unsigned DEFAULT NULL, `acc_services_id` bigint(20) unsigned DEFAULT NULL, `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `quantity` int(11) NOT NULL DEFAULT 1, `currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `from_amount` decimal(14,2) NOT NULL DEFAULT 0.00, `from_currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `exchange_rate` decimal(16,8) NOT NULL, `to_amount` decimal(14,2) NOT NULL DEFAULT 0.00, `to_currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `line_net` decimal(14,2) NOT NULL, `tax_percentage` decimal(14,2) NOT NULL, `tax_amount` decimal(14,2) NOT NULL DEFAULT 0.00, `tax_value` decimal(14,2) NOT NULL, `line_gross` decimal(14,2) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_invoice_items_invoice_id_index` (`invoice_id`), KEY `acc_invoice_items_acc_sections_id_index` (`acc_sections_id`), KEY `acc_invoice_items_acc_services_id_index` (`acc_services_id`), CONSTRAINT `acc_invoice_items_acc_sections_id_foreign` FOREIGN KEY (`acc_sections_id`) REFERENCES `acc_sections` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `acc_invoice_items_acc_services_id_foreign` FOREIGN KEY (`acc_services_id`) REFERENCES `acc_services` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `acc_invoice_items_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `acc_invoices` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4356 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_invoice_payments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_invoice_payments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `invoice_id` bigint(20) unsigned NOT NULL, `method` enum('bank_transfer','credit_card','cash','other') COLLATE utf8mb4_unicode_ci NOT NULL, `amount` decimal(14,2) NOT NULL, `currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL, `exchange_rate` decimal(16,8) NOT NULL, `paid_on` date NOT NULL, `transaction_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_invoice_payments_invoice_id_paid_on_index` (`invoice_id`,`paid_on`), CONSTRAINT `acc_invoice_payments_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `acc_invoices` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=418 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_invoices`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_invoices` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `client_id` bigint(20) unsigned NOT NULL, `client_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `invoice_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `invoice_date` date NOT NULL, `status` enum('draft','issued','paid','cancelled','paid_partial','non_paid') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft', `discount_amount` decimal(14,2) NOT NULL DEFAULT 0.00, `gross_amount` decimal(14,2) NOT NULL DEFAULT 0.00, `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `acc_invoices_invoice_number_unique` (`invoice_number`), KEY `acc_invoices_client_id_status_index` (`client_id`,`status`), KEY `acc_invoices_invoice_date_index` (`invoice_date`), KEY `acc_invoices_added_by_index` (`added_by`), CONSTRAINT `acc_invoices_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `acc_invoices_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `acc_clients` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=629 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_sections`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_sections` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `section_name` varchar(999) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_sections_added_by_index` (`added_by`), CONSTRAINT `acc_sections_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=147 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_services`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_services` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `service_name` varchar(999) COLLATE utf8mb4_unicode_ci NOT NULL, `section_name` varchar(999) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `acc_sections_id` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_services_acc_sections_id_index` (`acc_sections_id`), CONSTRAINT `acc_services_acc_sections_id_foreign` FOREIGN KEY (`acc_sections_id`) REFERENCES `acc_sections` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=151 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_statement_lines`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_statement_lines` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `statement_id` bigint(20) unsigned NOT NULL, `date` date NOT NULL COMMENT 'Date of invoice or transaction', `invoice_id` bigint(20) unsigned DEFAULT NULL, `particulars` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Description or particulars', `line_no` int(11) DEFAULT NULL COMMENT 'Sequence or document number', `official_fees` decimal(14,2) NOT NULL DEFAULT 0.00, `attorney_fees` decimal(14,2) NOT NULL DEFAULT 0.00, `discount_amount` decimal(14,2) NOT NULL DEFAULT 0.00, `vat_amount` decimal(14,2) NOT NULL DEFAULT 0.00, `total_fees` decimal(14,2) NOT NULL DEFAULT 0.00, `exchange_rate` decimal(16,8) NOT NULL COMMENT 'Rate used for USD ↔ SAR conversions', `debit_usd` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'Amount debited in USD', `payment_sar` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'Payment recorded in SAR', `credit_usd` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'Credit in USD', `balance_sar` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'Running balance in SAR', `balance_usd` decimal(14,2) NOT NULL DEFAULT 0.00 COMMENT 'Running balance in USD', `remarks` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_statement_lines_invoice_id_foreign` (`invoice_id`), KEY `acc_statement_lines_statement_id_date_index` (`statement_id`,`date`), CONSTRAINT `acc_statement_lines_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `acc_invoices` (`id`) ON DELETE SET NULL, CONSTRAINT `acc_statement_lines_statement_id_foreign` FOREIGN KEY (`statement_id`) REFERENCES `acc_statements` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3844 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_statements`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_statements` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `client_id` bigint(20) unsigned NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `period_start` date NOT NULL COMMENT 'Start date for statement', `period_end` date DEFAULT NULL COMMENT 'End date (optional)', `opening_balance_usd` decimal(14,2) NOT NULL DEFAULT 0.00, `opening_balance_sar` decimal(14,2) NOT NULL DEFAULT 0.00, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_statements_client_id_foreign` (`client_id`), KEY `acc_statements_country_id_index` (`country_id`), KEY `acc_statements_added_by_index` (`added_by`), CONSTRAINT `acc_statements_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `acc_clients` (`id`) ON DELETE CASCADE, CONSTRAINT `acc_statements_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=244 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_transactions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_transactions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `aiptref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `clientref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `type_transactions_id` bigint(20) unsigned NOT NULL, `status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `role_ranks` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`role_ranks`)), `department_id` bigint(20) unsigned NOT NULL, `country` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `deadline` date DEFAULT NULL, `bill_deadline` date DEFAULT NULL, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `id_type` bigint(20) unsigned NOT NULL, `procedure` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `value` decimal(20,8) NOT NULL DEFAULT 1.00000000, `exchangeRate` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `currency` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'SAR', `currency_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `value_usd` decimal(20,8) NOT NULL DEFAULT 1.00000000, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `related_table` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `related_id` bigint(20) unsigned NOT NULL, `has_bills` tinyint(1) NOT NULL, `InvoiceNo` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `bill_status` enum('paid','non_paid','paid_partially','') COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_by` bigint(20) unsigned NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_transactions_type_transactions_id_foreign` (`type_transactions_id`), KEY `acc_transactions_department_id_foreign` (`department_id`), KEY `acc_transactions_created_by_foreign` (`created_by`), CONSTRAINT `acc_transactions_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`), CONSTRAINT `acc_transactions_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON UPDATE CASCADE, CONSTRAINT `acc_transactions_type_transactions_id_foreign` FOREIGN KEY (`type_transactions_id`) REFERENCES `actions_type_transactions` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1429 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `acc_transactions_comments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `acc_transactions_comments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `comment` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `related_TKT` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `acc_transactions_comments_added_by_index` (`added_by`), KEY `acc_transactions_comments_related_tkt_index` (`related_TKT`), CONSTRAINT `acc_transactions_comments_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `acc_transactions_comments_related_tkt_foreign` FOREIGN KEY (`related_TKT`) REFERENCES `acc_transactions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1869 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `actions_type_transactions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `actions_type_transactions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `role_ranks` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`role_ranks`)), `department_id` bigint(20) unsigned NOT NULL, `has_bills` tinyint(1) NOT NULL, `draft` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `actions_type_transactions_name_unique` (`name`), KEY `actions_type_transactions_department_id_foreign` (`department_id`), CONSTRAINT `actions_type_transactions_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `applicants`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `applicants` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `applicants_country_id_index` (`country_id`), KEY `applicants_added_by_index` (`added_by`), CONSTRAINT `applicants_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `applicants_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=7843 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `associate_clients`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `associate_clients` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `client_id` bigint(20) unsigned DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `associate_clients_client_id_index` (`client_id`), KEY `associate_clients_associate_id_index` (`associate_id`), CONSTRAINT `associate_clients_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `associates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `associate_clients_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `associates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `associates` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `associate_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `img_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `associates_associate_name_unique` (`associate_name`), KEY `associates_country_id_index` (`country_id`), KEY `associates_added_by_index` (`added_by`), CONSTRAINT `associates_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `associates_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=656 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `audit_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `audit_logs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `auditable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `auditable_id` bigint(20) unsigned NOT NULL, `event` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)), `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)), `changed_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`changed_fields`)), `user_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_id` bigint(20) unsigned DEFAULT NULL, `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_agent` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `method` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`tags`)), `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `audit_logs_auditable_type_auditable_id_index` (`auditable_type`,`auditable_id`), KEY `audit_logs_user_type_user_id_index` (`user_type`,`user_id`), KEY `audit_logs_event_index` (`event`), KEY `audit_logs_created_at_index` (`created_at`), KEY `audit_logs_auditable_type_event_index` (`auditable_type`,`event`), KEY `audit_logs_user_id_foreign` (`user_id`), CONSTRAINT `audit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `branches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `branches` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Branch code for identification', `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `country` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `postal_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `website` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `manager_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `manager_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `manager_phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `opening_date` date DEFAULT NULL, `timezone` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'UTC', `currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `tax_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `registration_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_by` bigint(20) unsigned DEFAULT NULL, `updated_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `branches_code_unique` (`code`), KEY `branches_is_active_code_index` (`is_active`,`code`), KEY `branches_country_index` (`country`), KEY `branches_currency_index` (`currency`), KEY `branches_created_by_foreign` (`created_by`), KEY `branches_updated_by_foreign` (`updated_by`), CONSTRAINT `branches_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `branches_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `categories` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `category_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `clients`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `clients` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `client_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `type_clients_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, `img_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `following` tinyint(1) DEFAULT NULL, `code` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `clients_country_id_index` (`country_id`), KEY `clients_type_clients_id_index` (`type_clients_id`), KEY `clients_added_by_index` (`added_by`), KEY `clients_associate_id_index` (`associate_id`), KEY `idx_client_name` (`client_name`), CONSTRAINT `clients_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `clients_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `associates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `clients_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `clients_type_clients_id_foreign` FOREIGN KEY (`type_clients_id`) REFERENCES `type_clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3018 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `contacts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `contacts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `body` text COLLATE utf8mb4_unicode_ci NOT NULL, `resone` text COLLATE utf8mb4_unicode_ci NOT NULL, `solved_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `contacts_solved_by_index` (`solved_by`), CONSTRAINT `contacts_solved_by_foreign` FOREIGN KEY (`solved_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `countries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `countries` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `country_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `languages` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, `88` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `72` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `82` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `85` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `76` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `208` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `83` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `106` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `75` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `81` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `78` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `192` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `80` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `209` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `5` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `113` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `8` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `117` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `1327` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `86` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `idx_country_name` (`country_name`) ) ENGINE=InnoDB AUTO_INCREMENT=262 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `deadline_dates_messages`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `deadline_dates_messages` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `predate` date NOT NULL, `date` date NOT NULL, `current_status` int(11) DEFAULT NULL, `noti_to` bigint(20) unsigned DEFAULT 1, `client_id` bigint(20) unsigned DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, `methode_id` bigint(20) unsigned DEFAULT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `applicant_id` bigint(20) unsigned DEFAULT NULL, `related_TKT` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `deadline_dates_messages_noti_to_index` (`noti_to`), KEY `deadline_dates_messages_client_id_index` (`client_id`), KEY `deadline_dates_messages_associate_id_index` (`associate_id`), KEY `deadline_dates_messages_methode_id_index` (`methode_id`), KEY `deadline_dates_messages_country_id_index` (`country_id`), KEY `deadline_dates_messages_applicant_id_index` (`applicant_id`), KEY `deadline_dates_messages_related_tkt_index` (`related_TKT`), KEY `deadline_dates_messages_added_by_index` (`added_by`), CONSTRAINT `deadline_dates_messages_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `deadline_dates_messages_applicant_id_foreign` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`id`) ON DELETE CASCADE, CONSTRAINT `deadline_dates_messages_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `associates` (`id`) ON DELETE CASCADE, CONSTRAINT `deadline_dates_messages_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE, CONSTRAINT `deadline_dates_messages_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE, CONSTRAINT `deadline_dates_messages_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `methodes` (`id`) ON DELETE CASCADE, CONSTRAINT `deadline_dates_messages_noti_to_foreign` FOREIGN KEY (`noti_to`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `deadline_dates_messages_related_tkt_foreign` FOREIGN KEY (`related_TKT`) REFERENCES `trade_mark_tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=19120 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `departments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `departments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `department_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `failed_jobs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `failed_jobs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `connection` text COLLATE utf8mb4_unicode_ci NOT NULL, `queue` text COLLATE utf8mb4_unicode_ci NOT NULL, `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `failed_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `follow_clients`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `follow_clients` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `followed_by` bigint(20) unsigned DEFAULT NULL, `client_id` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `follow_clients_followed_by_index` (`followed_by`), KEY `follow_clients_client_id_index` (`client_id`), CONSTRAINT `follow_clients_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `follow_clients_followed_by_foreign` FOREIGN KEY (`followed_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_attendances`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_attendances` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint(20) unsigned NOT NULL, `date` date NOT NULL, `check_in_time` time DEFAULT NULL, `check_out_time` time DEFAULT NULL, `break_start_time` time DEFAULT NULL, `break_end_time` time DEFAULT NULL, `total_working_hours` int(11) DEFAULT NULL, `break_duration` int(11) DEFAULT NULL, `overtime_hours` int(11) DEFAULT NULL, `status` enum('present','absent','late','early_departure','half_day','work_from_home','business_trip','sick_leave','vacation') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'present', `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `location` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `ip_address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `device_info` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `check_in_method` enum('manual','biometric','card_swipe','mobile_app','web') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'manual', `check_out_method` enum('manual','biometric','card_swipe','mobile_app','web') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'manual', `is_approved` tinyint(1) NOT NULL DEFAULT 0, `approved_by` bigint(20) unsigned DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `approval_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_attendances_employee_id_date_unique` (`employee_id`,`date`), KEY `hr_attendances_approved_by_foreign` (`approved_by`), KEY `hr_attendances_date_status_index` (`date`,`status`), KEY `hr_attendances_employee_id_date_index` (`employee_id`,`date`), KEY `hr_attendances_status_is_approved_index` (`status`,`is_approved`), CONSTRAINT `hr_attendances_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_attendances_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=784 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_benefit_enrollments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_benefit_enrollments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `benefit_id` bigint(20) unsigned NOT NULL, `employee_id` bigint(20) unsigned NOT NULL, `enrollment_date` date NOT NULL, `effective_date` date NOT NULL, `expiry_date` date DEFAULT NULL, `status` enum('pending','active','inactive','rejected','cancelled','expired') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `employer_contribution` decimal(10,2) NOT NULL, `employee_contribution` decimal(10,2) NOT NULL, `coverage_amount` decimal(15,2) DEFAULT NULL, `deductible_amount` decimal(10,2) DEFAULT NULL, `co_pay_amount` decimal(10,2) DEFAULT NULL, `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `approved_by` bigint(20) unsigned DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `rejected_by` bigint(20) unsigned DEFAULT NULL, `rejected_at` timestamp NULL DEFAULT NULL, `rejection_reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_by` bigint(20) unsigned NOT NULL, `updated_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_benefit_enrollments_benefit_id_employee_id_unique` (`benefit_id`,`employee_id`), KEY `hr_benefit_enrollments_approved_by_foreign` (`approved_by`), KEY `hr_benefit_enrollments_rejected_by_foreign` (`rejected_by`), KEY `hr_benefit_enrollments_created_by_foreign` (`created_by`), KEY `hr_benefit_enrollments_updated_by_foreign` (`updated_by`), KEY `hr_benefit_enrollments_employee_id_status_index` (`employee_id`,`status`), KEY `hr_benefit_enrollments_benefit_id_status_index` (`benefit_id`,`status`), KEY `hr_benefit_enrollments_status_effective_date_index` (`status`,`effective_date`), KEY `hr_benefit_enrollments_status_expiry_date_index` (`status`,`expiry_date`), CONSTRAINT `hr_benefit_enrollments_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_benefit_enrollments_benefit_id_foreign` FOREIGN KEY (`benefit_id`) REFERENCES `hr_benefits` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_benefit_enrollments_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_benefit_enrollments_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_benefit_enrollments_rejected_by_foreign` FOREIGN KEY (`rejected_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_benefit_enrollments_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_benefits`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_benefits` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `type` enum('health_insurance','dental_insurance','vision_insurance','life_insurance','retirement_plan','paid_time_off','tuition_reimbursement','transportation','meal_allowance','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'other', `category` enum('mandatory','optional','voluntary') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'optional', `cost_type` enum('fixed','percentage','variable') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'fixed', `cost_value` decimal(10,2) DEFAULT NULL, `currency` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `frequency` enum('monthly','quarterly','annually','one_time') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'monthly', `is_active` tinyint(1) NOT NULL DEFAULT 1, `effective_date` date DEFAULT NULL, `expiry_date` date DEFAULT NULL, `eligibility_criteria` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `hr_benefits_type_is_active_index` (`type`,`is_active`), KEY `hr_benefits_category_cost_type_index` (`category`,`cost_type`), KEY `hr_benefits_effective_date_expiry_date_index` (`effective_date`,`expiry_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_branches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_branches` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `country` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `postal_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `manager_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `manager_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `manager_phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `opening_time` time DEFAULT NULL, `closing_time` time DEFAULT NULL, `working_days` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`working_days`)), `latitude` decimal(10,8) DEFAULT NULL, `longitude` decimal(11,8) DEFAULT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `is_head_office` tinyint(1) NOT NULL DEFAULT 0, `sort_order` int(11) NOT NULL DEFAULT 0, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_branches_code_unique` (`code`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_compensations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_compensations` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint(20) unsigned NOT NULL, `type` enum('salary','bonus','commission','allowance','overtime','incentive','stock_option','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'salary', `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `amount` decimal(12,2) NOT NULL, `currency` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `frequency` enum('hourly','daily','weekly','monthly','quarterly','annually','one_time') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'monthly', `effective_date` date NOT NULL, `end_date` date DEFAULT NULL, `status` enum('active','inactive','pending','approved','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active', `approval_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `approved_by` bigint(20) unsigned DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `is_taxable` tinyint(1) NOT NULL DEFAULT 1, `tax_rate` decimal(5,2) DEFAULT NULL, `benefits` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`benefits`)), `conditions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`conditions`)), `document_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `hr_compensations_approved_by_foreign` (`approved_by`), KEY `hr_compensations_employee_id_status_index` (`employee_id`,`status`), KEY `hr_compensations_type_status_index` (`type`,`status`), KEY `hr_compensations_effective_date_end_date_index` (`effective_date`,`end_date`), KEY `hr_compensations_status_approved_by_index` (`status`,`approved_by`), KEY `hr_compensations_amount_index` (`amount`), CONSTRAINT `hr_compensations_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_compensations_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_contracts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_contracts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `contract_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `type` enum('employment','consultancy','service','partnership','nda','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'employment', `status` enum('draft','active','expired','terminated','renewed','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft', `priority` enum('low','medium','high','critical') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'medium', `employee_id` bigint(20) unsigned DEFAULT NULL, `department_id` bigint(20) unsigned DEFAULT NULL, `position_id` bigint(20) unsigned DEFAULT NULL, `external_party_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `external_party_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `external_party_contact` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `external_party_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `external_party_phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `start_date` date NOT NULL, `end_date` date DEFAULT NULL, `probation_end_date` date DEFAULT NULL, `notice_period_days` int(11) NOT NULL DEFAULT 30, `is_renewable` tinyint(1) NOT NULL DEFAULT 0, `renewal_term_months` int(11) DEFAULT NULL, `max_renewals` int(11) DEFAULT NULL, `base_salary` decimal(12,2) DEFAULT NULL, `salary_currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `salary_frequency` enum('hourly','daily','weekly','monthly','annually') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'monthly', `bonus_amount` decimal(12,2) DEFAULT NULL, `bonus_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `allowance_amount` decimal(12,2) DEFAULT NULL, `allowance_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `working_hours_per_week` int(11) DEFAULT NULL, `work_location` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `remote_work_allowed` tinyint(1) NOT NULL DEFAULT 0, `remote_work_days_per_week` int(11) DEFAULT NULL, `work_schedule` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `job_responsibilities` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `performance_expectations` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `benefits_included` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`benefits_included`)), `additional_perks` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `health_insurance` tinyint(1) NOT NULL DEFAULT 0, `dental_insurance` tinyint(1) NOT NULL DEFAULT 0, `vision_insurance` tinyint(1) NOT NULL DEFAULT 0, `life_insurance` tinyint(1) NOT NULL DEFAULT 0, `retirement_plan` tinyint(1) NOT NULL DEFAULT 0, `paid_time_off` tinyint(1) NOT NULL DEFAULT 0, `pto_days_per_year` int(11) DEFAULT NULL, `legal_requirements` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `compliance_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `governing_law` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `jurisdiction` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `confidentiality_terms` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `non_compete_terms` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `intellectual_property_terms` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_by` bigint(20) unsigned NOT NULL, `approved_by` bigint(20) unsigned DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `manager_id` bigint(20) unsigned DEFAULT NULL, `approval_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `rejection_reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `renewal_date` date DEFAULT NULL, `termination_date` date DEFAULT NULL, `termination_reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `termination_type` enum('voluntary','involuntary','mutual','expiration') COLLATE utf8mb4_unicode_ci DEFAULT NULL, `termination_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `severance_pay` decimal(12,2) DEFAULT NULL, `contract_file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `contract_file_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `contract_file_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `contract_file_size` int(11) DEFAULT NULL, `attachments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attachments`)), `change_history` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `internal_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `requires_review` tinyint(1) NOT NULL DEFAULT 0, `next_review_date` date DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_contracts_contract_number_unique` (`contract_number`), KEY `hr_contracts_contract_number_index` (`contract_number`), KEY `hr_contracts_type_status_index` (`type`,`status`), KEY `hr_contracts_employee_id_index` (`employee_id`), KEY `hr_contracts_department_id_index` (`department_id`), KEY `hr_contracts_start_date_end_date_index` (`start_date`,`end_date`), KEY `hr_contracts_status_end_date_index` (`status`,`end_date`), KEY `hr_contracts_created_by_index` (`created_by`), KEY `hr_contracts_approved_by_index` (`approved_by`), KEY `hr_contracts_manager_id_index` (`manager_id`), KEY `hr_contracts_position_id_foreign` (`position_id`), CONSTRAINT `hr_contracts_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_contracts_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_contracts_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `hr_departments` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_contracts_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_contracts_manager_id_foreign` FOREIGN KEY (`manager_id`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_contracts_position_id_foreign` FOREIGN KEY (`position_id`) REFERENCES `hr_positions` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_departments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_departments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `location` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `contact_person` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `contact_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `contact_phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `manager_id` bigint(20) unsigned DEFAULT NULL, `parent_department_id` bigint(20) unsigned DEFAULT NULL, `status` enum('active','inactive','suspended') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active', `established_date` date DEFAULT NULL, `budget` int(11) DEFAULT NULL, `headcount` int(11) NOT NULL DEFAULT 0, `founded_date` date DEFAULT NULL, `mission` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `vision` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `contact_info` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`contact_info`)), `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `color_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_departments_code_unique` (`code`), KEY `hr_departments_status_manager_id_index` (`status`,`manager_id`), KEY `hr_departments_parent_department_id_index` (`parent_department_id`), KEY `hr_departments_code_index` (`code`), KEY `hr_departments_status_name_index` (`status`,`name`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_documents`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_documents` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `file_path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `file_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `file_size` int(11) DEFAULT NULL, `category` enum('contract','policy','procedure','form','template','report','certificate','id_card','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'other', `status` enum('draft','active','archived','expired') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft', `access_level` enum('public','hr_only','managers_only','employees_only','restricted') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'hr_only', `uploaded_by` bigint(20) unsigned NOT NULL, `documentable_id` bigint(20) unsigned DEFAULT NULL, `documentable_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `effective_date` date DEFAULT NULL, `expiry_date` date DEFAULT NULL, `version` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `tags` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `requires_acknowledgment` tinyint(1) NOT NULL DEFAULT 0, `is_confidential` tinyint(1) NOT NULL DEFAULT 0, `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `hr_documents_category_status_index` (`category`,`status`), KEY `hr_documents_access_level_status_index` (`access_level`,`status`), KEY `hr_documents_effective_date_expiry_date_index` (`effective_date`,`expiry_date`), KEY `hr_documents_documentable_type_documentable_id_index` (`documentable_type`,`documentable_id`), KEY `hr_documents_uploaded_by_index` (`uploaded_by`), CONSTRAINT `hr_documents_uploaded_by_foreign` FOREIGN KEY (`uploaded_by`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_employees`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_employees` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `employee_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `last_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `middle_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `emergency_contact` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `emergency_phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `date_of_birth` date DEFAULT NULL, `gender` enum('male','female','other','prefer_not_to_say') COLLATE utf8mb4_unicode_ci DEFAULT NULL, `nationality` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `passport_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `passport_expiry` date DEFAULT NULL, `address` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `city` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `country` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `postal_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `department_id` bigint(20) unsigned NOT NULL, `branch_id` bigint(20) unsigned DEFAULT NULL, `position_id` bigint(20) unsigned NOT NULL, `manager_id` bigint(20) unsigned DEFAULT NULL, `hr_role` enum('hr_team','manager','employee') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'employee', `employment_status` enum('active','inactive','terminated','resigned','retired','suspended') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active', `employment_type` enum('full_time','part_time','contract','internship','freelance') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'full_time', `hire_date` date NOT NULL, `probation_end_date` date DEFAULT NULL, `contract_end_date` date DEFAULT NULL, `termination_date` date DEFAULT NULL, `termination_reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `base_salary` decimal(10,2) NOT NULL, `currency` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `hourly_rate` decimal(8,2) DEFAULT NULL, `working_hours_per_week` int(11) NOT NULL DEFAULT 40, `pay_frequency` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'monthly', `bank_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `bank_account_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `bank_routing_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `tax_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `social_security_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `marital_status` enum('single','married','divorced','widowed') COLLATE utf8mb4_unicode_ci DEFAULT NULL, `spouse_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `dependents_count` int(11) NOT NULL DEFAULT 0, `skills` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `certifications` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `languages` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_employees_user_id_unique` (`user_id`), UNIQUE KEY `hr_employees_employee_id_unique` (`employee_id`), UNIQUE KEY `hr_employees_email_unique` (`email`), KEY `hr_employees_department_id_foreign` (`department_id`), KEY `hr_employees_position_id_foreign` (`position_id`), KEY `hr_employees_employment_status_department_id_index` (`employment_status`,`department_id`), KEY `hr_employees_hr_role_employment_status_index` (`hr_role`,`employment_status`), KEY `hr_employees_manager_id_employment_status_index` (`manager_id`,`employment_status`), KEY `hr_employees_employee_id_index` (`employee_id`), KEY `hr_employees_hire_date_index` (`hire_date`), KEY `hr_employees_branch_id_foreign` (`branch_id`), CONSTRAINT `hr_employees_branch_id_foreign` FOREIGN KEY (`branch_id`) REFERENCES `hr_branches` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_employees_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `hr_departments` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_employees_manager_id_foreign` FOREIGN KEY (`manager_id`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_employees_position_id_foreign` FOREIGN KEY (`position_id`) REFERENCES `hr_positions` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_employees_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=130 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_excuses`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_excuses` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint(20) unsigned NOT NULL, `date` date NOT NULL, `start_time` time DEFAULT NULL, `end_time` time DEFAULT NULL, `duration_hours` int(11) NOT NULL, `type` enum('late_arrival','early_departure','absence','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'other', `reason` text COLLATE utf8mb4_unicode_ci NOT NULL, `manager_id` bigint(20) unsigned DEFAULT NULL, `hr_approver_id` bigint(20) unsigned DEFAULT NULL, `manager_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `hr_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` enum('pending','approved','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `approved_by` bigint(20) unsigned DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `rejected_by` bigint(20) unsigned DEFAULT NULL, `rejected_at` timestamp NULL DEFAULT NULL, `rejection_reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `attachment_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_emergency` tinyint(1) NOT NULL DEFAULT 0, `priority` enum('low','normal','high') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal', `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `hr_excuses_rejected_by_foreign` (`rejected_by`), KEY `hr_excuses_employee_id_date_index` (`employee_id`,`date`), KEY `hr_excuses_status_type_index` (`status`,`type`), KEY `hr_excuses_date_status_index` (`date`,`status`), KEY `hr_excuses_approved_by_index` (`approved_by`), KEY `hr_excuses_manager_id_status_index` (`manager_id`,`status`), KEY `hr_excuses_hr_approver_id_foreign` (`hr_approver_id`), CONSTRAINT `hr_excuses_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_excuses_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_excuses_hr_approver_id_foreign` FOREIGN KEY (`hr_approver_id`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_excuses_manager_id_foreign` FOREIGN KEY (`manager_id`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_excuses_rejected_by_foreign` FOREIGN KEY (`rejected_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_leave_balances`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_leave_balances` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint(20) unsigned NOT NULL, `year` int(11) NOT NULL, `leave_type` enum('vacation','sick_leave','personal_leave','maternity_leave','paternity_leave','bereavement_leave','jury_duty','military_leave','unpaid_leave') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'vacation', `total_days` int(11) NOT NULL, `used_days` int(11) NOT NULL DEFAULT 0, `remaining_days` int(11) NOT NULL, `carried_over_days` int(11) NOT NULL DEFAULT 0, `reset_date` date NOT NULL, `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_leave_balances_employee_id_year_leave_type_unique` (`employee_id`,`year`,`leave_type`), KEY `hr_leave_balances_employee_id_leave_type_index` (`employee_id`,`leave_type`), KEY `hr_leave_balances_year_leave_type_index` (`year`,`leave_type`), KEY `hr_leave_balances_reset_date_index` (`reset_date`), CONSTRAINT `hr_leave_balances_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_leaves`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_leaves` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint(20) unsigned NOT NULL, `leave_type` enum('vacation','sick_leave','personal_leave','maternity_leave','paternity_leave','bereavement_leave','jury_duty','military_leave','unpaid_leave','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'vacation', `start_date` date NOT NULL, `end_date` date NOT NULL, `total_days` decimal(5,2) NOT NULL, `status` enum('pending','approved','rejected','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `reason` text COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `emergency_contact` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `destination` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `manager_id` bigint(20) unsigned DEFAULT NULL, `hr_approver_id` bigint(20) unsigned DEFAULT NULL, `approval_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `rejection_reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `approved_by` bigint(20) unsigned DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `rejected_by` bigint(20) unsigned DEFAULT NULL, `rejected_at` timestamp NULL DEFAULT NULL, `cancelled_at` timestamp NULL DEFAULT NULL, `cancellation_reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_emergency` tinyint(1) NOT NULL DEFAULT 0, `is_compensatory` tinyint(1) NOT NULL DEFAULT 0, `compensatory_date` date DEFAULT NULL, `half_day` tinyint(1) NOT NULL DEFAULT 0, `half_day_type` enum('morning','afternoon') COLLATE utf8mb4_unicode_ci DEFAULT NULL, `attachments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attachments`)), `priority` enum('low','normal','high','urgent') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal', `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_by` bigint(20) unsigned NOT NULL, `updated_by` bigint(20) unsigned DEFAULT NULL, `deleted_by` bigint(20) unsigned DEFAULT NULL, `restored_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `hr_leaves_hr_approver_id_foreign` (`hr_approver_id`), KEY `hr_leaves_approved_by_foreign` (`approved_by`), KEY `hr_leaves_rejected_by_foreign` (`rejected_by`), KEY `hr_leaves_updated_by_foreign` (`updated_by`), KEY `hr_leaves_deleted_by_foreign` (`deleted_by`), KEY `hr_leaves_restored_by_foreign` (`restored_by`), KEY `hr_leaves_employee_id_status_index` (`employee_id`,`status`), KEY `hr_leaves_leave_type_status_index` (`leave_type`,`status`), KEY `hr_leaves_start_date_end_date_index` (`start_date`,`end_date`), KEY `hr_leaves_status_approved_by_index` (`status`,`approved_by`), KEY `hr_leaves_total_days_index` (`total_days`), KEY `hr_leaves_manager_id_status_index` (`manager_id`,`status`), KEY `hr_leaves_created_by_created_at_index` (`created_by`,`created_at`), CONSTRAINT `hr_leaves_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_leaves_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_leaves_deleted_by_foreign` FOREIGN KEY (`deleted_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_leaves_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_leaves_hr_approver_id_foreign` FOREIGN KEY (`hr_approver_id`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_leaves_manager_id_foreign` FOREIGN KEY (`manager_id`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_leaves_rejected_by_foreign` FOREIGN KEY (`rejected_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_leaves_restored_by_foreign` FOREIGN KEY (`restored_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_leaves_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_onboardings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_onboardings` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint(20) unsigned NOT NULL, `recruitment_id` bigint(20) unsigned DEFAULT NULL, `status` enum('planned','in_progress','completed','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'planned', `start_date` date NOT NULL, `completion_date` date DEFAULT NULL, `target_completion_date` date NOT NULL, `onboarding_manager_id` bigint(20) unsigned NOT NULL, `buddy_id` bigint(20) unsigned DEFAULT NULL, `welcome_message` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `checklist_items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`checklist_items`)), `completed_items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`completed_items`)), `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `feedback` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `satisfaction_score` decimal(3,1) DEFAULT NULL, `is_remote_onboarding` tinyint(1) NOT NULL DEFAULT 0, `equipment_provided` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `access_granted` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `hr_onboardings_recruitment_id_foreign` (`recruitment_id`), KEY `hr_onboardings_buddy_id_foreign` (`buddy_id`), KEY `hr_onboardings_employee_id_status_index` (`employee_id`,`status`), KEY `hr_onboardings_status_start_date_index` (`status`,`start_date`), KEY `hr_onboardings_onboarding_manager_id_index` (`onboarding_manager_id`), KEY `hr_onboardings_target_completion_date_index` (`target_completion_date`), CONSTRAINT `hr_onboardings_buddy_id_foreign` FOREIGN KEY (`buddy_id`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_onboardings_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_onboardings_onboarding_manager_id_foreign` FOREIGN KEY (`onboarding_manager_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_onboardings_recruitment_id_foreign` FOREIGN KEY (`recruitment_id`) REFERENCES `hr_recruitments` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_payrolls`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_payrolls` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `payroll_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `employee_id` bigint(20) unsigned NOT NULL, `month_year` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `pay_date` date NOT NULL, `basic_salary` decimal(12,2) NOT NULL, `gross_salary` decimal(12,2) NOT NULL, `net_salary` decimal(12,2) NOT NULL, `currency` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `allowances` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`allowances`)), `deductions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`deductions`)), `overtime` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`overtime`)), `bonuses` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`bonuses`)), `tax_amount` decimal(10,2) NOT NULL DEFAULT 0.00, `insurance_amount` decimal(10,2) NOT NULL DEFAULT 0.00, `status` enum('draft','processed','paid','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft', `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `processed_by` bigint(20) unsigned DEFAULT NULL, `processed_at` timestamp NULL DEFAULT NULL, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_payrolls_employee_id_month_year_unique` (`employee_id`,`month_year`), UNIQUE KEY `hr_payrolls_payroll_number_unique` (`payroll_number`), KEY `hr_payrolls_processed_by_foreign` (`processed_by`), KEY `hr_payrolls_month_year_status_index` (`month_year`,`status`), KEY `hr_payrolls_pay_date_status_index` (`pay_date`,`status`), KEY `hr_payrolls_payroll_number_index` (`payroll_number`), CONSTRAINT `hr_payrolls_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_payrolls_processed_by_foreign` FOREIGN KEY (`processed_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_performances`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_performances` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint(20) unsigned NOT NULL, `reviewer_id` bigint(20) unsigned NOT NULL, `review_type` enum('self_assessment','peer_review','manager_review','hr_review','360_degree') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'manager_review', `review_period` enum('monthly','quarterly','semi_annual','annual') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'annual', `review_date` date NOT NULL, `next_review_date` date NOT NULL, `overall_rating` enum('excellent','very_good','good','satisfactory','needs_improvement','unsatisfactory') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'good', `overall_score` decimal(3,1) DEFAULT NULL, `strengths` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `areas_for_improvement` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `goals` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `achievements` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `challenges` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `support_needed` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `career_aspirations` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `reviewer_comments` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `employee_comments` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `hr_comments` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` enum('draft','submitted','in_review','completed','archived') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft', `is_acknowledged` tinyint(1) NOT NULL DEFAULT 0, `acknowledged_at` timestamp NULL DEFAULT NULL, `kpi_scores` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`kpi_scores`)), `competency_scores` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`competency_scores`)), `document_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `hr_performances_employee_id_review_period_index` (`employee_id`,`review_period`), KEY `hr_performances_reviewer_id_status_index` (`reviewer_id`,`status`), KEY `hr_performances_review_date_next_review_date_index` (`review_date`,`next_review_date`), KEY `hr_performances_overall_rating_status_index` (`overall_rating`,`status`), KEY `hr_performances_overall_score_index` (`overall_score`), CONSTRAINT `hr_performances_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_performances_reviewer_id_foreign` FOREIGN KEY (`reviewer_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_positions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_positions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `department_id` bigint(20) unsigned NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `requirements` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `responsibilities` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `level` enum('entry','junior','mid','senior','lead','manager','director','executive') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'mid', `type` enum('full_time','part_time','contract','internship','freelance') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'full_time', `status` enum('active','inactive','filled','closed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active', `min_salary` int(11) DEFAULT NULL, `max_salary` int(11) DEFAULT NULL, `currency` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `headcount` int(11) NOT NULL DEFAULT 1, `filled_count` int(11) NOT NULL DEFAULT 0, `open_date` date DEFAULT NULL, `close_date` date DEFAULT NULL, `benefits` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`benefits`)), `skills_required` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`skills_required`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_positions_code_unique` (`code`), KEY `hr_positions_department_id_foreign` (`department_id`), KEY `hr_positions_status_department_id_index` (`status`,`department_id`), KEY `hr_positions_level_type_index` (`level`,`type`), KEY `hr_positions_code_index` (`code`), CONSTRAINT `hr_positions_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `hr_departments` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_recruitments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_recruitments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `position_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `department_id` bigint(20) unsigned NOT NULL, `position_id` bigint(20) unsigned NOT NULL, `job_description` text COLLATE utf8mb4_unicode_ci NOT NULL, `requirements` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `responsibilities` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` enum('draft','open','in_progress','closed','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'draft', `priority` enum('low','normal','high','urgent') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'normal', `headcount` int(11) NOT NULL, `filled_count` int(11) NOT NULL DEFAULT 0, `open_date` date NOT NULL, `close_date` date DEFAULT NULL, `target_start_date` date DEFAULT NULL, `min_salary` decimal(10,2) DEFAULT NULL, `max_salary` decimal(10,2) DEFAULT NULL, `currency` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `employment_type` enum('full_time','part_time','contract','internship','freelance') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'full_time', `location` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_remote` tinyint(1) NOT NULL DEFAULT 0, `benefits` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `hiring_manager_id` bigint(20) unsigned NOT NULL, `recruiter_id` bigint(20) unsigned DEFAULT NULL, `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `hr_recruitments_department_id_foreign` (`department_id`), KEY `hr_recruitments_position_id_foreign` (`position_id`), KEY `hr_recruitments_recruiter_id_foreign` (`recruiter_id`), KEY `hr_recruitments_status_department_id_index` (`status`,`department_id`), KEY `hr_recruitments_priority_status_index` (`priority`,`status`), KEY `hr_recruitments_open_date_close_date_index` (`open_date`,`close_date`), KEY `hr_recruitments_hiring_manager_id_index` (`hiring_manager_id`), CONSTRAINT `hr_recruitments_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `hr_departments` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_recruitments_hiring_manager_id_foreign` FOREIGN KEY (`hiring_manager_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_recruitments_position_id_foreign` FOREIGN KEY (`position_id`) REFERENCES `hr_positions` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_recruitments_recruiter_id_foreign` FOREIGN KEY (`recruiter_id`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_salary_confirmations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_salary_confirmations` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint(20) unsigned NOT NULL, `month_year` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `salary_amount` decimal(12,2) NOT NULL, `currency` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `status` enum('pending','confirmed','disputed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `confirmed_at` timestamp NULL DEFAULT NULL, `disputed_at` timestamp NULL DEFAULT NULL, `dispute_reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `bank_transaction_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `payment_date` date DEFAULT NULL, `payment_method` enum('bank_transfer','check','cash','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'bank_transfer', `salary_breakdown` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`salary_breakdown`)), `deductions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`deductions`)), `bonuses` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`bonuses`)), `is_processed` tinyint(1) NOT NULL DEFAULT 0, `processed_at` timestamp NULL DEFAULT NULL, `processed_by` bigint(20) unsigned DEFAULT NULL, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_salary_confirmations_employee_id_month_year_unique` (`employee_id`,`month_year`), KEY `hr_salary_confirmations_processed_by_foreign` (`processed_by`), KEY `hr_salary_confirmations_month_year_status_index` (`month_year`,`status`), KEY `hr_salary_confirmations_employee_id_status_index` (`employee_id`,`status`), KEY `hr_salary_confirmations_payment_date_index` (`payment_date`), CONSTRAINT `hr_salary_confirmations_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_salary_confirmations_processed_by_foreign` FOREIGN KEY (`processed_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_training_enrollments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_training_enrollments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `employee_id` bigint(20) unsigned NOT NULL, `training_id` bigint(20) unsigned NOT NULL, `status` enum('enrolled','in_progress','completed','dropped','failed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'enrolled', `enrollment_date` date NOT NULL, `completion_date` date DEFAULT NULL, `score` decimal(5,2) DEFAULT NULL, `grade` enum('A','B','C','D','F','Pass','Fail') COLLATE utf8mb4_unicode_ci DEFAULT NULL, `feedback` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_certified` tinyint(1) NOT NULL DEFAULT 0, `certificate_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `certificate_expiry` date DEFAULT NULL, `enrolled_by` bigint(20) unsigned DEFAULT NULL, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `hr_training_enrollments_employee_id_training_id_unique` (`employee_id`,`training_id`), KEY `hr_training_enrollments_enrolled_by_foreign` (`enrolled_by`), KEY `hr_training_enrollments_employee_id_status_index` (`employee_id`,`status`), KEY `hr_training_enrollments_training_id_status_index` (`training_id`,`status`), KEY `hr_training_enrollments_status_completion_date_index` (`status`,`completion_date`), CONSTRAINT `hr_training_enrollments_employee_id_foreign` FOREIGN KEY (`employee_id`) REFERENCES `hr_employees` (`id`) ON DELETE CASCADE, CONSTRAINT `hr_training_enrollments_enrolled_by_foreign` FOREIGN KEY (`enrolled_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL, CONSTRAINT `hr_training_enrollments_training_id_foreign` FOREIGN KEY (`training_id`) REFERENCES `hr_trainings` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_trainings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_trainings` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `type` enum('internal','external','online','workshop','seminar','conference','certification','degree_program','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'internal', `category` enum('technical','soft_skills','leadership','compliance','safety','product_knowledge','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'technical', `level` enum('beginner','intermediate','advanced','expert') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'intermediate', `status` enum('planned','in_progress','completed','cancelled','postponed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'planned', `start_date` date NOT NULL, `end_date` date NOT NULL, `start_time` time DEFAULT NULL, `end_time` time DEFAULT NULL, `duration_hours` int(11) NOT NULL, `location` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `instructor` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `provider` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `cost` decimal(10,2) DEFAULT NULL, `currency` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `max_participants` int(11) DEFAULT NULL, `current_participants` int(11) NOT NULL DEFAULT 0, `prerequisites` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `objectives` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `materials` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `certificate_template` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `provides_certificate` tinyint(1) NOT NULL DEFAULT 0, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `hr_trainings_type_status_index` (`type`,`status`), KEY `hr_trainings_category_level_index` (`category`,`level`), KEY `hr_trainings_start_date_end_date_index` (`start_date`,`end_date`), KEY `hr_trainings_status_current_participants_index` (`status`,`current_participants`), KEY `hr_trainings_duration_hours_index` (`duration_hours`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `hr_work_schedules`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `hr_work_schedules` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `type` enum('fixed','flexible','rotating','shift') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'fixed', `start_time` time NOT NULL, `end_time` time NOT NULL, `total_hours` int(11) NOT NULL, `break_times` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`break_times`)), `working_days` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`working_days`)), `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_by` bigint(20) unsigned DEFAULT NULL, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `hr_work_schedules_created_by_foreign` (`created_by`), KEY `hr_work_schedules_type_is_active_index` (`type`,`is_active`), KEY `hr_work_schedules_total_hours_index` (`total_hours`), CONSTRAINT `hr_work_schedules_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `hr_employees` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `legal_tickets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `legal_tickets` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_associates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_associates` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `associate_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `li_associates_associate_name_unique` (`associate_name`) USING HASH, KEY `li_associates_country_id_index` (`country_id`), KEY `li_associates_added_by_index` (`added_by`) ) ENGINE=MyISAM AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_case_audit_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_case_audit_logs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `case_id` bigint(20) unsigned NOT NULL, `user_id` bigint(20) unsigned NOT NULL, `action` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `field_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `old_value` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `new_value` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `changes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`changes`)), `ip_address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_case_audit_logs_case_id_created_at_index` (`case_id`,`created_at`), KEY `li_case_audit_logs_action_created_at_index` (`action`,`created_at`), KEY `li_case_audit_logs_user_id_index` (`user_id`), KEY `li_case_audit_logs_case_id_index` (`case_id`), CONSTRAINT `li_case_audit_logs_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `li_cases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_case_audit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_case_note`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_case_note` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `case_id` bigint(20) unsigned DEFAULT NULL, `attorney_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `note` text COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_case_note_case_id_index` (`case_id`), KEY `li_case_note_attorney_id_index` (`attorney_id`), KEY `li_case_note_added_by_index` (`added_by`), CONSTRAINT `li_case_note_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_case_note_attorney_id_foreign` FOREIGN KEY (`attorney_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_case_note_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `li_cases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_cases`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_cases` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `aiptref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `department` int(11) DEFAULT NULL, `case_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `client_id` bigint(20) unsigned DEFAULT NULL, `attorney_id` bigint(20) unsigned DEFAULT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `methode_id` bigint(20) unsigned DEFAULT NULL, `case_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` enum('Open','Closed','Pending','Appeal') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Open', `filing_date` date DEFAULT NULL, `closing_date` date DEFAULT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `next_procedure_date` datetime NOT NULL, `procedure_id` bigint(20) unsigned DEFAULT NULL, `img_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_cases_client_id_index` (`client_id`), KEY `li_cases_attorney_id_index` (`attorney_id`), KEY `li_cases_country_id_index` (`country_id`), KEY `li_cases_methode_id_index` (`methode_id`), KEY `li_cases_added_by_index` (`added_by`), KEY `li_cases_procedure_id_index` (`procedure_id`), CONSTRAINT `li_cases_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_cases_attorney_id_foreign` FOREIGN KEY (`attorney_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_cases_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `li_clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_cases_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `li_countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_cases_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `li_methodes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_cases_procedure_id_foreign` FOREIGN KEY (`procedure_id`) REFERENCES `li_procedures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_cases_ids`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_cases_ids` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `case_id` bigint(20) unsigned DEFAULT NULL, `id_tkt` bigint(20) DEFAULT NULL, `type` bigint(20) DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_cases_ids_case_id_index` (`case_id`), KEY `li_cases_ids_added_by_index` (`added_by`), CONSTRAINT `li_cases_ids_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_cases_ids_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `li_cases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_client_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_client_types` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type_client_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_client_types_added_by_index` (`added_by`), CONSTRAINT `li_client_types_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_clients`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_clients` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `client_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `type_clients_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `li_clients_email_unique` (`email`), KEY `li_clients_country_id_index` (`country_id`), KEY `li_clients_type_clients_id_index` (`type_clients_id`), KEY `li_clients_added_by_index` (`added_by`), CONSTRAINT `li_clients_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_clients_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `li_countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_clients_type_clients_id_foreign` FOREIGN KEY (`type_clients_id`) REFERENCES `li_client_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=151 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_countries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_countries` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=252 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_court_hearings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_court_hearings` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `case_id` bigint(20) unsigned DEFAULT NULL, `hearing_date` datetime NOT NULL, `court_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `judge_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` enum('Scheduled','Completed','Rescheduled','Cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Scheduled', `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_court_hearings_case_id_index` (`case_id`), KEY `li_court_hearings_added_by_index` (`added_by`), CONSTRAINT `li_court_hearings_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_court_hearings_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `li_cases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_documents`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_documents` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `attorney_id` bigint(20) unsigned DEFAULT NULL, `case_id` bigint(20) unsigned DEFAULT NULL, `document_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `document_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `file_path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_documents_attorney_id_index` (`attorney_id`), KEY `li_documents_case_id_index` (`case_id`), KEY `li_documents_added_by_index` (`added_by`), CONSTRAINT `li_documents_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_documents_attorney_id_foreign` FOREIGN KEY (`attorney_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_documents_case_id_foreign` FOREIGN KEY (`case_id`) REFERENCES `li_cases` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=396 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_methodes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_methodes` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `_methode_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `color` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_methodes_added_by_index` (`added_by`), CONSTRAINT `li_methodes_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_procedures`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_procedures` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `procedure_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_procedures_added_by_index` (`added_by`), CONSTRAINT `li_procedures_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_requests`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_requests` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `answred` tinyint(1) NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `phone` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `message` text COLLATE utf8mb4_unicode_ci NOT NULL, `response` text COLLATE utf8mb4_unicode_ci NOT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `handeled_by` bigint(20) unsigned DEFAULT NULL, `related_TKT` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_requests_related_tkt_foreign` (`related_TKT`), KEY `li_requests_added_by_index` (`added_by`), KEY `li_requests_handeled_by_index` (`handeled_by`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `li_requirments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `li_requirments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `Requirements` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `procedure_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `li_requirments_country_id_index` (`country_id`), KEY `li_requirments_procedure_id_index` (`procedure_id`), KEY `li_requirments_added_by_index` (`added_by`), CONSTRAINT `li_requirments_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `li_requirments_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `li_countries` (`id`) ON UPDATE CASCADE, CONSTRAINT `li_requirments_procedure_id_foreign` FOREIGN KEY (`procedure_id`) REFERENCES `li_procedures` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=108 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `meetings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `meetings` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `from` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `to` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `discription` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `invited` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`invited`)), `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `meetings_added_by_index` (`added_by`), CONSTRAINT `meetings_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `messages`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `messages` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `title` bigint(20) NOT NULL, `message` text COLLATE utf8mb4_unicode_ci NOT NULL, `date` date NOT NULL, `department_id` bigint(20) unsigned DEFAULT NULL, `status` tinyint(1) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `messages_department_id_index` (`department_id`), CONSTRAINT `messages_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=22815 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `methodes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `methodes` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `_methode_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `color` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `department_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `methodes_department_id_index` (`department_id`), KEY `methodes_added_by_index` (`added_by`), CONSTRAINT `methodes_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `methodes_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2670 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `migrations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `migrations` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `batch` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=147 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `model_has_permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `model_has_permissions` ( `permission_id` bigint(20) unsigned NOT NULL, `model_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `model_id` bigint(20) unsigned NOT NULL, PRIMARY KEY (`permission_id`,`model_id`,`model_type`), KEY `model_has_permissions_model_id_model_type_index` (`model_id`,`model_type`), CONSTRAINT `model_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `model_has_roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `model_has_roles` ( `role_id` bigint(20) unsigned NOT NULL, `model_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `model_id` bigint(20) unsigned NOT NULL, PRIMARY KEY (`role_id`,`model_id`,`model_type`), KEY `model_has_roles_model_id_model_type_index` (`model_id`,`model_type`), CONSTRAINT `model_has_roles_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `notifications`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `notifications` ( `id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `notifiable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `notifiable_id` bigint(20) unsigned NOT NULL, `data` text COLLATE utf8mb4_unicode_ci NOT NULL, `read_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `notifications_notifiable_type_notifiable_id_index` (`notifiable_type`,`notifiable_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `other_tickets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `other_tickets` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `discription` text COLLATE utf8mb4_unicode_ci NOT NULL, `resone` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `solved_by` bigint(20) unsigned DEFAULT NULL, `assigned_to` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `related_TKT_trademark` bigint(20) unsigned DEFAULT NULL, `related_TKT_patent` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `other_tickets_solved_by_index` (`solved_by`), KEY `other_tickets_assigned_to_index` (`assigned_to`), KEY `other_tickets_added_by_index` (`added_by`), KEY `other_tickets_related_tkt_trademark_index` (`related_TKT_trademark`), KEY `other_tickets_related_tkt_patent_index` (`related_TKT_patent`), CONSTRAINT `other_tickets_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `other_tickets_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `other_tickets_related_tkt_patent_foreign` FOREIGN KEY (`related_TKT_patent`) REFERENCES `patent_tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `other_tickets_related_tkt_trademark_foreign` FOREIGN KEY (`related_TKT_trademark`) REFERENCES `patent_tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `other_tickets_solved_by_foreign` FOREIGN KEY (`solved_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `password_resets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `password_resets` ( `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, KEY `password_resets_email_index` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `patent_ticket_details`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `patent_ticket_details` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `patent_tickets_id` bigint(20) unsigned DEFAULT NULL, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `detail` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `related_TKT` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `patent_ticket_details_patent_tickets_id_index` (`patent_tickets_id`), KEY `patent_ticket_details_added_by_index` (`added_by`), KEY `patent_ticket_details_related_tkt_index` (`related_TKT`), CONSTRAINT `patent_ticket_details_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `patent_ticket_details_patent_tickets_id_foreign` FOREIGN KEY (`patent_tickets_id`) REFERENCES `patent_tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `patent_ticket_details_related_tkt_foreign` FOREIGN KEY (`related_TKT`) REFERENCES `patent_tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `patent_ticket_histories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `patent_ticket_histories` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `comment` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `related_TKT` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `patent_ticket_histories_added_by_index` (`added_by`), KEY `patent_ticket_histories_related_tkt_index` (`related_TKT`), CONSTRAINT `patent_ticket_histories_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `patent_ticket_histories_related_tkt_foreign` FOREIGN KEY (`related_TKT`) REFERENCES `patent_tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `patent_tickets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `patent_tickets` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `client_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, `methode_id` bigint(20) unsigned DEFAULT NULL, `assigned_to` bigint(20) unsigned DEFAULT NULL, `class_id` bigint(20) unsigned DEFAULT NULL, `procedure_id` bigint(20) unsigned DEFAULT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `aiptref` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `clientref` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `pct_date` date DEFAULT NULL, `pct_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `regular_date` date DEFAULT NULL, `regular_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `filingno` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `filingdate` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `requesteddate` date DEFAULT NULL, `annuity` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `annual_office_fee` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `annual_deadline` date DEFAULT NULL, `deadline_Status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `deadline_date` date NOT NULL, `related_TKT` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `patent_tickets_client_id_index` (`client_id`), KEY `patent_tickets_added_by_index` (`added_by`), KEY `patent_tickets_associate_id_index` (`associate_id`), KEY `patent_tickets_methode_id_index` (`methode_id`), KEY `patent_tickets_assigned_to_index` (`assigned_to`), KEY `patent_tickets_class_id_index` (`class_id`), KEY `patent_tickets_procedure_id_index` (`procedure_id`), KEY `patent_tickets_country_id_index` (`country_id`), KEY `patent_tickets_related_tkt_index` (`related_TKT`), CONSTRAINT `patent_tickets_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `patent_tickets_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `patent_tickets_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `associates` (`id`) ON DELETE CASCADE, CONSTRAINT `patent_tickets_class_id_foreign` FOREIGN KEY (`class_id`) REFERENCES `trademark_classes` (`id`) ON DELETE CASCADE, CONSTRAINT `patent_tickets_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE, CONSTRAINT `patent_tickets_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE, CONSTRAINT `patent_tickets_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `methodes` (`id`) ON DELETE CASCADE, CONSTRAINT `patent_tickets_procedure_id_foreign` FOREIGN KEY (`procedure_id`) REFERENCES `procedures` (`id`) ON DELETE CASCADE, CONSTRAINT `patent_tickets_related_tkt_foreign` FOREIGN KEY (`related_TKT`) REFERENCES `patent_tickets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `permissions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `guard_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `permissions_name_guard_name_unique` (`name`,`guard_name`) ) ENGINE=InnoDB AUTO_INCREMENT=236 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `personal_access_tokens`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `personal_access_tokens` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `tokenable_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `tokenable_id` bigint(20) unsigned NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `token` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `abilities` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `last_used_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `personal_access_tokens_token_unique` (`token`), KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `procedures`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `procedures` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `procedure_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `methode_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `procedures_methode_id_index` (`methode_id`), KEY `procedures_added_by_index` (`added_by`), CONSTRAINT `procedures_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `procedures_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `methodes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=964 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `products_recognisers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `products_recognisers` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `country_id` bigint(20) unsigned DEFAULT NULL, `class` bigint(20) DEFAULT NULL, `en_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `ar_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `product_number` bigint(20) DEFAULT NULL, `product_number_local` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `products_recognisers_country_id_index` (`country_id`), FULLTEXT KEY `index_name` (`en_name`), FULLTEXT KEY `en_name` (`en_name`), FULLTEXT KEY `ar_name` (`ar_name`), FULLTEXT KEY `en_name_2` (`en_name`,`ar_name`), CONSTRAINT `products_recognisers_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=11814 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_annual_fee_deadlines`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_annual_fee_deadlines` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `patent_id` bigint(20) unsigned NOT NULL, `country_id` bigint(20) unsigned NOT NULL, `fee_year` int(11) NOT NULL COMMENT 'Year for which this fee is due', `deadline_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'annual' COMMENT 'Type of deadline (annual, maintenance, renewal, etc.)', `priority` enum('low','medium','high','urgent') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'medium', `is_urgent` tinyint(1) NOT NULL DEFAULT 0, `client_confirmation_status` enum('pending','confirmed','rejected','no_response') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `client_confirmation_date` timestamp NULL DEFAULT NULL, `client_confirmation_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `due_date` date NOT NULL COMMENT 'Date when fee is due', `fee_amount` decimal(10,2) NOT NULL COMMENT 'Original fee amount', `currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD' COMMENT 'Currency for the fee', `status` enum('pending','paid','overdue','waived') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `paid_date` date DEFAULT NULL COMMENT 'Date when fee was paid', `paid_amount` decimal(10,2) DEFAULT NULL COMMENT 'Amount actually paid', `late_fees` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Late fees charged', `total_amount_due` decimal(10,2) NOT NULL COMMENT 'Total amount including late fees', `invoice_id` bigint(20) unsigned DEFAULT NULL, `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Additional notes', `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `client_confirmed_by` bigint(20) unsigned DEFAULT NULL, `reminder_sent_count` int(11) NOT NULL DEFAULT 0, `last_reminder_sent` timestamp NULL DEFAULT NULL, `reminder_history` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'History of sent reminders' CHECK (json_valid(`reminder_history`)), `partial_payment_amount` decimal(10,2) NOT NULL DEFAULT 0.00, `payment_installments` int(11) NOT NULL DEFAULT 0, `payment_plan_active` tinyint(1) NOT NULL DEFAULT 0, `waiver_requested` tinyint(1) NOT NULL DEFAULT 0, `waiver_approved` tinyint(1) NOT NULL DEFAULT 0, `waiver_requested_at` timestamp NULL DEFAULT NULL, `waiver_approved_at` timestamp NULL DEFAULT NULL, `waiver_approved_by` bigint(20) unsigned DEFAULT NULL, `escalated` tinyint(1) NOT NULL DEFAULT 0, `escalated_at` timestamp NULL DEFAULT NULL, `escalated_by` bigint(20) unsigned DEFAULT NULL, `escalation_reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `compliance_checked` tinyint(1) NOT NULL DEFAULT 0, `compliance_checked_at` timestamp NULL DEFAULT NULL, `compliance_checked_by` bigint(20) unsigned DEFAULT NULL, `compliance_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `last_modified_at` timestamp NULL DEFAULT NULL, `last_modified_by` bigint(20) unsigned DEFAULT NULL, `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Additional metadata for the deadline' CHECK (json_valid(`metadata`)), PRIMARY KEY (`id`), KEY `pt_annual_fee_deadlines_country_id_foreign` (`country_id`), KEY `pt_annual_fee_deadlines_added_by_foreign` (`added_by`), KEY `pt_annual_fee_deadlines_patent_id_country_id_fee_year_index` (`patent_id`,`country_id`,`fee_year`), KEY `pt_annual_fee_deadlines_due_date_index` (`due_date`), KEY `pt_annual_fee_deadlines_status_index` (`status`), KEY `pt_annual_fee_deadlines_invoice_id_index` (`invoice_id`), KEY `pt_annual_fee_deadlines_client_confirmed_by_foreign` (`client_confirmed_by`), KEY `pt_annual_fee_deadlines_waiver_approved_by_foreign` (`waiver_approved_by`), KEY `pt_annual_fee_deadlines_escalated_by_foreign` (`escalated_by`), KEY `pt_annual_fee_deadlines_compliance_checked_by_foreign` (`compliance_checked_by`), KEY `pt_annual_fee_deadlines_last_modified_by_foreign` (`last_modified_by`), KEY `deadline_type` (`deadline_type`), KEY `priority_urgent` (`priority`,`is_urgent`), KEY `client_confirmation_status` (`client_confirmation_status`), KEY `waiver_status` (`waiver_requested`,`waiver_approved`), KEY `escalated` (`escalated`), KEY `compliance_checked` (`compliance_checked`), KEY `due_date_status` (`due_date`,`status`), CONSTRAINT `pt_annual_fee_deadlines_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_annual_fee_deadlines_client_confirmed_by_foreign` FOREIGN KEY (`client_confirmed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_annual_fee_deadlines_compliance_checked_by_foreign` FOREIGN KEY (`compliance_checked_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_annual_fee_deadlines_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `pt_countries` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_annual_fee_deadlines_escalated_by_foreign` FOREIGN KEY (`escalated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_annual_fee_deadlines_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `acc_invoices` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_annual_fee_deadlines_last_modified_by_foreign` FOREIGN KEY (`last_modified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_annual_fee_deadlines_patent_id_foreign` FOREIGN KEY (`patent_id`) REFERENCES `pt_patent_tkts` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_annual_fee_deadlines_waiver_approved_by_foreign` FOREIGN KEY (`waiver_approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_annual_fee_payments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_annual_fee_payments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `deadline_id` bigint(20) unsigned NOT NULL, `amount_paid` decimal(10,2) NOT NULL COMMENT 'Amount paid', `currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD' COMMENT 'Currency of payment', `exchange_rate` decimal(16,8) NOT NULL DEFAULT 1.00000000 COMMENT 'Exchange rate to USD', `amount_usd` decimal(10,2) NOT NULL COMMENT 'Amount in USD', `payment_date` date NOT NULL COMMENT 'Date when payment was made', `payment_method` enum('bank_transfer','credit_card','cash','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'bank_transfer', `transaction_reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Bank or payment reference', `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Payment notes', `recorded_by` bigint(20) unsigned NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_annual_fee_payments_recorded_by_foreign` (`recorded_by`), KEY `pt_annual_fee_payments_deadline_id_index` (`deadline_id`), KEY `pt_annual_fee_payments_payment_date_index` (`payment_date`), KEY `pt_annual_fee_payments_payment_method_index` (`payment_method`), CONSTRAINT `pt_annual_fee_payments_deadline_id_foreign` FOREIGN KEY (`deadline_id`) REFERENCES `pt_annual_fee_deadlines` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_annual_fee_payments_recorded_by_foreign` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_annual_fee_rules`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_annual_fee_rules` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `country_id` bigint(20) unsigned NOT NULL, `patent_type` enum('patent','utility_model','design') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'patent', `year_from` int(11) NOT NULL COMMENT 'Starting year for this fee rule', `year_to` int(11) DEFAULT NULL COMMENT 'Ending year for this fee rule (null = ongoing)', `fee_amount` decimal(10,2) NOT NULL COMMENT 'Fee amount in the specified currency', `currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD' COMMENT 'Currency code for the fee', `due_date_rule` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Rule for calculating due date (e.g., "filing_date + 3 years + 6 months")', `grace_period_days` int(11) NOT NULL DEFAULT 0 COMMENT 'Grace period in days after due date', `late_fee_percentage` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Late fee as percentage of original fee', `late_fee_fixed` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Fixed late fee amount', `inflation_rate` decimal(5,2) DEFAULT NULL COMMENT 'Annual inflation rate for fee adjustments', `priority` int(11) NOT NULL DEFAULT 0 COMMENT 'Priority for rule selection when multiple rules apply', `category` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Rule category (standard, premium, small_entity, etc.)', `effective_from` date DEFAULT NULL COMMENT 'Rule effective from date', `effective_to` date DEFAULT NULL COMMENT 'Rule effective to date', `validation_rules` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'JSON validation rules for this fee rule' CHECK (json_valid(`validation_rules`)), `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Additional notes about this rule', `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Whether this rule is currently active', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `last_modified_at` timestamp NULL DEFAULT NULL, `last_modified_by` bigint(20) unsigned DEFAULT NULL, `conditions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'JSON array of rule conditions' CHECK (json_valid(`conditions`)), `condition_logic` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Logic operator: AND, OR, custom expression', `fee_formula` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Formula for calculating fees dynamically', `fee_variables` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Variables for fee calculation' CHECK (json_valid(`fee_variables`)), `base_fee` decimal(10,2) DEFAULT NULL COMMENT 'Base fee amount', `fee_multiplier` decimal(8,4) NOT NULL DEFAULT 1.0000 COMMENT 'Fee multiplier', `due_date_formula` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Formula for calculating due dates', `due_date_variables` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Variables for due date calculation' CHECK (json_valid(`due_date_variables`)), `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Custom fields for this rule' CHECK (json_valid(`custom_fields`)), `rule_description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Human-readable rule description', `rule_version` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1.0' COMMENT 'Rule version for tracking changes', `rule_group` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Rule group for organization', `is_template` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this a template rule', `applicable_conditions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Conditions when this rule applies' CHECK (json_valid(`applicable_conditions`)), `exclusion_conditions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Conditions when this rule does NOT apply' CHECK (json_valid(`exclusion_conditions`)), `requires_approval` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Does this rule require manual approval', `dynamic_columns` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'JSON array of dynamic column definitions' CHECK (json_valid(`dynamic_columns`)), `dynamic_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'JSON object storing dynamic column values' CHECK (json_valid(`dynamic_values`)), `column_schema_version` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1.0' COMMENT 'Version of the column schema', `has_custom_columns` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Whether this rule has custom columns', PRIMARY KEY (`id`), KEY `pt_annual_fee_rules_country_id_patent_type_year_from_index` (`country_id`,`patent_type`,`year_from`), KEY `pt_annual_fee_rules_is_active_index` (`is_active`), KEY `pt_annual_fee_rules_added_by_foreign` (`added_by`), KEY `pt_annual_fee_rules_last_modified_by_foreign` (`last_modified_by`), KEY `country_patent_year_active` (`country_id`,`patent_type`,`year_from`,`is_active`), KEY `effective_dates` (`effective_from`,`effective_to`), KEY `category` (`category`), KEY `priority` (`priority`), CONSTRAINT `pt_annual_fee_rules_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_annual_fee_rules_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `pt_countries` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_annual_fee_rules_last_modified_by_foreign` FOREIGN KEY (`last_modified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_annual_fees`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_annual_fees` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `patent_id` bigint(20) unsigned NOT NULL COMMENT 'Reference to patent', `patent_year` int(11) NOT NULL COMMENT 'Patent year (1, 2, 3, etc.)', `due_date` date NOT NULL COMMENT 'Fee due date', `calculated_amount` decimal(12,2) NOT NULL COMMENT 'Calculated fee amount', `paid_amount` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Amount actually paid', `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending' COMMENT 'pending, paid, overdue, waived', `payment_date` date DEFAULT NULL COMMENT 'Date when payment was made', `payment_reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Payment reference number', `entity_size` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'large' COMMENT 'large, small, micro', `discount_applied` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Discount percentage applied', `late_fee_amount` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Late payment penalty', `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Additional notes', `created_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User who created this record', `updated_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User who last updated this record', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_annual_fees_patent_id_patent_year_index` (`patent_id`,`patent_year`), KEY `pt_annual_fees_due_date_status_index` (`due_date`,`status`), KEY `pt_annual_fees_status_due_date_index` (`status`,`due_date`), KEY `pt_annual_fees_patent_id_status_index` (`patent_id`,`status`), CONSTRAINT `pt_annual_fees_patent_id_foreign` FOREIGN KEY (`patent_id`) REFERENCES `pt_patent_tkts` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_applicants`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_applicants` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `type` enum('individual','company') COLLATE utf8mb4_unicode_ci DEFAULT NULL, `beneficiary_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `registry_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_applicants_country_id_foreign` (`country_id`), KEY `pt_applicants_added_by_foreign` (`added_by`), CONSTRAINT `pt_applicants_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_applicants_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `pt_countries` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=364 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_applicants_fks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_applicants_fks` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `table_id` bigint(20) unsigned NOT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `pt_applicant_id` bigint(20) unsigned NOT NULL, `added_by` int(11) NOT NULL, `created_at` date NOT NULL DEFAULT current_timestamp(), `updated_at` date NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `pt_applicants_fks_pt_applicant_id_foreign` (`pt_applicant_id`), CONSTRAINT `pt_applicants_fks_pt_applicant_id_foreign` FOREIGN KEY (`pt_applicant_id`) REFERENCES `pt_applicants` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3275 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_application_changes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_application_changes` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `application_id` bigint(20) unsigned DEFAULT NULL, `application_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `change_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `field_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `old_value` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `new_value` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)), `section` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `severity` enum('low','medium','high','critical') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'medium', `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_system_change` tinyint(1) NOT NULL DEFAULT 0, `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_agent` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `changed_by` bigint(20) unsigned DEFAULT NULL, `changed_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_application_changes_application_id_application_type_index` (`application_id`,`application_type`), KEY `pt_application_changes_change_type_index` (`change_type`), KEY `pt_application_changes_changed_by_index` (`changed_by`), KEY `pt_application_changes_changed_at_index` (`changed_at`), KEY `pt_application_changes_severity_index` (`severity`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_assigneds_fks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_assigneds_fks` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `table_id` bigint(20) unsigned NOT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `user_id` bigint(20) unsigned NOT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_assigneds_fks_user_id_foreign` (`user_id`), KEY `pt_assigneds_fks_added_by_foreign` (`added_by`), CONSTRAINT `pt_assigneds_fks_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_assigneds_fks_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_associates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_associates` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `associate_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_associates_country_id_foreign` (`country_id`), KEY `pt_associates_added_by_foreign` (`added_by`), CONSTRAINT `pt_associates_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_associates_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `pt_countries` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_associates_fks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_associates_fks` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `associate_id` bigint(20) unsigned NOT NULL, `table_id` bigint(20) unsigned NOT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pt_patent_tkts', `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_associates_fks_associate_id_foreign` (`associate_id`), KEY `pt_associates_fks_added_by_foreign` (`added_by`), CONSTRAINT `pt_associates_fks_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_associates_fks_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `pt_associates` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1022 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_audit_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_audit_logs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned DEFAULT NULL, `action` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `table_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `record_id` bigint(20) unsigned DEFAULT NULL, `old_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_data`)), `new_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_data`)), `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_client_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_client_types` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type_client_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_client_types_added_by_foreign` (`added_by`), CONSTRAINT `pt_client_types_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_clients`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_clients` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `client_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `type_clients_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_clients_country_id_foreign` (`country_id`), KEY `pt_clients_type_clients_id_foreign` (`type_clients_id`), KEY `pt_clients_added_by_foreign` (`added_by`), CONSTRAINT `pt_clients_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_clients_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `pt_countries` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_clients_type_clients_id_foreign` FOREIGN KEY (`type_clients_id`) REFERENCES `pt_client_types` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=1136 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_clients_fks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_clients_fks` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `table_id` bigint(20) unsigned NOT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pt_patent_tkts', `pt_client_id` bigint(20) unsigned NOT NULL, `added_by` int(11) NOT NULL, `created_at` date NOT NULL DEFAULT current_timestamp(), `updated_at` date NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `pt_clients_fks_pt_client_id_foreign` (`pt_client_id`), CONSTRAINT `pt_clients_fks_pt_client_id_foreign` FOREIGN KEY (`pt_client_id`) REFERENCES `pt_clients` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=5378 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_copyright_tkts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_copyright_tkts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `work_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `work_type` enum('software','literature','music') COLLATE utf8mb4_unicode_ci NOT NULL, `client_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `aipt_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `filing_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `filing_date` date DEFAULT NULL, `registration_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `registration_date` date DEFAULT NULL, `activity_status` enum('open','in_progress','closed') COLLATE utf8mb4_unicode_ci NOT NULL, `service_type_id` bigint(20) unsigned DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, `procedure_id` bigint(20) unsigned DEFAULT NULL, `methode_id` bigint(20) unsigned DEFAULT NULL, `assigned_to` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_copyright_tkts_service_type_id_foreign` (`service_type_id`), KEY `pt_copyright_tkts_associate_id_foreign` (`associate_id`), KEY `pt_copyright_tkts_procedure_id_foreign` (`procedure_id`), KEY `pt_copyright_tkts_methode_id_foreign` (`methode_id`), KEY `pt_copyright_tkts_assigned_to_foreign` (`assigned_to`), KEY `pt_copyright_tkts_added_by_foreign` (`added_by`), CONSTRAINT `pt_copyright_tkts_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_copyright_tkts_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_copyright_tkts_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `pt_associates` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_copyright_tkts_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `pt_methodes` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_copyright_tkts_procedure_id_foreign` FOREIGN KEY (`procedure_id`) REFERENCES `pt_procedures` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_copyright_tkts_service_type_id_foreign` FOREIGN KEY (`service_type_id`) REFERENCES `pt_service_types` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_countries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_countries` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_country_deadlines`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_country_deadlines` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `country_id` bigint(20) unsigned NOT NULL, `methode_id` bigint(20) unsigned NOT NULL, `deadline_value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_country_deadlines_country_id_foreign` (`country_id`), KEY `pt_country_deadlines_methode_id_foreign` (`methode_id`), CONSTRAINT `pt_country_deadlines_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `pt_countries` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_country_deadlines_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `pt_methodes` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=6832 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_country_details_tkts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_country_details_tkts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `table_id` bigint(20) unsigned NOT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `route` enum('Validation','PCT','Regular','Local','Other') COLLATE utf8mb4_unicode_ci DEFAULT NULL, `filing_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `filing_date` date DEFAULT NULL, `priority_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `priority_date` date DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, `methode_id` bigint(20) unsigned DEFAULT NULL, `assigned_to` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `attachment` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '{"poa":0,"doa":0,"correct_Translation":0,"priority_Document":0}', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_country_details_tkts_associate_id_foreign` (`associate_id`), KEY `pt_country_details_tkts_methode_id_foreign` (`methode_id`), KEY `pt_country_details_tkts_assigned_to_foreign` (`assigned_to`), KEY `pt_country_details_tkts_added_by_foreign` (`added_by`), KEY `pt_country_details_tkts_country_id_foreign` (`country_id`), CONSTRAINT `pt_country_details_tkts_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_country_details_tkts_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_country_details_tkts_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `pt_associates` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_country_details_tkts_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `pt_countries` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_country_details_tkts_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `pt_methodes` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=7225 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_country_fks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_country_fks` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `table_id` bigint(20) unsigned NOT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` bigint(20) unsigned NOT NULL, `added_by` int(11) DEFAULT NULL, `created_at` date NOT NULL DEFAULT current_timestamp(), `updated_at` date NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `pt_country_fks_country_id_foreign` (`country_id`), CONSTRAINT `pt_country_fks_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `pt_countries` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=7221 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_design_tkts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_design_tkts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `service_type_id` bigint(20) unsigned DEFAULT NULL, `application_status` enum('Active','Under filing','Closed','Transferred','Rejected','Inactive','Abandon','Inquiry','*****') COLLATE utf8mb4_unicode_ci DEFAULT NULL, `client_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `aipt_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `design_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `filing_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `filing_date` date DEFAULT NULL, `registration_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `registration_date` date DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, `procedure_id` bigint(20) unsigned DEFAULT NULL, `methode_id` bigint(20) unsigned DEFAULT NULL, `assigned_to` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_design_tkts_service_type_id_foreign` (`service_type_id`), KEY `pt_design_tkts_associate_id_foreign` (`associate_id`), KEY `pt_design_tkts_procedure_id_foreign` (`procedure_id`), KEY `pt_design_tkts_methode_id_foreign` (`methode_id`), KEY `pt_design_tkts_assigned_to_foreign` (`assigned_to`), KEY `pt_design_tkts_added_by_foreign` (`added_by`), CONSTRAINT `pt_design_tkts_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_design_tkts_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_design_tkts_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `pt_associates` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_design_tkts_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `pt_methodes` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_design_tkts_procedure_id_foreign` FOREIGN KEY (`procedure_id`) REFERENCES `pt_procedures` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_design_tkts_service_type_id_foreign` FOREIGN KEY (`service_type_id`) REFERENCES `pt_service_types` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_dynamic_column_schemas`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_dynamic_column_schemas` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Schema name (e.g., "US_Utility_Patent_Columns")', `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Schema description', `country_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Country-specific schema', `patent_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Patent type-specific schema', `user_id` bigint(20) unsigned DEFAULT NULL COMMENT 'User-specific schema', `column_definitions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'JSON array of column definitions' CHECK (json_valid(`column_definitions`)), `version` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1.0' COMMENT 'Schema version', `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Whether schema is active', `is_global` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Whether schema applies globally', `is_template` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Whether this is a template schema', `validation_rules` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Validation rules for columns' CHECK (json_valid(`validation_rules`)), `display_settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Display settings for columns' CHECK (json_valid(`display_settings`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_dynamic_column_schemas_country_id_patent_type_is_active_index` (`country_id`,`patent_type`,`is_active`), KEY `pt_dynamic_column_schemas_user_id_is_active_index` (`user_id`,`is_active`), KEY `pt_dynamic_column_schemas_is_global_is_active_index` (`is_global`,`is_active`), KEY `pt_dynamic_column_schemas_is_template_is_active_index` (`is_template`,`is_active`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_fee_installments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_fee_installments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `deadline_id` bigint(20) unsigned NOT NULL, `installment_plan_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Name of the installment plan', `total_installments` int(11) NOT NULL COMMENT 'Total number of installments', `installment_number` int(11) NOT NULL COMMENT 'Current installment number', `installment_amount` decimal(10,2) NOT NULL COMMENT 'Amount for this installment', `total_amount` decimal(10,2) NOT NULL COMMENT 'Total amount for all installments', `paid_amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Amount paid so far', `remaining_amount` decimal(10,2) NOT NULL COMMENT 'Remaining amount to be paid', `due_date` date NOT NULL COMMENT 'Due date for this installment', `paid_date` date DEFAULT NULL COMMENT 'Date when installment was paid', `status` enum('pending','paid','overdue','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `late_fee` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Late fee for this installment', `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Notes for this installment', `payment_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Payment method and reference details' CHECK (json_valid(`payment_details`)), `created_by` bigint(20) unsigned NOT NULL, `paid_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_fee_installments_paid_by_foreign` (`paid_by`), KEY `pt_fee_installments_deadline_id_installment_number_index` (`deadline_id`,`installment_number`), KEY `pt_fee_installments_status_due_date_index` (`status`,`due_date`), KEY `pt_fee_installments_created_by_index` (`created_by`), CONSTRAINT `pt_fee_installments_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`), CONSTRAINT `pt_fee_installments_deadline_id_foreign` FOREIGN KEY (`deadline_id`) REFERENCES `pt_annual_fee_deadlines` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_fee_installments_paid_by_foreign` FOREIGN KEY (`paid_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_fee_notifications`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_fee_notifications` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `deadline_id` bigint(20) unsigned NOT NULL, `notification_type` enum('early_reminder','standard_reminder','urgent_reminder','final_reminder','overdue','escalation') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Type of notification sent', `channel` enum('email','sms','phone','mail','system') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'email' COMMENT 'Notification channel used', `sent_to` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Recipients of the notification' CHECK (json_valid(`sent_to`)), `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Notification subject', `message` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Notification message content', `status` enum('pending','sent','delivered','failed','bounced') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `sent_at` timestamp NULL DEFAULT NULL, `delivered_at` timestamp NULL DEFAULT NULL, `read_at` timestamp NULL DEFAULT NULL, `error_message` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Additional notification metadata' CHECK (json_valid(`metadata`)), `sent_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_fee_notifications_sent_by_foreign` (`sent_by`), KEY `pt_fee_notifications_deadline_id_notification_type_index` (`deadline_id`,`notification_type`), KEY `pt_fee_notifications_status_sent_at_index` (`status`,`sent_at`), KEY `pt_fee_notifications_channel_index` (`channel`), CONSTRAINT `pt_fee_notifications_deadline_id_foreign` FOREIGN KEY (`deadline_id`) REFERENCES `pt_annual_fee_deadlines` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_fee_notifications_sent_by_foreign` FOREIGN KEY (`sent_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_fee_payments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_fee_payments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `annual_fee_id` bigint(20) unsigned NOT NULL COMMENT 'Reference to annual fee', `payment_amount` decimal(12,2) NOT NULL COMMENT 'Payment amount', `payment_date` date NOT NULL COMMENT 'Date payment was made', `payment_method` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'cash, check, wire, credit_card, etc.', `reference_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Payment reference number', `receipt_document_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Document ID for receipt', `payment_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Payment notes', `processed_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Who processed the payment', `is_verified` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Whether payment is verified', `verified_date` date DEFAULT NULL COMMENT 'Date payment was verified', `verified_by` bigint(20) unsigned DEFAULT NULL COMMENT 'User who verified payment', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_fee_payments_annual_fee_id_payment_date_index` (`annual_fee_id`,`payment_date`), KEY `pt_fee_payments_payment_date_is_verified_index` (`payment_date`,`is_verified`), KEY `pt_fee_payments_reference_number_index` (`reference_number`), CONSTRAINT `pt_fee_payments_annual_fee_id_foreign` FOREIGN KEY (`annual_fee_id`) REFERENCES `pt_annual_fees` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_fee_structures`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_fee_structures` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `country_code` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'ISO country code', `patent_year` int(11) NOT NULL COMMENT 'Patent year (1, 2, 3, etc.)', `base_fee_amount` decimal(12,2) NOT NULL COMMENT 'Base fee amount', `small_entity_discount` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Small entity discount percentage', `micro_entity_discount` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT 'Micro entity discount percentage', `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD' COMMENT 'Currency code', `effective_date` date NOT NULL COMMENT 'When this fee structure becomes effective', `expiration_date` date DEFAULT NULL COMMENT 'When this fee structure expires', `special_rules` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Country-specific special rules' CHECK (json_valid(`special_rules`)), `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pt_fee_structures_country_code_patent_year_effective_date_unique` (`country_code`,`patent_year`,`effective_date`), KEY `pt_fee_structures_country_code_patent_year_is_active_index` (`country_code`,`patent_year`,`is_active`), KEY `pt_fee_structures_effective_date_expiration_date_index` (`effective_date`,`expiration_date`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_fee_waivers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_fee_waivers` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `deadline_id` bigint(20) unsigned NOT NULL, `waiver_reason` enum('client_request','administrative','financial_hardship','legal_issue','patent_abandoned','patent_rejected','patent_transferred','duplicate_payment','system_error','other') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Reason for waiver request', `waiver_amount` decimal(10,2) NOT NULL COMMENT 'Amount to be waived', `remaining_amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Remaining amount after waiver', `justification` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Detailed justification for waiver', `supporting_documents` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Supporting document paths' CHECK (json_valid(`supporting_documents`)), `status` enum('pending','approved','rejected','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `approval_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Notes from approver', `requested_by` bigint(20) unsigned NOT NULL, `approved_by` bigint(20) unsigned DEFAULT NULL, `requested_at` timestamp NOT NULL DEFAULT current_timestamp(), `approved_at` timestamp NULL DEFAULT NULL, `rejected_at` timestamp NULL DEFAULT NULL, `rejection_reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `rejected_by` bigint(20) unsigned DEFAULT NULL, `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Additional waiver metadata' CHECK (json_valid(`metadata`)), `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_fee_waivers_rejected_by_foreign` (`rejected_by`), KEY `pt_fee_waivers_deadline_id_status_index` (`deadline_id`,`status`), KEY `pt_fee_waivers_waiver_reason_index` (`waiver_reason`), KEY `pt_fee_waivers_requested_by_requested_at_index` (`requested_by`,`requested_at`), KEY `pt_fee_waivers_approved_by_approved_at_index` (`approved_by`,`approved_at`), CONSTRAINT `pt_fee_waivers_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_fee_waivers_deadline_id_foreign` FOREIGN KEY (`deadline_id`) REFERENCES `pt_annual_fee_deadlines` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_fee_waivers_rejected_by_foreign` FOREIGN KEY (`rejected_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_fee_waivers_requested_by_foreign` FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_histories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_histories` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `comment` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL, `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `table_id` bigint(20) unsigned DEFAULT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12990 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_messages`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_messages` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `table_id` bigint(20) NOT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `message` text COLLATE utf8mb4_unicode_ci NOT NULL, `date` date NOT NULL, `department_id` bigint(20) unsigned DEFAULT NULL, `status` tinyint(1) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `messages_department_id_index` (`department_id`) ) ENGINE=InnoDB AUTO_INCREMENT=22842 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_method_deadline_fks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_method_deadline_fks` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `method_id` bigint(20) unsigned NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `table_id` bigint(20) unsigned NOT NULL, `date_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `date` date DEFAULT NULL, `deadline_add` date DEFAULT NULL, `is_done` tinyint(1) NOT NULL DEFAULT 0, `done_by` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_method_deadline_fks_method_id_foreign` (`method_id`), KEY `pt_method_deadline_fks_country_id_foreign` (`country_id`), KEY `pt_method_deadline_fks_added_by_foreign` (`added_by`), KEY `pt_method_deadline_fks_done_by_foreign` (`done_by`), KEY `pt_method_deadline_fks_table_type_table_id_index` (`table_type`,`table_id`), CONSTRAINT `pt_method_deadline_fks_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_method_deadline_fks_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `pt_countries` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_method_deadline_fks_done_by_foreign` FOREIGN KEY (`done_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_method_deadline_fks_method_id_foreign` FOREIGN KEY (`method_id`) REFERENCES `pt_methodes` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=138 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_methodes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_methodes` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `methode_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `color` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `has_deadline` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1 = has deadline', `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_methodes_added_by_foreign` (`added_by`), CONSTRAINT `pt_methodes_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_notification_rules`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_notification_rules` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `country_code` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'ISO country code (null for global)', `patent_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Patent type (null for all types)', `days_before_due` int(11) NOT NULL COMMENT 'Days before due date to send notification', `notification_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'email, sms, dashboard, all', `recipient_roles` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Roles to notify' CHECK (json_valid(`recipient_roles`)), `recipient_users` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Specific users to notify' CHECK (json_valid(`recipient_users`)), `email_template` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Email template name', `sms_template` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'SMS template name', `is_active` tinyint(1) NOT NULL DEFAULT 1, `priority` int(11) NOT NULL DEFAULT 1 COMMENT 'Priority order (1=highest)', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_notification_rules_country_code_patent_type_is_active_index` (`country_code`,`patent_type`,`is_active`), KEY `pt_notification_rules_days_before_due_is_active_index` (`days_before_due`,`is_active`), KEY `pt_notification_rules_notification_type_is_active_index` (`notification_type`,`is_active`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_patent_tkts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_patent_tkts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `application_status` enum('Active','Under filing','Closed','Transferred','Rejected','Inactive','Abandon','Inquiry','*****') COLLATE utf8mb4_unicode_ci NOT NULL, `service_type_id` bigint(20) unsigned DEFAULT NULL, `client_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `aipt_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `prosecution_aipt` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `application_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `total_annual_fees_paid` decimal(15,2) NOT NULL DEFAULT 0.00, `last_annual_fee_paid_date` date DEFAULT NULL, `pct_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `pct_date` date DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, `procedure_id` bigint(20) unsigned DEFAULT NULL, `methode_id` bigint(20) unsigned DEFAULT NULL, `attachment` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '{"poa":0,"doa":0,"correct_Translation":0,"priority_Document":0}' CHECK (json_valid(`attachment`)), `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_patent_tkts_service_type_id_foreign` (`service_type_id`), KEY `pt_patent_tkts_associate_id_foreign` (`associate_id`), KEY `pt_patent_tkts_procedure_id_foreign` (`procedure_id`), KEY `pt_patent_tkts_methode_id_foreign` (`methode_id`), KEY `pt_patent_tkts_added_by_foreign` (`added_by`), CONSTRAINT `pt_patent_tkts_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_patent_tkts_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `pt_associates` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_patent_tkts_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `pt_methodes` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_patent_tkts_procedure_id_foreign` FOREIGN KEY (`procedure_id`) REFERENCES `pt_procedures` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_patent_tkts_service_type_id_foreign` FOREIGN KEY (`service_type_id`) REFERENCES `pt_service_types` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=9277 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_procedures`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_procedures` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `procedure_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_procedures_added_by_foreign` (`added_by`), CONSTRAINT `pt_procedures_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_requests`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_requests` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `answred` tinyint(1) NOT NULL DEFAULT 0, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `phone` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `message` text COLLATE utf8mb4_unicode_ci NOT NULL, `response` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `handeled_by` bigint(20) unsigned DEFAULT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `table_id` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_requirments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_requirments` ( `id` bigint(20) unsigned NOT NULL, `Requirements` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `procedure_id` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_service_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_service_types` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `service_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_service_types_added_by_foreign` (`added_by`), CONSTRAINT `pt_service_types_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_service_types_fks`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_service_types_fks` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `table_id` bigint(20) unsigned NOT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `pt_service_type_id` bigint(20) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `pt_service_types_fks_pt_service_type_id_foreign` (`pt_service_type_id`), CONSTRAINT `pt_service_types_fks_pt_service_type_id_foreign` FOREIGN KEY (`pt_service_type_id`) REFERENCES `pt_service_types` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=5203 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_team_filters`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_team_filters` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `team_id` bigint(20) unsigned NOT NULL, `method_id` bigint(20) unsigned DEFAULT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_team_filters_team_id_foreign` (`team_id`), KEY `pt_team_filters_method_id_foreign` (`method_id`), KEY `pt_team_filters_country_id_foreign` (`country_id`), CONSTRAINT `pt_team_filters_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `pt_countries` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_team_filters_method_id_foreign` FOREIGN KEY (`method_id`) REFERENCES `pt_methodes` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_team_filters_team_id_foreign` FOREIGN KEY (`team_id`) REFERENCES `pt_teams` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_team_users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_team_users` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `team_id` bigint(20) unsigned NOT NULL, `user_id` bigint(20) unsigned NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_team_users_team_id_foreign` (`team_id`), KEY `pt_team_users_user_id_foreign` (`user_id`), CONSTRAINT `pt_team_users_team_id_foreign` FOREIGN KEY (`team_id`) REFERENCES `pt_teams` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_team_users_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=279 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_teams`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_teams` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `table_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_transaction_comments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_transaction_comments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `transaction_id` bigint(20) unsigned NOT NULL, `comment` text COLLATE utf8mb4_unicode_ci NOT NULL, `status` enum('public','private') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'public', `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `added_by` bigint(20) unsigned NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_transaction_comments_transaction_id_foreign` (`transaction_id`), KEY `pt_transaction_comments_added_by_foreign` (`added_by`), CONSTRAINT `pt_transaction_comments_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_transaction_comments_transaction_id_foreign` FOREIGN KEY (`transaction_id`) REFERENCES `pt_transactions` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_transactions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_transactions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `reference_no` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `patent_id` bigint(20) unsigned NOT NULL, `transaction_type_id` bigint(20) unsigned NOT NULL, `aipt_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `country` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `amount` decimal(15,2) NOT NULL DEFAULT 0.00, `amount_usd` decimal(15,2) NOT NULL DEFAULT 0.00, `currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'USD', `exchange_rate` decimal(10,4) NOT NULL DEFAULT 1.0000, `transaction_date` date DEFAULT NULL, `deadline` date DEFAULT NULL, `bill_deadline` date DEFAULT NULL, `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `payment_method` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `transaction_reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `invoice_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` enum('pending','approved','rejected','paid','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending', `bill_status` enum('not_billed','billed','paid') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'not_billed', `has_bills` tinyint(1) NOT NULL DEFAULT 0, `stages` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`stages`)), `role_ranks` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`role_ranks`)), `created_by` bigint(20) unsigned NOT NULL, `approved_by` bigint(20) unsigned DEFAULT NULL, `approved_at` timestamp NULL DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pt_transactions_reference_no_unique` (`reference_no`), KEY `pt_transactions_transaction_type_id_foreign` (`transaction_type_id`), KEY `pt_transactions_created_by_foreign` (`created_by`), KEY `pt_transactions_approved_by_foreign` (`approved_by`), KEY `pt_transactions_patent_id_status_index` (`patent_id`,`status`), KEY `pt_transactions_transaction_date_index` (`transaction_date`), KEY `pt_transactions_deadline_index` (`deadline`), KEY `pt_transactions_country_index` (`country`), CONSTRAINT `pt_transactions_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_transactions_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`), CONSTRAINT `pt_transactions_patent_id_foreign` FOREIGN KEY (`patent_id`) REFERENCES `pt_patent_tkts` (`id`) ON DELETE CASCADE, CONSTRAINT `pt_transactions_transaction_type_id_foreign` FOREIGN KEY (`transaction_type_id`) REFERENCES `actions_type_transactions` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `pt_utility_model_tkts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `pt_utility_model_tkts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `application_type` enum('national','international') COLLATE utf8mb4_unicode_ci NOT NULL, `route` enum('direct','convention') COLLATE utf8mb4_unicode_ci NOT NULL, `service_type_id` bigint(20) unsigned DEFAULT NULL, `activity_status` enum('open','in_progress','closed') COLLATE utf8mb4_unicode_ci NOT NULL, `client_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `aipt_ref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `application_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `filing_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `filing_date` date DEFAULT NULL, `registration_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `registration_date` date DEFAULT NULL, `annual_fees` decimal(10,2) DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, `procedure_id` bigint(20) unsigned DEFAULT NULL, `methode_id` bigint(20) unsigned DEFAULT NULL, `assigned_to` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `pt_utility_model_tkts_service_type_id_foreign` (`service_type_id`), KEY `pt_utility_model_tkts_associate_id_foreign` (`associate_id`), KEY `pt_utility_model_tkts_procedure_id_foreign` (`procedure_id`), KEY `pt_utility_model_tkts_methode_id_foreign` (`methode_id`), KEY `pt_utility_model_tkts_assigned_to_foreign` (`assigned_to`), KEY `pt_utility_model_tkts_added_by_foreign` (`added_by`), CONSTRAINT `pt_utility_model_tkts_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_utility_model_tkts_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_utility_model_tkts_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `pt_associates` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_utility_model_tkts_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `pt_methodes` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_utility_model_tkts_procedure_id_foreign` FOREIGN KEY (`procedure_id`) REFERENCES `pt_procedures` (`id`) ON DELETE SET NULL, CONSTRAINT `pt_utility_model_tkts_service_type_id_foreign` FOREIGN KEY (`service_type_id`) REFERENCES `pt_service_types` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `requirments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `requirments` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `Requirements` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `procedure_id` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `requirments_country_id_index` (`country_id`), KEY `requirments_procedure_id_index` (`procedure_id`), CONSTRAINT `requirments_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE CASCADE, CONSTRAINT `requirments_procedure_id_foreign` FOREIGN KEY (`procedure_id`) REFERENCES `procedures` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=426 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `role_has_permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `role_has_permissions` ( `permission_id` bigint(20) unsigned NOT NULL, `role_id` bigint(20) unsigned NOT NULL, PRIMARY KEY (`permission_id`,`role_id`), KEY `role_has_permissions_role_id_foreign` (`role_id`), CONSTRAINT `role_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE, CONSTRAINT `role_has_permissions_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `roles` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `guard_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `roles_name_guard_name_unique` (`name`,`guard_name`) ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `url` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `telescope_entries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `telescope_entries` ( `sequence` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL, `batch_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL, `family_hash` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `should_display_on_index` tinyint(1) NOT NULL DEFAULT 1, `type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL, `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` datetime DEFAULT NULL, PRIMARY KEY (`sequence`), UNIQUE KEY `telescope_entries_uuid_unique` (`uuid`), KEY `telescope_entries_batch_id_index` (`batch_id`), KEY `telescope_entries_family_hash_index` (`family_hash`), KEY `telescope_entries_created_at_index` (`created_at`), KEY `telescope_entries_type_should_display_on_index_index` (`type`,`should_display_on_index`) ) ENGINE=InnoDB AUTO_INCREMENT=1718993 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `telescope_entries_tags`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `telescope_entries_tags` ( `entry_uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL, `tag` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`entry_uuid`,`tag`), KEY `telescope_entries_tags_tag_index` (`tag`), CONSTRAINT `telescope_entries_tags_entry_uuid_foreign` FOREIGN KEY (`entry_uuid`) REFERENCES `telescope_entries` (`uuid`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `telescope_monitoring`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `telescope_monitoring` ( `tag` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`tag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `tmw_drafts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `tmw_drafts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `status` enum('created','assigned','rejected','sent to cleint','client responded','closed','sent to ligitation') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'created', `related_TKT` bigint(20) unsigned DEFAULT NULL, `publication_date` date DEFAULT NULL, `filing_date` date DEFAULT NULL, `filing_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `class` bigint(20) NOT NULL, `country_id_similar` bigint(20) unsigned DEFAULT NULL, `img_paths` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `Applicant` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `opposition_date` date DEFAULT NULL, `opposition_deadline` bigint(20) DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `assigned_to` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `tmw_drafts_related_tkt_index` (`related_TKT`), KEY `tmw_drafts_country_id_similar_index` (`country_id_similar`), KEY `tmw_drafts_added_by_index` (`added_by`), KEY `tmw_drafts_assigned_to_index` (`assigned_to`) ) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `tmw_drafts_histories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `tmw_drafts_histories` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `comment` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `related_TKT` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `tmw_drafts_histories_related_tkt_index` (`related_TKT`), KEY `tmw_drafts_histories_added_by_index` (`added_by`), CONSTRAINT `tmw_drafts_histories_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tmw_drafts_histories_related_tkt_foreign` FOREIGN KEY (`related_TKT`) REFERENCES `tmw_drafts` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `trade_mark_tickets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `trade_mark_tickets` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `TKT_Type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `client_id` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `associate_id` bigint(20) unsigned DEFAULT NULL, `methode_id` bigint(20) unsigned DEFAULT NULL, `assigned_to` bigint(20) unsigned DEFAULT 1, `procedure_id` bigint(20) unsigned DEFAULT NULL, `country_id` bigint(20) unsigned DEFAULT NULL, `applicant_id` bigint(20) unsigned DEFAULT NULL, `clientref` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT 'n/a', `aiptref` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `filing_date` date DEFAULT NULL, `filing_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `imageexport` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `class` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `register_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `trademark_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'no trade mark', `renewal_date` date DEFAULT NULL, `img_paths` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `folder_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `related_TKT` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `trade_mark_tickets_client_id_index` (`client_id`), KEY `trade_mark_tickets_added_by_index` (`added_by`), KEY `trade_mark_tickets_associate_id_index` (`associate_id`), KEY `trade_mark_tickets_methode_id_index` (`methode_id`), KEY `trade_mark_tickets_assigned_to_index` (`assigned_to`), KEY `trade_mark_tickets_procedure_id_index` (`procedure_id`), KEY `trade_mark_tickets_country_id_index` (`country_id`), KEY `trade_mark_tickets_applicant_id_index` (`applicant_id`), KEY `trade_mark_tickets_related_tkt_index` (`related_TKT`), KEY `idx_tmt_deleted_at` (`deleted_at`), KEY `idx_tmt_created_at` (`created_at`), KEY `idx_tmt_updated_at` (`updated_at`), KEY `idx_tmt_country_id` (`country_id`), KEY `idx_tmt_procedure_id` (`procedure_id`), KEY `idx_tmt_added_by` (`added_by`), KEY `idx_tmt_deleted_created` (`deleted_at`,`created_at`), KEY `idx_tmt_deleted_country` (`deleted_at`,`country_id`), KEY `idx_tmt_deleted_procedure` (`deleted_at`,`procedure_id`), KEY `idx_trademark_name` (`trademark_name`), KEY `idx_aiptref` (`aiptref`(768)), KEY `idx_class` (`class`), KEY `idx_created_trademark` (`created_at`,`trademark_name`), KEY `idx_client_id` (`client_id`), KEY `idx_country_id` (`country_id`), CONSTRAINT `trade_mark_tickets_added_by_foreign` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `trade_mark_tickets_applicant_id_foreign` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`id`) ON DELETE SET NULL, CONSTRAINT `trade_mark_tickets_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `trade_mark_tickets_associate_id_foreign` FOREIGN KEY (`associate_id`) REFERENCES `associates` (`id`) ON DELETE SET NULL, CONSTRAINT `trade_mark_tickets_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `trade_mark_tickets_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL, CONSTRAINT `trade_mark_tickets_methode_id_foreign` FOREIGN KEY (`methode_id`) REFERENCES `methodes` (`id`) ON DELETE SET NULL, CONSTRAINT `trade_mark_tickets_procedure_id_foreign` FOREIGN KEY (`procedure_id`) REFERENCES `procedures` (`id`) ON DELETE SET NULL, CONSTRAINT `trade_mark_tickets_related_tkt_foreign` FOREIGN KEY (`related_TKT`) REFERENCES `trade_mark_tickets` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=38188 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `trademark_change_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `trademark_change_logs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `trademark_id` bigint(20) unsigned NOT NULL, `old_methode_id` bigint(20) unsigned DEFAULT NULL, `new_methode_id` bigint(20) unsigned DEFAULT NULL, `old_procedure_id` bigint(20) unsigned DEFAULT NULL, `new_procedure_id` bigint(20) unsigned DEFAULT NULL, `change_reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `changed_by` bigint(20) unsigned NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `trademark_change_logs_old_methode_id_foreign` (`old_methode_id`), KEY `trademark_change_logs_new_methode_id_foreign` (`new_methode_id`), KEY `trademark_change_logs_old_procedure_id_foreign` (`old_procedure_id`), KEY `trademark_change_logs_new_procedure_id_foreign` (`new_procedure_id`), KEY `trademark_change_logs_changed_by_foreign` (`changed_by`), KEY `trademark_change_logs_trademark_id_index` (`trademark_id`), KEY `trademark_change_logs_created_at_index` (`created_at`), CONSTRAINT `trademark_change_logs_changed_by_foreign` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE CASCADE, CONSTRAINT `trademark_change_logs_new_methode_id_foreign` FOREIGN KEY (`new_methode_id`) REFERENCES `methodes` (`id`) ON DELETE SET NULL, CONSTRAINT `trademark_change_logs_new_procedure_id_foreign` FOREIGN KEY (`new_procedure_id`) REFERENCES `procedures` (`id`) ON DELETE SET NULL, CONSTRAINT `trademark_change_logs_old_methode_id_foreign` FOREIGN KEY (`old_methode_id`) REFERENCES `methodes` (`id`) ON DELETE SET NULL, CONSTRAINT `trademark_change_logs_old_procedure_id_foreign` FOREIGN KEY (`old_procedure_id`) REFERENCES `procedures` (`id`) ON DELETE SET NULL, CONSTRAINT `trademark_change_logs_trademark_id_foreign` FOREIGN KEY (`trademark_id`) REFERENCES `trade_mark_tickets` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `trademark_classes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `trademark_classes` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `trademark_class_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `trademark_ticket_histories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `trademark_ticket_histories` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `comment` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `related_TKT` bigint(20) unsigned DEFAULT NULL, `added_by` bigint(20) unsigned DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `trademark_ticket_histories_related_tkt_index` (`related_TKT`), KEY `trademark_ticket_histories_added_by_index` (`added_by`), KEY `idx_tth_added_by` (`added_by`), KEY `idx_tth_related_tkt` (`related_TKT`), KEY `idx_tth_created_at` (`created_at`) ) ENGINE=InnoDB AUTO_INCREMENT=81878 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `type_clients`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `type_clients` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `type_client_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `users` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `gender` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email_verified_at` timestamp NULL DEFAULT NULL, `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `department_id` bigint(20) unsigned DEFAULT NULL, `manger_id` bigint(20) unsigned DEFAULT NULL, `role_id` bigint(20) unsigned DEFAULT NULL, `img_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` tinyint(1) NOT NULL, `login_status` tinyint(1) NOT NULL, `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `users_email_unique` (`email`), KEY `users_department_id_index` (`department_id`), KEY `users_manger_id_index` (`manger_id`), KEY `users_role_id_index` (`role_id`), KEY `idx_users_department_id` (`department_id`) ) ENGINE=InnoDB AUTO_INCREMENT=156 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */;