-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `erptech_erp_db`
--

DELIMITER $$
--
-- Procedures
--
$$

$$

DELIMITER ;

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

--
-- Table structure for table `accounts`
--

CREATE TABLE `accounts` (
  `id` int(10) UNSIGNED NOT NULL,
  `code` varchar(20) NOT NULL,
  `name` varchar(150) NOT NULL,
  `type` enum('asset','liability','equity','revenue','expense') NOT NULL,
  `parent_id` int(10) UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `description` varchar(255) DEFAULT NULL,
  `sort_order` smallint(5) UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `accounts`
--

INSERT INTO `accounts` (`id`, `code`, `name`, `type`, `parent_id`, `is_active`, `description`, `sort_order`) VALUES
(1, '1000', 'Cash in Hand', 'asset', NULL, 1, NULL, 0),
(2, '1010', 'Petty Cash', 'asset', NULL, 1, NULL, 0),
(3, '1100', 'Bank Account', 'asset', NULL, 1, NULL, 0),
(4, '1200', 'Accounts Receivable', 'asset', NULL, 1, NULL, 0),
(5, '1300', 'Inventory / Stock', 'asset', NULL, 1, NULL, 0),
(6, '1400', 'Prepaid Expenses', 'asset', NULL, 1, NULL, 0),
(7, '2000', 'Accounts Payable', 'liability', NULL, 1, NULL, 0),
(8, '2100', 'VAT Payable', 'liability', NULL, 1, NULL, 0),
(9, '2200', 'Accrued Expenses', 'liability', NULL, 1, NULL, 0),
(10, '2300', 'Short-Term Loans', 'liability', NULL, 1, NULL, 0),
(11, '2900', 'Long-Term Loans', 'liability', NULL, 1, NULL, 0),
(12, '3000', 'Owner Equity', 'equity', NULL, 1, NULL, 0),
(13, '3100', 'Retained Earnings', 'equity', NULL, 1, NULL, 0),
(14, '4000', 'Sales Revenue', 'revenue', NULL, 1, NULL, 0),
(15, '4100', 'Service Revenue', 'revenue', NULL, 1, NULL, 0),
(16, '4900', 'Other Income', 'revenue', NULL, 1, NULL, 0),
(17, '5000', 'Cost of Goods Sold', 'expense', NULL, 1, NULL, 0),
(18, '6000', 'Salaries & Wages', 'expense', NULL, 1, NULL, 0),
(19, '6100', 'Rent & Utilities', 'expense', NULL, 1, NULL, 0),
(20, '6200', 'Marketing & Advertising', 'expense', NULL, 1, NULL, 0),
(21, '6300', 'Office & Admin Expenses', 'expense', NULL, 1, NULL, 0),
(22, '6900', 'Miscellaneous Expenses', 'expense', NULL, 1, NULL, 0);

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

--
-- Table structure for table `ai_reports`
--

CREATE TABLE `ai_reports` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL,
  `prompt` text DEFAULT NULL,
  `report` longtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `approvals`
--

CREATE TABLE `approvals` (
  `id` int(10) UNSIGNED NOT NULL,
  `rule_id` int(10) UNSIGNED DEFAULT NULL,
  `module` varchar(40) NOT NULL,
  `record_id` int(10) UNSIGNED NOT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `approver_id` int(10) UNSIGNED DEFAULT NULL,
  `notes` varchar(300) DEFAULT NULL,
  `decided_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `approval_rules`
--

CREATE TABLE `approval_rules` (
  `id` int(10) UNSIGNED NOT NULL,
  `module` varchar(40) NOT NULL,
  `condition` varchar(100) DEFAULT NULL,
  `threshold` decimal(14,4) DEFAULT 0.0000,
  `approver_role` varchar(40) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `app_logs`
--

CREATE TABLE `app_logs` (
  `id` int(10) UNSIGNED NOT NULL,
  `level` enum('debug','info','notice','warning','error','critical','alert','emergency') NOT NULL DEFAULT 'info',
  `channel` varchar(80) NOT NULL DEFAULT 'app',
  `message` text NOT NULL,
  `context` longtext DEFAULT NULL COMMENT 'JSON context data',
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `url` varchar(512) DEFAULT NULL,
  `user_agent` varchar(512) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Application-level log (PHP errors, warnings, events)';

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

--
-- Table structure for table `asset_depreciations`
--

CREATE TABLE `asset_depreciations` (
  `id` int(10) UNSIGNED NOT NULL,
  `asset_id` int(10) UNSIGNED NOT NULL,
  `period_year` smallint(6) NOT NULL,
  `period_month` tinyint(4) NOT NULL,
  `amount` decimal(14,4) NOT NULL,
  `book_value` decimal(14,4) NOT NULL,
  `posted` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `attendance`
--

CREATE TABLE `attendance` (
  `id` int(10) UNSIGNED NOT NULL,
  `employee_id` int(10) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `check_in` time DEFAULT NULL,
  `check_out` time DEFAULT NULL,
  `hours_worked` decimal(5,2) DEFAULT 0.00,
  `overtime` decimal(5,2) DEFAULT 0.00,
  `status` enum('present','absent','late','half_day','leave','holiday') DEFAULT 'present',
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `audit_log`
--

CREATE TABLE `audit_log` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `module` varchar(50) DEFAULT NULL,
  `action` varchar(100) DEFAULT NULL,
  `record_id` int(10) UNSIGNED DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `audit_log`
--

INSERT INTO `audit_log` (`id`, `user_id`, `module`, `action`, `record_id`, `ip`, `created_at`) VALUES
(1, 1, 'auth', 'logout', 1, '37.186.45.19', '2026-03-13 00:24:53'),
(2, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:24:56'),
(3, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:25:06'),
(4, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:25:33'),
(5, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:25:52'),
(6, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:26:50'),
(7, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:27:01'),
(8, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:41:47'),
(9, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:41:51'),
(10, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:41:58'),
(11, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:42:47'),
(12, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:42:51'),
(13, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:53:36'),
(14, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:53:43'),
(15, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:57:35'),
(16, NULL, 'auth', 'login_failed', NULL, '37.186.45.19', '2026-03-13 00:57:39'),
(17, 1, 'auth', 'login', 1, '37.186.45.19', '2026-03-13 00:58:41'),
(18, 1, 'auth', 'logout', 1, '37.186.45.19', '2026-03-13 01:00:33'),
(19, 4, 'auth', 'login', 4, '37.186.45.19', '2026-03-13 01:00:42'),
(20, 4, 'auth', 'logout', 4, '37.186.45.19', '2026-03-13 01:00:55'),
(21, 5, 'auth', 'login', 5, '37.186.45.19', '2026-03-13 01:00:59'),
(22, 5, 'auth', 'logout', 5, '37.186.45.19', '2026-03-13 01:01:36'),
(23, 7, 'auth', 'login', 7, '37.186.45.19', '2026-03-13 01:01:41'),
(24, 7, 'pos', 'shift_closed', 1, '37.186.45.19', '2026-03-13 01:02:18'),
(25, 7, 'auth', 'logout', 7, '37.186.45.19', '2026-03-13 01:02:18'),
(26, 8, 'auth', 'login', 8, '37.186.45.19', '2026-03-13 01:02:21'),
(27, 8, 'auth', 'logout', 8, '37.186.45.19', '2026-03-13 01:02:40'),
(28, 3, 'auth', 'login', 3, '37.186.45.19', '2026-03-13 01:02:46'),
(29, 3, 'auth', 'logout', 3, '37.186.45.19', '2026-03-13 01:03:45'),
(30, 5, 'auth', 'login', 5, '37.186.45.19', '2026-03-13 01:03:49'),
(31, 5, 'auth', 'logout', 5, '37.186.45.19', '2026-03-13 01:27:26'),
(32, 1, 'auth', 'login', 1, '37.186.45.19', '2026-03-13 09:56:31');

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

--
-- Table structure for table `automation_log`
--

CREATE TABLE `automation_log` (
  `id` int(10) UNSIGNED NOT NULL,
  `rule_id` int(10) UNSIGNED NOT NULL,
  `trigger` varchar(80) DEFAULT NULL,
  `status` enum('success','failed') DEFAULT 'success',
  `message` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `automation_rules`
--

CREATE TABLE `automation_rules` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `trigger` varchar(80) NOT NULL,
  `conditions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`conditions`)),
  `actions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`actions`)),
  `is_active` tinyint(1) DEFAULT 1,
  `run_count` int(10) UNSIGNED DEFAULT 0,
  `last_run` datetime DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `bank_reconciliation`
--

CREATE TABLE `bank_reconciliation` (
  `id` int(10) UNSIGNED NOT NULL,
  `bank_account_id` int(10) UNSIGNED NOT NULL,
  `period_from` date NOT NULL,
  `period_to` date NOT NULL,
  `statement_balance` decimal(14,4) DEFAULT 0.0000,
  `book_balance` decimal(14,4) DEFAULT 0.0000,
  `difference` decimal(14,4) DEFAULT 0.0000,
  `status` enum('open','reconciled') DEFAULT 'open',
  `notes` text DEFAULT NULL,
  `reconciled_by` int(10) UNSIGNED DEFAULT NULL,
  `reconciled_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `bank_transactions`
--

