-- ============================================================================ -- Garage Management System — Full Database WITH Demo Data -- This is a complete, self-contained dump: schema + required seed data + -- realistic sample business data (customers, vehicles, work orders, an -- insurance claim, inventory, purchases, invoices, payments, coupons). -- Import this INSTEAD OF database.sql for a ready-to-explore install. -- -- Default login: admin@garage.local / Admin@12345 (change immediately) -- ============================================================================ CREATE DATABASE IF NOT EXISTS `garage_management` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE `garage_management`; -- MariaDB dump 10.19 Distrib 10.4.32-MariaDB, for Win64 (AMD64) -- -- Host: localhost Database: garage_management -- ------------------------------------------------------ -- Server version 10.4.32-MariaDB /*!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 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `audit_logs` -- DROP TABLE IF EXISTS `audit_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `audit_logs` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned DEFAULT NULL, `action` varchar(50) NOT NULL, `module` varchar(60) NOT NULL, `record_id` int(10) unsigned DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_audit_user` (`user_id`), KEY `idx_audit_module` (`module`), CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `audit_logs` -- LOCK TABLES `audit_logs` WRITE; /*!40000 ALTER TABLE `audit_logs` DISABLE KEYS */; INSERT INTO `audit_logs` (`id`, `user_id`, `action`, `module`, `record_id`, `description`, `ip_address`, `created_at`) VALUES (1,1,'login','auth',1,'User logged in','127.0.0.1','2026-09-16 16:09:56'),(2,1,'create','customers',1,'Created customer Abebe Kebede','127.0.0.1','2026-09-16 16:10:07'),(3,1,'create','vehicles',1,'Registered vehicle AA-12345','127.0.0.1','2026-09-16 16:10:25'),(4,1,'update','settings',NULL,'Updated branding settings','127.0.0.1','2026-09-16 16:10:54'),(5,1,'update','settings',NULL,'Updated branding settings','127.0.0.1','2026-09-16 16:11:07'),(6,1,'create','users',2,'Created user mechanic@garage.local','127.0.0.1','2026-09-16 16:11:21'),(7,1,'logout','auth',1,'User logged out','127.0.0.1','2026-09-16 16:11:27'),(8,2,'login','auth',2,'User logged in','127.0.0.1','2026-09-16 16:11:33'),(9,2,'logout','auth',2,'User logged out','127.0.0.1','2026-09-16 16:12:12'),(10,NULL,'password_reset','auth',1,'Password reset via token','127.0.0.1','2026-09-16 16:12:39'),(11,1,'login','auth',1,'User logged in','127.0.0.1','2026-09-16 16:12:51'),(12,1,'create','mechanics',1,'Added mechanic Dawit Alemu','127.0.0.1','2026-09-16 16:22:03'),(13,1,'create','workorders',1,'Quick check-in created work order WO-2026-0001','127.0.0.1','2026-09-16 16:23:55'),(14,1,'update','workorders',1,'Updated parts/labor items','127.0.0.1','2026-09-16 16:24:07'),(15,1,'status_change','workorders',1,'Status changed to completed','127.0.0.1','2026-09-16 16:24:16'),(16,1,'create','services',1,'Created service package Basic Service Package','127.0.0.1','2026-09-16 16:25:27'),(17,1,'create','appointments',1,'Scheduled appointment','127.0.0.1','2026-09-16 16:25:52'),(18,1,'create','workorders',2,'Created work order WO-2026-0002','127.0.0.1','2026-09-16 16:31:58'),(19,1,'create','insurance',1,'Created insurance claim CLM-2026-0001','127.0.0.1','2026-09-16 16:32:17'),(20,1,'create','insurance',1,'Added survey to claim #1','127.0.0.1','2026-09-16 16:32:35'),(21,1,'approve','insurance',1,'Approved claim #1 for 16500','127.0.0.1','2026-09-16 16:33:14'),(22,1,'create','insurance',1,'Requested supplement for claim #1','127.0.0.1','2026-09-16 16:33:27'),(23,1,'approved','insurance',1,'Supplement approved for claim #1','127.0.0.1','2026-09-16 16:33:56'),(24,1,'update','inspections',2,'Updated vehicle inspection checklist','127.0.0.1','2026-09-16 16:35:52'),(25,1,'login','auth',1,'User logged in','127.0.0.1','2026-09-16 16:37:15'),(26,1,'create','documents',1,'Uploaded vehicle photo','127.0.0.1','2026-09-16 16:37:15'),(27,1,'create','inventory',1,'Added part Engine Oil Filter','127.0.0.1','2026-09-16 16:45:42'),(28,1,'create','purchases',1,'Created purchase order','127.0.0.1','2026-09-16 16:47:01'),(29,1,'login','auth',1,'User logged in','127.0.0.1','2026-09-16 16:47:45'),(30,1,'update','purchases',1,'Marked purchase as received (stock updated)','127.0.0.1','2026-09-16 16:47:46'),(31,1,'update','workorders',2,'Used 2 x Engine Oil Filter from inventory','127.0.0.1','2026-09-16 16:48:22'),(32,1,'update','workorders',2,'Updated parts/labor items','127.0.0.1','2026-09-16 16:48:46'),(33,1,'login','auth',1,'User logged in','127.0.0.1','2026-09-16 16:49:04'),(34,1,'update','workorders',2,'Removed inventory part usage, stock restored','127.0.0.1','2026-09-16 16:49:04'),(35,1,'login','auth',1,'User logged in','127.0.0.1','2026-09-16 16:58:03'),(36,1,'status_change','workorders',2,'Status changed to completed','127.0.0.1','2026-09-16 16:58:04'),(37,1,'create','invoices',1,'Created invoice from work order #2','127.0.0.1','2026-09-16 16:58:14'),(38,1,'create','payments',1,'Recorded payment of 500 for invoice #1','127.0.0.1','2026-09-16 16:58:27'),(39,1,'create','payments',2,'Recorded payment of 880 for invoice #1','127.0.0.1','2026-09-16 16:58:42'),(40,NULL,'password_reset','auth',1,'Password reset via token','127.0.0.1','2026-09-16 17:05:17'),(41,1,'login','auth',1,'User logged in','127.0.0.1','2026-09-16 17:05:25'),(42,1,'login','auth',1,'User logged in','127.0.0.1','2026-09-16 20:49:53'),(43,1,'create','estimates',1,'Created estimate','127.0.0.1','2026-09-16 20:50:35'),(44,1,'status_change','estimates',1,'Estimate status -> approved','127.0.0.1','2026-09-16 20:54:24'),(45,1,'convert','estimates',1,'Converted estimate to work order #5','127.0.0.1','2026-09-16 20:57:00'),(46,1,'create','expenses',1,'Recorded expense: Utilities — 1500','127.0.0.1','2026-09-16 20:57:45'),(47,1,'create','coupons',1,'Created coupon WELCOME10','127.0.0.1','2026-09-16 20:58:11'),(48,1,'status_change','workorders',5,'Status changed to ready','127.0.0.1','2026-09-16 21:05:17'),(49,1,'deliver','workorders',5,'Vehicle delivered to customer','127.0.0.1','2026-09-16 21:05:37'); /*!40000 ALTER TABLE `audit_logs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `coupon_usage` -- DROP TABLE IF EXISTS `coupon_usage`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `coupon_usage` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `coupon_id` int(10) unsigned NOT NULL, `invoice_id` int(10) unsigned DEFAULT NULL, `customer_id` int(10) unsigned DEFAULT NULL, `discount_applied` decimal(12,2) NOT NULL DEFAULT 0.00, `used_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_coupon_usage_coupon` (`coupon_id`), KEY `fk_coupon_usage_invoice` (`invoice_id`), KEY `fk_coupon_usage_customer` (`customer_id`), CONSTRAINT `fk_coupon_usage_coupon` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_coupon_usage_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_coupon_usage_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `coupon_usage` -- LOCK TABLES `coupon_usage` WRITE; /*!40000 ALTER TABLE `coupon_usage` DISABLE KEYS */; /*!40000 ALTER TABLE `coupon_usage` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `coupons` -- DROP TABLE IF EXISTS `coupons`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `coupons` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `code` varchar(40) NOT NULL, `discount_type` enum('percentage','fixed') NOT NULL DEFAULT 'percentage', `discount_value` decimal(12,2) NOT NULL, `expiry_date` date DEFAULT NULL, `usage_limit` int(10) unsigned DEFAULT NULL, `used_count` int(10) unsigned NOT NULL DEFAULT 0, `min_amount` decimal(12,2) NOT NULL DEFAULT 0.00, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_coupons_code` (`code`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `coupons` -- LOCK TABLES `coupons` WRITE; /*!40000 ALTER TABLE `coupons` DISABLE KEYS */; INSERT INTO `coupons` (`id`, `code`, `discount_type`, `discount_value`, `expiry_date`, `usage_limit`, `used_count`, `min_amount`, `is_active`, `created_at`) VALUES (1,'WELCOME10','percentage',10.00,NULL,NULL,0,0.00,1,'2026-09-16 20:58:11'),(2,'FLEET15','percentage',15.00,'2026-12-31',50,0,2000.00,1,'2026-08-01 09:00:00'),(3,'NEWCUSTOMER500','fixed',500.00,'2026-12-31',NULL,0,1000.00,1,'2026-08-01 09:00:00'); /*!40000 ALTER TABLE `coupons` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customers` -- DROP TABLE IF EXISTS `customers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `customers` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `customer_code` varchar(30) NOT NULL, `full_name` varchar(150) NOT NULL, `company_name` varchar(150) DEFAULT NULL, `phone` varchar(30) NOT NULL, `alt_phone` varchar(30) DEFAULT NULL, `email` varchar(150) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `city` varchar(100) DEFAULT NULL, `customer_type` enum('individual','company','fleet','insurance') NOT NULL DEFAULT 'individual', `notes` text DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_customers_code` (`customer_code`), KEY `idx_customers_phone` (`phone`), KEY `idx_customers_name` (`full_name`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `customers` -- LOCK TABLES `customers` WRITE; /*!40000 ALTER TABLE `customers` DISABLE KEYS */; INSERT INTO `customers` (`id`, `customer_code`, `full_name`, `company_name`, `phone`, `alt_phone`, `email`, `address`, `city`, `customer_type`, `notes`, `created_at`, `updated_at`, `deleted_at`) VALUES (1,'CUS-2026-0001','Abebe Kebede','','0911223344','','abebe@example.com','','Addis Ababa','individual','','2026-09-16 16:10:07',NULL,NULL),(2,'CUS-2026-0002','Selamawit Bekele',NULL,'0911556677',NULL,'selamawit.b@example.com','Bole Road','Addis Ababa','individual',NULL,'2026-08-01 09:00:00',NULL,NULL),(3,'CUS-2026-0003','Tewodros Alemu',NULL,'0922334488',NULL,NULL,'Kazanchis','Addis Ababa','individual',NULL,'2026-08-05 10:00:00',NULL,NULL),(4,'CUS-2026-0004','Ethio Logistics PLC','Ethio Logistics PLC','0115501234','0911998877','fleet@ethiologistics.et','Gerji','Addis Ababa','fleet','Fleet of 8 delivery vans.','2026-08-10 11:00:00',NULL,NULL),(5,'CUS-2026-0005','Hana Girma',NULL,'0933221100',NULL,'hana.girma@example.com','CMC','Addis Ababa','individual',NULL,'2026-08-15 14:00:00',NULL,NULL),(6,'CUS-2026-0006','Meron Tesfaye',NULL,'0944112233',NULL,NULL,'Sarbet','Addis Ababa','individual',NULL,'2026-01-10 09:00:00',NULL,NULL),(7,'CUS-2026-0007','Nyala General Insurance Client','Nyala Insurance S.C.','0116621900',NULL,'claims@nyalainsurance.com','Piassa','Addis Ababa','insurance',NULL,'2026-02-01 09:00:00',NULL,NULL); /*!40000 ALTER TABLE `customers` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `estimate_items` -- DROP TABLE IF EXISTS `estimate_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `estimate_items` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `estimate_id` int(10) unsigned NOT NULL, `item_type` enum('service','part','labor') NOT NULL DEFAULT 'part', `description` varchar(200) NOT NULL, `quantity` decimal(10,2) NOT NULL DEFAULT 1.00, `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00, `line_total` decimal(12,2) NOT NULL DEFAULT 0.00, PRIMARY KEY (`id`), KEY `idx_estimate_items_estimate` (`estimate_id`), CONSTRAINT `fk_estimate_items_estimate` FOREIGN KEY (`estimate_id`) REFERENCES `estimates` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `estimate_items` -- LOCK TABLES `estimate_items` WRITE; /*!40000 ALTER TABLE `estimate_items` DISABLE KEYS */; INSERT INTO `estimate_items` (`id`, `estimate_id`, `item_type`, `description`, `quantity`, `unit_price`, `line_total`) VALUES (1,1,'part','Brake pad replacement estimate',1.00,2200.00,2200.00); /*!40000 ALTER TABLE `estimate_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `estimates` -- DROP TABLE IF EXISTS `estimates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `estimates` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `estimate_number` varchar(30) NOT NULL, `customer_id` int(10) unsigned NOT NULL, `vehicle_id` int(10) unsigned NOT NULL, `work_order_id` int(10) unsigned DEFAULT NULL, `status` enum('draft','sent','approved','rejected','expired','converted') NOT NULL DEFAULT 'draft', `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00, `discount` decimal(12,2) NOT NULL DEFAULT 0.00, `tax` decimal(12,2) NOT NULL DEFAULT 0.00, `total` decimal(12,2) NOT NULL DEFAULT 0.00, `valid_until` date DEFAULT NULL, `notes` text DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_estimate_number` (`estimate_number`), KEY `idx_estimates_customer` (`customer_id`), KEY `fk_estimates_vehicle` (`vehicle_id`), KEY `fk_estimates_wo` (`work_order_id`), CONSTRAINT `fk_estimates_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`), CONSTRAINT `fk_estimates_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`), CONSTRAINT `fk_estimates_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `estimates` -- LOCK TABLES `estimates` WRITE; /*!40000 ALTER TABLE `estimates` DISABLE KEYS */; INSERT INTO `estimates` (`id`, `estimate_number`, `customer_id`, `vehicle_id`, `work_order_id`, `status`, `subtotal`, `discount`, `tax`, `total`, `valid_until`, `notes`, `created_at`, `updated_at`) VALUES (1,'EST-2026-0001',1,1,5,'converted',2200.00,0.00,0.00,2200.00,NULL,'','2026-09-16 20:50:35','2026-09-16 20:57:00'); /*!40000 ALTER TABLE `estimates` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `expenses` -- DROP TABLE IF EXISTS `expenses`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `expenses` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `category` varchar(100) NOT NULL, `description` varchar(255) DEFAULT NULL, `amount` decimal(12,2) NOT NULL, `expense_date` date NOT NULL, `payment_method` enum('cash','bank_transfer','mobile_money','card','other') NOT NULL DEFAULT 'cash', `reference` varchar(100) DEFAULT NULL, `attachment` varchar(255) DEFAULT NULL, `notes` varchar(255) DEFAULT NULL, `created_by` int(10) unsigned DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `fk_expenses_user` (`created_by`), CONSTRAINT `fk_expenses_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `expenses` -- LOCK TABLES `expenses` WRITE; /*!40000 ALTER TABLE `expenses` DISABLE KEYS */; INSERT INTO `expenses` (`id`, `category`, `description`, `amount`, `expense_date`, `payment_method`, `reference`, `attachment`, `notes`, `created_by`, `created_at`) VALUES (1,'Utilities','Electricity bill',1500.00,'2026-09-16','cash','',NULL,NULL,1,'2026-09-16 20:57:45'); /*!40000 ALTER TABLE `expenses` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `insurance_approvals` -- DROP TABLE IF EXISTS `insurance_approvals`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `insurance_approvals` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `insurance_claim_id` int(10) unsigned NOT NULL, `approved_amount` decimal(12,2) NOT NULL DEFAULT 0.00, `approved_by` varchar(150) DEFAULT NULL, `approval_date` date DEFAULT NULL, `notes` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_approvals_claim` (`insurance_claim_id`), CONSTRAINT `fk_approvals_claim` FOREIGN KEY (`insurance_claim_id`) REFERENCES `insurance_claims` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `insurance_approvals` -- LOCK TABLES `insurance_approvals` WRITE; /*!40000 ALTER TABLE `insurance_approvals` DISABLE KEYS */; INSERT INTO `insurance_approvals` (`id`, `insurance_claim_id`, `approved_amount`, `approved_by`, `approval_date`, `notes`, `created_at`) VALUES (1,1,16500.00,'Getachew Worku','2026-09-16','Approved less depreciation on headlight','2026-09-16 16:33:14'); /*!40000 ALTER TABLE `insurance_approvals` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `insurance_claims` -- DROP TABLE IF EXISTS `insurance_claims`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `insurance_claims` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `claim_number` varchar(30) NOT NULL, `work_order_id` int(10) unsigned DEFAULT NULL, `vehicle_id` int(10) unsigned NOT NULL, `customer_id` int(10) unsigned NOT NULL, `insurance_company_id` int(10) unsigned NOT NULL, `policy_number` varchar(60) DEFAULT NULL, `accident_date` date DEFAULT NULL, `claim_date` date DEFAULT NULL, `surveyor_name` varchar(150) DEFAULT NULL, `status` enum('open','submitted','surveyed','approved','repair_in_progress','supplement_requested','completed','closed','rejected') NOT NULL DEFAULT 'open', `estimated_amount` decimal(12,2) NOT NULL DEFAULT 0.00, `approved_amount` decimal(12,2) NOT NULL DEFAULT 0.00, `customer_contribution` decimal(12,2) NOT NULL DEFAULT 0.00, `insurance_contribution` decimal(12,2) NOT NULL DEFAULT 0.00, `insurance_paid_amount` decimal(12,2) NOT NULL DEFAULT 0.00, `notes` text DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_claim_number` (`claim_number`), KEY `idx_claims_vehicle` (`vehicle_id`), KEY `idx_claims_customer` (`customer_id`), KEY `idx_claims_company` (`insurance_company_id`), KEY `fk_claims_wo` (`work_order_id`), CONSTRAINT `fk_claims_company` FOREIGN KEY (`insurance_company_id`) REFERENCES `insurance_companies` (`id`), CONSTRAINT `fk_claims_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`), CONSTRAINT `fk_claims_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`), CONSTRAINT `fk_claims_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `insurance_claims` -- LOCK TABLES `insurance_claims` WRITE; /*!40000 ALTER TABLE `insurance_claims` DISABLE KEYS */; INSERT INTO `insurance_claims` (`id`, `claim_number`, `work_order_id`, `vehicle_id`, `customer_id`, `insurance_company_id`, `policy_number`, `accident_date`, `claim_date`, `surveyor_name`, `status`, `estimated_amount`, `approved_amount`, `customer_contribution`, `insurance_contribution`, `insurance_paid_amount`, `notes`, `created_at`, `updated_at`) VALUES (1,'CLM-2026-0001',2,1,1,1,'POL-88291','2026-09-14','2026-09-16','Mekonnen Tadesse','supplement_requested',18500.00,18500.00,2000.00,18500.00,0.00,'','2026-09-16 16:32:17','2026-09-16 16:33:56'),(2,'CLM-2026-0002',NULL,4,4,1,'POL-55210','2026-09-13','2026-09-16','Biniam Assefa','open',9500.00,0.00,1000.00,0.00,0.00,'Rear-end collision, minor damage to tailgate.','2026-09-16 21:13:34',NULL); /*!40000 ALTER TABLE `insurance_claims` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `insurance_companies` -- DROP TABLE IF EXISTS `insurance_companies`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `insurance_companies` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(150) NOT NULL, `contact_person` varchar(150) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `email` varchar(150) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_insurance_companies_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `insurance_companies` -- LOCK TABLES `insurance_companies` WRITE; /*!40000 ALTER TABLE `insurance_companies` DISABLE KEYS */; INSERT INTO `insurance_companies` (`id`, `name`, `contact_person`, `phone`, `email`, `address`, `is_active`, `created_at`) VALUES (1,'Ethiopian Insurance Corporation','Claims Department','+251 11 551 0900','claims@eic.com.et',NULL,1,'2026-09-16 16:27:20'),(2,'Nyala Insurance S.C.','Claims Department','+251 11 662 1900','claims@nyalainsurance.com',NULL,1,'2026-09-16 16:27:20'),(3,'Awash Insurance Company','Claims Department','+251 11 466 6667','claims@awashinsurance.com',NULL,1,'2026-09-16 16:27:20'),(4,'Nile Insurance Company','Claims Department','+251 11 551 8127','claims@nileinsurance.com.et',NULL,1,'2026-09-16 16:27:20'),(5,'Lion Insurance Company','Claims Department','+251 11 552 1919','claims@lioninsurance.com.et',NULL,1,'2026-09-16 21:12:07'); /*!40000 ALTER TABLE `insurance_companies` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `insurance_supplements` -- DROP TABLE IF EXISTS `insurance_supplements`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `insurance_supplements` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `insurance_claim_id` int(10) unsigned NOT NULL, `description` text NOT NULL, `additional_cost` decimal(12,2) NOT NULL DEFAULT 0.00, `explanation` text DEFAULT NULL, `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending', `approved_amount` decimal(12,2) DEFAULT NULL, `submitted_at` datetime DEFAULT current_timestamp(), `decided_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_supplements_claim` (`insurance_claim_id`), CONSTRAINT `fk_supplements_claim` FOREIGN KEY (`insurance_claim_id`) REFERENCES `insurance_claims` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `insurance_supplements` -- LOCK TABLES `insurance_supplements` WRITE; /*!40000 ALTER TABLE `insurance_supplements` DISABLE KEYS */; INSERT INTO `insurance_supplements` (`id`, `insurance_claim_id`, `description`, `additional_cost`, `explanation`, `status`, `approved_amount`, `submitted_at`, `decided_at`) VALUES (1,1,'Rear door dent discovered during teardown',2200.00,'Damage was hidden behind the bumper panel, found once removed','approved',2000.00,'2026-09-16 16:33:27','2026-09-16 16:33:56'); /*!40000 ALTER TABLE `insurance_supplements` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `insurance_surveys` -- DROP TABLE IF EXISTS `insurance_surveys`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `insurance_surveys` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `insurance_claim_id` int(10) unsigned NOT NULL, `surveyor_name` varchar(150) DEFAULT NULL, `survey_date` date DEFAULT NULL, `findings` text DEFAULT NULL, `recommended_amount` decimal(12,2) DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_surveys_claim` (`insurance_claim_id`), CONSTRAINT `fk_surveys_claim` FOREIGN KEY (`insurance_claim_id`) REFERENCES `insurance_claims` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `insurance_surveys` -- LOCK TABLES `insurance_surveys` WRITE; /*!40000 ALTER TABLE `insurance_surveys` DISABLE KEYS */; INSERT INTO `insurance_surveys` (`id`, `insurance_claim_id`, `surveyor_name`, `survey_date`, `findings`, `recommended_amount`, `created_at`) VALUES (1,1,'Getachew Worku','2026-09-16','Front bumper cracked, headlight assembly broken, minor fender dent',17800.00,'2026-09-16 16:32:35'); /*!40000 ALTER TABLE `insurance_surveys` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `inventory_transactions` -- DROP TABLE IF EXISTS `inventory_transactions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `inventory_transactions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `part_id` int(10) unsigned NOT NULL, `transaction_type` enum('purchase','service_usage','repair_usage','return','adjustment','damage','transfer') NOT NULL, `quantity` decimal(12,2) NOT NULL COMMENT 'signed: positive = stock in, negative = stock out', `reference_type` varchar(30) DEFAULT NULL, `reference_id` int(10) unsigned DEFAULT NULL, `notes` varchar(255) DEFAULT NULL, `user_id` int(10) unsigned DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_inv_txn_part` (`part_id`), KEY `idx_inv_txn_type` (`transaction_type`), KEY `fk_inv_txn_user` (`user_id`), CONSTRAINT `fk_inv_txn_part` FOREIGN KEY (`part_id`) REFERENCES `parts` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_inv_txn_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `inventory_transactions` -- LOCK TABLES `inventory_transactions` WRITE; /*!40000 ALTER TABLE `inventory_transactions` DISABLE KEYS */; INSERT INTO `inventory_transactions` (`id`, `part_id`, `transaction_type`, `quantity`, `reference_type`, `reference_id`, `notes`, `user_id`, `created_at`) VALUES (1,1,'purchase',20.00,'purchase',1,'Received purchase PO-2026-0001',1,'2026-09-16 16:47:46'),(2,1,'repair_usage',-2.00,'work_order',2,'Used on work order #2',1,'2026-09-16 16:48:21'),(3,1,'return',2.00,'work_order',2,'Removed from work order #2',1,'2026-09-16 16:49:04'); /*!40000 ALTER TABLE `inventory_transactions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `invoice_items` -- DROP TABLE IF EXISTS `invoice_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `invoice_items` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `invoice_id` int(10) unsigned NOT NULL, `item_type` enum('service','part','labor') NOT NULL DEFAULT 'part', `description` varchar(200) NOT NULL, `quantity` decimal(10,2) NOT NULL DEFAULT 1.00, `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00, `line_total` decimal(12,2) NOT NULL DEFAULT 0.00, PRIMARY KEY (`id`), KEY `idx_invoice_items_invoice` (`invoice_id`), CONSTRAINT `fk_invoice_items_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `invoice_items` -- LOCK TABLES `invoice_items` WRITE; /*!40000 ALTER TABLE `invoice_items` DISABLE KEYS */; INSERT INTO `invoice_items` (`id`, `invoice_id`, `item_type`, `description`, `quantity`, `unit_price`, `line_total`) VALUES (1,1,'labor','Bumper replacement labor',1.00,1200.00,1200.00),(2,2,'part','Engine oil 5L + oil filter',1.00,850.00,850.00),(3,2,'part','Air Filter',1.00,420.00,420.00),(4,2,'labor','General service labor',1.00,1780.00,1780.00); /*!40000 ALTER TABLE `invoice_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `invoices` -- DROP TABLE IF EXISTS `invoices`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `invoices` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `invoice_number` varchar(30) NOT NULL, `customer_id` int(10) unsigned NOT NULL, `vehicle_id` int(10) unsigned DEFAULT NULL, `work_order_id` int(10) unsigned DEFAULT NULL, `insurance_claim_id` int(10) unsigned DEFAULT NULL, `status` enum('draft','unpaid','partial','paid','cancelled') NOT NULL DEFAULT 'unpaid', `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00, `discount` decimal(12,2) NOT NULL DEFAULT 0.00, `tax` decimal(12,2) NOT NULL DEFAULT 0.00, `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00, `paid_amount` decimal(12,2) NOT NULL DEFAULT 0.00, `coupon_id` int(10) unsigned DEFAULT NULL, `notes` text DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_invoice_number` (`invoice_number`), KEY `idx_invoices_customer` (`customer_id`), KEY `idx_invoices_status` (`status`), KEY `fk_invoices_vehicle` (`vehicle_id`), KEY `fk_invoices_wo` (`work_order_id`), KEY `fk_invoices_coupon` (`coupon_id`), CONSTRAINT `fk_invoices_coupon` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_invoices_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`), CONSTRAINT `fk_invoices_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_invoices_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `invoices` -- LOCK TABLES `invoices` WRITE; /*!40000 ALTER TABLE `invoices` DISABLE KEYS */; INSERT INTO `invoices` (`id`, `invoice_number`, `customer_id`, `vehicle_id`, `work_order_id`, `insurance_claim_id`, `status`, `subtotal`, `discount`, `tax`, `total_amount`, `paid_amount`, `coupon_id`, `notes`, `created_at`, `updated_at`) VALUES (1,'INV-2026-0001',1,1,2,1,'paid',1200.00,0.00,180.00,1380.00,1380.00,NULL,NULL,'2026-09-16 16:58:14','2026-09-16 16:58:42'),(2,'INV-2026-0002',6,7,6,NULL,'paid',3050.00,0.00,457.50,3507.50,3507.50,NULL,NULL,'2026-09-07 21:12:53',NULL); /*!40000 ALTER TABLE `invoices` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `job_cards` -- DROP TABLE IF EXISTS `job_cards`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `job_cards` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `work_order_id` int(10) unsigned NOT NULL, `mechanic_id` int(10) unsigned DEFAULT NULL, `job_description` varchar(255) NOT NULL, `estimated_hours` decimal(6,2) DEFAULT NULL, `actual_hours` decimal(6,2) DEFAULT NULL, `labor_rate` decimal(12,2) DEFAULT NULL, `start_time` datetime DEFAULT NULL, `end_time` datetime DEFAULT NULL, `status` enum('pending','in_progress','completed','rework') NOT NULL DEFAULT 'pending', `notes` text DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_jobcards_wo` (`work_order_id`), KEY `idx_jobcards_mechanic` (`mechanic_id`), CONSTRAINT `fk_jobcards_mechanic` FOREIGN KEY (`mechanic_id`) REFERENCES `mechanics` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_jobcards_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `job_cards` -- LOCK TABLES `job_cards` WRITE; /*!40000 ALTER TABLE `job_cards` DISABLE KEYS */; /*!40000 ALTER TABLE `job_cards` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `maintenance_schedules` -- DROP TABLE IF EXISTS `maintenance_schedules`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `maintenance_schedules` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `vehicle_id` int(10) unsigned NOT NULL, `service_category_id` int(10) unsigned NOT NULL, `last_service_date` date DEFAULT NULL, `last_service_mileage` int(10) unsigned DEFAULT NULL, `interval_km` int(10) unsigned DEFAULT NULL, `interval_days` int(10) unsigned DEFAULT NULL, `next_due_date` date DEFAULT NULL, `next_due_mileage` int(10) unsigned DEFAULT NULL, `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_schedule_vehicle_category` (`vehicle_id`,`service_category_id`), KEY `fk_schedule_category` (`service_category_id`), CONSTRAINT `fk_schedule_category` FOREIGN KEY (`service_category_id`) REFERENCES `service_categories` (`id`), CONSTRAINT `fk_schedule_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `maintenance_schedules` -- LOCK TABLES `maintenance_schedules` WRITE; /*!40000 ALTER TABLE `maintenance_schedules` DISABLE KEYS */; INSERT INTO `maintenance_schedules` (`id`, `vehicle_id`, `service_category_id`, `last_service_date`, `last_service_mileage`, `interval_km`, `interval_days`, `next_due_date`, `next_due_mileage`, `updated_at`) VALUES (1,1,1,'2026-09-16',62000,5000,180,'2027-03-15',67000,NULL),(2,7,2,'2026-09-07',88000,10000,180,'2027-03-06',98000,NULL); /*!40000 ALTER TABLE `maintenance_schedules` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `mechanics` -- DROP TABLE IF EXISTS `mechanics`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `mechanics` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned DEFAULT NULL, `full_name` varchar(150) NOT NULL, `phone` varchar(30) DEFAULT NULL, `specialization` varchar(150) DEFAULT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT current_timestamp(), `deleted_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_mechanics_user` (`user_id`), CONSTRAINT `fk_mechanics_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `mechanics` -- LOCK TABLES `mechanics` WRITE; /*!40000 ALTER TABLE `mechanics` DISABLE KEYS */; INSERT INTO `mechanics` (`id`, `user_id`, `full_name`, `phone`, `specialization`, `is_active`, `created_at`, `deleted_at`) VALUES (1,NULL,'Dawit Alemu','0922334455','Engine & Diagnostics',1,'2026-09-16 16:22:03',NULL),(2,NULL,'Solomon Bekele','0911223300','Brakes & Suspension',1,'2026-01-05 08:00:00',NULL),(3,NULL,'Yared Mulugeta','0922334400','AC & Electrical',1,'2026-01-05 08:00:00',NULL),(4,NULL,'Kalkidan Fikru','0933445500','Body & Paint',1,'2026-01-05 08:00:00',NULL); /*!40000 ALTER TABLE `mechanics` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `part_categories` -- DROP TABLE IF EXISTS `part_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `part_categories` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `description` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_part_categories_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `part_categories` -- LOCK TABLES `part_categories` WRITE; /*!40000 ALTER TABLE `part_categories` DISABLE KEYS */; INSERT INTO `part_categories` (`id`, `name`, `description`, `created_at`) VALUES (1,'Engine Oil & Fluids','Engine oil, coolant, brake fluid, transmission fluid','2026-09-16 16:39:01'),(2,'Filters','Oil, air, cabin, and fuel filters','2026-09-16 16:39:01'),(3,'Brakes','Brake pads, discs, calipers, brake lines','2026-09-16 16:39:01'),(4,'Tires & Wheels','Tires, rims, valves, balancing weights','2026-09-16 16:39:01'),(5,'Battery & Electrical','Batteries, alternators, starters, wiring, bulbs','2026-09-16 16:39:01'),(6,'Engine Parts','Belts, hoses, spark plugs, gaskets','2026-09-16 16:39:01'),(7,'Suspension & Steering','Shocks, struts, control arms, tie rods','2026-09-16 16:39:01'),(8,'Body & Exterior','Bumpers, panels, mirrors, lights, glass','2026-09-16 16:39:01'),(9,'AC & Cooling','AC compressors, radiators, condensers','2026-09-16 16:39:01'),(10,'Other','Miscellaneous parts and consumables','2026-09-16 16:39:01'); /*!40000 ALTER TABLE `part_categories` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `parts` -- DROP TABLE IF EXISTS `parts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `parts` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `sku` varchar(40) NOT NULL, `part_number` varchar(60) DEFAULT NULL, `name` varchar(150) NOT NULL, `part_category_id` int(10) unsigned DEFAULT NULL, `brand` varchar(100) DEFAULT NULL, `compatible_vehicle` varchar(150) DEFAULT NULL, `unit` varchar(30) NOT NULL DEFAULT 'pcs', `cost_price` decimal(12,2) NOT NULL DEFAULT 0.00, `selling_price` decimal(12,2) NOT NULL DEFAULT 0.00, `stock_quantity` decimal(12,2) NOT NULL DEFAULT 0.00, `min_stock_level` decimal(12,2) NOT NULL DEFAULT 0.00, `max_stock_level` decimal(12,2) DEFAULT NULL, `supplier_id` int(10) unsigned DEFAULT NULL, `location` varchar(100) DEFAULT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT current_timestamp(), `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_parts_sku` (`sku`), KEY `idx_parts_category` (`part_category_id`), KEY `idx_parts_supplier` (`supplier_id`), KEY `idx_parts_name` (`name`), CONSTRAINT `fk_parts_category` FOREIGN KEY (`part_category_id`) REFERENCES `part_categories` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_parts_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `parts` -- LOCK TABLES `parts` WRITE; /*!40000 ALTER TABLE `parts` DISABLE KEYS */; INSERT INTO `parts` (`id`, `sku`, `part_number`, `name`, `part_category_id`, `brand`, `compatible_vehicle`, `unit`, `cost_price`, `selling_price`, `stock_quantity`, `min_stock_level`, `max_stock_level`, `supplier_id`, `location`, `is_active`, `created_at`, `updated_at`) VALUES (1,'PRT-00001','','Engine Oil Filter',2,'Toyota','','pcs',0.00,450.00,20.00,5.00,NULL,NULL,'',1,'2026-09-16 16:45:42','2026-09-16 16:49:04'),(2,'PRT-00002','AF-2201','Air Filter',2,'Denso','Toyota/Hyundai','pcs',280.00,420.00,15.00,5.00,50.00,1,'Shelf B1',1,'2026-08-01 09:00:00',NULL),(3,'PRT-00003','BP-3301','Front Brake Pad Set',3,'Bosch','Universal Sedan','set',950.00,1450.00,8.00,4.00,30.00,1,'Shelf C2',1,'2026-08-01 09:00:00',NULL),(4,'PRT-00004','BAT-4401','Car Battery 12V 60Ah',5,'Exide','Universal','pcs',3200.00,4500.00,4.00,3.00,15.00,2,'Shelf D1',1,'2026-08-01 09:00:00',NULL),(5,'PRT-00005','SPK-5501','Spark Plug Set (4pcs)',6,'NGK','Universal Petrol','set',600.00,900.00,2.00,5.00,25.00,1,'Shelf E1',1,'2026-08-01 09:00:00',NULL),(6,'PRT-00006','COOL-6601','Engine Coolant 4L',1,'Prestone','Universal','pcs',350.00,550.00,0.00,5.00,30.00,2,'Shelf A3',1,'2026-08-01 09:00:00',NULL); /*!40000 ALTER TABLE `parts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `payments` -- DROP TABLE IF EXISTS `payments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `payments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `payment_number` varchar(30) NOT NULL, `invoice_id` int(10) unsigned NOT NULL, `amount` decimal(12,2) NOT NULL, `method` enum('cash','bank_transfer','mobile_money','card','other') NOT NULL DEFAULT 'cash', `reference` varchar(100) DEFAULT NULL, `payment_date` date NOT NULL, `received_by` int(10) unsigned DEFAULT NULL, `notes` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_payment_number` (`payment_number`), KEY `idx_payments_invoice` (`invoice_id`), KEY `fk_payments_user` (`received_by`), CONSTRAINT `fk_payments_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`), CONSTRAINT `fk_payments_user` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `payments` -- LOCK TABLES `payments` WRITE; /*!40000 ALTER TABLE `payments` DISABLE KEYS */; INSERT INTO `payments` (`id`, `payment_number`, `invoice_id`, `amount`, `method`, `reference`, `payment_date`, `received_by`, `notes`, `created_at`) VALUES (1,'PMT-2026-0001',1,500.00,'cash','Cash deposit','2026-09-16',1,NULL,'2026-09-16 16:58:27'),(2,'PMT-2026-0002',1,880.00,'cash','','2026-09-16',1,NULL,'2026-09-16 16:58:42'),(4,'PMT-2026-0003',2,3507.50,'bank_transfer',NULL,'2026-09-07',1,NULL,'2026-09-07 21:13:34'); /*!40000 ALTER TABLE `payments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `permissions` -- DROP TABLE IF EXISTS `permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `permissions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `module` varchar(60) NOT NULL, `slug` varchar(100) NOT NULL, `name` varchar(150) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_permissions_slug` (`slug`), KEY `idx_permissions_module` (`module`) ) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `permissions` -- LOCK TABLES `permissions` WRITE; /*!40000 ALTER TABLE `permissions` DISABLE KEYS */; INSERT INTO `permissions` (`id`, `module`, `slug`, `name`) VALUES (1,'users','users.view','View Users'),(2,'users','users.manage','Create/Edit/Deactivate Users'),(3,'roles','roles.view','View Roles & Permissions'),(4,'roles','roles.manage','Create/Edit/Delete Roles'),(5,'customers','customers.view','View Customers'),(6,'customers','customers.manage','Create/Edit/Delete Customers'),(7,'vehicles','vehicles.view','View Vehicles'),(8,'vehicles','vehicles.manage','Create/Edit/Delete Vehicles'),(9,'settings','settings.manage','Manage Branding & System Settings'),(10,'mechanics','mechanics.view','View Mechanics'),(11,'mechanics','mechanics.manage','Create/Edit Mechanics'),(12,'services','services.view','View Service Categories & Packages'),(13,'services','services.manage','Manage Service Categories & Packages'),(14,'appointments','appointments.view','View Appointments'),(15,'appointments','appointments.manage','Create/Edit Appointments & Check-in'),(16,'workorders','workorders.view','View Work Orders'),(17,'workorders','workorders.manage','Create/Edit Work Orders & Change Status'),(18,'jobcards','jobcards.view','View Job Cards'),(19,'jobcards','jobcards.manage','Update Job Card Progress'),(20,'inspections','inspections.view','View Vehicle Inspections'),(21,'inspections','inspections.manage','Create/Edit Vehicle Inspections'),(22,'insurance','insurance.view','View Insurance Claims'),(23,'insurance','insurance.manage','Manage Insurance Claims, Surveys & Approvals'),(24,'documents','documents.view','View Vehicle Photos & Documents'),(25,'documents','documents.manage','Upload Vehicle Photos & Documents'),(26,'inventory','inventory.view','View Inventory & Stock'),(27,'inventory','inventory.manage','Manage Parts & Stock Adjustments'),(28,'suppliers','suppliers.view','View Suppliers'),(29,'suppliers','suppliers.manage','Manage Suppliers'),(30,'purchases','purchases.view','View Purchases'),(31,'purchases','purchases.manage','Create/Receive Purchases'),(32,'estimates','estimates.view','View Estimates'),(33,'estimates','estimates.manage','Create/Edit Estimates'),(34,'invoices','invoices.view','View Invoices'),(35,'invoices','invoices.manage','Create/Edit Invoices'),(36,'payments','payments.view','View Payments'),(37,'payments','payments.manage','Record Payments'),(38,'expenses','expenses.view','View Expenses'),(39,'expenses','expenses.manage','Record Expenses'),(40,'coupons','coupons.view','View Coupons'),(41,'coupons','coupons.manage','Create/Edit Coupons'),(42,'reports','reports.view','View Reports'),(43,'audit','audit.view','View Audit Logs'); /*!40000 ALTER TABLE `permissions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `purchase_items` -- DROP TABLE IF EXISTS `purchase_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `purchase_items` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `purchase_id` int(10) unsigned NOT NULL, `part_id` int(10) unsigned DEFAULT NULL, `description` varchar(200) NOT NULL, `quantity` decimal(12,2) NOT NULL DEFAULT 1.00, `unit_cost` decimal(12,2) NOT NULL DEFAULT 0.00, `line_total` decimal(12,2) NOT NULL DEFAULT 0.00, PRIMARY KEY (`id`), KEY `idx_purchase_items_purchase` (`purchase_id`), KEY `fk_purchase_items_part` (`part_id`), CONSTRAINT `fk_purchase_items_part` FOREIGN KEY (`part_id`) REFERENCES `parts` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_purchase_items_purchase` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `purchase_items` -- LOCK TABLES `purchase_items` WRITE; /*!40000 ALTER TABLE `purchase_items` DISABLE KEYS */; INSERT INTO `purchase_items` (`id`, `purchase_id`, `part_id`, `description`, `quantity`, `unit_cost`, `line_total`) VALUES (1,1,1,'Engine Oil Filter',20.00,300.00,6000.00); /*!40000 ALTER TABLE `purchase_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `purchases` -- DROP TABLE IF EXISTS `purchases`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `purchases` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `purchase_number` varchar(30) NOT NULL, `supplier_id` int(10) unsigned NOT NULL, `purchase_date` date NOT NULL, `status` enum('pending','received','cancelled') NOT NULL DEFAULT 'pending', `payment_status` enum('unpaid','partial','paid') NOT NULL DEFAULT 'unpaid', `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00, `discount` decimal(12,2) NOT NULL DEFAULT 0.00, `tax` decimal(12,2) NOT NULL DEFAULT 0.00, `total` decimal(12,2) NOT NULL DEFAULT 0.00, `paid_amount` decimal(12,2) NOT NULL DEFAULT 0.00, `notes` varchar(255) DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), `received_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_purchase_number` (`purchase_number`), KEY `idx_purchases_supplier` (`supplier_id`), CONSTRAINT `fk_purchases_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `purchases` -- LOCK TABLES `purchases` WRITE; /*!40000 ALTER TABLE `purchases` DISABLE KEYS */; INSERT INTO `purchases` (`id`, `purchase_number`, `supplier_id`, `purchase_date`, `status`, `payment_status`, `subtotal`, `discount`, `tax`, `total`, `paid_amount`, `notes`, `created_at`, `received_at`) VALUES (1,'PO-2026-0001',1,'2026-09-16','received','unpaid',6000.00,0.00,0.00,6000.00,0.00,'','2026-09-16 16:47:01','2026-09-16 16:47:46'); /*!40000 ALTER TABLE `purchases` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `role_permissions` -- DROP TABLE IF EXISTS `role_permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `role_permissions` ( `role_id` int(10) unsigned NOT NULL, `permission_id` int(10) unsigned NOT NULL, PRIMARY KEY (`role_id`,`permission_id`), KEY `fk_rp_permission` (`permission_id`), CONSTRAINT `fk_rp_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `role_permissions` -- LOCK TABLES `role_permissions` WRITE; /*!40000 ALTER TABLE `role_permissions` DISABLE KEYS */; INSERT INTO `role_permissions` (`role_id`, `permission_id`) VALUES (2,1),(2,2),(2,3),(2,4),(2,5),(2,6),(2,7),(2,8),(2,9),(2,10),(2,11),(2,12),(2,13),(2,14),(2,15),(2,16),(2,17),(2,18),(2,19),(2,20),(2,21),(2,22),(2,23),(2,24),(2,25),(2,26),(2,27),(2,28),(2,29),(2,30),(2,31),(2,32),(2,33),(2,34),(2,35),(2,36),(2,37),(2,38),(2,39),(2,40),(2,41),(2,42),(2,43),(3,5),(3,6),(3,7),(3,8),(3,10),(3,12),(3,14),(3,15),(3,16),(3,17),(3,18),(3,20),(3,21),(3,22),(3,23),(3,24),(3,25),(3,26),(3,27),(3,28),(3,29),(3,30),(3,31),(3,32),(3,33),(3,34),(3,35),(3,36),(3,37),(3,38),(3,39),(3,40),(3,41),(3,42),(3,43),(4,5),(4,6),(4,7),(4,8),(4,10),(4,12),(4,14),(4,15),(4,16),(4,17),(4,22),(4,24),(4,25),(5,5),(5,7),(5,10),(5,12),(5,14),(5,15),(5,16),(5,17),(5,18),(5,19),(5,20),(5,21),(5,22),(5,23),(5,24),(5,25),(5,26),(5,32),(5,33),(5,34),(6,5),(6,7),(6,16),(6,18),(6,19),(6,20),(6,24),(6,26),(7,5),(7,7),(7,26),(7,27),(7,28),(7,29),(7,30),(7,31),(8,5),(8,7),(8,32),(8,34),(8,35),(8,36),(8,37),(9,5),(9,7),(9,32),(9,33),(9,34),(9,35),(9,36),(9,37),(9,38),(9,39),(9,40),(9,41),(9,42); /*!40000 ALTER TABLE `role_permissions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `roles` -- DROP TABLE IF EXISTS `roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `roles` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `description` varchar(255) DEFAULT NULL, `is_super_admin` tinyint(1) NOT NULL DEFAULT 0, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_roles_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `roles` -- LOCK TABLES `roles` WRITE; /*!40000 ALTER TABLE `roles` DISABLE KEYS */; INSERT INTO `roles` (`id`, `name`, `description`, `is_super_admin`, `created_at`) VALUES (1,'Super Admin','Full unrestricted access to every module and setting.',1,'2026-09-16 16:07:33'),(2,'Admin','Administrative access to daily operations and configuration.',0,'2026-09-16 16:07:33'),(3,'Manager','Oversees workshop operations, approvals, and reporting.',0,'2026-09-16 16:07:33'),(4,'Receptionist','Handles customer check-in, appointments, and front-desk tasks.',0,'2026-09-16 16:07:33'),(5,'Service Advisor','Manages service work orders and customer communication.',0,'2026-09-16 16:07:33'),(6,'Mechanic','Works on assigned job cards and updates job status.',0,'2026-09-16 16:07:33'),(7,'Storekeeper','Manages inventory, parts, and purchases.',0,'2026-09-16 16:07:33'),(8,'Cashier','Handles invoices and payment collection.',0,'2026-09-16 16:07:33'),(9,'Accountant','Manages financial records, expenses, and reports.',0,'2026-09-16 16:07:33'); /*!40000 ALTER TABLE `roles` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `service_appointments` -- DROP TABLE IF EXISTS `service_appointments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `service_appointments` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `customer_id` int(10) unsigned NOT NULL, `vehicle_id` int(10) unsigned NOT NULL, `service_category_id` int(10) unsigned DEFAULT NULL, `service_package_id` int(10) unsigned DEFAULT NULL, `appointment_date` date NOT NULL, `appointment_time` time DEFAULT NULL, `expected_duration_minutes` int(10) unsigned DEFAULT NULL, `advisor_id` int(10) unsigned DEFAULT NULL, `mechanic_id` int(10) unsigned DEFAULT NULL, `notes` text DEFAULT NULL, `status` enum('scheduled','confirmed','arrived','in_service','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled', `work_order_id` int(10) unsigned DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `idx_appt_customer` (`customer_id`), KEY `idx_appt_vehicle` (`vehicle_id`), KEY `idx_appt_date` (`appointment_date`), KEY `fk_appt_category` (`service_category_id`), KEY `fk_appt_package` (`service_package_id`), KEY `fk_appt_advisor` (`advisor_id`), KEY `fk_appt_mechanic` (`mechanic_id`), CONSTRAINT `fk_appt_advisor` FOREIGN KEY (`advisor_id`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_appt_category` FOREIGN KEY (`service_category_id`) REFERENCES `service_categories` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_appt_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`), CONSTRAINT `fk_appt_mechanic` FOREIGN KEY (`mechanic_id`) REFERENCES `mechanics` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_appt_package` FOREIGN KEY (`service_package_id`) REFERENCES `service_packages` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_appt_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `service_appointments` -- LOCK TABLES `service_appointments` WRITE; /*!40000 ALTER TABLE `service_appointments` DISABLE KEYS */; INSERT INTO `service_appointments` (`id`, `customer_id`, `vehicle_id`, `service_category_id`, `service_package_id`, `appointment_date`, `appointment_time`, `expected_duration_minutes`, `advisor_id`, `mechanic_id`, `notes`, `status`, `work_order_id`, `created_at`, `updated_at`) VALUES (1,1,1,NULL,NULL,'2026-09-20',NULL,NULL,1,NULL,'','scheduled',NULL,'2026-09-16 16:25:52',NULL),(2,4,4,2,NULL,'2026-09-16','09:00:00',NULL,NULL,NULL,'Fleet vehicle routine service','scheduled',NULL,'2026-09-16 21:12:53',NULL),(3,5,6,3,NULL,'2026-09-18','11:00:00',NULL,NULL,NULL,'Customer reports squeaking brakes','confirmed',NULL,'2026-09-16 21:12:53',NULL); /*!40000 ALTER TABLE `service_appointments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `service_categories` -- DROP TABLE IF EXISTS `service_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `service_categories` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `description` varchar(255) DEFAULT NULL, `default_interval_km` int(10) unsigned DEFAULT NULL, `default_interval_days` int(10) unsigned DEFAULT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_service_categories_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `service_categories` -- LOCK TABLES `service_categories` WRITE; /*!40000 ALTER TABLE `service_categories` DISABLE KEYS */; INSERT INTO `service_categories` (`id`, `name`, `description`, `default_interval_km`, `default_interval_days`, `is_active`, `created_at`) VALUES (1,'Oil Change','Engine oil and filter replacement',5000,180,1,'2026-09-16 16:14:29'),(2,'General Service','Routine multi-point vehicle service',10000,180,1,'2026-09-16 16:14:29'),(3,'Brake Service','Brake pads, discs, and fluid inspection/service',20000,365,1,'2026-09-16 16:14:29'),(4,'Tire Service','Tire rotation, balancing, and replacement',10000,180,1,'2026-09-16 16:14:29'),(5,'Battery Service','Battery testing, charging, and replacement',NULL,365,1,'2026-09-16 16:14:29'),(6,'Engine Service','Engine diagnostics and repair work',NULL,NULL,1,'2026-09-16 16:14:29'),(7,'Transmission Service','Transmission fluid and system service',40000,730,1,'2026-09-16 16:14:29'),(8,'AC Service','Air conditioning inspection and recharge',NULL,365,1,'2026-09-16 16:14:29'),(9,'Electrical Service','Electrical system diagnostics and repair',NULL,NULL,1,'2026-09-16 16:14:29'),(10,'Inspection','General vehicle inspection',NULL,180,1,'2026-09-16 16:14:29'),(11,'Diagnostic Service','Computerized diagnostic scan',NULL,NULL,1,'2026-09-16 16:14:29'),(12,'Filter Replacement','Air, cabin, and fuel filter replacement',10000,180,1,'2026-09-16 16:14:29'),(13,'Fluid Replacement','Coolant, brake, and other fluid replacement',20000,365,1,'2026-09-16 16:14:29'),(14,'Preventive Maintenance','Scheduled preventive maintenance',10000,180,1,'2026-09-16 16:14:29'),(15,'Other','Other service not otherwise categorized',NULL,NULL,1,'2026-09-16 16:14:29'); /*!40000 ALTER TABLE `service_categories` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `service_history` -- DROP TABLE IF EXISTS `service_history`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `service_history` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `vehicle_id` int(10) unsigned NOT NULL, `work_order_id` int(10) unsigned DEFAULT NULL, `service_category_id` int(10) unsigned DEFAULT NULL, `description` varchar(255) NOT NULL, `mileage` int(10) unsigned DEFAULT NULL, `mechanic_id` int(10) unsigned DEFAULT NULL, `performed_at` datetime NOT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_history_vehicle` (`vehicle_id`), KEY `fk_history_wo` (`work_order_id`), KEY `fk_history_category` (`service_category_id`), KEY `fk_history_mechanic` (`mechanic_id`), CONSTRAINT `fk_history_category` FOREIGN KEY (`service_category_id`) REFERENCES `service_categories` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_history_mechanic` FOREIGN KEY (`mechanic_id`) REFERENCES `mechanics` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_history_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_history_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `service_history` -- LOCK TABLES `service_history` WRITE; /*!40000 ALTER TABLE `service_history` DISABLE KEYS */; INSERT INTO `service_history` (`id`, `vehicle_id`, `work_order_id`, `service_category_id`, `description`, `mileage`, `mechanic_id`, `performed_at`, `created_at`) VALUES (1,1,1,1,'Oil Change completed',62000,1,'2026-09-16 16:24:16','2026-09-16 16:24:16'),(2,7,6,2,'General Service completed',88000,2,'2026-09-07 21:12:53','2026-09-16 21:12:53'); /*!40000 ALTER TABLE `service_history` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `service_package_items` -- DROP TABLE IF EXISTS `service_package_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `service_package_items` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `service_package_id` int(10) unsigned NOT NULL, `item_type` enum('service','part','labor') NOT NULL DEFAULT 'part', `description` varchar(200) NOT NULL, `quantity` decimal(10,2) NOT NULL DEFAULT 1.00, `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00, PRIMARY KEY (`id`), KEY `idx_package_items_package` (`service_package_id`), CONSTRAINT `fk_package_items_package` FOREIGN KEY (`service_package_id`) REFERENCES `service_packages` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `service_package_items` -- LOCK TABLES `service_package_items` WRITE; /*!40000 ALTER TABLE `service_package_items` DISABLE KEYS */; INSERT INTO `service_package_items` (`id`, `service_package_id`, `item_type`, `description`, `quantity`, `unit_price`) VALUES (1,1,'part','Engine oil + filter',1.00,850.00); /*!40000 ALTER TABLE `service_package_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `service_packages` -- DROP TABLE IF EXISTS `service_packages`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `service_packages` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `service_category_id` int(10) unsigned DEFAULT NULL, `name` varchar(150) NOT NULL, `description` text DEFAULT NULL, `price` decimal(12,2) NOT NULL DEFAULT 0.00, `estimated_duration_minutes` int(10) unsigned DEFAULT NULL, `recommended_mileage_interval` int(10) unsigned DEFAULT NULL, `recommended_days_interval` int(10) unsigned DEFAULT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_packages_category` (`service_category_id`), CONSTRAINT `fk_packages_category` FOREIGN KEY (`service_category_id`) REFERENCES `service_categories` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `service_packages` -- LOCK TABLES `service_packages` WRITE; /*!40000 ALTER TABLE `service_packages` DISABLE KEYS */; INSERT INTO `service_packages` (`id`, `service_category_id`, `name`, `description`, `price`, `estimated_duration_minutes`, `recommended_mileage_interval`, `recommended_days_interval`, `is_active`, `created_at`) VALUES (1,2,'Basic Service Package','',1500.00,NULL,5000,180,1,'2026-09-16 16:25:27'); /*!40000 ALTER TABLE `service_packages` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `settings` -- DROP TABLE IF EXISTS `settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `settings` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `setting_key` varchar(100) NOT NULL, `setting_value` text DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_settings_key` (`setting_key`) ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `settings` -- LOCK TABLES `settings` WRITE; /*!40000 ALTER TABLE `settings` DISABLE KEYS */; INSERT INTO `settings` (`id`, `setting_key`, `setting_value`) VALUES (1,'garage_name','Sample Garage Management'),(2,'garage_short_name','Sample Garage'),(3,'garage_phone','+251 11 000 0000'),(4,'garage_email','info@samplegarage.et'),(5,'garage_address','Bole Road, Addis Ababa, Ethiopia'),(6,'garage_website','www.samplegarage.et'),(7,'currency_symbol','ETB'),(8,'currency_code','ETB'),(9,'tax_enabled','1'),(10,'tax_rate','15'),(11,'tax_number',''),(12,'invoice_prefix','INV'),(13,'estimate_prefix','EST'),(14,'workorder_prefix','WO'),(15,'invoice_footer','Thank you for trusting us with your vehicle.'),(16,'invoice_terms','Payment is due upon vehicle collection unless otherwise agreed in writing.'),(17,'default_labor_rate','350'),(18,'low_stock_default_threshold','5'),(19,'service_due_km_threshold','1000'),(20,'service_due_days_threshold','30'),(21,'inactive_customer_months','6'),(22,'vehicle_aging_days_threshold','3'),(23,'brand_primary_color','#0f5132'),(24,'brand_secondary_color','#1c2b36'),(25,'brand_accent_color','#c9a227'),(26,'brand_background_color','#f5f6f8'); /*!40000 ALTER TABLE `settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `suppliers` -- DROP TABLE IF EXISTS `suppliers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `suppliers` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(150) NOT NULL, `contact_person` varchar(150) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `email` varchar(150) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `tax_number` varchar(60) DEFAULT NULL, `payment_terms` varchar(100) DEFAULT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_suppliers_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `suppliers` -- LOCK TABLES `suppliers` WRITE; /*!40000 ALTER TABLE `suppliers` DISABLE KEYS */; INSERT INTO `suppliers` (`id`, `name`, `contact_person`, `phone`, `email`, `address`, `tax_number`, `payment_terms`, `is_active`, `created_at`) VALUES (1,'Addis Auto Parts PLC','Yohannes Girma','+251 91 234 5678','sales@addisautoparts.et','Merkato, Addis Ababa',NULL,'Net 30',1,'2026-09-16 16:39:01'),(2,'Horizon Spare Parts Import','Sara Alemayehu','+251 92 345 6789','info@horizonspares.et','Bole, Addis Ababa',NULL,'Net 15',1,'2026-09-16 16:39:01'); /*!40000 ALTER TABLE `suppliers` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `users` -- DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `full_name` varchar(150) NOT NULL, `email` varchar(150) NOT NULL, `phone` varchar(30) DEFAULT NULL, `password` varchar(255) NOT NULL, `role_id` int(10) unsigned NOT NULL, `is_active` tinyint(1) NOT NULL DEFAULT 1, `reset_token` varchar(64) DEFAULT NULL, `reset_expires` datetime DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_users_email` (`email`), KEY `idx_users_role` (`role_id`), CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `users` -- LOCK TABLES `users` WRITE; /*!40000 ALTER TABLE `users` DISABLE KEYS */; INSERT INTO `users` (`id`, `full_name`, `email`, `phone`, `password`, `role_id`, `is_active`, `reset_token`, `reset_expires`, `created_at`, `updated_at`, `deleted_at`) VALUES (1,'System Administrator','admin@garage.local','+251900000000','$2y$10$nPXcIei6n/WIvpgwcMHbJOrOoM6gvAGJZzHzV8Ix9w.NgDYSgeXxy',1,1,NULL,NULL,'2026-09-16 16:07:33','2026-09-16 17:05:17',NULL),(2,'Test Mechanic','mechanic@garage.local','','$2y$10$4cYZ0qvb8.FRx/ju92d/bOPmQmfFiLjWdk89Edy4tFCy2iAvQBR8u',6,1,NULL,NULL,'2026-09-16 16:11:21',NULL,NULL); /*!40000 ALTER TABLE `users` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `vehicle_documents` -- DROP TABLE IF EXISTS `vehicle_documents`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `vehicle_documents` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `vehicle_id` int(10) unsigned DEFAULT NULL, `work_order_id` int(10) unsigned DEFAULT NULL, `customer_id` int(10) unsigned DEFAULT NULL, `insurance_claim_id` int(10) unsigned DEFAULT NULL, `title` varchar(150) NOT NULL, `file_path` varchar(255) NOT NULL, `uploaded_by` int(10) unsigned DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_docs_vehicle` (`vehicle_id`), KEY `idx_docs_wo` (`work_order_id`), KEY `idx_docs_customer` (`customer_id`), KEY `fk_docs_user` (`uploaded_by`), CONSTRAINT `fk_docs_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_docs_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_docs_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_docs_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `vehicle_documents` -- LOCK TABLES `vehicle_documents` WRITE; /*!40000 ALTER TABLE `vehicle_documents` DISABLE KEYS */; /*!40000 ALTER TABLE `vehicle_documents` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `vehicle_inspections` -- DROP TABLE IF EXISTS `vehicle_inspections`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `vehicle_inspections` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `work_order_id` int(10) unsigned DEFAULT NULL, `vehicle_id` int(10) unsigned NOT NULL, `area` enum('exterior','interior','engine','electrical','brakes','tires','suspension','transmission','cooling','ac','other') NOT NULL, `condition_rating` enum('good','fair','poor','na') NOT NULL DEFAULT 'good', `notes` varchar(255) DEFAULT NULL, `inspector_id` int(10) unsigned DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_inspections_wo` (`work_order_id`), KEY `idx_inspections_vehicle` (`vehicle_id`), KEY `fk_inspections_inspector` (`inspector_id`), CONSTRAINT `fk_inspections_inspector` FOREIGN KEY (`inspector_id`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_inspections_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_inspections_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `vehicle_inspections` -- LOCK TABLES `vehicle_inspections` WRITE; /*!40000 ALTER TABLE `vehicle_inspections` DISABLE KEYS */; INSERT INTO `vehicle_inspections` (`id`, `work_order_id`, `vehicle_id`, `area`, `condition_rating`, `notes`, `inspector_id`, `created_at`) VALUES (1,2,1,'exterior','poor','Front bumper cracked, headlight broken',1,'2026-09-16 16:35:52'),(2,2,1,'engine','good',NULL,1,'2026-09-16 16:35:52'); /*!40000 ALTER TABLE `vehicle_inspections` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `vehicle_photos` -- DROP TABLE IF EXISTS `vehicle_photos`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `vehicle_photos` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `vehicle_id` int(10) unsigned NOT NULL, `work_order_id` int(10) unsigned DEFAULT NULL, `category` enum('front','rear','left','right','interior','engine','damage','other') NOT NULL DEFAULT 'other', `file_path` varchar(255) NOT NULL, `caption` varchar(150) DEFAULT NULL, `uploaded_by` int(10) unsigned DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_photos_vehicle` (`vehicle_id`), KEY `idx_photos_wo` (`work_order_id`), KEY `fk_photos_user` (`uploaded_by`), CONSTRAINT `fk_photos_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_photos_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_photos_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `vehicle_photos` -- LOCK TABLES `vehicle_photos` WRITE; /*!40000 ALTER TABLE `vehicle_photos` DISABLE KEYS */; INSERT INTO `vehicle_photos` (`id`, `vehicle_id`, `work_order_id`, `category`, `file_path`, `caption`, `uploaded_by`, `created_at`) VALUES (1,1,2,'damage','files/vehicles/71ab1c7ce13e13e8.png','Front bumper crack',1,'2026-09-16 16:37:15'); /*!40000 ALTER TABLE `vehicle_photos` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `vehicles` -- DROP TABLE IF EXISTS `vehicles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `vehicles` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `customer_id` int(10) unsigned NOT NULL, `plate_number` varchar(30) NOT NULL, `vin` varchar(50) DEFAULT NULL, `engine_number` varchar(50) DEFAULT NULL, `make` varchar(60) NOT NULL, `model` varchar(60) NOT NULL, `year` smallint(5) unsigned DEFAULT NULL, `color` varchar(40) DEFAULT NULL, `vehicle_type` enum('sedan','suv','pickup','van','truck','bus','motorcycle','other') NOT NULL DEFAULT 'sedan', `fuel_type` enum('petrol','diesel','hybrid','electric','other') NOT NULL DEFAULT 'petrol', `current_mileage` int(10) unsigned NOT NULL DEFAULT 0, `insurance_company_name` varchar(150) DEFAULT NULL, `notes` text DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), `deleted_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_vehicles_customer` (`customer_id`), KEY `idx_vehicles_plate` (`plate_number`), KEY `idx_vehicles_vin` (`vin`), CONSTRAINT `fk_vehicles_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `vehicles` -- LOCK TABLES `vehicles` WRITE; /*!40000 ALTER TABLE `vehicles` DISABLE KEYS */; INSERT INTO `vehicles` (`id`, `customer_id`, `plate_number`, `vin`, `engine_number`, `make`, `model`, `year`, `color`, `vehicle_type`, `fuel_type`, `current_mileage`, `insurance_company_name`, `notes`, `created_at`, `updated_at`, `deleted_at`) VALUES (1,1,'AA-12345','JT2BF22K1W0123456','','Toyota','Corolla',2018,'White','sedan','petrol',62500,'','','2026-09-16 16:10:25','2026-09-16 21:05:37',NULL),(2,2,'AA-22334','JT3HP10V5W7123456','4A-8812334','Toyota','RAV4',2019,'Silver','suv','petrol',48000,'Awash Insurance Company',NULL,'2026-08-01 09:05:00',NULL,NULL),(3,3,'AA-45566','KMHCT4AE9DU123456','G4-9982211','Hyundai','Elantra',2016,'Blue','sedan','petrol',95000,NULL,NULL,'2026-08-05 10:05:00',NULL,NULL),(4,4,'AA-77812','JHMFA16506S123456','L15-771122','Isuzu','NPR Van',2021,'White','van','diesel',32000,'Ethiopian Insurance Corporation','Fleet vehicle #1','2026-08-10 11:05:00',NULL,NULL),(5,4,'AA-77813','JHMFA16507S123457','L15-771123','Isuzu','NPR Van',2021,'White','van','diesel',29500,'Ethiopian Insurance Corporation','Fleet vehicle #2','2026-08-10 11:06:00',NULL,NULL),(6,5,'AA-33221','1HGCM82633A123456','K24-556677','Honda','Civic',2020,'Black','sedan','petrol',21000,NULL,NULL,'2026-08-15 14:05:00',NULL,NULL),(7,6,'AA-55990','WBAPK73579A123456','N47-334455','BMW','320i',2017,'Grey','sedan','petrol',88000,NULL,'Regular customer, prefers OEM parts.','2026-01-10 09:05:00',NULL,NULL); /*!40000 ALTER TABLE `vehicles` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `work_order_items` -- DROP TABLE IF EXISTS `work_order_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `work_order_items` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `work_order_id` int(10) unsigned NOT NULL, `item_type` enum('service','part','labor') NOT NULL DEFAULT 'part', `part_id` int(10) unsigned DEFAULT NULL, `description` varchar(200) NOT NULL, `quantity` decimal(10,2) NOT NULL DEFAULT 1.00, `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00, `line_total` decimal(12,2) NOT NULL DEFAULT 0.00, `created_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_wo_items_wo` (`work_order_id`), KEY `fk_wo_items_part` (`part_id`), CONSTRAINT `fk_wo_items_part` FOREIGN KEY (`part_id`) REFERENCES `parts` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_wo_items_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `work_order_items` -- LOCK TABLES `work_order_items` WRITE; /*!40000 ALTER TABLE `work_order_items` DISABLE KEYS */; INSERT INTO `work_order_items` (`id`, `work_order_id`, `item_type`, `part_id`, `description`, `quantity`, `unit_price`, `line_total`, `created_at`) VALUES (1,1,'part',NULL,'Engine oil 5L + oil filter',1.00,850.00,850.00,'2026-09-16 16:24:07'),(3,2,'labor',NULL,'Bumper replacement labor',1.00,1200.00,1200.00,'2026-09-16 16:48:46'),(4,5,'part',NULL,'Brake pad replacement estimate',1.00,2200.00,2200.00,'2026-09-16 20:57:00'),(5,6,'part',NULL,'Engine oil 5L + oil filter',1.00,850.00,850.00,'2026-09-16 21:12:53'),(6,6,'part',NULL,'Air Filter',1.00,420.00,420.00,'2026-09-16 21:12:53'),(7,6,'labor',NULL,'General service labor',1.00,1780.00,1780.00,'2026-09-16 21:12:53'); /*!40000 ALTER TABLE `work_order_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `work_order_status_history` -- DROP TABLE IF EXISTS `work_order_status_history`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `work_order_status_history` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `work_order_id` int(10) unsigned NOT NULL, `from_status` varchar(30) DEFAULT NULL, `to_status` varchar(30) NOT NULL, `changed_by` int(10) unsigned DEFAULT NULL, `notes` varchar(255) DEFAULT NULL, `changed_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `idx_wo_history_wo` (`work_order_id`), KEY `fk_wo_history_user` (`changed_by`), CONSTRAINT `fk_wo_history_user` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_wo_history_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `work_order_status_history` -- LOCK TABLES `work_order_status_history` WRITE; /*!40000 ALTER TABLE `work_order_status_history` DISABLE KEYS */; INSERT INTO `work_order_status_history` (`id`, `work_order_id`, `from_status`, `to_status`, `changed_by`, `notes`, `changed_at`) VALUES (1,1,'received','in_progress',1,'Quick service check-in','2026-09-16 16:23:55'),(2,1,'in_progress','completed',1,NULL,'2026-09-16 16:24:16'),(3,2,'','received',1,'Work order created','2026-09-16 16:31:58'),(4,2,'received','completed',1,NULL,'2026-09-16 16:58:04'),(5,5,'','received',1,'Created from estimate EST-2026-0001','2026-09-16 20:56:59'),(6,5,'received','ready',1,NULL,'2026-09-16 21:05:17'),(7,5,'ready','delivered',1,'Delivered to customer. Final mileage: 62500','2026-09-16 21:05:37'); /*!40000 ALTER TABLE `work_order_status_history` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `work_orders` -- DROP TABLE IF EXISTS `work_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `work_orders` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `work_order_number` varchar(30) NOT NULL, `wo_type` enum('service','repair') NOT NULL DEFAULT 'service', `customer_id` int(10) unsigned NOT NULL, `vehicle_id` int(10) unsigned NOT NULL, `appointment_id` int(10) unsigned DEFAULT NULL, `service_category_id` int(10) unsigned DEFAULT NULL, `service_package_id` int(10) unsigned DEFAULT NULL, `advisor_id` int(10) unsigned DEFAULT NULL, `mechanic_id` int(10) unsigned DEFAULT NULL, `customer_complaint` text DEFAULT NULL, `diagnosis` text DEFAULT NULL, `mileage_at_service` int(10) unsigned DEFAULT NULL, `fuel_level` enum('empty','quarter','half','three_quarter','full') DEFAULT NULL, `damage_notes` text DEFAULT NULL, `insurance_claim_id` int(10) unsigned DEFAULT NULL, `status` enum('received','inspection','estimate','waiting_approval','waiting_parts','in_progress','qc','ready','completed','delivered','cancelled') NOT NULL DEFAULT 'received', `priority` enum('normal','high','urgent') NOT NULL DEFAULT 'normal', `estimated_cost` decimal(12,2) NOT NULL DEFAULT 0.00, `actual_cost` decimal(12,2) NOT NULL DEFAULT 0.00, `notes` text DEFAULT NULL, `promised_at` datetime DEFAULT NULL, `completed_at` datetime DEFAULT NULL, `delivered_at` datetime DEFAULT NULL, `created_at` datetime DEFAULT current_timestamp(), `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uq_wo_number` (`work_order_number`), KEY `idx_wo_customer` (`customer_id`), KEY `idx_wo_vehicle` (`vehicle_id`), KEY `idx_wo_status` (`status`), KEY `fk_wo_appointment` (`appointment_id`), KEY `fk_wo_category` (`service_category_id`), KEY `fk_wo_package` (`service_package_id`), KEY `fk_wo_advisor` (`advisor_id`), KEY `fk_wo_mechanic` (`mechanic_id`), KEY `fk_wo_insurance_claim` (`insurance_claim_id`), CONSTRAINT `fk_wo_advisor` FOREIGN KEY (`advisor_id`) REFERENCES `users` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_wo_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `service_appointments` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_wo_category` FOREIGN KEY (`service_category_id`) REFERENCES `service_categories` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_wo_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`), CONSTRAINT `fk_wo_insurance_claim` FOREIGN KEY (`insurance_claim_id`) REFERENCES `insurance_claims` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_wo_mechanic` FOREIGN KEY (`mechanic_id`) REFERENCES `mechanics` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_wo_package` FOREIGN KEY (`service_package_id`) REFERENCES `service_packages` (`id`) ON DELETE SET NULL, CONSTRAINT `fk_wo_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `work_orders` -- LOCK TABLES `work_orders` WRITE; /*!40000 ALTER TABLE `work_orders` DISABLE KEYS */; INSERT INTO `work_orders` (`id`, `work_order_number`, `wo_type`, `customer_id`, `vehicle_id`, `appointment_id`, `service_category_id`, `service_package_id`, `advisor_id`, `mechanic_id`, `customer_complaint`, `diagnosis`, `mileage_at_service`, `fuel_level`, `damage_notes`, `insurance_claim_id`, `status`, `priority`, `estimated_cost`, `actual_cost`, `notes`, `promised_at`, `completed_at`, `delivered_at`, `created_at`, `updated_at`) VALUES (1,'WO-2026-0001','service',1,1,NULL,1,NULL,1,1,'Customer requests oil change and general inspection.',NULL,62000,NULL,NULL,NULL,'completed','normal',0.00,850.00,NULL,NULL,'2026-09-16 16:24:16',NULL,'2026-09-16 16:23:55','2026-09-16 16:24:16'),(2,'WO-2026-0002','repair',1,1,NULL,NULL,NULL,1,NULL,'Front bumper and headlight damage from a collision','',NULL,NULL,NULL,1,'completed','normal',0.00,1200.00,'',NULL,'2026-09-16 16:58:04',NULL,'2026-09-16 16:31:58','2026-09-16 16:58:04'),(5,'WO-2026-0003','service',1,1,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'delivered','normal',2200.00,2200.00,'Converted from estimate EST-2026-0001',NULL,NULL,'2026-09-16 21:05:37','2026-09-16 20:56:59','2026-09-16 21:05:37'),(6,'WO-2026-0004','service',6,7,NULL,2,NULL,1,2,'Routine 90,000km service',NULL,88000,NULL,NULL,NULL,'completed','normal',3200.00,3050.00,'Full multi-point inspection performed.',NULL,'2026-09-07 21:12:53','2026-09-07 21:12:53','2026-09-06 21:12:53',NULL); /*!40000 ALTER TABLE `work_orders` ENABLE KEYS */; UNLOCK TABLES; -- ============================================================================ -- PHASE 7 — Service Reminders (reset-on-return cycle) & Rework Cost Tracking -- ============================================================================ SET FOREIGN_KEY_CHECKS = 0; CREATE TABLE IF NOT EXISTS `service_reminders` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `vehicle_id` INT UNSIGNED NOT NULL, `last_service_date` DATE NOT NULL, `next_reminder_date` DATE NOT NULL, `status` ENUM('pending','due','sent','dismissed') NOT NULL DEFAULT 'pending', `email_sent_at` DATETIME DEFAULT NULL, `whatsapp_sent_at` DATETIME DEFAULT NULL, `dismissed_at` DATETIME DEFAULT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY `uq_reminder_vehicle` (`vehicle_id`), KEY `idx_reminder_next_date` (`next_reminder_date`), CONSTRAINT `fk_reminder_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE `work_orders` ADD COLUMN IF NOT EXISTS `is_rework` TINYINT(1) NOT NULL DEFAULT 0 AFTER `wo_type`, ADD COLUMN IF NOT EXISTS `rework_of_id` INT UNSIGNED DEFAULT NULL AFTER `is_rework`, ADD COLUMN IF NOT EXISTS `rework_reason` TEXT DEFAULT NULL AFTER `rework_of_id`; ALTER TABLE `work_orders` ADD CONSTRAINT `fk_wo_rework_of` FOREIGN KEY IF NOT EXISTS (`rework_of_id`) REFERENCES `work_orders`(`id`) ON DELETE SET NULL; ALTER TABLE `inventory_transactions` MODIFY COLUMN `transaction_type` ENUM('purchase','service_usage','repair_usage','rework_usage','return','adjustment','damage','transfer') NOT NULL; ALTER TABLE `work_order_items` ADD COLUMN IF NOT EXISTS `cost_at_time` DECIMAL(12,2) DEFAULT NULL AFTER `line_total`; SET FOREIGN_KEY_CHECKS = 1; INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('reminder_cycle_months', '3'), ('smtp_host', ''), ('smtp_port', '587'), ('smtp_username', ''), ('smtp_password', ''), ('smtp_encryption', 'tls'), ('smtp_from_email', ''), ('smtp_from_name', '') ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`; INSERT INTO `permissions` (`module`, `slug`, `name`) VALUES ('reminders', 'reminders.view', 'View Next Service Reminders'), ('reminders', 'reminders.manage', 'Send/Dismiss Service Reminders'), ('rework', 'rework.view', 'View Rework Cost Report') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Admin'), p.id FROM permissions p WHERE p.module IN ('reminders','rework') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Manager'), p.id FROM permissions p WHERE p.module IN ('reminders','rework') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Receptionist'), p.id FROM permissions p WHERE p.slug IN ('reminders.view','reminders.manage') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Service Advisor'), p.id FROM permissions p WHERE p.slug IN ('reminders.view','reminders.manage') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- ============================================================================ -- PHASE 8 — VIN identity, Make/Model catalog, service base pricing, and the -- mechanic-request / storekeeper-approve spare parts workflow. -- ============================================================================ SET FOREIGN_KEY_CHECKS = 0; UPDATE `vehicles` SET `vin` = CONCAT('UNKNOWN-VIN-', `id`) WHERE `vin` IS NULL OR `vin` = ''; ALTER TABLE `vehicles` MODIFY COLUMN `vin` VARCHAR(50) NOT NULL; ALTER TABLE `vehicles` ADD UNIQUE KEY IF NOT EXISTS `uq_vehicles_vin` (`vin`); CREATE TABLE IF NOT EXISTS `vehicle_makes` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(60) NOT NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `uq_vehicle_makes_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS `vehicle_models` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `make_id` INT UNSIGNED NOT NULL, `name` VARCHAR(60) NOT NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `uq_vehicle_models` (`make_id`, `name`), CONSTRAINT `fk_vehicle_models_make` FOREIGN KEY (`make_id`) REFERENCES `vehicle_makes`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE `service_categories` ADD COLUMN IF NOT EXISTS `base_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER `description`; CREATE TABLE IF NOT EXISTS `part_requests` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `work_order_id` INT UNSIGNED NOT NULL, `requested_by` INT UNSIGNED NOT NULL, `status` ENUM('pending','issued','rejected') NOT NULL DEFAULT 'pending', `request_notes` VARCHAR(255) DEFAULT NULL, `review_notes` VARCHAR(255) DEFAULT NULL, `reviewed_by` INT UNSIGNED DEFAULT NULL, `reviewed_at` DATETIME DEFAULT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, KEY `idx_part_requests_wo` (`work_order_id`), KEY `idx_part_requests_status` (`status`), CONSTRAINT `fk_part_requests_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE, CONSTRAINT `fk_part_requests_requester` FOREIGN KEY (`requested_by`) REFERENCES `users`(`id`), CONSTRAINT `fk_part_requests_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS `part_request_items` ( `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `part_request_id` INT UNSIGNED NOT NULL, `part_id` INT UNSIGNED NOT NULL, `quantity_requested` DECIMAL(12,2) NOT NULL, `quantity_issued` DECIMAL(12,2) DEFAULT NULL, `unit_price` DECIMAL(12,2) DEFAULT NULL, `work_order_item_id` INT UNSIGNED DEFAULT NULL, KEY `idx_pri_request` (`part_request_id`), CONSTRAINT `fk_pri_request` FOREIGN KEY (`part_request_id`) REFERENCES `part_requests`(`id`) ON DELETE CASCADE, CONSTRAINT `fk_pri_part` FOREIGN KEY (`part_id`) REFERENCES `parts`(`id`), CONSTRAINT `fk_pri_woitem` FOREIGN KEY (`work_order_item_id`) REFERENCES `work_order_items`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SET FOREIGN_KEY_CHECKS = 1; UPDATE `settings` SET `setting_value` = 'JC' WHERE `setting_key` = 'workorder_prefix'; INSERT INTO `vehicle_makes` (`name`) VALUES ('Suzuki'), ('Toyota'), ('Other') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); INSERT INTO `vehicle_models` (`make_id`, `name`) SELECT m.id, v.name FROM vehicle_makes m JOIN ( SELECT 'Suzuki' make, 'Dzire' name UNION ALL SELECT 'Suzuki', 'Alto' UNION ALL SELECT 'Suzuki', 'Swift' UNION ALL SELECT 'Suzuki', 'Vitara' UNION ALL SELECT 'Suzuki', 'Grand Vitara' UNION ALL SELECT 'Suzuki', 'Baleno' UNION ALL SELECT 'Suzuki', 'Wagon R' UNION ALL SELECT 'Suzuki', 'Ertiga' UNION ALL SELECT 'Suzuki', 'Celerio' UNION ALL SELECT 'Suzuki', 'Jimny' UNION ALL SELECT 'Suzuki', 'APV' UNION ALL SELECT 'Toyota', 'Corolla' UNION ALL SELECT 'Toyota', 'Vitz' UNION ALL SELECT 'Toyota', 'Yaris' UNION ALL SELECT 'Toyota', 'Camry' UNION ALL SELECT 'Toyota', 'RAV4' UNION ALL SELECT 'Toyota', 'Land Cruiser' UNION ALL SELECT 'Toyota', 'Land Cruiser Prado' UNION ALL SELECT 'Toyota', 'Hilux' UNION ALL SELECT 'Toyota', 'Hiace' UNION ALL SELECT 'Toyota', 'Corona' UNION ALL SELECT 'Toyota', 'Premio' UNION ALL SELECT 'Toyota', 'Allion' UNION ALL SELECT 'Toyota', 'Fielder' UNION ALL SELECT 'Toyota', 'Noah' UNION ALL SELECT 'Toyota', 'Avensis' UNION ALL SELECT 'Other', 'Other' ) v ON v.make = m.name ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); UPDATE `service_categories` SET `base_price` = 1500 WHERE `name` = 'Oil Change'; UPDATE `service_categories` SET `base_price` = 2500 WHERE `name` = 'General Service'; UPDATE `service_categories` SET `base_price` = 1800 WHERE `name` = 'Brake Service'; UPDATE `service_categories` SET `base_price` = 2200 WHERE `name` = 'AC Service'; UPDATE `service_categories` SET `base_price` = 800 WHERE `name` = 'Battery Service'; UPDATE `service_categories` SET `base_price` = 500 WHERE `name` = 'Diagnostic Service'; UPDATE `service_categories` SET `base_price` = 1200 WHERE `name` = 'Electrical Service'; UPDATE `service_categories` SET `base_price` = 3500 WHERE `name` = 'Engine Service'; UPDATE `service_categories` SET `base_price` = 900 WHERE `name` = 'Filter Replacement'; UPDATE `service_categories` SET `base_price` = 1000 WHERE `name` = 'Fluid Replacement'; UPDATE `service_categories` SET `base_price` = 400 WHERE `name` = 'Inspection'; UPDATE `service_categories` SET `base_price` = 2000 WHERE `name` = 'Preventive Maintenance'; UPDATE `service_categories` SET `base_price` = 600 WHERE `name` = 'Tire Service'; UPDATE `service_categories` SET `base_price` = 4000 WHERE `name` = 'Transmission Service'; INSERT INTO `permissions` (`module`, `slug`, `name`) VALUES ('parts', 'parts.request', 'Request Spare Parts on a Job Card'), ('parts', 'parts.approve', 'Approve/Issue Spare Part Requests') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Admin'), p.id FROM permissions p WHERE p.module = 'parts' ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Manager'), p.id FROM permissions p WHERE p.module = 'parts' ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Mechanic'), p.id FROM permissions p WHERE p.slug = 'parts.request' ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Storekeeper'), p.id FROM permissions p WHERE p.slug = 'parts.approve' ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Storekeeper'), p.id FROM permissions p WHERE p.slug = 'workorders.view' ON DUPLICATE KEY UPDATE `role_id` = `role_id`; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!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 */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2026-09-16 21:14:32