-- ============================================================================ -- Garage Management System — Full Database Schema (MySQL 8 / MariaDB 10.4+) -- Generated from the actual tested database. Includes required seed data -- (roles, permissions, default admin, settings, service categories, etc.) -- appended at the end. For sample business data see demo_data.sql. -- ============================================================================ 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 */; -- -- 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 */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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 */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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 */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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 */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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 */; -- -- 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=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- 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 */; -- -- 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=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping routines for database 'garage_management' -- /*!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:09:57 -- ============================================================================ -- REQUIRED SEED DATA (roles, permissions, default admin, settings, categories) -- ============================================================================ -- ---- seed.sql ---- -- ============================================================================ -- Garage Management System — Required Seed Data (roles, permissions, admin, settings) -- Safe to re-run: uses INSERT IGNORE / ON DUPLICATE KEY UPDATE. -- ============================================================================ USE `garage_management`; -- --------------------------------------------------------------------------- -- Default roles -- --------------------------------------------------------------------------- INSERT INTO `roles` (`name`, `description`, `is_super_admin`) VALUES ('Super Admin', 'Full unrestricted access to every module and setting.', 1), ('Admin', 'Administrative access to daily operations and configuration.', 0), ('Manager', 'Oversees workshop operations, approvals, and reporting.', 0), ('Receptionist', 'Handles customer check-in, appointments, and front-desk tasks.', 0), ('Service Advisor', 'Manages service work orders and customer communication.', 0), ('Mechanic', 'Works on assigned job cards and updates job status.', 0), ('Storekeeper', 'Manages inventory, parts, and purchases.', 0), ('Cashier', 'Handles invoices and payment collection.', 0), ('Accountant', 'Manages financial records, expenses, and reports.', 0) ON DUPLICATE KEY UPDATE `description` = VALUES(`description`); -- --------------------------------------------------------------------------- -- Phase 1 permissions (Users, Roles, Customers, Vehicles, Settings) -- --------------------------------------------------------------------------- INSERT INTO `permissions` (`module`, `slug`, `name`) VALUES ('users', 'users.view', 'View Users'), ('users', 'users.manage', 'Create/Edit/Deactivate Users'), ('roles', 'roles.view', 'View Roles & Permissions'), ('roles', 'roles.manage', 'Create/Edit/Delete Roles'), ('customers', 'customers.view', 'View Customers'), ('customers', 'customers.manage', 'Create/Edit/Delete Customers'), ('vehicles', 'vehicles.view', 'View Vehicles'), ('vehicles', 'vehicles.manage', 'Create/Edit/Delete Vehicles'), ('settings', 'settings.manage', 'Manage Branding & System Settings') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); -- --------------------------------------------------------------------------- -- Give the Admin role every Phase 1 permission by default (adjustable in-app) -- --------------------------------------------------------------------------- INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Admin'), p.id FROM permissions p ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- Manager, Receptionist, Service Advisor: view-only on customers/vehicles by default INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('customers.view','customers.manage','vehicles.view','vehicles.manage') WHERE r.name = 'Manager' ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('customers.view','customers.manage','vehicles.view','vehicles.manage') WHERE r.name = 'Receptionist' ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('customers.view','vehicles.view') WHERE r.name IN ('Service Advisor','Mechanic','Storekeeper','Cashier','Accountant') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- --------------------------------------------------------------------------- -- Default Super Admin account — CHANGE THIS PASSWORD IMMEDIATELY AFTER INSTALL -- Email: admin@garage.local Password: Admin@12345 -- --------------------------------------------------------------------------- INSERT INTO `users` (`full_name`, `email`, `phone`, `password`, `role_id`, `is_active`) VALUES ('System Administrator', 'admin@garage.local', '+251900000000', '$2y$10$055SKQx.1J0gXk.poCwx/u/W.cVhrQKE64BowzFK5UcrKptus.LB2', (SELECT id FROM roles WHERE name = 'Super Admin'), 1) ON DUPLICATE KEY UPDATE `full_name` = VALUES(`full_name`); -- --------------------------------------------------------------------------- -- Default settings -- --------------------------------------------------------------------------- INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('garage_name', 'Sample Garage Management'), ('garage_short_name', 'Sample Garage'), ('garage_phone', '+251 11 000 0000'), ('garage_email', 'info@samplegarage.et'), ('garage_address', 'Bole Road, Addis Ababa, Ethiopia'), ('garage_website', 'www.samplegarage.et'), ('currency_symbol', 'ETB'), ('currency_code', 'ETB'), ('tax_enabled', '1'), ('tax_rate', '15'), ('tax_number', ''), ('invoice_prefix', 'INV'), ('estimate_prefix', 'EST'), ('workorder_prefix', 'WO'), ('invoice_footer', 'Thank you for trusting us with your vehicle.'), ('invoice_terms', 'Payment is due upon vehicle collection unless otherwise agreed in writing.'), ('default_labor_rate', '350'), ('low_stock_default_threshold', '5'), ('service_due_km_threshold', '1000'), ('service_due_days_threshold', '30'), ('inactive_customer_months', '6'), ('vehicle_aging_days_threshold', '3'), ('brand_primary_color', '#0f5132'), ('brand_secondary_color', '#1c2b36'), ('brand_accent_color', '#c9a227'), ('brand_background_color', '#f5f6f8') ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`; -- ---- seed_phase2.sql ---- -- ============================================================================ -- PHASE 2 — Seed: permissions, role grants, default service categories -- Safe to re-run. -- ============================================================================ USE `garage_management`; INSERT INTO `permissions` (`module`, `slug`, `name`) VALUES ('mechanics', 'mechanics.view', 'View Mechanics'), ('mechanics', 'mechanics.manage', 'Create/Edit Mechanics'), ('services', 'services.view', 'View Service Categories & Packages'), ('services', 'services.manage', 'Manage Service Categories & Packages'), ('appointments', 'appointments.view', 'View Appointments'), ('appointments', 'appointments.manage', 'Create/Edit Appointments & Check-in'), ('workorders', 'workorders.view', 'View Work Orders'), ('workorders', 'workorders.manage', 'Create/Edit Work Orders & Change Status'), ('jobcards', 'jobcards.view', 'View Job Cards'), ('jobcards', 'jobcards.manage', 'Update Job Card Progress') ON DUPLICATE KEY UPDATE `name` = VALUES(`name`); -- Admin: everything from Phase 2 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 ('mechanics','services','appointments','workorders','jobcards') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- Manager: view everything, manage work orders/appointments INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Manager'), p.id FROM permissions p WHERE p.slug IN ('mechanics.view','services.view','appointments.view','appointments.manage','workorders.view','workorders.manage','jobcards.view') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- Receptionist: appointments + check-in 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 ('appointments.view','appointments.manage','services.view','workorders.view','workorders.manage','mechanics.view') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- Service Advisor: work orders + appointments + services 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 ('appointments.view','appointments.manage','services.view','workorders.view','workorders.manage','jobcards.view','jobcards.manage','mechanics.view') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- Mechanic: view work orders, manage own job cards INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Mechanic'), p.id FROM permissions p WHERE p.slug IN ('workorders.view','jobcards.view','jobcards.manage') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- --------------------------------------------------------------------------- -- Default service categories (core to spec section 1A) -- --------------------------------------------------------------------------- INSERT INTO `service_categories` (`name`, `description`, `default_interval_km`, `default_interval_days`) VALUES ('Oil Change', 'Engine oil and filter replacement', 5000, 180), ('General Service', 'Routine multi-point vehicle service', 10000, 180), ('Brake Service', 'Brake pads, discs, and fluid inspection/service', 20000, 365), ('Tire Service', 'Tire rotation, balancing, and replacement', 10000, 180), ('Battery Service', 'Battery testing, charging, and replacement', NULL, 365), ('Engine Service', 'Engine diagnostics and repair work', NULL, NULL), ('Transmission Service', 'Transmission fluid and system service', 40000, 730), ('AC Service', 'Air conditioning inspection and recharge', NULL, 365), ('Electrical Service', 'Electrical system diagnostics and repair', NULL, NULL), ('Inspection', 'General vehicle inspection', NULL, 180), ('Diagnostic Service', 'Computerized diagnostic scan', NULL, NULL), ('Filter Replacement', 'Air, cabin, and fuel filter replacement', 10000, 180), ('Fluid Replacement', 'Coolant, brake, and other fluid replacement', 20000, 365), ('Preventive Maintenance', 'Scheduled preventive maintenance', 10000, 180), ('Other', 'Other service not otherwise categorized', NULL, NULL) ON DUPLICATE KEY UPDATE `description` = VALUES(`description`); -- ---- seed_phase3.sql ---- -- ============================================================================ -- PHASE 3 — Seed: permissions, role grants, default insurance companies -- Safe to re-run. -- ============================================================================ USE `garage_management`; INSERT INTO `permissions` (`module`, `slug`, `name`) VALUES ('inspections', 'inspections.view', 'View Vehicle Inspections'), ('inspections', 'inspections.manage', 'Create/Edit Vehicle Inspections'), ('insurance', 'insurance.view', 'View Insurance Claims'), ('insurance', 'insurance.manage', 'Manage Insurance Claims, Surveys & Approvals'), ('documents', 'documents.view', 'View Vehicle Photos & Documents'), ('documents', 'documents.manage', 'Upload Vehicle Photos & Documents') 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 ('inspections','insurance','documents') 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.slug IN ('inspections.view','inspections.manage','insurance.view','insurance.manage','documents.view','documents.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 ('inspections.view','inspections.manage','insurance.view','insurance.manage','documents.view','documents.manage') 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 IN ('inspections.view','documents.view') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- ============================================================================ -- 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`; 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 ('documents.view','documents.manage','insurance.view') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- --------------------------------------------------------------------------- -- Default insurance companies (common in the Ethiopian market) -- --------------------------------------------------------------------------- INSERT INTO `insurance_companies` (`name`, `contact_person`, `phone`, `email`) VALUES ('Ethiopian Insurance Corporation', 'Claims Department', '+251 11 551 0900', 'claims@eic.com.et'), ('Nyala Insurance S.C.', 'Claims Department', '+251 11 662 1900', 'claims@nyalainsurance.com'), ('Awash Insurance Company', 'Claims Department', '+251 11 466 6667', 'claims@awashinsurance.com'), ('Nile Insurance Company', 'Claims Department', '+251 11 551 8127', 'claims@nileinsurance.com.et') ON DUPLICATE KEY UPDATE `contact_person` = VALUES(`contact_person`); -- ---- seed_phase4.sql ---- -- ============================================================================ -- PHASE 4 — Seed: permissions, role grants, default part categories -- Safe to re-run. -- ============================================================================ USE `garage_management`; INSERT INTO `permissions` (`module`, `slug`, `name`) VALUES ('inventory', 'inventory.view', 'View Inventory & Stock'), ('inventory', 'inventory.manage', 'Manage Parts & Stock Adjustments'), ('suppliers', 'suppliers.view', 'View Suppliers'), ('suppliers', 'suppliers.manage', 'Manage Suppliers'), ('purchases', 'purchases.view', 'View Purchases'), ('purchases', 'purchases.manage', 'Create/Receive Purchases') 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 ('inventory','suppliers','purchases') 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 ('inventory','suppliers','purchases') 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.module IN ('inventory','suppliers','purchases') 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 ('inventory.view') 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 IN ('inventory.view') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- --------------------------------------------------------------------------- -- Default part categories -- --------------------------------------------------------------------------- INSERT INTO `part_categories` (`name`, `description`) VALUES ('Engine Oil & Fluids', 'Engine oil, coolant, brake fluid, transmission fluid'), ('Filters', 'Oil, air, cabin, and fuel filters'), ('Brakes', 'Brake pads, discs, calipers, brake lines'), ('Tires & Wheels', 'Tires, rims, valves, balancing weights'), ('Battery & Electrical', 'Batteries, alternators, starters, wiring, bulbs'), ('Engine Parts', 'Belts, hoses, spark plugs, gaskets'), ('Suspension & Steering', 'Shocks, struts, control arms, tie rods'), ('Body & Exterior', 'Bumpers, panels, mirrors, lights, glass'), ('AC & Cooling', 'AC compressors, radiators, condensers'), ('Other', 'Miscellaneous parts and consumables') ON DUPLICATE KEY UPDATE `description` = VALUES(`description`); -- --------------------------------------------------------------------------- -- Demo supplier -- --------------------------------------------------------------------------- INSERT INTO `suppliers` (`name`, `contact_person`, `phone`, `email`, `address`, `payment_terms`, `is_active`) VALUES ('Addis Auto Parts PLC', 'Yohannes Girma', '+251 91 234 5678', 'sales@addisautoparts.et', 'Merkato, Addis Ababa', 'Net 30', 1), ('Horizon Spare Parts Import', 'Sara Alemayehu', '+251 92 345 6789', 'info@horizonspares.et', 'Bole, Addis Ababa', 'Net 15', 1) ON DUPLICATE KEY UPDATE `contact_person` = VALUES(`contact_person`); -- ---- seed_phase5.sql ---- USE `garage_management`; INSERT INTO `permissions` (`module`, `slug`, `name`) VALUES ('estimates', 'estimates.view', 'View Estimates'), ('estimates', 'estimates.manage', 'Create/Edit Estimates'), ('invoices', 'invoices.view', 'View Invoices'), ('invoices', 'invoices.manage', 'Create/Edit Invoices'), ('payments', 'payments.view', 'View Payments'), ('payments', 'payments.manage', 'Record Payments'), ('expenses', 'expenses.view', 'View Expenses'), ('expenses', 'expenses.manage', 'Record Expenses'), ('coupons', 'coupons.view', 'View Coupons'), ('coupons', 'coupons.manage', 'Create/Edit Coupons') 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 ('estimates','invoices','payments','expenses','coupons') 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 ('estimates','invoices','payments','expenses','coupons') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Accountant'), p.id FROM permissions p WHERE p.module IN ('estimates','invoices','payments','expenses','coupons') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Cashier'), p.id FROM permissions p WHERE p.slug IN ('invoices.view','invoices.manage','payments.view','payments.manage','estimates.view') 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 ('estimates.view','estimates.manage','invoices.view') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; -- ---- seed_phase6.sql ---- USE `garage_management`; INSERT INTO `permissions` (`module`, `slug`, `name`) VALUES ('reports', 'reports.view', 'View Reports'), ('audit', 'audit.view', 'View Audit Logs') 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.slug IN ('reports.view','audit.view') 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.slug IN ('reports.view','audit.view') ON DUPLICATE KEY UPDATE `role_id` = `role_id`; INSERT INTO `role_permissions` (`role_id`, `permission_id`) SELECT (SELECT id FROM roles WHERE name = 'Accountant'), p.id FROM permissions p WHERE p.slug IN ('reports.view') 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`;