CREATE TABLE `bank_transactions` (
  `id` int(10) UNSIGNED NOT NULL,
  `bank_account_id` int(10) UNSIGNED NOT NULL,
  `txn_date` date NOT NULL,
  `type` enum('debit','credit') NOT NULL,
  `amount` decimal(14,4) NOT NULL,
  `balance` decimal(14,4) DEFAULT 0.0000,
  `description` varchar(300) DEFAULT NULL,
  `reference` varchar(80) DEFAULT NULL,
  `reconciled` tinyint(1) DEFAULT 0,
  `reconciled_at` datetime DEFAULT NULL,
  `journal_id` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `bill_items`
--

CREATE TABLE `bill_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `bill_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED DEFAULT NULL,
  `description` varchar(300) NOT NULL,
  `qty` decimal(12,4) DEFAULT 1.0000,
  `unit_cost` decimal(14,4) DEFAULT 0.0000,
  `tax_rate` decimal(5,2) DEFAULT 0.00,
  `line_total` decimal(14,4) DEFAULT 0.0000
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `bill_payments`
--

CREATE TABLE `bill_payments` (
  `id` int(10) UNSIGNED NOT NULL,
  `bill_id` int(10) UNSIGNED NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `amount` decimal(14,4) NOT NULL,
  `method` varchar(30) DEFAULT 'bank_transfer',
  `reference` varchar(80) DEFAULT NULL,
  `payment_date` date NOT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `branches` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(120) NOT NULL,
  `code` varchar(20) NOT NULL,
  `address` varchar(300) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `branches`
--

INSERT INTO `branches` (`id`, `name`, `code`, `address`, `phone`, `is_active`) VALUES
(1, 'HQ - Doha', 'HQ', 'West Bay, Doha, Qatar', '+974 4444 0001', 1),
(2, 'Branch - West Bay', 'WB', 'West Bay Tower, Doha', '+974 4444 0002', 1),
(3, 'Branch - Al Khor', 'AKH', 'Al Khor, Qatar', '+974 4444 0003', 1);

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

--
-- Table structure for table `brands`
--

CREATE TABLE `brands` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(80) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `budgets`
--

CREATE TABLE `budgets` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `category` varchar(80) DEFAULT NULL,
  `period_year` smallint(6) NOT NULL,
  `period_month` tinyint(4) NOT NULL,
  `budgeted_amt` decimal(14,4) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `calendar_events`
--

CREATE TABLE `calendar_events` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `start_at` datetime NOT NULL,
  `end_at` datetime NOT NULL,
  `all_day` tinyint(1) DEFAULT 0,
  `color` varchar(20) DEFAULT '#3b82f6',
  `type` enum('meeting','task','reminder','holiday','other') DEFAULT 'other',
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `ref_type` varchar(30) DEFAULT NULL,
  `ref_id` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(80) NOT NULL,
  `name_ar` varchar(80) DEFAULT NULL,
  `parent_id` int(10) UNSIGNED DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `sort_order` smallint(5) UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `name`, `name_ar`, `parent_id`, `description`, `is_active`, `sort_order`) VALUES
(1, 'Electronics', '??????????', NULL, NULL, 1, 0),
(2, 'Groceries', '?????', NULL, NULL, 1, 0),
(3, 'Building Materials', '???? ????', NULL, NULL, 1, 0),
(4, 'Office Supplies', '???????? ??????', NULL, NULL, 1, 0),
(5, 'Clothing', '?????', NULL, NULL, 1, 0),
(6, 'Food & Beverage', '???? ????????', NULL, NULL, 1, 0),
(7, 'Tools & Hardware', '????? ??????', NULL, NULL, 1, 0),
(8, 'General', '???', NULL, NULL, 1, 0);

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

--
-- Table structure for table `chat_members`
--

CREATE TABLE `chat_members` (
  `room_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `joined_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `chat_messages`
--

CREATE TABLE `chat_messages` (
  `id` int(10) UNSIGNED NOT NULL,
  `room_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `type` enum('text','file','image','system') DEFAULT 'text',
  `message` text DEFAULT NULL,
  `file_url` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `chat_rooms`
--

CREATE TABLE `chat_rooms` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `type` enum('general','department','direct','project') DEFAULT 'general',
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `chat_rooms`
--

INSERT INTO `chat_rooms` (`id`, `name`, `type`, `created_by`, `created_at`) VALUES
(1, 'General', 'general', 1, '2026-03-13 00:24:20');

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

--
-- Table structure for table `clients`
--

CREATE TABLE `clients` (
  `id` int(10) UNSIGNED NOT NULL,
  `client_no` varchar(30) NOT NULL,
  `company` varchar(200) NOT NULL,
  `first_name` varchar(80) DEFAULT NULL,
  `last_name` varchar(80) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `mobile` varchar(30) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(80) DEFAULT NULL,
  `country` varchar(60) DEFAULT 'Qatar',
  `trn` varchar(30) DEFAULT NULL,
  `currency` varchar(10) DEFAULT 'QAR',
  `payment_terms` varchar(50) DEFAULT 'net30',
  `credit_limit` decimal(14,4) DEFAULT 0.0000,
  `balance` decimal(14,4) DEFAULT 0.0000,
  `status` enum('active','inactive','blocked') DEFAULT 'active',
  `is_active` tinyint(1) DEFAULT 1,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `assigned_to` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `complaints`
--

CREATE TABLE `complaints` (
  `id` int(10) UNSIGNED NOT NULL,
  `complaint_no` varchar(30) NOT NULL,
  `subject` varchar(300) NOT NULL,
  `description` text DEFAULT NULL,
  `source` enum('customer','employee','vendor','other') DEFAULT 'customer',
  `customer_id` int(10) UNSIGNED DEFAULT NULL,
  `assigned_to` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('open','investigating','resolved','closed') DEFAULT 'open',
  `priority` enum('low','medium','high') DEFAULT 'medium',
  `resolution` text DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `customers` (
  `id` int(10) UNSIGNED NOT NULL,
  `code` varchar(25) NOT NULL,
  `name` varchar(150) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(80) DEFAULT NULL,
  `country` varchar(60) DEFAULT 'UAE',
  `trn` varchar(30) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` enum('male','female','other') DEFAULT NULL,
  `customer_group_id` int(10) UNSIGNED DEFAULT NULL,
  `price_list_id` int(10) UNSIGNED DEFAULT NULL,
  `credit_limit` decimal(14,2) DEFAULT 0.00,
  `credit_balance` decimal(14,2) DEFAULT 0.00,
  `loyalty_points` decimal(12,2) DEFAULT 0.00,
  `total_orders` int(10) UNSIGNED DEFAULT 0,
  `total_spent` decimal(14,2) DEFAULT 0.00,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `code`, `name`, `phone`, `email`, `address`, `city`, `country`, `trn`, `dob`, `gender`, `customer_group_id`, `price_list_id`, `credit_limit`, `credit_balance`, `loyalty_points`, `total_orders`, `total_spent`, `is_active`, `created_at`) VALUES
(1, 'WALKIN', 'Walk-In Customer', NULL, NULL, NULL, NULL, 'UAE', NULL, NULL, NULL, 1, NULL, 0.00, 0.00, 0.00, 0, 0.00, 1, '2026-03-13 00:24:19');

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

--
-- Table structure for table `customer_groups`
--

CREATE TABLE `customer_groups` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(80) NOT NULL,
  `discount_pct` decimal(5,2) DEFAULT 0.00,
  `price_list_id` int(10) UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `customer_groups`
--

INSERT INTO `customer_groups` (`id`, `name`, `discount_pct`, `price_list_id`, `is_active`) VALUES
(1, 'Retail', 0.00, NULL, 1),
(2, 'Wholesale', 10.00, NULL, 1),
(3, 'VIP', 15.00, NULL, 1),
(4, 'Corporate', 20.00, NULL, 1);

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

--
-- Table structure for table `customer_payments`
--

CREATE TABLE `customer_payments` (
  `id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `sale_id` int(10) UNSIGNED DEFAULT NULL,
  `amount` decimal(14,4) NOT NULL,
  `method` varchar(20) DEFAULT 'cash',
  `reference` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `deliveries`
--

CREATE TABLE `deliveries` (
  `id` int(10) UNSIGNED NOT NULL,
  `delivery_no` varchar(25) NOT NULL,
  `sale_id` int(10) UNSIGNED DEFAULT NULL,
  `customer_id` int(10) UNSIGNED DEFAULT NULL,
  `customer_name` varchar(150) DEFAULT NULL,
  `delivery_address` text DEFAULT NULL,
  `driver_id` int(10) UNSIGNED DEFAULT NULL,
  `scheduled_at` datetime DEFAULT NULL,
  `delivered_at` datetime DEFAULT NULL,
  `status` varchar(20) DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `departments`
--

CREATE TABLE `departments` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `name_ar` varchar(100) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `departments`
--

INSERT INTO `departments` (`id`, `name`, `name_ar`, `description`, `is_active`) VALUES
(1, 'Management', '???????', NULL, 1),
(2, 'Sales', '????????', NULL, 1),
(3, 'Warehouse', '????????', NULL, 1),
(4, 'Finance', '???????', NULL, 1),
(5, 'Human Resources', '??????? ???????', NULL, 1),
(6, 'IT', '????? ?????????', NULL, 1),
(7, 'Delivery', '???????', NULL, 1);

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

--
-- Table structure for table `dms_access_log`
--

CREATE TABLE `dms_access_log` (
  `id` int(10) UNSIGNED NOT NULL,
  `document_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `action` enum('view','download','upload','edit','delete','share','restore','archive') NOT NULL DEFAULT 'view',
  `ip` varchar(45) DEFAULT NULL,
  `user_agent` varchar(512) DEFAULT NULL,
  `notes` varchar(300) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `dms_batches`
--

CREATE TABLE `dms_batches` (
  `id` int(10) UNSIGNED NOT NULL,
  `batch_no` varchar(30) NOT NULL,
  `name` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `branch_id` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('open','locked','archived') NOT NULL DEFAULT 'open',
  `doc_count` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `locked_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `dms_batch_items`
--

CREATE TABLE `dms_batch_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `batch_id` int(10) UNSIGNED NOT NULL,
  `document_id` int(10) UNSIGNED NOT NULL,
  `added_by` int(10) UNSIGNED DEFAULT NULL,
  `added_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `dms_documents`
--

CREATE TABLE `dms_documents` (
  `id` int(10) UNSIGNED NOT NULL,
  `doc_no` varchar(30) NOT NULL,
  `folder_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(300) NOT NULL,
  `description` text DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(512) NOT NULL,
  `file_type` varchar(80) DEFAULT NULL,
  `file_ext` varchar(10) DEFAULT NULL,
  `file_size` int(10) UNSIGNED DEFAULT 0,
  `branch_id` int(10) UNSIGNED DEFAULT NULL,
  `department` varchar(80) DEFAULT NULL,
  `employee_id` int(10) UNSIGNED DEFAULT NULL,
  `ref_type` varchar(40) DEFAULT NULL,
  `ref_id` int(10) UNSIGNED DEFAULT NULL,
  `version` tinyint(4) NOT NULL DEFAULT 1,
  `status` enum('active','archived','deleted') NOT NULL DEFAULT 'active',
  `expiry_date` date DEFAULT NULL,
  `doc_date` date DEFAULT NULL,
  `tags` varchar(500) DEFAULT NULL,
  `is_confidential` tinyint(1) NOT NULL DEFAULT 0,
  `uploaded_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `dms_folders`
--

CREATE TABLE `dms_folders` (
  `id` int(10) UNSIGNED NOT NULL,
  `parent_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `slug` varchar(160) NOT NULL,
  `description` varchar(300) DEFAULT NULL,
  `branch_id` int(10) UNSIGNED DEFAULT NULL,
  `department` varchar(80) DEFAULT NULL,
  `color` varchar(10) DEFAULT '#3b82f6',
  `icon` varchar(40) DEFAULT 'folder',
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `dms_folders`
--

INSERT INTO `dms_folders` (`id`, `parent_id`, `name`, `slug`, `description`, `branch_id`, `department`, `color`, `icon`, `is_system`, `is_active`, `created_by`, `created_at`) VALUES
(1, NULL, 'HR Documents', 'hr-documents', NULL, NULL, NULL, '#8b5cf6', 'person-badge', 1, 1, NULL, '2026-03-12 01:29:25'),
(2, NULL, 'Finance', 'finance', NULL, NULL, NULL, '#16a34a', 'cash-coin', 1, 1, NULL, '2026-03-12 01:29:25'),
(3, NULL, 'Sales', 'sales', NULL, NULL, NULL, '#3b82f6', 'bag-check', 1, 1, NULL, '2026-03-12 01:29:25'),
(4, NULL, 'Purchasing', 'purchasing', NULL, NULL, NULL, '#f59e0b', 'cart-check', 1, 1, NULL, '2026-03-12 01:29:25'),
(5, NULL, 'Legal', 'legal', NULL, NULL, NULL, '#dc2626', 'shield-check', 1, 1, NULL, '2026-03-12 01:29:25'),
(6, NULL, 'General', 'general', NULL, NULL, NULL, '#64748b', 'folder', 1, 1, NULL, '2026-03-12 01:29:25'),
(7, 1, 'Contracts', 'hr-contracts', NULL, NULL, NULL, '#8b5cf6', 'file-text', 1, 1, NULL, '2026-03-12 01:29:25'),
(8, 1, 'Passports & IDs', 'hr-ids', NULL, NULL, NULL, '#8b5cf6', 'person-vcard', 1, 1, NULL, '2026-03-12 01:29:25'),
(9, 1, 'Visa & Residency', 'hr-visa', NULL, NULL, NULL, '#8b5cf6', 'geo-alt', 1, 1, NULL, '2026-03-12 01:29:25'),
(10, 2, 'Invoices', 'finance-invoices', NULL, NULL, NULL, '#16a34a', 'receipt', 1, 1, NULL, '2026-03-12 01:29:25'),
(11, 2, 'Bank Statements', 'finance-bank', NULL, NULL, NULL, '#16a34a', 'bank', 1, 1, NULL, '2026-03-12 01:29:25'),
(12, 2, 'Tax Documents', 'finance-tax', NULL, NULL, NULL, '#16a34a', 'calculator', 1, 1, NULL, '2026-03-12 01:29:25'),
(13, 3, 'Contracts', 'sales-contracts', NULL, NULL, NULL, '#3b82f6', 'file-earmark', 1, 1, NULL, '2026-03-12 01:29:25'),
(14, 3, 'Quotations', 'sales-quotes', NULL, NULL, NULL, '#3b82f6', 'file-text', 1, 1, NULL, '2026-03-12 01:29:25'),
(15, 4, 'Purchase Orders', 'po-docs', NULL, NULL, NULL, '#f59e0b', 'cart-plus', 1, 1, NULL, '2026-03-12 01:29:25'),
(16, 5, 'Licenses', 'legal-licenses', NULL, NULL, NULL, '#dc2626', 'award', 1, 1, NULL, '2026-03-12 01:29:25'),
(17, NULL, 'HR Documents', 'hr-documents', 'Employee contracts, visas, certificates', NULL, NULL, '#8b5cf6', 'person-badge', 1, 1, NULL, '2026-03-12 09:29:27'),
(18, NULL, 'Finance', 'finance', 'Invoices, receipts, financial records', NULL, NULL, '#16a34a', 'cash-coin', 1, 1, NULL, '2026-03-12 09:29:27'),
(19, NULL, 'Sales & Contracts', 'sales-contracts', 'Sales agreements and client contracts', NULL, NULL, '#3b82f6', 'file-text', 1, 1, NULL, '2026-03-12 09:29:27'),
(20, NULL, 'Legal', 'legal', 'Legal documents, licenses, permits', NULL, NULL, '#ef4444', 'shield-check', 1, 1, NULL, '2026-03-12 09:29:27'),
(21, NULL, 'Operations', 'operations', 'SOP, manuals, operational documents', NULL, NULL, '#f59e0b', 'gear-fill', 1, 1, NULL, '2026-03-12 09:29:27'),
(22, NULL, 'General', 'general', 'Miscellaneous documents', NULL, NULL, '#64748b', 'folder', 1, 1, NULL, '2026-03-12 09:29:27'),
(23, NULL, 'HR Documents', 'hr-documents', 'Employee contracts, visas, certificates', NULL, NULL, '#8b5cf6', 'person-badge', 1, 1, NULL, '2026-03-12 15:32:53'),
(24, NULL, 'Finance', 'finance', 'Invoices, receipts, financial records', NULL, NULL, '#16a34a', 'cash-coin', 1, 1, NULL, '2026-03-12 15:32:53'),
(25, NULL, 'Sales & Contracts', 'sales-contracts', 'Sales agreements and client contracts', NULL, NULL, '#3b82f6', 'file-text', 1, 1, NULL, '2026-03-12 15:32:53'),
(26, NULL, 'Legal', 'legal', 'Legal documents, licenses, permits', NULL, NULL, '#ef4444', 'shield-check', 1, 1, NULL, '2026-03-12 15:32:53'),
(27, NULL, 'Operations', 'operations', 'SOP, manuals, operational documents', NULL, NULL, '#f59e0b', 'gear-fill', 1, 1, NULL, '2026-03-12 15:32:53'),
(28, NULL, 'General', 'general', 'Miscellaneous documents', NULL, NULL, '#64748b', 'folder', 1, 1, NULL, '2026-03-12 15:32:53'),
(29, NULL, 'HR Documents', 'hr-documents', 'Employee contracts, visas, certificates', NULL, NULL, '#8b5cf6', 'person-badge', 1, 1, NULL, '2026-03-12 15:38:39'),
(30, NULL, 'Finance', 'finance', 'Invoices, receipts, financial records', NULL, NULL, '#16a34a', 'cash-coin', 1, 1, NULL, '2026-03-12 15:38:39'),
(31, NULL, 'Sales & Contracts', 'sales-contracts', 'Sales agreements and client contracts', NULL, NULL, '#3b82f6', 'file-text', 1, 1, NULL, '2026-03-12 15:38:39'),
(32, NULL, 'Legal', 'legal', 'Legal documents, licenses, permits', NULL, NULL, '#ef4444', 'shield-check', 1, 1, NULL, '2026-03-12 15:38:39'),
(33, NULL, 'Operations', 'operations', 'SOP, manuals, operational documents', NULL, NULL, '#f59e0b', 'gear-fill', 1, 1, NULL, '2026-03-12 15:38:39'),
(34, NULL, 'General', 'general', 'Miscellaneous documents', NULL, NULL, '#64748b', 'folder', 1, 1, NULL, '2026-03-12 15:38:39'),
(35, NULL, 'HR Documents', 'hr-documents', 'Employee contracts, visas, certificates', NULL, NULL, '#8b5cf6', 'person-badge', 1, 1, NULL, '2026-03-13 00:04:38'),
(36, NULL, 'Finance', 'finance', 'Invoices, receipts, financial records', NULL, NULL, '#16a34a', 'cash-coin', 1, 1, NULL, '2026-03-13 00:04:38'),
(37, NULL, 'Sales & Contracts', 'sales-contracts', 'Sales agreements and client contracts', NULL, NULL, '#3b82f6', 'file-text', 1, 1, NULL, '2026-03-13 00:04:38'),
(38, NULL, 'Legal', 'legal', 'Legal documents, licenses, permits', NULL, NULL, '#ef4444', 'shield-check', 1, 1, NULL, '2026-03-13 00:04:38'),
(39, NULL, 'Operations', 'operations', 'SOP, manuals, operational documents', NULL, NULL, '#f59e0b', 'gear-fill', 1, 1, NULL, '2026-03-13 00:04:38'),
(40, NULL, 'General', 'general', 'Miscellaneous documents', NULL, NULL, '#64748b', 'folder', 1, 1, NULL, '2026-03-13 00:04:38'),
(41, NULL, 'HR Documents', 'hr-documents', 'Employee contracts, visas, certificates', NULL, NULL, '#8b5cf6', 'person-badge', 1, 1, NULL, '2026-03-13 00:24:04'),
(42, NULL, 'Finance', 'finance', 'Invoices, receipts, financial records', NULL, NULL, '#16a34a', 'cash-coin', 1, 1, NULL, '2026-03-13 00:24:04'),
(43, NULL, 'Sales & Contracts', 'sales-contracts', 'Sales agreements and client contracts', NULL, NULL, '#3b82f6', 'file-text', 1, 1, NULL, '2026-03-13 00:24:04'),
(44, NULL, 'Legal', 'legal', 'Legal documents, licenses, permits', NULL, NULL, '#ef4444', 'shield-check', 1, 1, NULL, '2026-03-13 00:24:04'),
(45, NULL, 'Operations', 'operations', 'SOP, manuals, operational documents', NULL, NULL, '#f59e0b', 'gear-fill', 1, 1, NULL, '2026-03-13 00:24:04'),
(46, NULL, 'General', 'general', 'Miscellaneous documents', NULL, NULL, '#64748b', 'folder', 1, 1, NULL, '2026-03-13 00:24:04'),
(47, NULL, 'HR Documents', 'hr-documents', 'Employee contracts, visas, certificates', NULL, NULL, '#8b5cf6', 'person-badge', 1, 1, NULL, '2026-03-13 00:26:18'),
(48, NULL, 'Finance', 'finance', 'Invoices, receipts, financial records', NULL, NULL, '#16a34a', 'cash-coin', 1, 1, NULL, '2026-03-13 00:26:18'),
(49, NULL, 'Sales & Contracts', 'sales-contracts', 'Sales agreements and client contracts', NULL, NULL, '#3b82f6', 'file-text', 1, 1, NULL, '2026-03-13 00:26:18'),
(50, NULL, 'Legal', 'legal', 'Legal documents, licenses, permits', NULL, NULL, '#ef4444', 'shield-check', 1, 1, NULL, '2026-03-13 00:26:18'),
(51, NULL, 'Operations', 'operations', 'SOP, manuals, operational documents', NULL, NULL, '#f59e0b', 'gear-fill', 1, 1, NULL, '2026-03-13 00:26:18'),
(52, NULL, 'General', 'general', 'Miscellaneous documents', NULL, NULL, '#64748b', 'folder', 1, 1, NULL, '2026-03-13 00:26:18');

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

--
-- Table structure for table `ecommerce_orders`
--

CREATE TABLE `ecommerce_orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_ref` varchar(60) NOT NULL,
  `customer_id` int(10) UNSIGNED DEFAULT NULL,
  `customer_name` varchar(150) DEFAULT NULL,
  `customer_email` varchar(150) DEFAULT NULL,
  `channel` varchar(30) DEFAULT 'web',
  `item_count` tinyint(3) UNSIGNED DEFAULT 0,
  `total_amount` decimal(12,4) DEFAULT 0.0000,
  `status` varchar(20) DEFAULT 'pending',
  `platform_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`platform_data`)),
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `einvoice_log`
--

CREATE TABLE `einvoice_log` (
  `id` int(10) UNSIGNED NOT NULL,
  `ref_type` varchar(30) DEFAULT NULL,
  `ref_id` int(10) UNSIGNED DEFAULT NULL,
  `invoice_no` varchar(50) DEFAULT NULL,
  `status` varchar(30) DEFAULT 'pending',
  `response` text DEFAULT NULL,
  `qr_code` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `erp_invoices`
--

CREATE TABLE `erp_invoices` (
  `id` int(10) UNSIGNED NOT NULL,
  `invoice_no` varchar(30) NOT NULL,
  `client_id` int(10) UNSIGNED NOT NULL,
  `quote_id` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('draft','sent','viewed','partial','paid','overdue','cancelled') DEFAULT 'draft',
  `issue_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `subtotal` decimal(14,4) DEFAULT 0.0000,
  `discount_amt` decimal(14,4) DEFAULT 0.0000,
  `tax_amt` decimal(14,4) DEFAULT 0.0000,
  `grand_total` decimal(14,4) DEFAULT 0.0000,
  `amount_paid` decimal(14,4) DEFAULT 0.0000,
  `balance_due` decimal(14,4) DEFAULT 0.0000,
  `currency` varchar(10) DEFAULT 'QAR',
  `terms` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `erp_payments`
--

CREATE TABLE `erp_payments` (
  `id` int(10) UNSIGNED NOT NULL,
  `payment_no` varchar(30) NOT NULL,
  `invoice_id` int(10) UNSIGNED NOT NULL,
  `client_id` int(10) UNSIGNED NOT NULL,
  `amount` decimal(14,4) NOT NULL,
  `method` varchar(30) DEFAULT 'bank_transfer',
  `reference` varchar(80) DEFAULT NULL,
  `payment_date` date NOT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `expenses`
--

CREATE TABLE `expenses` (
  `id` int(10) UNSIGNED NOT NULL,
  `expense_no` varchar(25) NOT NULL,
  `category_id` int(10) UNSIGNED DEFAULT NULL,
  `amount` decimal(12,4) NOT NULL,
  `vendor` varchar(150) DEFAULT NULL,
  `reference` varchar(80) DEFAULT NULL,
  `method` varchar(20) DEFAULT 'cash',
  `expense_date` date NOT NULL,
  `description` text DEFAULT NULL,
  `receipt` varchar(200) DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `title` varchar(200) DEFAULT NULL,
  `status` enum('draft','submitted','approved','rejected','paid') DEFAULT 'draft',
  `submitted_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `tax_amount` decimal(12,4) DEFAULT 0.0000,
  `total_amount` decimal(12,4) DEFAULT 0.0000,
  `currency` varchar(10) DEFAULT 'QAR',
  `category` varchar(80) DEFAULT 'general',
  `project` varchar(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `expense_categories`
--

CREATE TABLE `expense_categories` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(80) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `expense_categories`
--

INSERT INTO `expense_categories` (`id`, `name`, `is_active`) VALUES
(1, 'Rent', 1),
(2, 'Utilities', 1),
(3, 'Salaries', 1),
(4, 'Marketing', 1),
(5, 'Office Supplies', 1),
(6, 'Maintenance', 1),
(7, 'Transport', 1),
(8, 'Miscellaneous', 1);

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

--
-- Table structure for table `fixed_assets`
--

CREATE TABLE `fixed_assets` (
  `id` int(10) UNSIGNED NOT NULL,
  `asset_no` varchar(30) NOT NULL,
  `name` varchar(200) NOT NULL,
  `category` varchar(80) DEFAULT NULL,
  `purchase_date` date NOT NULL,
  `purchase_cost` decimal(14,4) NOT NULL,
  `useful_life` int(11) DEFAULT 5,
  `salvage_value` decimal(14,4) DEFAULT 0.0000,
  `depreciation_method` enum('straight_line','declining') DEFAULT 'straight_line',
  `account_id` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('active','disposed','written_off') DEFAULT 'active',
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `held_sales`
--

CREATE TABLE `held_sales` (
  `id` int(10) UNSIGNED NOT NULL,
  `shift_id` int(10) UNSIGNED DEFAULT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `label` varchar(80) DEFAULT NULL,
  `cart_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`cart_data`)),
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `inventory`
--

CREATE TABLE `inventory` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `qty_on_hand` decimal(12,3) DEFAULT 0.000,
  `qty_committed` decimal(12,3) DEFAULT 0.000,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `inventory`
--

INSERT INTO `inventory` (`id`, `product_id`, `warehouse_id`, `qty_on_hand`, `qty_committed`, `updated_at`) VALUES
(1, 1, 1, 150.000, 0.000, '2026-03-13 00:24:19'),
(2, 2, 1, 200.000, 0.000, '2026-03-13 00:24:19'),
(3, 3, 1, 75.000, 0.000, '2026-03-13 00:24:19'),
(4, 4, 1, 300.000, 0.000, '2026-03-13 00:24:19'),
(5, 5, 1, 500.000, 0.000, '2026-03-13 00:24:19');

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

--
-- Table structure for table `inventory_movements`
--

CREATE TABLE `inventory_movements` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL,
  `movement_type` varchar(30) NOT NULL,
  `qty` decimal(12,3) NOT NULL,
  `balance_after` decimal(12,3) DEFAULT 0.000,
  `reference_id` int(10) UNSIGNED DEFAULT NULL,
  `reference_type` varchar(30) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `invoices` (
  `id` int(10) UNSIGNED NOT NULL,
  `invoice_no` varchar(30) NOT NULL,
  `client_id` int(10) UNSIGNED DEFAULT NULL,
  `quote_id` int(10) UNSIGNED DEFAULT NULL,
  `so_id` int(10) UNSIGNED DEFAULT NULL,
  `project_id` int(10) UNSIGNED DEFAULT NULL,
  `sale_id` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('draft','sent','viewed','partial','paid','overdue','cancelled') DEFAULT 'draft',
  `invoice_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `subtotal` decimal(14,4) DEFAULT 0.0000,
  `discount_amount` decimal(14,4) DEFAULT 0.0000,
  `tax_amount` decimal(14,4) DEFAULT 0.0000,
  `total` decimal(14,4) DEFAULT 0.0000,
  `paid_amount` decimal(14,4) DEFAULT 0.0000,
  `balance` decimal(14,4) DEFAULT 0.0000,
  `currency` varchar(10) DEFAULT 'QAR',
  `terms` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `invoice_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `invoice_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED DEFAULT NULL,
  `description` varchar(300) NOT NULL,
  `qty` decimal(12,4) DEFAULT 1.0000,
  `unit_price` decimal(14,4) DEFAULT 0.0000,
  `discount` decimal(5,2) DEFAULT 0.00,
  `tax_rate` decimal(5,2) DEFAULT 0.00,
  `line_total` decimal(14,4) DEFAULT 0.0000,
  `sort_order` smallint(6) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `journal_entries`
--

CREATE TABLE `journal_entries` (
  `id` int(10) UNSIGNED NOT NULL,
  `entry_no` varchar(25) NOT NULL,
  `entry_date` date NOT NULL,
  `description` varchar(300) DEFAULT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `status` enum('draft','posted','reversed') DEFAULT 'draft',
  `reversed_by` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `journal_lines`
--

CREATE TABLE `journal_lines` (
  `id` int(10) UNSIGNED NOT NULL,
  `entry_id` int(10) UNSIGNED NOT NULL,
  `account_id` int(10) UNSIGNED NOT NULL,
  `debit` decimal(14,4) DEFAULT 0.0000,
  `credit` decimal(14,4) DEFAULT 0.0000,
  `description` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `kb_articles`
--

CREATE TABLE `kb_articles` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(300) NOT NULL,
  `content` longtext NOT NULL,
  `category` varchar(80) DEFAULT 'general',
  `tags` varchar(300) DEFAULT NULL,
  `status` enum('draft','published','archived') DEFAULT 'draft',
  `views` int(10) UNSIGNED DEFAULT 0,
  `helpful` int(10) UNSIGNED DEFAULT 0,
  `not_helpful` int(10) UNSIGNED DEFAULT 0,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `updated_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `leads`
--

CREATE TABLE `leads` (
  `id` int(10) UNSIGNED NOT NULL,
  `lead_no` varchar(30) NOT NULL,
  `title` varchar(200) NOT NULL,
  `company` varchar(150) DEFAULT NULL,
  `contact_name` varchar(120) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `source` varchar(50) DEFAULT 'manual',
  `status` enum('new','contacted','qualified','proposal','negotiation','won','lost') DEFAULT 'new',
  `priority` enum('low','medium','high') DEFAULT 'medium',
  `value` decimal(14,4) DEFAULT 0.0000,
  `expected_close` date DEFAULT NULL,
  `owner_id` int(10) UNSIGNED DEFAULT NULL,
  `client_id` int(10) UNSIGNED DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `lost_reason` varchar(200) DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `lead_activities`
--

CREATE TABLE `lead_activities` (
  `id` int(10) UNSIGNED NOT NULL,
  `lead_id` int(10) UNSIGNED NOT NULL,
  `type` enum('call','email','meeting','note','task','demo') DEFAULT 'note',
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `done` tinyint(1) DEFAULT 0,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `leaves`
--

CREATE TABLE `leaves` (
  `id` int(10) UNSIGNED NOT NULL,
  `employee_id` int(10) UNSIGNED NOT NULL,
  `type` enum('annual','sick','emergency','unpaid','maternity','paternity','other') DEFAULT 'annual',
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `days` tinyint(3) UNSIGNED DEFAULT 1,
  `reason` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected','cancelled') DEFAULT 'pending',
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `leave_type_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `leave_types`
--

CREATE TABLE `leave_types` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(80) NOT NULL,
  `days_allowed` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `leave_types`
--

INSERT INTO `leave_types` (`id`, `name`, `days_allowed`, `is_active`) VALUES
(1, 'Annual Leave', 30, 1),
(2, 'Sick Leave', 15, 1),
(3, 'Emergency Leave', 3, 1),
(4, 'Unpaid Leave', 0, 1);

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

--
-- Table structure for table `login_attempts`
--

CREATE TABLE `login_attempts` (
  `id` int(10) UNSIGNED NOT NULL,
  `identifier` varchar(150) NOT NULL,
  `ip` varchar(45) NOT NULL,
  `attempted_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `login_attempts`
--

INSERT INTO `login_attempts` (`id`, `identifier`, `ip`, `attempted_at`) VALUES
(1, 'finance@tftech.qa', '37.186.45.19', '2026-03-13 00:53:36'),
(2, 'admin@tftech.qa', '37.186.45.19', '2026-03-13 00:53:43'),
(3, 'admin@tftech.qa', '37.186.45.19', '2026-03-13 00:57:35'),
(4, 'admin@tftech.qa', '37.186.45.19', '2026-03-13 00:57:39');

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

--
-- Table structure for table `loyalty_transactions`
--

CREATE TABLE `loyalty_transactions` (
  `id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `type` enum('earn','redeem','credit','debit','expire') DEFAULT 'earn',
  `points` decimal(10,2) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `reference_id` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `mail_queue`
--

CREATE TABLE `mail_queue` (
  `id` int(10) UNSIGNED NOT NULL,
  `to_email` varchar(200) NOT NULL,
  `to_name` varchar(150) DEFAULT NULL,
  `subject` varchar(300) NOT NULL,
  `body` longtext DEFAULT NULL,
  `cc` varchar(500) DEFAULT NULL,
  `status` enum('pending','sent','failed') DEFAULT 'pending',
  `attempts` tinyint(3) UNSIGNED DEFAULT 0,
  `last_error` text DEFAULT NULL,
  `type` varchar(40) DEFAULT NULL,
  `related_id` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `sent_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `notifications` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `role` varchar(40) DEFAULT NULL,
  `type` varchar(40) DEFAULT 'info',
  `title` varchar(200) NOT NULL,
  `message` text DEFAULT NULL,
  `link` varchar(300) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `payroll`
--

CREATE TABLE `payroll` (
  `id` int(10) UNSIGNED NOT NULL,
  `pay_no` varchar(20) NOT NULL,
  `employee_id` int(10) UNSIGNED NOT NULL,
  `period_month` tinyint(4) NOT NULL,
  `period_year` smallint(6) NOT NULL,
  `basic_salary` decimal(12,2) DEFAULT 0.00,
  `housing_allow` decimal(12,2) DEFAULT 0.00,
  `transport_allow` decimal(12,2) DEFAULT 0.00,
  `other_allow` decimal(12,2) DEFAULT 0.00,
  `overtime_pay` decimal(12,2) DEFAULT 0.00,
  `overtime_hours` decimal(5,2) DEFAULT 0.00,
  `deductions` decimal(12,2) DEFAULT 0.00,
  `advance` decimal(12,2) DEFAULT 0.00,
  `gross_salary` decimal(12,2) DEFAULT 0.00,
  `net_salary` decimal(12,2) DEFAULT 0.00,
  `working_days` tinyint(4) DEFAULT 26,
  `present_days` tinyint(4) DEFAULT 0,
  `absent_days` tinyint(4) DEFAULT 0,
  `status` enum('draft','approved','paid') DEFAULT 'draft',
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `paid_by` int(10) UNSIGNED DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `payroll`
--

INSERT INTO `payroll` (`id`, `pay_no`, `employee_id`, `period_month`, `period_year`, `basic_salary`, `housing_allow`, `transport_allow`, `other_allow`, `overtime_pay`, `overtime_hours`, `deductions`, `advance`, `gross_salary`, `net_salary`, `working_days`, `present_days`, `absent_days`, `status`, `approved_by`, `paid_at`, `paid_by`, `notes`, `created_by`, `created_at`) VALUES
(1, 'PAY-00001', 1, 4, 2026, 8000.00, 2000.00, 500.00, 0.00, 0.00, 0.00, 0.00, 0.00, 10500.00, 10500.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(2, 'PAY-00002', 2, 4, 2026, 4000.00, 1000.00, 500.00, 0.00, 0.00, 0.00, 0.00, 0.00, 5500.00, 5500.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(3, 'PAY-00003', 3, 4, 2026, 5000.00, 1500.00, 500.00, 0.00, 0.00, 0.00, 0.00, 0.00, 7000.00, 7000.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(4, 'PAY-00004', 4, 4, 2026, 6000.00, 2000.00, 500.00, 0.00, 0.00, 0.00, 0.00, 0.00, 8500.00, 8500.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(5, 'PAY-00005', 5, 4, 2026, 25000.00, 5000.00, 1000.00, 0.00, 0.00, 0.00, 0.00, 0.00, 31000.00, 31000.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(6, 'PAY-00006', 6, 4, 2026, 12000.00, 3000.00, 800.00, 0.00, 0.00, 0.00, 0.00, 0.00, 15800.00, 15800.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(7, 'PAY-00007', 7, 4, 2026, 11000.00, 3000.00, 700.00, 0.00, 0.00, 0.00, 0.00, 0.00, 14700.00, 14700.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(8, 'PAY-00008', 8, 4, 2026, 9000.00, 2500.00, 700.00, 0.00, 0.00, 0.00, 0.00, 0.00, 12200.00, 12200.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(9, 'PAY-00009', 9, 4, 2026, 10000.00, 3000.00, 700.00, 0.00, 0.00, 0.00, 0.00, 0.00, 13700.00, 13700.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(10, 'PAY-00010', 10, 4, 2026, 4500.00, 1500.00, 500.00, 0.00, 0.00, 0.00, 0.00, 0.00, 6500.00, 6500.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(11, 'PAY-00011', 11, 4, 2026, 4500.00, 1500.00, 500.00, 0.00, 0.00, 0.00, 0.00, 0.00, 6500.00, 6500.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(12, 'PAY-00012', 12, 4, 2026, 6000.00, 2000.00, 600.00, 0.00, 0.00, 0.00, 0.00, 0.00, 8600.00, 8600.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(13, 'PAY-00013', 13, 4, 2026, 9000.00, 2500.00, 700.00, 0.00, 0.00, 0.00, 0.00, 0.00, 12200.00, 12200.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(14, 'PAY-00014', 14, 4, 2026, 5500.00, 1800.00, 600.00, 0.00, 0.00, 0.00, 0.00, 0.00, 7900.00, 7900.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(15, 'PAY-00015', 15, 4, 2026, 13000.00, 3500.00, 800.00, 0.00, 0.00, 0.00, 0.00, 0.00, 17300.00, 17300.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(16, 'PAY-00016', 16, 4, 2026, 4500.00, 1200.00, 500.00, 0.00, 0.00, 0.00, 0.00, 0.00, 6200.00, 6200.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(17, 'PAY-00017', 17, 4, 2026, 5000.00, 1500.00, 500.00, 0.00, 0.00, 0.00, 0.00, 0.00, 7000.00, 7000.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(18, 'PAY-00018', 18, 4, 2026, 3800.00, 1200.00, 400.00, 0.00, 0.00, 0.00, 0.00, 0.00, 5400.00, 5400.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56'),
(19, 'PAY-00019', 19, 4, 2026, 7000.00, 2000.00, 600.00, 0.00, 0.00, 0.00, 0.00, 0.00, 9600.00, 9600.00, 26, 0, 0, 'draft', NULL, NULL, NULL, NULL, 5, '2026-03-13 01:03:56');

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

--
-- Table structure for table `period_locks`
--

CREATE TABLE `period_locks` (
  `id` int(10) UNSIGNED NOT NULL,
  `period_from` date NOT NULL,
  `period_to` date NOT NULL,
  `locked_by` int(10) UNSIGNED DEFAULT NULL,
  `locked_at` datetime DEFAULT NULL,
  `notes` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `pos_shifts`
--

CREATE TABLE `pos_shifts` (
  `id` int(10) UNSIGNED NOT NULL,
  `terminal_id` int(10) UNSIGNED DEFAULT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `branch_id` int(10) UNSIGNED DEFAULT 1,
  `opened_at` datetime NOT NULL,
  `closed_at` datetime DEFAULT NULL,
  `opening_cash` decimal(12,2) DEFAULT 0.00,
  `closing_cash` decimal(12,2) DEFAULT 0.00,
  `expected_cash` decimal(12,2) DEFAULT 0.00,
  `cash_variance` decimal(12,2) DEFAULT 0.00,
  `total_sales` decimal(14,2) DEFAULT 0.00,
  `total_returns` decimal(14,2) DEFAULT 0.00,
  `total_cash` decimal(12,2) DEFAULT 0.00,
  `total_card` decimal(12,2) DEFAULT 0.00,
  `total_wallet` decimal(12,2) DEFAULT 0.00,
  `transaction_cnt` int(10) UNSIGNED DEFAULT 0,
  `status` enum('open','closed') DEFAULT 'open',
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `pos_shifts`
--

INSERT INTO `pos_shifts` (`id`, `terminal_id`, `user_id`, `branch_id`, `opened_at`, `closed_at`, `opening_cash`, `closing_cash`, `expected_cash`, `cash_variance`, `total_sales`, `total_returns`, `total_cash`, `total_card`, `total_wallet`, `transaction_cnt`, `status`, `notes`) VALUES
(1, NULL, 7, 2, '2026-03-13 04:01:45', '2026-03-13 04:02:18', 100.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0, 'closed', NULL);

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

--
-- Table structure for table `pos_terminals`
--

CREATE TABLE `pos_terminals` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(80) NOT NULL,
  `code` varchar(20) NOT NULL,
  `branch_id` int(10) UNSIGNED DEFAULT 1,
  `warehouse_id` int(10) UNSIGNED DEFAULT 1,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `pos_terminals`
--

INSERT INTO `pos_terminals` (`id`, `name`, `code`, `branch_id`, `warehouse_id`, `is_active`) VALUES
(1, 'POS Terminal 1', 'POS-1', 1, 1, 1),
(2, 'POS Terminal 2', 'POS-2', 1, 1, 1);

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

--
-- Table structure for table `po_items`
--

CREATE TABLE `po_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `po_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED DEFAULT NULL,
  `description` varchar(300) NOT NULL,
  `qty` decimal(12,4) DEFAULT 1.0000,
  `unit_cost` decimal(14,4) DEFAULT 0.0000,
  `tax_rate` decimal(5,2) DEFAULT 0.00,
  `line_total` decimal(14,4) DEFAULT 0.0000
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `price_lists`
--

CREATE TABLE `price_lists` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(80) NOT NULL,
  `discount_pct` decimal(5,2) DEFAULT 0.00,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `price_list_items`
--

CREATE TABLE `price_list_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `price_list_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `price` decimal(14,4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `products` (
  `id` int(10) UNSIGNED NOT NULL,
  `sku` varchar(60) NOT NULL,
  `barcode` varchar(80) DEFAULT NULL,
  `name` varchar(200) NOT NULL,
  `name_ar` varchar(200) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `category_id` int(10) UNSIGNED DEFAULT NULL,
  `brand_id` int(10) UNSIGNED DEFAULT NULL,
  `unit_id` int(10) UNSIGNED DEFAULT NULL,
  `type` enum('simple','variant','bundle','service') DEFAULT 'simple',
  `cost_price` decimal(14,4) DEFAULT 0.0000,
  `retail_price` decimal(14,4) DEFAULT 0.0000,
  `wholesale_price` decimal(14,4) DEFAULT 0.0000,
  `special_price` decimal(14,4) DEFAULT 0.0000,
  `min_price` decimal(14,4) DEFAULT 0.0000,
  `max_price` decimal(14,4) DEFAULT 0.0000,
  `tax_rate` decimal(5,2) DEFAULT 5.00,
  `image` varchar(200) DEFAULT NULL,
  `track_inventory` tinyint(1) DEFAULT 1,
  `reorder_level` decimal(10,2) DEFAULT 10.00,
  `reorder_qty` decimal(10,2) DEFAULT 20.00,
  `min_sale_qty` decimal(10,2) DEFAULT 1.00,
  `is_pos` tinyint(1) DEFAULT 1,
  `is_ecommerce` tinyint(1) DEFAULT 0,
  `allow_discount` tinyint(1) DEFAULT 1,
  `track_serial` tinyint(1) DEFAULT 0,
  `track_batch` tinyint(1) DEFAULT 0,
  `preferred_supplier_id` int(10) UNSIGNED DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `price1` decimal(14,4) GENERATED ALWAYS AS (`retail_price`) VIRTUAL,
  `price2` decimal(14,4) GENERATED ALWAYS AS (`wholesale_price`) VIRTUAL,
  `price3` decimal(14,4) GENERATED ALWAYS AS (`special_price`) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `sku`, `barcode`, `name`, `name_ar`, `description`, `category_id`, `brand_id`, `unit_id`, `type`, `cost_price`, `retail_price`, `wholesale_price`, `special_price`, `min_price`, `max_price`, `tax_rate`, `image`, `track_inventory`, `reorder_level`, `reorder_qty`, `min_sale_qty`, `is_pos`, `is_ecommerce`, `allow_discount`, `track_serial`, `track_batch`, `preferred_supplier_id`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'SKU-0001', '6901234567890', 'Premium Coffee 500g', '???? ????? 500 ??', NULL, 2, NULL, 2, 'simple', 8.0000, 15.0000, 12.0000, 0.0000, 0.0000, 0.0000, 5.00, NULL, 1, 20.00, 50.00, 1.00, 1, 0, 1, 0, 0, NULL, 1, '2026-03-13 00:24:19', '2026-03-13 00:24:19'),
(2, 'SKU-0002', '6901234567891', 'A4 Paper Ream', '??? A4', NULL, 4, NULL, 8, 'simple', 10.0000, 18.0000, 14.0000, 0.0000, 0.0000, 0.0000, 5.00, NULL, 1, 30.00, 60.00, 1.00, 1, 0, 1, 0, 0, NULL, 1, '2026-03-13 00:24:19', '2026-03-13 00:24:19'),
(3, 'SKU-0003', '6901234567892', 'USB Flash Drive 32GB', '????? USB 32 ????', NULL, 1, NULL, 1, 'simple', 15.0000, 35.0000, 28.0000, 0.0000, 0.0000, 0.0000, 5.00, NULL, 1, 10.00, 25.00, 1.00, 1, 0, 1, 0, 0, NULL, 1, '2026-03-13 00:24:19', '2026-03-13 00:24:19'),
(4, 'SKU-0004', '6901234567893', 'Cement Bag 50kg', '??? ????? 50 ??', NULL, 3, NULL, 15, 'simple', 9.0000, 14.0000, 11.0000, 0.0000, 0.0000, 0.0000, 0.00, NULL, 1, 50.00, 100.00, 1.00, 1, 0, 1, 0, 0, NULL, 1, '2026-03-13 00:24:19', '2026-03-13 00:24:19'),
(5, 'SKU-0005', '6901234567894', 'Mineral Water 1.5L', '???? ?????? 1.5 ?', NULL, 6, NULL, 4, 'simple', 0.5000, 1.5000, 1.0000, 0.0000, 0.0000, 0.0000, 0.00, NULL, 1, 100.00, 200.00, 1.00, 1, 0, 1, 0, 0, NULL, 1, '2026-03-13 00:24:19', '2026-03-13 00:24:19');

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

--
-- Table structure for table `product_variants`
--

CREATE TABLE `product_variants` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `sku` varchar(80) NOT NULL,
  `barcode` varchar(80) DEFAULT NULL,
  `attributes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attributes`)),
  `cost_price` decimal(14,4) DEFAULT 0.0000,
  `retail_price` decimal(14,4) DEFAULT 0.0000,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `projects` (
  `id` int(10) UNSIGNED NOT NULL,
  `project_no` varchar(30) NOT NULL,
  `name` varchar(200) NOT NULL,
  `client_id` int(10) UNSIGNED DEFAULT NULL,
  `manager_id` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('planning','active','on_hold','completed','cancelled') DEFAULT 'planning',
  `priority` enum('low','medium','high','critical') DEFAULT 'medium',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `budget` decimal(14,4) DEFAULT 0.0000,
  `currency` varchar(10) DEFAULT 'QAR',
  `hourly_rate` decimal(10,2) DEFAULT 0.00,
  `billed_amount` decimal(14,4) DEFAULT 0.0000,
  `description` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `progress` tinyint(3) UNSIGNED DEFAULT 0,
  `location` varchar(300) DEFAULT NULL,
  `site_address` varchar(500) DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `category` varchar(80) DEFAULT NULL,
  `project_type` varchar(80) DEFAULT NULL,
  `tags` varchar(300) DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `archived_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `project_audits`
--

CREATE TABLE `project_audits` (
  `id` int(10) UNSIGNED NOT NULL,
  `audit_no` varchar(30) NOT NULL,
  `project_id` int(10) UNSIGNED NOT NULL,
  `type` enum('quality','safety','compliance','internal') DEFAULT 'internal',
  `title` varchar(200) NOT NULL,
  `audit_date` date DEFAULT NULL,
  `auditor_id` int(10) UNSIGNED DEFAULT NULL,
  `overall_result` enum('pass','fail','partial','pending') DEFAULT 'pending',
  `score` decimal(5,2) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('draft','submitted','approved') DEFAULT 'draft',
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `project_audit_items`
--

CREATE TABLE `project_audit_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `audit_id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `item` varchar(300) NOT NULL,
  `result` enum('pass','fail','na','pending') DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `sort_order` smallint(5) UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `project_completion`
--

CREATE TABLE `project_completion` (
  `id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED NOT NULL,
  `checklist_items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`checklist_items`)),
  `final_inspection_by` int(10) UNSIGNED DEFAULT NULL,
  `inspection_date` date DEFAULT NULL,
  `inspection_notes` text DEFAULT NULL,
  `client_approved_by` varchar(150) DEFAULT NULL,
  `client_approval_date` date DEFAULT NULL,
  `client_signature` varchar(300) DEFAULT NULL,
  `completion_notes` text DEFAULT NULL,
  `certificate_no` varchar(50) DEFAULT NULL,
  `status` enum('pending','inspecting','client_approval','completed') DEFAULT 'pending',
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `project_costs`
--

CREATE TABLE `project_costs` (
  `id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED NOT NULL,
  `cost_type` enum('labour','material','equipment','transport','facility','other') DEFAULT 'other',
  `description` varchar(300) NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `date` date NOT NULL,
  `qty` decimal(10,3) DEFAULT 1.000,
  `unit` varchar(30) DEFAULT NULL,
  `unit_cost` decimal(12,4) DEFAULT 0.0000,
  `amount` decimal(14,4) DEFAULT 0.0000,
  `employee_id` int(10) UNSIGNED DEFAULT NULL,
  `product_id` int(10) UNSIGNED DEFAULT NULL,
  `expense_id` int(10) UNSIGNED DEFAULT NULL,
  `supplier_id` int(10) UNSIGNED DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `project_members`
--

CREATE TABLE `project_members` (
  `id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `role` varchar(50) DEFAULT 'member'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `project_milestones`
--

CREATE TABLE `project_milestones` (
  `id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(200) NOT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('pending','done') DEFAULT 'pending',
  `title` varchar(200) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `amount` decimal(14,4) DEFAULT 0.0000,
  `completed_at` datetime DEFAULT NULL,
  `sort_order` smallint(5) UNSIGNED DEFAULT 0,
  `completion_pct` tinyint(3) UNSIGNED DEFAULT 0,
  `completion_notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `project_site_visits`
--

CREATE TABLE `project_site_visits` (
  `id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED NOT NULL,
  `visit_no` varchar(30) NOT NULL,
  `visit_date` date NOT NULL,
  `visit_time` time DEFAULT NULL,
  `engineer_id` int(10) UNSIGNED DEFAULT NULL,
  `attendees` text DEFAULT NULL,
  `purpose` varchar(300) DEFAULT NULL,
  `observations` text DEFAULT NULL,
  `issues_found` text DEFAULT NULL,
  `next_action` text DEFAULT NULL,
  `work_done` text DEFAULT NULL,
  `materials_used` text DEFAULT NULL,
  `weather` varchar(50) DEFAULT NULL,
  `site_condition` enum('good','fair','poor') DEFAULT 'good',
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `status` enum('scheduled','completed','cancelled') DEFAULT 'scheduled',
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `project_visit_files`
--

CREATE TABLE `project_visit_files` (
  `id` int(10) UNSIGNED NOT NULL,
  `visit_id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED NOT NULL,
  `dms_doc_id` int(10) UNSIGNED DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` varchar(100) DEFAULT NULL,
  `file_ext` varchar(10) DEFAULT NULL,
  `file_size` int(10) UNSIGNED DEFAULT 0,
  `caption` varchar(300) DEFAULT NULL,
  `is_photo` tinyint(1) DEFAULT 0,
  `uploaded_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `project_visit_issues`
--

CREATE TABLE `project_visit_issues` (
  `id` int(10) UNSIGNED NOT NULL,
  `visit_id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED NOT NULL,
  `issue_no` varchar(20) DEFAULT NULL,
  `description` text NOT NULL,
  `severity` enum('low','medium','high','critical') DEFAULT 'medium',
  `status` enum('open','in_progress','resolved','closed') DEFAULT 'open',
  `assigned_to` int(10) UNSIGNED DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `promotions`
--

CREATE TABLE `promotions` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `code` varchar(40) DEFAULT NULL,
  `type` enum('coupon','auto','bogo','bundle','flash') DEFAULT 'coupon',
  `discount_type` enum('percent','fixed','free_shipping') DEFAULT 'percent',
  `discount_value` decimal(10,4) DEFAULT 0.0000,
  `min_order_amt` decimal(12,2) DEFAULT 0.00,
  `max_discount` decimal(12,2) DEFAULT 0.00,
  `usage_limit` int(10) UNSIGNED DEFAULT 0,
  `times_used` int(10) UNSIGNED DEFAULT 0,
  `per_customer` tinyint(4) DEFAULT 1,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `applies_to` varchar(20) DEFAULT 'all',
  `customer_group` int(10) UNSIGNED DEFAULT NULL,
  `min_qty` tinyint(4) DEFAULT 0,
  `buy_qty` tinyint(4) DEFAULT 0,
  `get_qty` tinyint(4) DEFAULT 0,
  `get_discount` decimal(5,2) DEFAULT 100.00,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `purchases`
--

CREATE TABLE `purchases` (
  `id` int(10) UNSIGNED NOT NULL,
  `po_no` varchar(25) NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `warehouse_id` int(10) UNSIGNED DEFAULT 1,
  `status` varchar(20) DEFAULT 'draft',
  `payment_status` varchar(20) DEFAULT 'unpaid',
  `subtotal` decimal(14,4) DEFAULT 0.0000,
  `tax_amt` decimal(12,4) DEFAULT 0.0000,
  `grand_total` decimal(14,4) DEFAULT 0.0000,
  `amount_paid` decimal(14,4) DEFAULT 0.0000,
  `balance_due` decimal(14,4) DEFAULT 0.0000,
  `due_date` date DEFAULT NULL,
  `ref_no` varchar(60) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `purchase_items`
--

CREATE TABLE `purchase_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `purchase_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `qty_ordered` decimal(12,3) NOT NULL,
  `qty_received` decimal(12,3) DEFAULT 0.000,
  `unit_cost` decimal(14,4) DEFAULT 0.0000,
  `line_total` decimal(14,4) DEFAULT 0.0000
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `purchase_orders`
--

CREATE TABLE `purchase_orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `po_no` varchar(30) NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `status` enum('draft','sent','partial','received','cancelled') DEFAULT 'draft',
  `order_date` date NOT NULL,
  `expected_date` date DEFAULT NULL,
  `subtotal` decimal(14,4) DEFAULT 0.0000,
  `tax_amt` decimal(14,4) DEFAULT 0.0000,
  `grand_total` decimal(14,4) DEFAULT 0.0000,
  `currency` varchar(10) DEFAULT 'QAR',
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `quotes`
--

CREATE TABLE `quotes` (
  `id` int(10) UNSIGNED NOT NULL,
  `quote_no` varchar(30) NOT NULL,
  `client_id` int(10) UNSIGNED DEFAULT NULL,
  `lead_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `status` enum('draft','sent','viewed','approved','rejected','expired') DEFAULT 'draft',
  `issue_date` date NOT NULL,
  `expiry_date` date DEFAULT NULL,
  `subtotal` decimal(14,4) DEFAULT 0.0000,
  `discount_amt` decimal(14,4) DEFAULT 0.0000,
  `tax_amt` decimal(14,4) DEFAULT 0.0000,
  `grand_total` decimal(14,4) DEFAULT 0.0000,
  `currency` varchar(10) DEFAULT 'QAR',
  `terms` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `quote_items`
--

CREATE TABLE `quote_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `quote_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED DEFAULT NULL,
  `description` varchar(300) NOT NULL,
  `qty` decimal(12,4) DEFAULT 1.0000,
  `unit_price` decimal(14,4) DEFAULT 0.0000,
  `discount` decimal(5,2) DEFAULT 0.00,
  `tax_rate` decimal(5,2) DEFAULT 0.00,
  `line_total` decimal(14,4) DEFAULT 0.0000,
  `sort_order` smallint(6) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `recurring_invoices`
--

CREATE TABLE `recurring_invoices` (
  `id` int(10) UNSIGNED NOT NULL,
  `client_id` int(10) UNSIGNED NOT NULL,
  `title` varchar(200) DEFAULT NULL,
  `amount` decimal(14,4) NOT NULL,
  `tax_amt` decimal(14,4) DEFAULT 0.0000,
  `frequency` enum('weekly','monthly','quarterly','yearly') DEFAULT 'monthly',
  `next_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('active','paused','ended') DEFAULT 'active',
  `template_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`template_data`)),
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `retention_invoices`
--

CREATE TABLE `retention_invoices` (
  `id` int(10) UNSIGNED NOT NULL,
  `retention_no` varchar(30) NOT NULL,
  `so_id` int(10) UNSIGNED DEFAULT NULL,
  `client_id` int(10) UNSIGNED NOT NULL,
  `amount` decimal(14,4) NOT NULL,
  `status` enum('held','released','invoiced') DEFAULT 'held',
  `due_date` date DEFAULT NULL,
  `released_at` datetime DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `roles` (
  `id` tinyint(3) UNSIGNED NOT NULL,
  `slug` varchar(40) NOT NULL,
  `name` varchar(80) NOT NULL,
  `dashboard` varchar(200) NOT NULL DEFAULT '/modules/admin/dashboard.php',
  `color` varchar(20) DEFAULT '#3b82f6',
  `is_system` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `roles` (`id`, `slug`, `name`, `dashboard`, `color`, `is_system`) VALUES
(9, 'admin', 'Administrator', '/modules/admin/dashboard', '#ef4444', 1),
(10, 'manager', 'Manager', '/modules/admin/dashboard', '#f59e0b', 1),
(11, 'cashier', 'Cashier', '/modules/pos/terminal', '#16a34a', 1),
(12, 'warehouse', 'Warehouse', '/modules/warehouse/dashboard', '#3b82f6', 1),
(13, 'accountant', 'Accountant', '/modules/finance/dashboard', '#06b6d4', 1),
(14, 'hr', 'HR Manager', '/modules/hr/dashboard', '#8b5cf6', 1),
(15, 'delivery', 'Delivery', '/modules/delivery/dashboard', '#64748b', 1),
(16, 'viewer', 'Viewer', '/modules/reports/dashboard', '#94a3b8', 1);

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

--
-- Table structure for table `sales`
--

CREATE TABLE `sales` (
  `id` int(10) UNSIGNED NOT NULL,
  `order_no` varchar(25) NOT NULL,
  `order_type` varchar(20) DEFAULT 'sale',
  `customer_id` int(10) UNSIGNED DEFAULT NULL,
  `branch_id` int(10) UNSIGNED DEFAULT 1,
  `pos_shift_id` int(10) UNSIGNED DEFAULT NULL,
  `status` varchar(20) DEFAULT 'completed',
  `payment_status` varchar(20) DEFAULT 'unpaid',
  `subtotal` decimal(14,4) DEFAULT 0.0000,
  `discount_pct` decimal(5,2) DEFAULT 0.00,
  `discount_amt` decimal(12,4) DEFAULT 0.0000,
  `tax_rate` decimal(5,2) DEFAULT 5.00,
  `tax_amt` decimal(12,4) DEFAULT 0.0000,
  `grand_total` decimal(14,4) DEFAULT 0.0000,
  `amount_paid` decimal(14,4) DEFAULT 0.0000,
  `balance_due` decimal(14,4) DEFAULT 0.0000,
  `promotion_id` int(10) UNSIGNED DEFAULT NULL,
  `promo_code` varchar(40) DEFAULT NULL,
  `loyalty_pts_used` decimal(10,2) DEFAULT 0.00,
  `loyalty_pts_earned` decimal(10,2) DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `sales_orders`
--

CREATE TABLE `sales_orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `so_no` varchar(30) NOT NULL,
  `client_id` int(10) UNSIGNED NOT NULL,
  `quote_id` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('draft','confirmed','processing','shipped','delivered','cancelled','on_hold') DEFAULT 'draft',
  `approval_status` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `order_date` date NOT NULL,
  `delivery_date` date DEFAULT NULL,
  `subtotal` decimal(14,4) DEFAULT 0.0000,
  `discount_amt` decimal(14,4) DEFAULT 0.0000,
  `tax_amt` decimal(14,4) DEFAULT 0.0000,
  `grand_total` decimal(14,4) DEFAULT 0.0000,
  `amount_paid` decimal(14,4) DEFAULT 0.0000,
  `balance_due` decimal(14,4) DEFAULT 0.0000,
  `currency` varchar(10) DEFAULT 'QAR',
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `sale_items`
--

CREATE TABLE `sale_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `sale_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `product_name` varchar(200) DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL,
  `unit_price` decimal(14,4) NOT NULL,
  `cost_price` decimal(14,4) DEFAULT 0.0000,
  `line_discount` decimal(5,2) DEFAULT 0.00,
  `tax_rate` decimal(5,2) DEFAULT 5.00,
  `line_total` decimal(14,4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `sale_payments`
--

CREATE TABLE `sale_payments` (
  `id` int(10) UNSIGNED NOT NULL,
  `sale_id` int(10) UNSIGNED NOT NULL,
  `method` varchar(20) NOT NULL DEFAULT 'cash',
  `amount` decimal(12,4) NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `sale_returns`
--

CREATE TABLE `sale_returns` (
  `id` int(10) UNSIGNED NOT NULL,
  `return_no` varchar(25) NOT NULL,
  `sale_id` int(10) UNSIGNED NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `refund_method` varchar(20) DEFAULT 'cash',
  `refund_amt` decimal(12,4) DEFAULT 0.0000,
  `status` varchar(20) DEFAULT 'completed',
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `sale_return_items`
--

CREATE TABLE `sale_return_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `return_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `qty` decimal(12,3) NOT NULL,
  `unit_price` decimal(14,4) DEFAULT 0.0000
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `serial_numbers`
--

CREATE TABLE `serial_numbers` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `serial_no` varchar(100) NOT NULL,
  `warehouse_id` int(10) UNSIGNED DEFAULT 1,
  `status` enum('available','sold','reserved','returned') DEFAULT 'available',
  `sale_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `settings` (
  `skey` varchar(120) NOT NULL,
  `sval` text DEFAULT NULL,
  `grp` varchar(50) DEFAULT 'general',
  `label` varchar(120) DEFAULT NULL,
  `type` varchar(20) DEFAULT 'text',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`skey`, `sval`, `grp`, `label`, `type`, `updated_at`) VALUES
('app_timezone', 'Asia/Qatar', 'system', 'Timezone', 'text', '2026-03-13 00:24:20'),
('company_address', 'West Bay, Doha, Qatar', 'company', 'Address', 'text', '2026-03-13 00:24:20'),
('company_email', 'info@tftech.qa', 'company', 'Email', 'text', '2026-03-13 00:24:20'),
('company_name', 'TF ERP', 'company', 'Company Name', 'text', '2026-03-13 00:24:20'),
('company_name_ar', '?? ?? ???????', 'company', 'Company Name AR', 'text', '2026-03-13 00:24:20'),
('company_phone', '+974 4444 0000', 'company', 'Phone', 'text', '2026-03-13 00:24:20'),
('company_trn', '100012345600003', 'company', 'TRN', 'text', '2026-03-13 00:24:20'),
('currency_code', 'QAR', 'general', 'Currency Code', 'text', '2026-03-13 00:24:20'),
('currency_symbol', 'QAR', 'general', 'Currency Symbol', 'text', '2026-03-13 00:24:20'),
('date_format', 'd M Y', 'system', 'Date Format', 'text', '2026-03-13 00:24:19'),
('default_vat_rate', '5', 'finance', 'Default VAT %', 'text', '2026-03-13 00:24:19'),
('ecom_enabled', '0', 'ecom', 'E-Commerce Enabled', 'text', '2026-03-13 00:24:19'),
('invoice_prefix', 'INV-', 'general', 'Invoice Prefix', 'text', '2026-03-13 00:24:19'),
('items_per_page', '25', 'system', 'Items Per Page', 'text', '2026-03-13 00:24:19'),
('lockout_minutes', '15', 'security', 'Lockout Minutes', 'text', '2026-03-13 00:24:19'),
('loyalty_bronze_min', '100', 'loyalty', 'Bronze Min', 'text', '2026-03-13 00:24:19'),
('loyalty_earn_rate', '1', 'loyalty', 'Earn Rate', 'text', '2026-03-13 00:24:19'),
('loyalty_enabled', '1', 'loyalty', 'Loyalty Enabled', 'text', '2026-03-13 00:24:19'),
('loyalty_expiry_days', '365', 'loyalty', 'Expiry Days', 'text', '2026-03-13 00:24:19'),
('loyalty_gold_min', '2000', 'loyalty', 'Gold Min', 'text', '2026-03-13 00:24:19'),
('loyalty_min_redeem', '100', 'loyalty', 'Min Redeem', 'text', '2026-03-13 00:24:19'),
('loyalty_platinum_min', '10000', 'loyalty', 'Platinum Min', 'text', '2026-03-13 00:24:19'),
('loyalty_redeem_value', '0.01', 'loyalty', 'Redeem Value', 'text', '2026-03-13 00:24:19'),
('loyalty_silver_min', '500', 'loyalty', 'Silver Min', 'text', '2026-03-13 00:24:19'),
('max_login_attempts', '5', 'security', 'Max Attempts', 'text', '2026-03-13 00:24:19'),
('notify_leave', '1', 'notify', 'Leave Alert', 'text', '2026-03-13 00:24:19'),
('notify_low_stock', '1', 'notify', 'Low Stock Alert', 'text', '2026-03-13 00:24:19'),
('notify_visa_expiry', '1', 'notify', 'Visa Expiry Alert', 'text', '2026-03-13 00:24:19'),
('pos_allow_negative_stock', '0', 'pos', 'Allow Negative Stock', 'text', '2026-03-13 00:24:19'),
('pos_auto_print_receipt', '1', 'pos', 'Auto Print Receipt', 'text', '2026-03-13 00:24:19'),
('pos_default_tax_rate', '5', 'pos', 'Default Tax Rate', 'text', '2026-03-13 00:24:19'),
('pos_idle_timeout', '900', 'pos', 'Idle Timeout', 'text', '2026-03-13 00:24:19'),
('pos_max_discount', '20', 'pos', 'Max Discount %', 'text', '2026-03-13 00:24:19'),
('pos_require_pin', '1', 'pos', 'Require PIN', 'text', '2026-03-13 00:24:19'),
('pos_tax_mode', 'exclusive', 'pos', 'Tax Mode', 'text', '2026-03-13 00:24:19'),
('po_prefix', 'PO-', 'general', 'PO Prefix', 'text', '2026-03-13 00:24:19'),
('receipt_footer', 'Thank you for visiting TF ERP!', 'pos', 'Receipt Footer', 'text', '2026-03-13 00:24:20'),
('session_lifetime', '28800', 'security', 'Session Lifetime', 'text', '2026-03-13 00:24:19');

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

--
-- Table structure for table `sla_rules`
--

CREATE TABLE `sla_rules` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `priority` varchar(20) NOT NULL,
  `response_hours` int(11) DEFAULT 4,
  `resolution_hours` int(11) DEFAULT 24,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `so_items`
--

CREATE TABLE `so_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `so_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED DEFAULT NULL,
  `description` varchar(300) NOT NULL,
  `qty` decimal(12,4) DEFAULT 1.0000,
  `unit_price` decimal(14,4) DEFAULT 0.0000,
  `discount` decimal(5,2) DEFAULT 0.00,
  `tax_rate` decimal(5,2) DEFAULT 0.00,
  `line_total` decimal(14,4) DEFAULT 0.0000
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `stock_batches`
--

CREATE TABLE `stock_batches` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `batch_no` varchar(80) NOT NULL,
  `warehouse_id` int(10) UNSIGNED DEFAULT 1,
  `qty` decimal(12,3) DEFAULT 0.000,
  `expiry_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `stock_transfers`
--

CREATE TABLE `stock_transfers` (
  `id` int(10) UNSIGNED NOT NULL,
  `transfer_no` varchar(25) NOT NULL,
  `from_wh` int(10) UNSIGNED NOT NULL,
  `to_wh` int(10) UNSIGNED NOT NULL,
  `status` enum('pending','in_transit','received','cancelled') DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `dispatched_at` datetime DEFAULT NULL,
  `dispatched_by` int(10) UNSIGNED DEFAULT NULL,
  `received_at` datetime DEFAULT NULL,
  `received_by` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `stock_transfer_items`
--

CREATE TABLE `stock_transfer_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `transfer_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `qty_requested` decimal(12,3) DEFAULT 0.000,
  `qty_sent` decimal(12,3) DEFAULT 0.000,
  `qty_received` decimal(12,3) DEFAULT 0.000
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `supplier_payments`
--

CREATE TABLE `supplier_payments` (
  `id` int(10) UNSIGNED NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `purchase_id` int(10) UNSIGNED DEFAULT NULL,
  `amount` decimal(14,4) NOT NULL,
  `method` varchar(20) DEFAULT 'bank',
  `reference` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `tasks`
--

CREATE TABLE `tasks` (
  `id` int(10) UNSIGNED NOT NULL,
  `task_no` varchar(30) NOT NULL,
  `title` varchar(300) NOT NULL,
  `description` text DEFAULT NULL,
  `project_id` int(10) UNSIGNED DEFAULT NULL,
  `assigned_to` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('todo','in_progress','review','done','cancelled') DEFAULT 'todo',
  `priority` enum('low','medium','high','critical') DEFAULT 'medium',
  `due_date` date DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `assigned_by` int(10) UNSIGNED DEFAULT NULL,
  `board_order` int(10) UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `task_attachments`
--

CREATE TABLE `task_attachments` (
  `id` int(10) UNSIGNED NOT NULL,
  `task_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `filename` varchar(255) NOT NULL,
  `filepath` varchar(500) NOT NULL,
  `filesize` int(10) UNSIGNED DEFAULT 0,
  `mime_type` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `task_comments`
--

CREATE TABLE `task_comments` (
  `id` int(10) UNSIGNED NOT NULL,
  `task_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `comment` text NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `tax_classes`
--

CREATE TABLE `tax_classes` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(60) NOT NULL,
  `rate` decimal(5,2) NOT NULL DEFAULT 5.00,
  `is_default` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tax_classes`
--

INSERT INTO `tax_classes` (`id`, `name`, `rate`, `is_default`) VALUES
(1, 'Standard Rate (5%)', 5.00, 1),
(2, 'Zero Rated (0%)', 0.00, 0),
(3, 'Exempt', 0.00, 0);

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

--
-- Table structure for table `tickets`
--

CREATE TABLE `tickets` (
  `id` int(10) UNSIGNED NOT NULL,
  `ticket_no` varchar(30) NOT NULL,
  `subject` varchar(300) NOT NULL,
  `description` text DEFAULT NULL,
  `type` enum('bug','feature','support','billing','other') DEFAULT 'support',
  `priority` enum('low','medium','high','critical') DEFAULT 'medium',
  `status` enum('open','in_progress','resolved','closed','cancelled') DEFAULT 'open',
  `customer_id` int(10) UNSIGNED DEFAULT NULL,
  `assigned_to` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `ticket_attachments`
--

CREATE TABLE `ticket_attachments` (
  `id` int(10) UNSIGNED NOT NULL,
  `ticket_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `filename` varchar(255) NOT NULL,
  `filepath` varchar(500) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `ticket_comments`
--

CREATE TABLE `ticket_comments` (
  `id` int(10) UNSIGNED NOT NULL,
  `ticket_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `comment` text NOT NULL,
  `is_internal` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `timesheets`
--

CREATE TABLE `timesheets` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `project_id` int(10) UNSIGNED DEFAULT NULL,
  `task_id` int(10) UNSIGNED DEFAULT NULL,
  `work_date` date NOT NULL,
  `hours` decimal(5,2) DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `status` enum('draft','submitted','approved','rejected') DEFAULT 'draft',
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `log_date` date DEFAULT NULL,
  `is_billable` tinyint(1) DEFAULT 1,
  `hourly_rate` decimal(10,2) DEFAULT 0.00,
  `amount` decimal(12,2) DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `todos`
--

CREATE TABLE `todos` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `title` varchar(300) NOT NULL,
  `done` tinyint(1) DEFAULT 0,
  `priority` tinyint(4) DEFAULT 2,
  `due_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `sort_order` smallint(5) UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `units` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(40) NOT NULL,
  `symbol` varchar(10) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `units`
--

INSERT INTO `units` (`id`, `name`, `symbol`, `is_active`) VALUES
(1, 'Piece', 'pcs', 1),
(2, 'Kilogram', 'kg', 1),
(3, 'Gram', 'g', 1),
(4, 'Litre', 'L', 1),
(5, 'Millilitre', 'ml', 1),
(6, 'Metre', 'm', 1),
(7, 'Square Metre', 'm?', 1),
(8, 'Box', 'box', 1),
(9, 'Carton', 'ctn', 1),
(10, 'Dozen', 'doz', 1),
(11, 'Pair', 'pair', 1),
(12, 'Set', 'set', 1),
(13, 'Pack', 'pk', 1),
(14, 'Roll', 'roll', 1),
(15, 'Bag', 'bag', 1);

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

--
-- Table structure for table `vat_returns`
--

CREATE TABLE `vat_returns` (
  `id` int(10) UNSIGNED NOT NULL,
  `period_from` date NOT NULL,
  `period_to` date NOT NULL,
  `output_vat` decimal(14,4) DEFAULT 0.0000,
  `input_vat` decimal(14,4) DEFAULT 0.0000,
  `net_vat` decimal(14,4) DEFAULT 0.0000,
  `status` enum('draft','submitted','paid') DEFAULT 'draft',
  `submitted_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `vendor_bills`
--

CREATE TABLE `vendor_bills` (
  `id` int(10) UNSIGNED NOT NULL,
  `bill_no` varchar(30) NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `po_id` int(10) UNSIGNED DEFAULT NULL,
  `bill_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('draft','pending','partial','paid','overdue','cancelled') DEFAULT 'pending',
  `subtotal` decimal(14,4) DEFAULT 0.0000,
  `tax_amt` decimal(14,4) DEFAULT 0.0000,
  `total` decimal(14,4) DEFAULT 0.0000,
  `balance` decimal(14,4) DEFAULT 0.0000,
  `currency` varchar(10) DEFAULT 'QAR',
  `ref_no` varchar(80) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `vendor_credits`
--

CREATE TABLE `vendor_credits` (
  `id` int(10) UNSIGNED NOT NULL,
  `credit_no` varchar(30) NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `bill_id` int(10) UNSIGNED DEFAULT NULL,
  `amount` decimal(14,4) NOT NULL,
  `balance` decimal(14,4) NOT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('open','partial','applied','cancelled') DEFAULT 'open',
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `warehouses`
--

CREATE TABLE `warehouses` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(120) NOT NULL,
  `code` varchar(20) NOT NULL,
  `branch_id` int(10) UNSIGNED DEFAULT 1,
  `address` varchar(300) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `warehouses`
--

INSERT INTO `warehouses` (`id`, `name`, `code`, `branch_id`, `address`, `is_active`) VALUES
(1, 'Main Warehouse Doha', 'WH-DOH', 1, 'Industrial Area, Doha', 1),
(2, 'Warehouse West Bay', 'WH-WB', 2, 'West Bay, Doha', 1),
(3, 'Warehouse Al Khor', 'WH-AKH', 3, 'Al Khor, Qatar', 1);

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

--
-- Table structure for table `wh_locations`
--

CREATE TABLE `wh_locations` (
  `id` int(10) UNSIGNED NOT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL,
  `location_code` varchar(40) NOT NULL,
  `zone` varchar(20) DEFAULT NULL,
  `aisle` varchar(20) DEFAULT NULL,
  `rack` varchar(20) DEFAULT NULL,
  `bin` varchar(20) DEFAULT NULL,
  `description` varchar(200) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `accounts`
--
ALTER TABLE `accounts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`),
  ADD KEY `idx_type` (`type`);

--
-- Indexes for table `ai_reports`
--
ALTER TABLE `ai_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`);

--
-- Indexes for table `approvals`
--
ALTER TABLE `approvals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_module` (`module`,`record_id`);

--
-- Indexes for table `approval_rules`
--
ALTER TABLE `approval_rules`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `app_logs`
--
ALTER TABLE `app_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_apl_level` (`level`),
  ADD KEY `idx_apl_channel` (`channel`),
  ADD KEY `idx_apl_created_at` (`created_at`),
  ADD KEY `idx_apl_user_id` (`user_id`);

--
-- Indexes for table `asset_depreciations`
--
ALTER TABLE `asset_depreciations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_asset` (`asset_id`);

--
-- Indexes for table `attendance`
--
ALTER TABLE `attendance`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `emp_date` (`employee_id`,`date`),
  ADD KEY `idx_date` (`date`);

--
-- Indexes for table `audit_log`
--
ALTER TABLE `audit_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_module` (`module`),
  ADD KEY `idx_ts` (`created_at`);

--
-- Indexes for table `automation_log`
--
ALTER TABLE `automation_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_rule` (`rule_id`);

--
-- Indexes for table `automation_rules`
--
ALTER TABLE `automation_rules`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `bank_reconciliation`
--
ALTER TABLE `bank_reconciliation`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_account` (`bank_account_id`);

--
-- Indexes for table `bank_transactions`
--
ALTER TABLE `bank_transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_account` (`bank_account_id`),
  ADD KEY `idx_date` (`txn_date`);

--
-- Indexes for table `bill_items`
--
ALTER TABLE `bill_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_bill` (`bill_id`);

--
-- Indexes for table `bill_payments`
--
ALTER TABLE `bill_payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_bill` (`bill_id`);

--
-- Indexes for table `branches`
--
ALTER TABLE `branches`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`);

--
-- Indexes for table `brands`
--
ALTER TABLE `brands`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `budgets`
--
ALTER TABLE `budgets`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_period` (`period_year`,`period_month`);

--
-- Indexes for table `calendar_events`
--
ALTER TABLE `calendar_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_dates` (`start_at`,`end_at`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `chat_members`
--
ALTER TABLE `chat_members`
  ADD PRIMARY KEY (`room_id`,`user_id`);

--
-- Indexes for table `chat_messages`
--
ALTER TABLE `chat_messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_room` (`room_id`),
  ADD KEY `idx_user` (`user_id`);

--
-- Indexes for table `chat_rooms`
--
ALTER TABLE `chat_rooms`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `clients`
--
ALTER TABLE `clients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `client_no` (`client_no`),
  ADD KEY `idx_company` (`company`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_clients_active` (`is_active`);

--
-- Indexes for table `complaints`
--
ALTER TABLE `complaints`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `complaint_no` (`complaint_no`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`);

--
-- Indexes for table `customer_groups`
--
ALTER TABLE `customer_groups`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `customer_payments`
--
ALTER TABLE `customer_payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_customer` (`customer_id`);

--
-- Indexes for table `deliveries`
--
ALTER TABLE `deliveries`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `delivery_no` (`delivery_no`),
  ADD KEY `idx_driver` (`driver_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `departments`
--
ALTER TABLE `departments`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `dms_access_log`
--
ALTER TABLE `dms_access_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_dal_doc` (`document_id`),
  ADD KEY `idx_dal_user` (`user_id`),
  ADD KEY `idx_dal_action` (`action`),
  ADD KEY `idx_dal_created` (`created_at`);

--
-- Indexes for table `dms_batches`
--
ALTER TABLE `dms_batches`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_dms_batch_no` (`batch_no`),
  ADD KEY `idx_dmb_branch` (`branch_id`),
  ADD KEY `idx_dmb_status` (`status`);

--
-- Indexes for table `dms_batch_items`
--
ALTER TABLE `dms_batch_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_dms_batch_doc` (`batch_id`,`document_id`);

--
-- Indexes for table `dms_documents`
--
ALTER TABLE `dms_documents`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_dms_doc_no` (`doc_no`),
  ADD KEY `idx_dmd_folder` (`folder_id`),
  ADD KEY `idx_dmd_branch` (`branch_id`),
  ADD KEY `idx_dmd_employee` (`employee_id`),
  ADD KEY `idx_dmd_status` (`status`),
  ADD KEY `idx_dmd_created` (`created_at`),
  ADD KEY `idx_ref` (`ref_type`,`ref_id`);

--
-- Indexes for table `dms_folders`
--
ALTER TABLE `dms_folders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_dmf_parent` (`parent_id`),
  ADD KEY `idx_dmf_branch` (`branch_id`);

--
-- Indexes for table `ecommerce_orders`
--
ALTER TABLE `ecommerce_orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `einvoice_log`
--
ALTER TABLE `einvoice_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ref` (`ref_type`,`ref_id`);

--
-- Indexes for table `erp_invoices`
--
ALTER TABLE `erp_invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `invoice_no` (`invoice_no`),
  ADD KEY `idx_client` (`client_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_due` (`due_date`);

--
-- Indexes for table `erp_payments`
--
ALTER TABLE `erp_payments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `payment_no` (`payment_no`),
  ADD KEY `idx_invoice` (`invoice_id`),
  ADD KEY `idx_client` (`client_id`);

--
-- Indexes for table `expenses`
--
ALTER TABLE `expenses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `expense_no` (`expense_no`),
  ADD KEY `idx_date` (`expense_date`),
  ADD KEY `idx_cat` (`category_id`);

--
-- Indexes for table `expense_categories`
--
ALTER TABLE `expense_categories`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `fixed_assets`
--
ALTER TABLE `fixed_assets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `asset_no` (`asset_no`);

--
-- Indexes for table `held_sales`
--
ALTER TABLE `held_sales`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `inventory`
--
ALTER TABLE `inventory`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `prod_wh` (`product_id`,`warehouse_id`);

--
-- Indexes for table `inventory_movements`
--
ALTER TABLE `inventory_movements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product` (`product_id`),
  ADD KEY `idx_wh` (`warehouse_id`),
  ADD KEY `idx_created` (`created_at`);

--
-- Indexes for table `invoices`
--
ALTER TABLE `invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `invoice_no` (`invoice_no`),
  ADD KEY `idx_client` (`client_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_date` (`invoice_date`);

--
-- Indexes for table `invoice_items`
--
ALTER TABLE `invoice_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_inv` (`invoice_id`);

--
-- Indexes for table `journal_entries`
--
ALTER TABLE `journal_entries`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `entry_no` (`entry_no`),
  ADD KEY `idx_date` (`entry_date`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `journal_lines`
--
ALTER TABLE `journal_lines`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_entry` (`entry_id`),
  ADD KEY `idx_account` (`account_id`);

--
-- Indexes for table `kb_articles`
--
ALTER TABLE `kb_articles`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `kb_articles` ADD FULLTEXT KEY `ft_kb` (`title`,`content`,`tags`);

--
-- Indexes for table `leads`
--
ALTER TABLE `leads`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `lead_no` (`lead_no`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_owner` (`owner_id`);

--
-- Indexes for table `lead_activities`
--
ALTER TABLE `lead_activities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_lead` (`lead_id`);

--
-- Indexes for table `leaves`
--
ALTER TABLE `leaves`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_emp` (`employee_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `leave_types`
--
ALTER TABLE `leave_types`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `login_attempts`
--
ALTER TABLE `login_attempts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ident` (`identifier`),
  ADD KEY `idx_ip` (`ip`);

--
-- Indexes for table `loyalty_transactions`
--
ALTER TABLE `loyalty_transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_customer` (`customer_id`);

--
-- Indexes for table `mail_queue`
--
ALTER TABLE `mail_queue`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created` (`created_at`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_role` (`role`),
  ADD KEY `idx_read` (`is_read`);

--
-- Indexes for table `payroll`
--
ALTER TABLE `payroll`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `pay_no` (`pay_no`),
  ADD UNIQUE KEY `emp_period` (`employee_id`,`period_year`,`period_month`);

--
-- Indexes for table `period_locks`
--
ALTER TABLE `period_locks`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `pos_shifts`
--
ALTER TABLE `pos_shifts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `pos_terminals`
--
ALTER TABLE `pos_terminals`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`);

--
-- Indexes for table `po_items`
--
ALTER TABLE `po_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_po` (`po_id`);

--
-- Indexes for table `price_lists`
--
ALTER TABLE `price_lists`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `price_list_items`
--
ALTER TABLE `price_list_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `pl_prod` (`price_list_id`,`product_id`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `sku` (`sku`),
  ADD KEY `idx_sku` (`sku`),
  ADD KEY `idx_barcode` (`barcode`),
  ADD KEY `idx_cat` (`category_id`);

--
-- Indexes for table `product_variants`
--
ALTER TABLE `product_variants`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `sku` (`sku`),
  ADD KEY `idx_prod` (`product_id`);

--
-- Indexes for table `projects`
--
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `project_no` (`project_no`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_client` (`client_id`);

--
-- Indexes for table `project_audits`
--
ALTER TABLE `project_audits`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `audit_no` (`audit_no`),
  ADD KEY `idx_project` (`project_id`),
  ADD KEY `idx_date` (`audit_date`);

--
-- Indexes for table `project_audit_items`
--
ALTER TABLE `project_audit_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_audit` (`audit_id`);

--
-- Indexes for table `project_completion`
--
ALTER TABLE `project_completion`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `project_id` (`project_id`);

--
-- Indexes for table `project_costs`
--
ALTER TABLE `project_costs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_project` (`project_id`),
  ADD KEY `idx_type` (`cost_type`),
  ADD KEY `idx_date` (`date`);

--
-- Indexes for table `project_members`
--
ALTER TABLE `project_members`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_pm` (`project_id`,`user_id`);

--
-- Indexes for table `project_milestones`
--
ALTER TABLE `project_milestones`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_project` (`project_id`);

--
-- Indexes for table `project_site_visits`
--
ALTER TABLE `project_site_visits`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `visit_no` (`visit_no`),
  ADD KEY `idx_project` (`project_id`),
  ADD KEY `idx_date` (`visit_date`),
  ADD KEY `idx_engineer` (`engineer_id`);

--
-- Indexes for table `project_visit_files`
--
ALTER TABLE `project_visit_files`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_visit` (`visit_id`),
  ADD KEY `idx_project` (`project_id`);

--
-- Indexes for table `project_visit_issues`
--
ALTER TABLE `project_visit_issues`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_visit` (`visit_id`),
  ADD KEY `idx_project` (`project_id`);

--
-- Indexes for table `promotions`
--
ALTER TABLE `promotions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_code` (`code`),
  ADD KEY `idx_active` (`is_active`);

--
-- Indexes for table `purchases`
--
ALTER TABLE `purchases`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `po_no` (`po_no`),
  ADD KEY `idx_supplier` (`supplier_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `purchase_items`
--
ALTER TABLE `purchase_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_purchase` (`purchase_id`);

--
-- Indexes for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `po_no` (`po_no`),
  ADD KEY `idx_supplier` (`supplier_id`);

--
-- Indexes for table `quotes`
--
ALTER TABLE `quotes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `quote_no` (`quote_no`),
  ADD KEY `idx_client` (`client_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `quote_items`
--
ALTER TABLE `quote_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_quote` (`quote_id`);

--
-- Indexes for table `recurring_invoices`
--
ALTER TABLE `recurring_invoices`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_client` (`client_id`),
  ADD KEY `idx_next` (`next_date`);

--
-- Indexes for table `retention_invoices`
--
ALTER TABLE `retention_invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `retention_no` (`retention_no`),
  ADD KEY `idx_client` (`client_id`);

--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`);

--
-- Indexes for table `sales`
--
ALTER TABLE `sales`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `order_no` (`order_no`),
  ADD KEY `idx_customer` (`customer_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created` (`created_at`);

--
-- Indexes for table `sales_orders`
--
ALTER TABLE `sales_orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `so_no` (`so_no`),
  ADD KEY `idx_client` (`client_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `sale_items`
--
ALTER TABLE `sale_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_sale` (`sale_id`),
  ADD KEY `idx_product` (`product_id`);

--
-- Indexes for table `sale_payments`
--
ALTER TABLE `sale_payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_sale` (`sale_id`);

--
-- Indexes for table `sale_returns`
--
ALTER TABLE `sale_returns`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `return_no` (`return_no`);

--
-- Indexes for table `sale_return_items`
--
ALTER TABLE `sale_return_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_return` (`return_id`);

--
-- Indexes for table `serial_numbers`
--
ALTER TABLE `serial_numbers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `prod_serial` (`product_id`,`serial_no`);

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`skey`);

--
-- Indexes for table `sla_rules`
--
ALTER TABLE `sla_rules`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `so_items`
--
ALTER TABLE `so_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_so` (`so_id`);

--
-- Indexes for table `stock_batches`
--
ALTER TABLE `stock_batches`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product` (`product_id`);

--
-- Indexes for table `stock_transfers`
--
ALTER TABLE `stock_transfers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `transfer_no` (`transfer_no`),
  ADD KEY `idx_from` (`from_wh`),
  ADD KEY `idx_to` (`to_wh`);

--
-- Indexes for table `stock_transfer_items`
--
ALTER TABLE `stock_transfer_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_transfer` (`transfer_id`);

--
-- Indexes for table `supplier_payments`
--
ALTER TABLE `supplier_payments`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tasks`
--
ALTER TABLE `tasks`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `task_no` (`task_no`),
  ADD KEY `idx_assigned` (`assigned_to`),
  ADD KEY `idx_project` (`project_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_tasks_assignedby` (`assigned_by`);

--
-- Indexes for table `task_attachments`
--
ALTER TABLE `task_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_task` (`task_id`);

--
-- Indexes for table `task_comments`
--
ALTER TABLE `task_comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_task` (`task_id`);

--
-- Indexes for table `tax_classes`
--
ALTER TABLE `tax_classes`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tickets`
--
ALTER TABLE `tickets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `ticket_no` (`ticket_no`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_assigned` (`assigned_to`);

--
-- Indexes for table `ticket_attachments`
--
ALTER TABLE `ticket_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ticket` (`ticket_id`);

--
-- Indexes for table `ticket_comments`
--
ALTER TABLE `ticket_comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ticket` (`ticket_id`);

--
-- Indexes for table `timesheets`
--
ALTER TABLE `timesheets`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`),
  ADD KEY `idx_project` (`project_id`),
  ADD KEY `idx_date` (`work_date`);

--
-- Indexes for table `todos`
--
ALTER TABLE `todos`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user` (`user_id`);

--
-- Indexes for table `units`
--
ALTER TABLE `units`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `vat_returns`
--
ALTER TABLE `vat_returns`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `vendor_bills`
--
ALTER TABLE `vendor_bills`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `bill_no` (`bill_no`),
  ADD KEY `idx_supplier` (`supplier_id`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `vendor_credits`
--
ALTER TABLE `vendor_credits`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `credit_no` (`credit_no`),
  ADD KEY `idx_supplier` (`supplier_id`);

--
-- Indexes for table `warehouses`
--
ALTER TABLE `warehouses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`);

--
-- Indexes for table `wh_locations`
--
ALTER TABLE `wh_locations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `wh_code` (`warehouse_id`,`location_code`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `accounts`
--
ALTER TABLE `accounts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT for table `ai_reports`
--
ALTER TABLE `ai_reports`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `approvals`
--
ALTER TABLE `approvals`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `approval_rules`
--
ALTER TABLE `approval_rules`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `app_logs`
--
ALTER TABLE `app_logs`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `asset_depreciations`
--
ALTER TABLE `asset_depreciations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `attendance`
--
ALTER TABLE `attendance`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `audit_log`
--
ALTER TABLE `audit_log`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=33;

--
-- AUTO_INCREMENT for table `automation_log`
--
ALTER TABLE `automation_log`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `automation_rules`
--
ALTER TABLE `automation_rules`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bank_reconciliation`
--
ALTER TABLE `bank_reconciliation`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bank_transactions`
--
ALTER TABLE `bank_transactions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bill_items`
--
ALTER TABLE `bill_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bill_payments`
--
ALTER TABLE `bill_payments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `branches`
--
ALTER TABLE `branches`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `brands`
--
ALTER TABLE `brands`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `budgets`
--
ALTER TABLE `budgets`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `calendar_events`
--
ALTER TABLE `calendar_events`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `chat_messages`
--
ALTER TABLE `chat_messages`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `chat_rooms`
--
ALTER TABLE `chat_rooms`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `clients`
--
ALTER TABLE `clients`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `complaints`
--
ALTER TABLE `complaints`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `customer_groups`
--
ALTER TABLE `customer_groups`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `customer_payments`
--
ALTER TABLE `customer_payments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `deliveries`
--
ALTER TABLE `deliveries`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `departments`
--
ALTER TABLE `departments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `dms_access_log`
--
ALTER TABLE `dms_access_log`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `dms_batches`
--
ALTER TABLE `dms_batches`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `dms_batch_items`
--
ALTER TABLE `dms_batch_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `dms_documents`
--
ALTER TABLE `dms_documents`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `dms_folders`
--
ALTER TABLE `dms_folders`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=53;

--
-- AUTO_INCREMENT for table `ecommerce_orders`
--
ALTER TABLE `ecommerce_orders`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `einvoice_log`
--
ALTER TABLE `einvoice_log`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `erp_invoices`
--
ALTER TABLE `erp_invoices`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `erp_payments`
--
ALTER TABLE `erp_payments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `expenses`
--
ALTER TABLE `expenses`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `expense_categories`
--
ALTER TABLE `expense_categories`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `fixed_assets`
--
ALTER TABLE `fixed_assets`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `held_sales`
--
ALTER TABLE `held_sales`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `inventory`
--
ALTER TABLE `inventory`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `inventory_movements`
--
ALTER TABLE `inventory_movements`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `invoices`
--
ALTER TABLE `invoices`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `invoice_items`
--
ALTER TABLE `invoice_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `journal_entries`
--
ALTER TABLE `journal_entries`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `journal_lines`
--
ALTER TABLE `journal_lines`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `kb_articles`
--
ALTER TABLE `kb_articles`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `leads`
--
ALTER TABLE `leads`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `lead_activities`
--
ALTER TABLE `lead_activities`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `leaves`
--
ALTER TABLE `leaves`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `leave_types`
--
ALTER TABLE `leave_types`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `login_attempts`
--
ALTER TABLE `login_attempts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `loyalty_transactions`
--
ALTER TABLE `loyalty_transactions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `mail_queue`
--
ALTER TABLE `mail_queue`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payroll`
--
ALTER TABLE `payroll`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

--
-- AUTO_INCREMENT for table `period_locks`
--
ALTER TABLE `period_locks`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `pos_shifts`
--
ALTER TABLE `pos_shifts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `pos_terminals`
--
ALTER TABLE `pos_terminals`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `po_items`
--
ALTER TABLE `po_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `price_lists`
--
ALTER TABLE `price_lists`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `price_list_items`
--
ALTER TABLE `price_list_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `product_variants`
--
ALTER TABLE `product_variants`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `projects`
--
ALTER TABLE `projects`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_audits`
--
ALTER TABLE `project_audits`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_audit_items`
--
ALTER TABLE `project_audit_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_completion`
--
ALTER TABLE `project_completion`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_costs`
--
ALTER TABLE `project_costs`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_members`
--
ALTER TABLE `project_members`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_milestones`
--
ALTER TABLE `project_milestones`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_site_visits`
--
ALTER TABLE `project_site_visits`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_visit_files`
--
ALTER TABLE `project_visit_files`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_visit_issues`
--
ALTER TABLE `project_visit_issues`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `promotions`
--
ALTER TABLE `promotions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchases`
--
ALTER TABLE `purchases`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchase_items`
--
ALTER TABLE `purchase_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `quotes`
--
ALTER TABLE `quotes`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `quote_items`
--
ALTER TABLE `quote_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `recurring_invoices`
--
ALTER TABLE `recurring_invoices`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `retention_invoices`
--
ALTER TABLE `retention_invoices`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
  MODIFY `id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `sales`
--
ALTER TABLE `sales`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sales_orders`
--
ALTER TABLE `sales_orders`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sale_items`
--
ALTER TABLE `sale_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sale_payments`
--
ALTER TABLE `sale_payments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sale_returns`
--
ALTER TABLE `sale_returns`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sale_return_items`
--
ALTER TABLE `sale_return_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `serial_numbers`
--
ALTER TABLE `serial_numbers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sla_rules`
--
ALTER TABLE `sla_rules`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `so_items`
--
ALTER TABLE `so_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `stock_batches`
--
ALTER TABLE `stock_batches`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `stock_transfers`
--
ALTER TABLE `stock_transfers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `stock_transfer_items`
--
ALTER TABLE `stock_transfer_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_payments`
--
ALTER TABLE `supplier_payments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tasks`
--
ALTER TABLE `tasks`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `task_attachments`
--
ALTER TABLE `task_attachments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `task_comments`
--
ALTER TABLE `task_comments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tax_classes`
--
ALTER TABLE `tax_classes`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `tickets`
--
ALTER TABLE `tickets`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ticket_attachments`
--
ALTER TABLE `ticket_attachments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ticket_comments`
--
ALTER TABLE `ticket_comments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `timesheets`
--
ALTER TABLE `timesheets`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `todos`
--
ALTER TABLE `todos`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `units`
--
ALTER TABLE `units`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `vat_returns`
--
ALTER TABLE `vat_returns`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `vendor_bills`
--
ALTER TABLE `vendor_bills`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `vendor_credits`
--
ALTER TABLE `vendor_credits`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `warehouses`
--
ALTER TABLE `warehouses`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `wh_locations`
--
ALTER TABLE `wh_locations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

