-- FM ERP — Fresh installation database
-- Schema only + minimal seed (roles, SLA, settings, one super_admin user)
-- No demo facilities, work orders, assets, or other users
--
-- Import: mysql -u USER -p DATABASE < app/Database/SQL/fm_fresh_install.sql
-- Login:  admin@fmerp.com / password  (change after first login)
--
-- Server: MariaDB 10.6+ / MySQL 8.0+

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

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

-- Drop existing tables (fresh install)
DROP TABLE IF EXISTS `wo_materials`;
DROP TABLE IF EXISTS `wo_labor`;
DROP TABLE IF EXISTS `wo_comments`;
DROP TABLE IF EXISTS `wo_chat_messages`;
DROP TABLE IF EXISTS `wo_attachments`;
DROP TABLE IF EXISTS `wo_approvals`;
DROP TABLE IF EXISTS `work_orders`;
DROP TABLE IF EXISTS `vendors`;
DROP TABLE IF EXISTS `utility_readings`;
DROP TABLE IF EXISTS `user_sessions`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `unit_checklists`;
DROP TABLE IF EXISTS `units`;
DROP TABLE IF EXISTS `system_settings`;
DROP TABLE IF EXISTS `stock_movements`;
DROP TABLE IF EXISTS `sla_rules`;
DROP TABLE IF EXISTS `roles`;
DROP TABLE IF EXISTS `rfq_vendors`;
DROP TABLE IF EXISTS `rfq_quotations`;
DROP TABLE IF EXISTS `rfq`;
DROP TABLE IF EXISTS `reimbursements`;
DROP TABLE IF EXISTS `purchase_requests`;
DROP TABLE IF EXISTS `purchase_orders`;
DROP TABLE IF EXISTS `petty_cash`;
DROP TABLE IF EXISTS `notifications`;
DROP TABLE IF EXISTS `maintenance_requests`;
DROP TABLE IF EXISTS `maintenance_costing`;
DROP TABLE IF EXISTS `job_cards`;
DROP TABLE IF EXISTS `jc_materials`;
DROP TABLE IF EXISTS `jc_attachments`;
DROP TABLE IF EXISTS `invoices`;
DROP TABLE IF EXISTS `inventory_items`;
DROP TABLE IF EXISTS `inspection_items`;
DROP TABLE IF EXISTS `inspection_checklists`;
DROP TABLE IF EXISTS `incidents`;
DROP TABLE IF EXISTS `helpdesk_tickets`;
DROP TABLE IF EXISTS `helpdesk_feedback`;
DROP TABLE IF EXISTS `grn_items`;
DROP TABLE IF EXISTS `grn`;
DROP TABLE IF EXISTS `facilities`;
DROP TABLE IF EXISTS `expenses`;
DROP TABLE IF EXISTS `estimation_items`;
DROP TABLE IF EXISTS `estimations`;
DROP TABLE IF EXISTS `employee_breaks`;
DROP TABLE IF EXISTS `employees`;
DROP TABLE IF EXISTS `documents`;
DROP TABLE IF EXISTS `contracts`;
DROP TABLE IF EXISTS `compliance_documents`;
DROP TABLE IF EXISTS `compliance_audits`;
DROP TABLE IF EXISTS `company_users`;
DROP TABLE IF EXISTS `company_roles`;
DROP TABLE IF EXISTS `companies`;
DROP TABLE IF EXISTS `attendance`;
DROP TABLE IF EXISTS `asset_meter_readings`;
DROP TABLE IF EXISTS `assets`;
DROP TABLE IF EXISTS `activity_logs`;
DROP TABLE IF EXISTS `login_attempts`;
DROP TABLE IF EXISTS `invoice_payments`;
-- Table structures
CREATE TABLE `activity_logs` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `action` varchar(100) NOT NULL,
  `module` varchar(50) NOT NULL,
  `record_id` int(10) UNSIGNED DEFAULT NULL,
  `description` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `assets` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `facility_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(200) NOT NULL,
  `asset_code` varchar(50) NOT NULL,
  `category` enum('hvac','elevator','electrical','plumbing','fire_safety','security','it','civil','other') NOT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `serial_number` varchar(100) DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `purchase_cost` decimal(12,2) DEFAULT NULL,
  `warranty_expiry` date DEFAULT NULL,
  `amc_expiry` date DEFAULT NULL,
  `location_in_facility` varchar(200) DEFAULT NULL,
  `status` enum('active','under_maintenance','retired','disposed') NOT NULL DEFAULT 'active',
  `health_score` tinyint(3) UNSIGNED NOT NULL DEFAULT 100 COMMENT '0–100',
  `last_maintenance` date DEFAULT NULL,
  `next_maintenance` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `asset_meter_readings` (
  `id` int(10) UNSIGNED NOT NULL,
  `asset_id` int(10) UNSIGNED NOT NULL,
  `wo_id` int(10) UNSIGNED DEFAULT NULL,
  `reading_type` varchar(100) NOT NULL DEFAULT 'hours',
  `reading_value` decimal(12,2) NOT NULL,
  `reading_date` date NOT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `recorded_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `attendance` (
  `id` int(10) UNSIGNED NOT NULL,
  `employee_id` int(10) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `check_in` datetime DEFAULT NULL,
  `check_out` datetime DEFAULT NULL,
  `status` enum('present','absent','late','half_day','leave') NOT NULL DEFAULT 'present',
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `hours_worked` decimal(5,2) DEFAULT NULL,
  `overtime_hrs` decimal(5,2) DEFAULT NULL,
  `early_reason` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `companies` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(200) NOT NULL,
  `code` varchar(20) NOT NULL,
  `address` text DEFAULT NULL,
  `contact_person` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `vat_number` varchar(50) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `company_roles` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `role_name` varchar(50) NOT NULL,
  `permissions` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `company_users` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `role` varchar(50) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `compliance_audits` (
  `id` int(10) UNSIGNED NOT NULL,
  `facility_id` int(10) UNSIGNED NOT NULL,
  `audit_type` varchar(100) NOT NULL,
  `audit_date` date NOT NULL,
  `score` tinyint(3) UNSIGNED DEFAULT NULL,
  `findings` text DEFAULT NULL,
  `status` enum('open','in_progress','closed','passed','failed') NOT NULL DEFAULT 'open',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `compliance_documents` (
  `id` int(10) UNSIGNED NOT NULL,
  `facility_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `doc_type` varchar(100) NOT NULL,
  `issue_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `status` enum('valid','expiring','expired') NOT NULL DEFAULT 'valid',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `contracts` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `facility_id` int(10) UNSIGNED NOT NULL,
  `contract_number` varchar(30) NOT NULL,
  `client_name` varchar(200) NOT NULL,
  `contract_type` enum('fm_services','amc','cleaning','security','it_support','other') NOT NULL DEFAULT 'fm_services',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `value` decimal(14,2) NOT NULL,
  `payment_terms` varchar(200) DEFAULT NULL,
  `status` enum('active','expired','terminated','draft') NOT NULL DEFAULT 'active',
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `client_email` varchar(100) DEFAULT NULL,
  `client_mobile` varchar(30) DEFAULT NULL,
  `unit_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `documents` (
  `id` int(10) UNSIGNED NOT NULL,
  `facility_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `doc_type` varchar(80) NOT NULL DEFAULT 'general',
  `description` text DEFAULT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `status` enum('valid','expiring','expired') NOT NULL DEFAULT 'valid',
  `uploaded_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `employees` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `user_id` int(10) UNSIGNED DEFAULT NULL,
  `facility_id` int(10) UNSIGNED DEFAULT NULL,
  `emp_code` varchar(20) NOT NULL,
  `department` varchar(100) NOT NULL,
  `designation` varchar(100) NOT NULL,
  `shift_start` time NOT NULL DEFAULT '08:00:00',
  `shift_end` time NOT NULL DEFAULT '17:00:00',
  `hourly_rate` decimal(8,2) NOT NULL DEFAULT 0.00,
  `hire_date` date DEFAULT NULL,
  `status` enum('active','on_leave','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `employee_breaks` (
  `id` int(10) UNSIGNED NOT NULL,
  `employee_id` int(10) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `break_start` datetime NOT NULL,
  `break_end` datetime DEFAULT NULL,
  `duration_mins` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `estimations` (
  `id` int(10) UNSIGNED NOT NULL,
  `est_number` varchar(30) NOT NULL,
  `facility_id` int(10) UNSIGNED NOT NULL,
  `wo_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `labor_cost` decimal(12,2) DEFAULT 0.00,
  `material_cost` decimal(12,2) DEFAULT 0.00,
  `other_cost` decimal(12,2) DEFAULT 0.00,
  `subtotal` decimal(12,2) DEFAULT 0.00,
  `vat_rate` decimal(5,2) DEFAULT 0.00,
  `vat_amount` decimal(12,2) DEFAULT 0.00,
  `total` decimal(12,2) DEFAULT 0.00,
  `status` enum('draft','pending_approval','approved','rejected','converted') DEFAULT 'draft',
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `revision` tinyint(3) UNSIGNED DEFAULT 1,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `estimation_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `est_id` int(10) UNSIGNED NOT NULL,
  `type` enum('labor','material','other') DEFAULT 'material',
  `description` varchar(300) NOT NULL,
  `quantity` decimal(10,2) DEFAULT 1.00,
  `unit_cost` decimal(12,2) DEFAULT 0.00,
  `total_cost` decimal(12,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `expenses` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `facility_id` int(10) UNSIGNED NOT NULL,
  `work_order_id` int(10) UNSIGNED DEFAULT NULL,
  `category` enum('labor','spare_parts','vendor','utility','administrative','emergency','other') NOT NULL DEFAULT 'other',
  `description` varchar(500) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'QAR',
  `expense_date` date NOT NULL,
  `receipt_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `approved_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `facilities` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(200) NOT NULL,
  `code` varchar(20) NOT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(100) NOT NULL,
  `country` varchar(100) NOT NULL DEFAULT 'Qatar',
  `manager_id` int(10) UNSIGNED DEFAULT NULL,
  `area_sqm` decimal(10,2) DEFAULT NULL,
  `floors` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `status` enum('active','inactive','under_maintenance') NOT NULL DEFAULT 'active',
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `total_units` int(10) UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `grn` (
  `id` int(10) UNSIGNED NOT NULL,
  `grn_number` varchar(30) NOT NULL,
  `po_id` int(10) UNSIGNED NOT NULL,
  `received_date` date NOT NULL,
  `received_by` int(10) UNSIGNED NOT NULL,
  `status` enum('partial','complete','rejected') DEFAULT 'partial',
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `grn_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `grn_id` int(10) UNSIGNED NOT NULL,
  `pr_id` int(10) UNSIGNED DEFAULT NULL,
  `item_id` int(10) UNSIGNED NOT NULL,
  `received_qty` int(10) UNSIGNED DEFAULT 0,
  `notes` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `helpdesk_feedback` (
  `id` int(10) UNSIGNED NOT NULL,
  `request_id` int(10) UNSIGNED NOT NULL,
  `rating` tinyint(3) UNSIGNED NOT NULL,
  `comment` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `helpdesk_tickets` (
`id` int(10) unsigned
,`ticket_number` varchar(30)
,`facility_id` int(10) unsigned
,`requester_name` varchar(150)
,`email` varchar(150)
,`requester_email` varchar(150)
,`requester_phone` varchar(30)
,`category` varchar(100)
,`description` text
,`priority` enum('critical','high','medium','low')
,`status` enum('pending','reviewed','converted','rejected')
,`image_path` varchar(255)
,`reviewed_by` int(10) unsigned
,`reviewed_at` datetime
,`converted_to_wo` int(10) unsigned
,`created_at` datetime
,`updated_at` datetime
);

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

--
-- Table structure for table `incidents`
--

CREATE TABLE `incidents` (
  `id` int(10) UNSIGNED NOT NULL,
  `facility_id` int(10) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `incident_date` date NOT NULL,
  `severity` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `status` enum('open','investigating','resolved','closed') NOT NULL DEFAULT 'open',
  `reported_by` int(10) UNSIGNED NOT NULL,
  `resolved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `inspection_checklists` (
  `id` int(11) NOT NULL,
  `facility_id` int(11) NOT NULL DEFAULT 0,
  `title` varchar(255) NOT NULL,
  `type` varchar(100) NOT NULL,
  `inspection_date` date NOT NULL,
  `inspector_name` varchar(100) DEFAULT NULL,
  `status` enum('pending','in_progress','passed','failed') NOT NULL DEFAULT 'pending',
  `score` tinyint(3) UNSIGNED DEFAULT NULL COMMENT 'Score 0-100',
  `notes` text DEFAULT NULL,
  `overall_remarks` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `completed_by` int(11) DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `inspection_items` (
  `id` int(11) NOT NULL,
  `checklist_id` int(11) NOT NULL DEFAULT 0,
  `item_text` varchar(500) NOT NULL,
  `result` enum('pending','pass','fail','na') NOT NULL DEFAULT 'pending',
  `remarks` varchar(500) DEFAULT NULL,
  `sort_order` smallint(5) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `inventory_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `item_code` varchar(50) NOT NULL,
  `name` varchar(200) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `unit` varchar(20) NOT NULL DEFAULT 'pcs',
  `quantity` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `min_quantity` int(10) UNSIGNED NOT NULL DEFAULT 5,
  `unit_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `location` varchar(100) DEFAULT NULL,
  `supplier` varchar(200) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `invoices` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `invoice_number` varchar(30) NOT NULL,
  `facility_id` int(10) UNSIGNED NOT NULL,
  `contract_id` int(10) UNSIGNED DEFAULT NULL,
  `work_order_id` int(10) UNSIGNED DEFAULT NULL,
  `invoice_type` enum('contract','work_order','adhoc') NOT NULL DEFAULT 'contract',
  `issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `subtotal` decimal(12,2) NOT NULL,
  `vat_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `vat_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'QAR',
  `status` enum('draft','sent','partial','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  `paid_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `paid_by` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `jc_attachments` (
  `id` int(10) UNSIGNED NOT NULL,
  `jc_id` int(10) UNSIGNED NOT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `uploaded_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `jc_materials` (
  `id` int(10) UNSIGNED NOT NULL,
  `jc_id` int(10) UNSIGNED NOT NULL,
  `item_id` int(10) UNSIGNED DEFAULT NULL,
  `item_name` varchar(200) NOT NULL,
  `quantity` decimal(10,2) DEFAULT 1.00,
  `unit_cost` decimal(12,2) DEFAULT 0.00,
  `total_cost` decimal(12,2) DEFAULT 0.00,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `job_cards` (
  `id` int(10) UNSIGNED NOT NULL,
  `jc_number` varchar(30) NOT NULL,
  `wo_id` int(10) UNSIGNED NOT NULL,
  `assigned_to` int(10) UNSIGNED NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('draft','in_progress','completed','approved') DEFAULT 'draft',
  `labor_hours` decimal(6,2) DEFAULT 0.00,
  `completion_notes` text DEFAULT NULL,
  `before_image` varchar(255) DEFAULT NULL,
  `after_image` varchar(255) DEFAULT NULL,
  `customer_signature` varchar(255) DEFAULT NULL,
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `maintenance_costing` (
  `id` int(10) UNSIGNED NOT NULL,
  `wo_id` int(10) UNSIGNED NOT NULL,
  `labor_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `labor_hours` decimal(6,2) NOT NULL DEFAULT 0.00,
  `parts_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `vendor_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `emergency_surcharge` decimal(10,2) NOT NULL DEFAULT 0.00,
  `cost_estimate` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `job_profit` decimal(10,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `maintenance_requests` (
  `id` int(10) UNSIGNED NOT NULL,
  `ticket_number` varchar(30) NOT NULL,
  `facility_id` int(10) UNSIGNED DEFAULT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `requester_name` varchar(150) NOT NULL,
  `requester_email` varchar(150) DEFAULT NULL,
  `requester_phone` varchar(30) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `description` text NOT NULL,
  `priority` enum('critical','high','medium','low') NOT NULL DEFAULT 'medium',
  `status` enum('pending','reviewed','converted','rejected') NOT NULL DEFAULT 'pending',
  `image_path` varchar(255) DEFAULT NULL,
  `reviewed_by` int(10) UNSIGNED DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `converted_to_wo` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `email` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `notifications` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text DEFAULT NULL,
  `type` enum('work_order','invoice','sla_breach','maintenance','general') NOT NULL DEFAULT 'general',
  `reference_id` int(10) UNSIGNED DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `petty_cash` (
  `id` int(10) UNSIGNED NOT NULL,
  `pc_number` varchar(30) NOT NULL,
  `facility_id` int(10) UNSIGNED DEFAULT NULL,
  `requested_by` int(10) UNSIGNED NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `purpose` text NOT NULL,
  `category` varchar(50) DEFAULT 'general',
  `status` enum('pending','approved','issued','reconciliation','closed','rejected') NOT NULL DEFAULT 'pending',
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `issued_by` int(10) UNSIGNED DEFAULT NULL,
  `issued_at` datetime DEFAULT NULL,
  `reconciled_by` int(10) UNSIGNED DEFAULT NULL,
  `reconciled_at` datetime DEFAULT NULL,
  `settled_at` datetime DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `receipt_path` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `purchase_orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `po_number` varchar(30) NOT NULL,
  `vendor_id` int(10) UNSIGNED DEFAULT NULL,
  `delivery_date` date DEFAULT NULL,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('pending','sent','received','cancelled') NOT NULL DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `purchase_requests` (
  `id` int(10) UNSIGNED NOT NULL,
  `item_id` int(10) UNSIGNED DEFAULT NULL,
  `quantity` int(10) UNSIGNED NOT NULL,
  `reason` text DEFAULT NULL,
  `priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `status` enum('pending','approved','rejected','ordered') NOT NULL DEFAULT 'pending',
  `requested_by` int(10) UNSIGNED NOT NULL,
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `po_id` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `reimbursements` (
  `id` int(10) UNSIGNED NOT NULL,
  `rmb_number` varchar(30) NOT NULL,
  `requested_by` int(10) UNSIGNED NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `description` text NOT NULL,
  `expense_date` date NOT NULL,
  `category` varchar(50) DEFAULT 'general',
  `receipt_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected','paid') DEFAULT 'pending',
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `rfq` (
  `id` int(10) UNSIGNED NOT NULL,
  `rfq_number` varchar(30) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `deadline` date DEFAULT NULL,
  `status` enum('open','closed','awarded','cancelled') DEFAULT 'open',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `rfq_quotations` (
  `id` int(10) UNSIGNED NOT NULL,
  `rfq_id` int(10) UNSIGNED NOT NULL,
  `vendor_id` int(10) UNSIGNED NOT NULL,
  `unit_price` decimal(12,2) DEFAULT 0.00,
  `total_amount` decimal(12,2) DEFAULT 0.00,
  `lead_time` varchar(100) DEFAULT NULL,
  `validity` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `is_selected` tinyint(1) DEFAULT 0,
  `added_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `rfq_vendors` (
  `id` int(10) UNSIGNED NOT NULL,
  `rfq_id` int(10) UNSIGNED NOT NULL,
  `vendor_id` int(10) UNSIGNED NOT NULL,
  `status` enum('sent','responded','declined') DEFAULT 'sent',
  `sent_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `roles` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `display_name` varchar(100) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `sla_rules` (
  `id` int(10) UNSIGNED NOT NULL,
  `priority` enum('critical','high','medium','low') NOT NULL,
  `response_hours` int(10) UNSIGNED NOT NULL,
  `resolution_hours` int(10) UNSIGNED NOT NULL,
  `escalation_hours` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `stock_movements` (
  `id` int(10) UNSIGNED NOT NULL,
  `item_id` int(10) UNSIGNED NOT NULL,
  `movement_type` enum('in','out','adjustment') NOT NULL,
  `quantity` int(10) UNSIGNED NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `system_settings` (
  `id` int(10) UNSIGNED NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_group` varchar(50) NOT NULL DEFAULT 'general',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `units` (
  `id` int(10) UNSIGNED NOT NULL,
  `facility_id` int(10) UNSIGNED NOT NULL,
  `unit_number` varchar(50) NOT NULL,
  `floor` varchar(20) DEFAULT NULL,
  `unit_type` varchar(50) DEFAULT NULL,
  `area_sqft` decimal(10,2) DEFAULT NULL,
  `owner_name` varchar(100) DEFAULT NULL,
  `owner_mobile` varchar(30) DEFAULT NULL,
  `owner_email` varchar(100) DEFAULT NULL,
  `tenant_name` varchar(100) DEFAULT NULL,
  `tenant_mobile` varchar(30) DEFAULT NULL,
  `tenant_email` varchar(100) DEFAULT NULL,
  `contract_number` varchar(50) DEFAULT NULL,
  `contract_start` date DEFAULT NULL,
  `contract_end` date DEFAULT NULL,
  `rent_amount` decimal(12,2) DEFAULT NULL,
  `security_deposit` decimal(12,2) DEFAULT NULL,
  `status` enum('occupied','vacant','maintenance') DEFAULT 'vacant',
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `contract_attachment` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `unit_checklists` (
  `id` int(10) UNSIGNED NOT NULL,
  `unit_id` int(10) UNSIGNED NOT NULL,
  `type` enum('move_in','move_out','routine','handover') NOT NULL DEFAULT 'routine',
  `items_json` mediumtext DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('draft','completed') NOT NULL DEFAULT 'draft',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `role_id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `status` enum('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `last_login` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `user_sessions` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `token` varchar(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `session_id` varchar(128) DEFAULT NULL,
  `logged_out_at` datetime DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `logged_in_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `utility_readings` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `facility_id` int(10) UNSIGNED NOT NULL,
  `type` enum('electricity','water','gas','diesel','other') NOT NULL,
  `reading_date` date NOT NULL,
  `units` decimal(10,2) NOT NULL,
  `cost` decimal(12,2) NOT NULL DEFAULT 0.00,
  `meter_reading` decimal(12,2) DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `vendors` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(200) NOT NULL,
  `category` varchar(100) NOT NULL,
  `contact` varchar(150) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `rating` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `status` enum('active','inactive','blacklisted') NOT NULL DEFAULT 'active',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `vat_number` varchar(50) DEFAULT NULL,
  `contact_person` varchar(150) DEFAULT NULL,
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `work_orders` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED DEFAULT NULL,
  `wo_number` varchar(30) NOT NULL,
  `facility_id` int(10) UNSIGNED NOT NULL,
  `asset_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `type` enum('corrective','preventive','predictive','breakdown','inspection','emergency','project') NOT NULL DEFAULT 'corrective',
  `category` enum('electrical','hvac','plumbing','cleaning','civil','it','fire_safety','security','other') DEFAULT NULL,
  `priority` enum('critical','high','medium','low') NOT NULL DEFAULT 'medium',
  `status` enum('new','assigned','in_progress','on_hold','completed','closed','cancelled') NOT NULL DEFAULT 'new',
  `assigned_to` int(10) UNSIGNED DEFAULT NULL,
  `vendor_id` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `sla_due` datetime DEFAULT NULL,
  `planned_start` datetime DEFAULT NULL,
  `planned_end` datetime DEFAULT NULL,
  `sla_breached` tinyint(1) NOT NULL DEFAULT 0,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `estimated_cost` decimal(12,2) DEFAULT NULL,
  `actual_cost` decimal(12,2) DEFAULT NULL,
  `completion_notes` text DEFAULT NULL,
  `requester_name` varchar(150) DEFAULT NULL,
  `requester_phone` varchar(30) DEFAULT NULL,
  `requester_email` varchar(100) DEFAULT NULL,
  `approval_status` enum('pending','approved','rejected') NOT NULL DEFAULT 'approved',
  `qa_status` enum('none','pending','approved','rejected') NOT NULL DEFAULT 'none',
  `qa_approved_by` int(10) UNSIGNED DEFAULT NULL,
  `qa_approved_at` datetime DEFAULT NULL,
  `client_approval_status` enum('none','pending','approved','rejected') NOT NULL DEFAULT 'none',
  `client_approved_by` int(10) UNSIGNED DEFAULT NULL,
  `client_approved_at` datetime DEFAULT NULL,
  `invoice_id` int(10) UNSIGNED DEFAULT NULL,
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `unit_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `wo_approvals` (
  `id` int(10) UNSIGNED NOT NULL,
  `wo_id` int(10) UNSIGNED NOT NULL,
  `approval_type` enum('supervisor','budget','completion','reopen') NOT NULL DEFAULT 'supervisor',
  `action` enum('approved','rejected') NOT NULL,
  `notes` text DEFAULT NULL,
  `actioned_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `wo_attachments` (
  `id` int(10) UNSIGNED NOT NULL,
  `wo_id` int(10) UNSIGNED NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `uploaded_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `wo_chat_messages` (
  `id` int(10) UNSIGNED NOT NULL,
  `wo_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `message` text NOT NULL,
  `image_path` varchar(500) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wo_comments` (
  `id` int(11) NOT NULL,
  `wo_id` int(11) NOT NULL DEFAULT 0,
  `user_id` int(11) NOT NULL DEFAULT 0,
  `comment` text NOT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wo_labor` (
  `id` int(10) UNSIGNED NOT NULL,
  `wo_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `work_date` date NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `hours_worked` decimal(5,2) NOT NULL DEFAULT 0.00,
  `overtime_hours` decimal(5,2) NOT NULL DEFAULT 0.00,
  `hourly_rate` decimal(8,2) NOT NULL DEFAULT 0.00,
  `labor_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `notes` varchar(500) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `wo_materials` (
  `id` int(10) UNSIGNED NOT NULL,
  `wo_id` int(10) UNSIGNED NOT NULL,
  `item_id` int(10) UNSIGNED DEFAULT NULL,
  `item_name` varchar(200) NOT NULL,
  `item_code` varchar(50) DEFAULT NULL,
  `quantity` decimal(10,2) NOT NULL DEFAULT 1.00,
  `unit_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `deducted_from_stock` tinyint(1) NOT NULL DEFAULT 0,
  `notes` varchar(500) DEFAULT NULL,
  `added_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `invoice_payments` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` int(10) UNSIGNED NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_method` enum('cash','bank','card','cheque','online') NOT NULL DEFAULT 'bank',
  `reference_no` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `paid_at` datetime NOT NULL,
  `recorded_by` int(10) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_inv_pay_invoice` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `login_attempts` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` varchar(120) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `success` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_login_email_time` (`email`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activity_logs`
--
ALTER TABLE `activity_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_actlog_user` (`user_id`),
  ADD KEY `idx_actlog_module` (`module`),
  ADD KEY `idx_actlog_created` (`created_at`);

--
-- Indexes for table `assets`
--
ALTER TABLE `assets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_assets_code` (`asset_code`),
  ADD KEY `fk_assets_facility` (`facility_id`),
  ADD KEY `idx_asset_facility` (`facility_id`),
  ADD KEY `idx_asset_status` (`status`);

--
-- Indexes for table `asset_meter_readings`
--
ALTER TABLE `asset_meter_readings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_amr_asset` (`asset_id`),
  ADD KEY `fk_amr_wo` (`wo_id`);

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

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

--
-- Indexes for table `company_roles`
--
ALTER TABLE `company_roles`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_cr_company` (`company_id`);

--
-- Indexes for table `company_users`
--
ALTER TABLE `company_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_company_user` (`company_id`,`user_id`);

--
-- Indexes for table `compliance_audits`
--
ALTER TABLE `compliance_audits`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_audit_facility` (`facility_id`),
  ADD KEY `fk_audit_created` (`created_by`);

--
-- Indexes for table `compliance_documents`
--
ALTER TABLE `compliance_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_doc_expiry` (`expiry_date`),
  ADD KEY `fk_doc_facility` (`facility_id`),
  ADD KEY `fk_doc_created` (`created_by`);

--
-- Indexes for table `contracts`
--
ALTER TABLE `contracts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_contracts_number` (`contract_number`),
  ADD KEY `fk_contracts_facility` (`facility_id`),
  ADD KEY `fk_contracts_created_by` (`created_by`),
  ADD KEY `idx_con_status` (`status`),
  ADD KEY `idx_con_end_date` (`end_date`),
  ADD KEY `idx_con_unit` (`unit_id`);

--
-- Indexes for table `documents`
--
ALTER TABLE `documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_documents_facility` (`facility_id`),
  ADD KEY `idx_documents_status` (`status`),
  ADD KEY `idx_documents_expiry` (`expiry_date`),
  ADD KEY `idx_documents_type` (`doc_type`),
  ADD KEY `fk_documents_user` (`uploaded_by`);

--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_emp_code` (`emp_code`),
  ADD KEY `fk_employees_user` (`user_id`),
  ADD KEY `fk_employees_facility` (`facility_id`);

--
-- Indexes for table `employee_breaks`
--
ALTER TABLE `employee_breaks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_breaks_employee` (`employee_id`);

--
-- Indexes for table `estimations`
--
ALTER TABLE `estimations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `est_number` (`est_number`),
  ADD KEY `idx_est_facility` (`facility_id`),
  ADD KEY `idx_est_status` (`status`);

--
-- Indexes for table `estimation_items`
--
ALTER TABLE `estimation_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_estitem_est` (`est_id`);

--
-- Indexes for table `expenses`
--
ALTER TABLE `expenses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_expenses_date` (`expense_date`),
  ADD KEY `fk_expenses_facility` (`facility_id`),
  ADD KEY `fk_expenses_work_order` (`work_order_id`),
  ADD KEY `fk_expenses_created_by` (`created_by`),
  ADD KEY `idx_exp_facility` (`facility_id`),
  ADD KEY `idx_exp_status` (`status`),
  ADD KEY `idx_exp_date` (`expense_date`);

--
-- Indexes for table `facilities`
--
ALTER TABLE `facilities`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_facilities_code` (`code`),
  ADD KEY `fk_facilities_manager` (`manager_id`),
  ADD KEY `idx_fac_company` (`company_id`),
  ADD KEY `idx_fac_status` (`status`);

--
-- Indexes for table `grn`
--
ALTER TABLE `grn`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `grn_number` (`grn_number`),
  ADD KEY `idx_grn_po` (`po_id`);

--
-- Indexes for table `grn_items`
--
ALTER TABLE `grn_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_grnitem_grn` (`grn_id`);

--
-- Indexes for table `helpdesk_feedback`
--
ALTER TABLE `helpdesk_feedback`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_feedback_request` (`request_id`);

--
-- Indexes for table `incidents`
--
ALTER TABLE `incidents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_incident_facility` (`facility_id`),
  ADD KEY `fk_incident_reported` (`reported_by`);

--
-- Indexes for table `inspection_checklists`
--
ALTER TABLE `inspection_checklists`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ic_facility` (`facility_id`),
  ADD KEY `idx_ic_status` (`status`),
  ADD KEY `idx_ic_date` (`inspection_date`);

--
-- Indexes for table `inspection_items`
--
ALTER TABLE `inspection_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ii_checklist` (`checklist_id`);

--
-- Indexes for table `inventory_items`
--
ALTER TABLE `inventory_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_item_code` (`item_code`),
  ADD KEY `idx_inventory_low_stock` (`quantity`,`min_quantity`),
  ADD KEY `idx_inv_item_code` (`item_code`),
  ADD KEY `idx_inv_qty` (`quantity`);

--
-- Indexes for table `invoices`
--
ALTER TABLE `invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_invoices_number` (`invoice_number`),
  ADD KEY `idx_invoices_status` (`status`),
  ADD KEY `idx_invoices_issue_date` (`issue_date`),
  ADD KEY `fk_invoices_facility` (`facility_id`),
  ADD KEY `fk_invoices_contract` (`contract_id`),
  ADD KEY `fk_invoices_created_by` (`created_by`),
  ADD KEY `idx_inv_status` (`status`),
  ADD KEY `idx_inv_facility` (`facility_id`),
  ADD KEY `idx_inv_due_date` (`due_date`),
  ADD KEY `idx_inv_issue_date` (`issue_date`);

--
-- Indexes for table `jc_attachments`
--
ALTER TABLE `jc_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_jcatt_jc` (`jc_id`);

--
-- Indexes for table `jc_materials`
--
ALTER TABLE `jc_materials`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_jcmat_jc` (`jc_id`);

--
-- Indexes for table `job_cards`
--
ALTER TABLE `job_cards`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `jc_number` (`jc_number`),
  ADD KEY `idx_jc_wo` (`wo_id`),
  ADD KEY `idx_jc_assigned` (`assigned_to`),
  ADD KEY `idx_jc_status` (`status`);

--
-- Indexes for table `maintenance_costing`
--
ALTER TABLE `maintenance_costing`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_costing_wo` (`wo_id`),
  ADD KEY `fk_costing_created_by` (`created_by`);

--
-- Indexes for table `maintenance_requests`
--
ALTER TABLE `maintenance_requests`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_mr_ticket` (`ticket_number`),
  ADD KEY `fk_mr_facility` (`facility_id`),
  ADD KEY `fk_mr_reviewed_by` (`reviewed_by`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_notif_user_read` (`user_id`,`is_read`),
  ADD KEY `idx_notif_created` (`created_at`);

--
-- Indexes for table `petty_cash`
--
ALTER TABLE `petty_cash`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `pc_number` (`pc_number`),
  ADD KEY `idx_pc_status` (`status`),
  ADD KEY `idx_pc_req_by` (`requested_by`);

--
-- Indexes for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_po_number` (`po_number`),
  ADD KEY `fk_po_vendor` (`vendor_id`),
  ADD KEY `fk_po_created_by` (`created_by`),
  ADD KEY `idx_po_status` (`status`),
  ADD KEY `idx_po_vendor` (`vendor_id`);

--
-- Indexes for table `purchase_requests`
--
ALTER TABLE `purchase_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_pr_item` (`item_id`),
  ADD KEY `fk_pr_requested_by` (`requested_by`),
  ADD KEY `idx_pr_status` (`status`),
  ADD KEY `idx_pr_po_id` (`po_id`);

--
-- Indexes for table `reimbursements`
--
ALTER TABLE `reimbursements`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `rmb_number` (`rmb_number`),
  ADD KEY `idx_rmb_status` (`status`),
  ADD KEY `idx_rmb_req_by` (`requested_by`);

--
-- Indexes for table `rfq`
--
ALTER TABLE `rfq`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `rfq_number` (`rfq_number`),
  ADD KEY `idx_rfq_status` (`status`);

--
-- Indexes for table `rfq_quotations`
--
ALTER TABLE `rfq_quotations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_rfqq_rfq` (`rfq_id`),
  ADD KEY `idx_rfqq_vendor` (`vendor_id`);

--
-- Indexes for table `rfq_vendors`
--
ALTER TABLE `rfq_vendors`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_rfq_vendor` (`rfq_id`,`vendor_id`);

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

--
-- Indexes for table `sla_rules`
--
ALTER TABLE `sla_rules`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_sla_priority` (`priority`);

--
-- Indexes for table `stock_movements`
--
ALTER TABLE `stock_movements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_stock_item` (`item_id`),
  ADD KEY `fk_stock_created_by` (`created_by`);

--
-- Indexes for table `system_settings`
--
ALTER TABLE `system_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_setting_key` (`setting_key`);

--
-- Indexes for table `units`
--
ALTER TABLE `units`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_unit_facility` (`facility_id`),
  ADD KEY `idx_unit_status` (`status`);

--
-- Indexes for table `unit_checklists`
--
ALTER TABLE `unit_checklists`
  ADD PRIMARY KEY (`id`),
  ADD KEY `unit_id` (`unit_id`),
  ADD KEY `type` (`type`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_users_email` (`email`),
  ADD KEY `fk_users_role` (`role_id`),
  ADD KEY `idx_users_role` (`role_id`),
  ADD KEY `idx_users_email` (`email`),
  ADD KEY `idx_users_status` (`status`);

--
-- Indexes for table `user_sessions`
--
ALTER TABLE `user_sessions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_user_sessions_token` (`token`),
  ADD KEY `fk_user_sessions_user` (`user_id`),
  ADD KEY `idx_sess_user` (`user_id`),
  ADD KEY `idx_sess_session` (`session_id`);

--
-- Indexes for table `utility_readings`
--
ALTER TABLE `utility_readings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_utility_date` (`reading_date`),
  ADD KEY `fk_utility_facility` (`facility_id`),
  ADD KEY `fk_utility_created` (`created_by`);

--
-- Indexes for table `vendors`
--
ALTER TABLE `vendors`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_vendor_created` (`created_by`);

--
-- Indexes for table `work_orders`
--
ALTER TABLE `work_orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_wo_number` (`wo_number`),
  ADD KEY `idx_wo_status` (`status`),
  ADD KEY `idx_wo_priority` (`priority`),
  ADD KEY `idx_wo_sla_due` (`sla_due`),
  ADD KEY `fk_wo_facility` (`facility_id`),
  ADD KEY `fk_wo_asset` (`asset_id`),
  ADD KEY `fk_wo_assigned` (`assigned_to`),
  ADD KEY `fk_wo_created` (`created_by`),
  ADD KEY `idx_wo_facility` (`facility_id`),
  ADD KEY `idx_wo_assigned` (`assigned_to`),
  ADD KEY `idx_wo_sla_breach` (`sla_breached`),
  ADD KEY `idx_wo_approval` (`approval_status`),
  ADD KEY `idx_wo_created` (`created_at`),
  ADD KEY `idx_wo_unit` (`unit_id`);

--
-- Indexes for table `wo_approvals`
--
ALTER TABLE `wo_approvals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_woapproval_wo` (`wo_id`);

--
-- Indexes for table `wo_attachments`
--
ALTER TABLE `wo_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_attach_wo` (`wo_id`),
  ADD KEY `fk_attach_user` (`uploaded_by`);

--
-- Indexes for table `wo_chat_messages`
--
ALTER TABLE `wo_chat_messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_wochat_wo` (`wo_id`),
  ADD KEY `idx_wochat_user` (`user_id`);

--
-- Indexes for table `wo_comments`
--
ALTER TABLE `wo_comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_woc_wo` (`wo_id`);

--
-- Indexes for table `wo_labor`
--
ALTER TABLE `wo_labor`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_wolabor_wo` (`wo_id`),
  ADD KEY `fk_wolabor_user` (`user_id`);

--
-- Indexes for table `wo_materials`
--
ALTER TABLE `wo_materials`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_womat_wo` (`wo_id`),
  ADD KEY `fk_womat_item` (`item_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

--
-- AUTO_INCREMENT for table `asset_meter_readings`
--
ALTER TABLE `asset_meter_readings`
  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 `companies`
--
ALTER TABLE `companies`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

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

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

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

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

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

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

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

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

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

--
-- AUTO_INCREMENT for table `estimation_items`
--
ALTER TABLE `estimation_items`
  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 `facilities`
--
ALTER TABLE `facilities`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

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

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

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

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

--
-- AUTO_INCREMENT for table `inspection_checklists`
--
ALTER TABLE `inspection_checklists`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `inspection_items`
--
ALTER TABLE `inspection_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `inventory_items`
--
ALTER TABLE `inventory_items`
  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 `jc_attachments`
--
ALTER TABLE `jc_attachments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

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

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

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

--
-- AUTO_INCREMENT for table `maintenance_requests`
--
ALTER TABLE `maintenance_requests`
  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 `petty_cash`
--
ALTER TABLE `petty_cash`
  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 `purchase_requests`
--
ALTER TABLE `purchase_requests`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

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

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

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

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

--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
  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 `stock_movements`
--
ALTER TABLE `stock_movements`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `system_settings`
--
ALTER TABLE `system_settings`
  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 for table `unit_checklists`
--
ALTER TABLE `unit_checklists`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

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

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

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

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

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

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

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

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

--
-- AUTO_INCREMENT for table `wo_comments`
--
ALTER TABLE `wo_comments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

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

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

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

CREATE ALGORITHM=UNDEFINED DEFINER=`cpses_er91886k85`@`localhost` SQL SECURITY DEFINER VIEW `helpdesk_tickets`  AS SELECT `maintenance_requests`.`id` AS `id`, `maintenance_requests`.`ticket_number` AS `ticket_number`, `maintenance_requests`.`facility_id` AS `facility_id`, `maintenance_requests`.`requester_name` AS `requester_name`, `maintenance_requests`.`requester_email` AS `email`, `maintenance_requests`.`requester_email` AS `requester_email`, `maintenance_requests`.`requester_phone` AS `requester_phone`, `maintenance_requests`.`category` AS `category`, `maintenance_requests`.`description` AS `description`, `maintenance_requests`.`priority` AS `priority`, `maintenance_requests`.`status` AS `status`, `maintenance_requests`.`image_path` AS `image_path`, `maintenance_requests`.`reviewed_by` AS `reviewed_by`, `maintenance_requests`.`reviewed_at` AS `reviewed_at`, `maintenance_requests`.`converted_to_wo` AS `converted_to_wo`, `maintenance_requests`.`created_at` AS `created_at`, `maintenance_requests`.`updated_at` AS `updated_at` FROM `maintenance_requests` ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `assets`
--
ALTER TABLE `assets`
  ADD CONSTRAINT `fk_assets_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`);

--
-- Constraints for table `asset_meter_readings`
--
ALTER TABLE `asset_meter_readings`
  ADD CONSTRAINT `fk_amr_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_amr_wo` FOREIGN KEY (`wo_id`) REFERENCES `work_orders` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `attendance`
--
ALTER TABLE `attendance`
  ADD CONSTRAINT `fk_attendance_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `compliance_audits`
--
ALTER TABLE `compliance_audits`
  ADD CONSTRAINT `fk_audit_created` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_audit_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`);

--
-- Constraints for table `compliance_documents`
--
ALTER TABLE `compliance_documents`
  ADD CONSTRAINT `fk_doc_created` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_doc_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `contracts`
--
ALTER TABLE `contracts`
  ADD CONSTRAINT `fk_contracts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_contracts_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`);

--
-- Constraints for table `documents`
--
ALTER TABLE `documents`
  ADD CONSTRAINT `fk_documents_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_documents_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `employees`
--
ALTER TABLE `employees`
  ADD CONSTRAINT `fk_employees_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_employees_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `employee_breaks`
--
ALTER TABLE `employee_breaks`
  ADD CONSTRAINT `fk_breaks_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `expenses`
--
ALTER TABLE `expenses`
  ADD CONSTRAINT `fk_expenses_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_expenses_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`),
  ADD CONSTRAINT `fk_expenses_work_order` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `facilities`
--
ALTER TABLE `facilities`
  ADD CONSTRAINT `fk_facilities_manager` FOREIGN KEY (`manager_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `helpdesk_feedback`
--
ALTER TABLE `helpdesk_feedback`
  ADD CONSTRAINT `fk_feedback_request` FOREIGN KEY (`request_id`) REFERENCES `maintenance_requests` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `incidents`
--
ALTER TABLE `incidents`
  ADD CONSTRAINT `fk_incident_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`),
  ADD CONSTRAINT `fk_incident_reported` FOREIGN KEY (`reported_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `invoices`
--
ALTER TABLE `invoices`
  ADD CONSTRAINT `fk_invoices_contract` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_invoices_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_invoices_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`);

--
-- Constraints for table `maintenance_costing`
--
ALTER TABLE `maintenance_costing`
  ADD CONSTRAINT `fk_costing_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_costing_wo` FOREIGN KEY (`wo_id`) REFERENCES `work_orders` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `maintenance_requests`
--
ALTER TABLE `maintenance_requests`
  ADD CONSTRAINT `fk_mr_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_mr_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

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

--
-- Constraints for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  ADD CONSTRAINT `fk_po_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_po_vendor` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `purchase_requests`
--
ALTER TABLE `purchase_requests`
  ADD CONSTRAINT `fk_pr_item` FOREIGN KEY (`item_id`) REFERENCES `inventory_items` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_pr_requested_by` FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `stock_movements`
--
ALTER TABLE `stock_movements`
  ADD CONSTRAINT `fk_stock_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_stock_item` FOREIGN KEY (`item_id`) REFERENCES `inventory_items` (`id`);

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`);

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

--
-- Constraints for table `utility_readings`
--
ALTER TABLE `utility_readings`
  ADD CONSTRAINT `fk_utility_created` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_utility_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`);

--
-- Constraints for table `vendors`
--
ALTER TABLE `vendors`
  ADD CONSTRAINT `fk_vendor_created` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `work_orders`
--
ALTER TABLE `work_orders`
  ADD CONSTRAINT `fk_wo_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_wo_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_wo_created` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_wo_facility` FOREIGN KEY (`facility_id`) REFERENCES `facilities` (`id`);

--
-- Constraints for table `wo_approvals`
--
ALTER TABLE `wo_approvals`
  ADD CONSTRAINT `fk_woapproval_wo` FOREIGN KEY (`wo_id`) REFERENCES `work_orders` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `wo_attachments`
--
ALTER TABLE `wo_attachments`
  ADD CONSTRAINT `fk_attach_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_attach_wo` FOREIGN KEY (`wo_id`) REFERENCES `work_orders` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `wo_labor`
--
ALTER TABLE `wo_labor`
  ADD CONSTRAINT `fk_wolabor_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fk_wolabor_wo` FOREIGN KEY (`wo_id`) REFERENCES `work_orders` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `wo_materials`
--
ALTER TABLE `wo_materials`
  ADD CONSTRAINT `fk_womat_item` FOREIGN KEY (`item_id`) REFERENCES `inventory_items` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_womat_wo` FOREIGN KEY (`wo_id`) REFERENCES `work_orders` (`id`) ON DELETE CASCADE;

--
-- Seed data (fresh install only)
--

INSERT INTO `roles` (`id`, `name`, `display_name`, `created_at`) VALUES
(1, 'super_admin', 'Super Admin / CEO', NOW()),
(2, 'facility_manager', 'Facility Manager', NOW()),
(3, 'technician', 'Technician', NOW()),
(4, 'client', 'Client', NOW()),
(5, 'finance_manager', 'Finance Manager', NOW()),
(6, 'finance_user', 'Finance User', NOW()),
(7, 'procurement_officer', 'Procurement Officer', NOW()),
(8, 'property_manager', 'Property Manager', NOW()),
(9, 'supervisor', 'Supervisor', NOW()),
(10, 'qa_inspector', 'QA Inspector', NOW());

INSERT INTO `sla_rules` (`id`, `priority`, `response_hours`, `resolution_hours`, `escalation_hours`, `created_at`) VALUES
(1, 'critical', 1, 4, 2, NOW()),
(2, 'high', 2, 12, 6, NOW()),
(3, 'medium', 4, 24, 12, NOW()),
(4, 'low', 8, 72, 48, NOW());

INSERT INTO `system_settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `updated_at`) VALUES
(1, 'company_name', 'FM ERP', 'branding', NOW()),
(2, 'currency', 'QAR', 'finance', NOW()),
(3, 'vat_enabled', '0', 'finance', NOW()),
(4, 'vat_rate', '0', 'finance', NOW()),
(5, 'timezone', 'Asia/Qatar', 'general', NOW()),
(6, 'sla_breach_notify', '1', 'notifications', NOW()),
(7, 'smtp_host', '', 'email', NOW()),
(8, 'smtp_user', '', 'email', NOW()),
(9, 'smtp_port', '587', 'email', NOW()),
(10, 'system_version', '1.0.0', 'general', NOW()),
(11, 'anthropic_api_key', '', 'ai', NOW()),
(12, 'company_tagline', 'Facility Management ERP', 'branding', NOW()),
(13, 'company_logo', '', 'branding', NOW()),
(14, 'primary_color', '#76002b', 'branding', NOW()),
(15, 'secondary_color', '#c7ba9a', 'branding', NOW()),
(16, 'wf_require_supervisor_approval', '1', 'workflow', NOW()),
(17, 'wf_require_qa_on_complete', '1', 'workflow', NOW()),
(18, 'wf_require_client_approval', '1', 'workflow', NOW()),
(19, 'wf_require_invoice_before_close', '1', 'workflow', NOW()),
(20, 'wf_require_labor_or_material', '1', 'workflow', NOW()),
(21, 'wf_auto_invoice_on_client_approve', '1', 'workflow', NOW());

INSERT INTO `users` (`id`, `role_id`, `name`, `email`, `phone`, `password`, `status`, `last_login`, `created_at`, `updated_at`, `deleted_at`, `company_id`) VALUES
(1, 1, 'Super Admin', 'admin@fmerp.com', NULL, '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active', NULL, NOW(), NOW(), NULL, NULL);

ALTER TABLE `roles` AUTO_INCREMENT = 11;
ALTER TABLE `sla_rules` AUTO_INCREMENT = 5;
ALTER TABLE `system_settings` AUTO_INCREMENT = 22;
ALTER TABLE `users` AUTO_INCREMENT = 2;

COMMIT;

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