-- Al-Runan Llumar Authorized Fitment Centre Database Schema
-- Created: 2024-01-01
-- Author: System Administrator

SET FOREIGN_KEY_CHECKS=0;

-- Create database
CREATE DATABASE IF NOT EXISTS `al_runan_fitment` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `al_runan_fitment`;

-- Users table (for admin, employee and customer accounts)
CREATE TABLE `users` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `firebase_uid` VARCHAR(255) NULL, -- Firebase UID for Google Sign-In
  `phone` VARCHAR(20),
  `role` ENUM('super_admin', 'admin', 'employee', 'customer') DEFAULT 'customer',
  `status` ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
  `last_login` DATETIME,
  `created_by` INT, -- Who created this user (for audit)
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_email` (`email`),
  INDEX `idx_firebase_uid` (`firebase_uid`),
  INDEX `idx_role` (`role`),
  INDEX `idx_status` (`status`)
);

-- Car makes table
CREATE TABLE `car_makes` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `logo_url` VARCHAR(255),
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_make` (`name`)
);

-- Car models table
CREATE TABLE `car_models` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `make_id` INT NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `year_start` INT,
  `year_end` INT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`make_id`) REFERENCES `car_makes`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_model` (`make_id`, `name`, `year_start`),
  INDEX `idx_make` (`make_id`)
);

-- Product categories table
CREATE TABLE `product_categories` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `description` TEXT,
  `image_url` VARCHAR(255),
  `product_type` ENUM('paint_protection', 'window_tint', 'accessory') DEFAULT 'window_tint',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_category` (`name`)
);

-- Products table
CREATE TABLE `products` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `category_id` INT NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `original_price` DECIMAL(10,2),
  `sku` VARCHAR(50) UNIQUE,
  `stock_quantity` INT DEFAULT 0,
  `image_url` VARCHAR(255),
  `images` JSON, -- Array of additional image URLs
  `features` JSON, -- Array of product features
  `specifications` JSON, -- Key-value pairs of specifications
  `compatible_cars` JSON, -- Array of compatible car model IDs
  `vlt_options` JSON, -- Array of available VLT percentages for tint products
  `warranty_months` INT DEFAULT 12,
  `status` ENUM('active', 'inactive', 'out_of_stock') DEFAULT 'active',
  `created_by` INT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `product_categories`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_category` (`category_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_price` (`price`),
  FULLTEXT `ft_search` (`name`, `description`)
);

-- Orders table
CREATE TABLE `orders` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `order_number` VARCHAR(20) UNIQUE NOT NULL,
  `customer_id` INT,
  `customer_name` VARCHAR(100) NOT NULL,
  `customer_email` VARCHAR(100) NOT NULL,
  `customer_phone` VARCHAR(20) NOT NULL,
  `customer_address` TEXT,
  `total_amount` DECIMAL(10,2) NOT NULL,
  `status` ENUM('pending', 'confirmed', 'processing', 'completed', 'cancelled') DEFAULT 'pending',
  `payment_status` ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
  `notes` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`customer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_customer` (`customer_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_payment_status` (`payment_status`),
  INDEX `idx_created` (`created_at`)
);

-- Order items table
CREATE TABLE `order_items` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `order_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  `unit_price` DECIMAL(10,2) NOT NULL,
  `total_price` DECIMAL(10,2) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE RESTRICT,
  INDEX `idx_order` (`order_id`),
  INDEX `idx_product` (`product_id`)
);

-- Payments table
CREATE TABLE `payments` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `order_id` INT NOT NULL,
  `payment_method` ENUM('mpesa', 'pesapal', 'cash') NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `transaction_id` VARCHAR(100) UNIQUE,
  `phone_number` VARCHAR(20), -- For M-Pesa payments
  `status` ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
  `response_data` JSON, -- Raw response from payment gateway
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  INDEX `idx_order` (`order_id`),
  INDEX `idx_transaction` (`transaction_id`),
  INDEX `idx_status` (`status`)
);

-- SMS logs table
CREATE TABLE `sms_logs` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `recipient_phone` VARCHAR(20) NOT NULL,
  `message` TEXT NOT NULL,
  `message_type` ENUM('order_confirmation', 'payment_notification', 'appointment_reminder', 'promotional') NOT NULL,
  `status` ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
  `response_data` JSON,
  `sent_at` DATETIME,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_phone` (`recipient_phone`),
  INDEX `idx_type` (`message_type`),
  INDEX `idx_status` (`status`)
);

-- Appointments table
CREATE TABLE `appointments` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `customer_id` INT,
  `customer_name` VARCHAR(100) NOT NULL,
  `customer_phone` VARCHAR(20) NOT NULL,
  `customer_email` VARCHAR(100),
  `car_make` VARCHAR(50) NOT NULL,
  `car_model` VARCHAR(50) NOT NULL,
  `car_year` INT,
  `service_type` VARCHAR(100) NOT NULL,
  `appointment_date` DATE NOT NULL,
  `appointment_time` TIME NOT NULL,
  `status` ENUM('scheduled', 'confirmed', 'completed', 'cancelled', 'no_show', 'pending_payment') DEFAULT 'scheduled',
  `notes` TEXT,
  `county` VARCHAR(50),
  `subcounty` VARCHAR(50),
  `address` TEXT,
  `latitude` DECIMAL(10,8),
  `longitude` DECIMAL(11,8),
  `service_location` ENUM('workshop', 'on_site') DEFAULT 'workshop',
  `estimated_price` DECIMAL(10,2) DEFAULT 0.00,
  `payment_status` ENUM('pending', 'paid', 'failed', 'refunded', 'partial') DEFAULT 'pending',
  `deposit_amount` DECIMAL(10,2) DEFAULT 0.00,
  `total_amount` DECIMAL(10,2) DEFAULT 0.00,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`customer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_customer` (`customer_id`),
  INDEX `idx_date` (`appointment_date`),
  INDEX `idx_status` (`status`),
  INDEX `idx_payment_status` (`payment_status`)
);

-- System settings table
CREATE TABLE `system_settings` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `setting_key` VARCHAR(50) UNIQUE NOT NULL,
  `setting_value` TEXT,
  `setting_type` ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
  `description` TEXT,
  `updated_by` INT,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`updated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_key` (`setting_key`)
);

-- Audit logs table
CREATE TABLE `audit_logs` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT,
  `action` VARCHAR(50) NOT NULL,
  `resource_type` VARCHAR(50) NOT NULL,
  `resource_id` INT,
  `old_values` JSON,
  `new_values` JSON,
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_action` (`action`),
  INDEX `idx_resource` (`resource_type`, `resource_id`),
  INDEX `idx_created` (`created_at`)
);

-- Chats table for chat sessions
CREATE TABLE `chats` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `admin_id` INT,
  `status` ENUM('active', 'closed', 'archived') DEFAULT 'active',
  `last_message_at` DATETIME,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`admin_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_admin` (`admin_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_last_message` (`last_message_at`)
);

-- Chat messages table
CREATE TABLE `chat_messages` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `chat_id` INT NOT NULL,
  `sender_id` INT NOT NULL,
  `message_type` ENUM('text', 'image', 'file') DEFAULT 'text',
  `message` TEXT NOT NULL,
  `file_url` VARCHAR(255),
  `is_read` BOOLEAN DEFAULT FALSE,
  `read_at` DATETIME,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`chat_id`) REFERENCES `chats`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX `idx_chat` (`chat_id`),
  INDEX `idx_sender` (`sender_id`),
  INDEX `idx_created` (`created_at`),
  INDEX `idx_read` (`is_read`)
);

-- User permissions table for role-based access control
CREATE TABLE `user_permissions` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `module` VARCHAR(50) NOT NULL, -- e.g., 'products', 'orders', 'appointments', 'payments', 'sms', 'users', 'cars', 'dashboard'
  `can_view` BOOLEAN DEFAULT FALSE,
  `can_create` BOOLEAN DEFAULT FALSE,
  `can_edit` BOOLEAN DEFAULT FALSE,
  `can_delete` BOOLEAN DEFAULT FALSE,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_user_module` (`user_id`, `module`),
  INDEX `idx_user` (`user_id`),
  INDEX `idx_module` (`module`)
);

-- Note: Default admin user will be created during installation
-- The installation wizard will create the first super_admin user

INSERT INTO `product_categories` (`name`, `description`, `product_type`) VALUES
('Window Tinting', 'High-quality window tinting films for all vehicle types', 'window_tint'),
('Paint Protection', 'Paint protection films and ceramic coatings', 'paint_protection'),
('Accessories', 'Car accessories and enhancement products', 'accessory');

INSERT INTO `car_makes` (`name`) VALUES 
('Toyota'), ('Honda'), ('Ford'), ('Nissan'), ('Mercedes-Benz'),
('BMW'), ('Audi'), ('Volkswagen'), ('Subaru'), ('Mitsubishi');

INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('company_name', 'Al-Runan Llumar Authorized Fitment Centre', 'string', 'Business name'),
('company_phone', '+254712345678', 'string', 'Business phone number'),
('company_email', 'info@alrunan.com', 'string', 'Business email'),
('mpesa_consumer_key', '', 'string', 'M-Pesa API Consumer Key'),
('mpesa_consumer_secret', '', 'string', 'M-Pesa API Consumer Secret'),
('pesapal_consumer_key', '', 'string', 'Pesapal API Consumer Key'),
('pesapal_consumer_secret', '', 'string', 'Pesapal API Consumer Secret'),
('africas_talking_api_key', '', 'string', 'Africa''s Talking API Key'),
('africas_talking_username', '', 'string', 'Africa''s Talking Username'),
('sms_enabled', 'true', 'boolean', 'Enable SMS notifications'),
('smtp_host', '', 'string', 'SMTP Host for email notifications'),
('smtp_port', '587', 'string', 'SMTP Port for email notifications'),
('smtp_username', '', 'string', 'SMTP Username for email notifications'),
('smtp_password', '', 'string', 'SMTP Password for email notifications'),
('smtp_encryption', 'tls', 'string', 'SMTP Encryption (none, ssl, tls)'),
('admin_email', 'admin@alrunan.com', 'string', 'Admin email for notifications');

SET FOREIGN_KEY_CHECKS=1;

-- Create views for reporting
CREATE VIEW `order_summary_view` AS
SELECT 
    o.id,
    o.order_number,
    o.customer_name,
    o.total_amount,
    o.status,
    o.payment_status,
    o.created_at,
    COUNT(oi.id) as item_count
FROM `orders` o
LEFT JOIN `order_items` oi ON o.id = oi.order_id
GROUP BY o.id;

CREATE VIEW `product_sales_view` AS
SELECT 
    p.id,
    p.name,
    p.category_id,
    pc.name as category_name,
    SUM(oi.quantity) as total_sold,
    SUM(oi.total_price) as total_revenue
FROM `products` p
LEFT JOIN `order_items` oi ON p.id = oi.product_id
LEFT JOIN `product_categories` pc ON p.category_id = pc.id
LEFT JOIN `orders` o ON oi.order_id = o.id AND o.status = 'completed'
GROUP BY p.id;

-- Create stored procedure for generating order numbers
DELIMITER //
CREATE PROCEDURE `GenerateOrderNumber`(OUT order_number VARCHAR(20))
BEGIN
    DECLARE last_id INT;
    DECLARE new_id VARCHAR(20);
    
    SELECT COALESCE(MAX(CAST(SUBSTRING(order_number, 5) AS UNSIGNED)), 0) + 1 
    INTO last_id 
    FROM `orders` 
    WHERE order_number LIKE 'ORD-%';
    
    SET new_id = LPAD(last_id, 6, '0');
    SET order_number = CONCAT('ORD-', new_id);
END//
DELIMITER ;

-- Insert demo products for testing
INSERT INTO `products` (`name`, `description`, `category_id`, `price`, `original_price`, `sku`, `stock_quantity`, `image_url`, `vlt_options`, `warranty_months`, `status`, `created_at`) VALUES
-- Paint Protection Products
('Ceramic Pro 9H', 'Premium ceramic coating with 9H hardness rating, provides exceptional protection against scratches, UV rays, and chemical stains. Lasts up to 5 years.', 2, 45000.00, 50000.00, 'CP-9H-001', 10, NULL, '[]', 60, 'active', NOW()),
('Gtechniq Crystal Serum Ultra', 'Advanced nano-ceramic coating offering superior protection and gloss enhancement. Self-cleaning properties and hydrophobic effect.', 2, 38000.00, 42000.00, 'GT-CSU-002', 8, NULL, '[]', 84, 'active', NOW()),
('XPEL Ultimate Plus', 'Self-healing paint protection film that protects against rock chips, scratches, and environmental contaminants. Virtually invisible.', 2, 55000.00, 60000.00, 'XP-UP-003', 5, NULL, '[]', 60, 'active', NOW()),
('3M Scotchgard Pro', 'Professional-grade paint protection with advanced technology for long-lasting shine and protection. Easy maintenance.', 2, 28000.00, 32000.00, '3M-SP-004', 12, NULL, '[]', 36, 'active', NOW()),

-- Window Tint Products
('Llumar CTX Ceramic Series', 'Premium ceramic window tint offering superior heat rejection and UV protection. Non-metallic, non-interfering with signals.', 1, 25000.00, 28000.00, 'LL-CTX-101', 15, NULL, '["20%", "35%", "50%", "70%"]', 60, 'active', NOW()),
('Llumar ATC Carbon Series', 'Carbon-based window tint providing excellent heat rejection and glare reduction. Non-reflective, maintains visibility.', 1, 18000.00, 21000.00, 'LL-ATC-102', 20, NULL, '["15%", "20%", "35%", "50%"]', 36, 'active', NOW()),
('Llumar AIR Blue Series', 'High-performance window tint with blue hue, offering maximum heat rejection and UV protection. Premium aesthetic.', 1, 32000.00, 35000.00, 'LL-AIR-103', 8, NULL, '["30%", "40%", "50%"]', 84, 'active', NOW()),
('3M Color Stable', 'Durable, non-dyed window film that won''t fade or change color over time. Excellent clarity and performance.', 1, 15000.00, 18000.00, '3M-CS-104', 25, NULL, '["20%", "35%", "50%"]', 60, 'active', NOW()),

-- Accessory Products
('Llumar Windshield Protection Film', 'Clear protective film for windshields, prevents cracks and chips from road debris. Virtually invisible.', 3, 12000.00, 15000.00, 'LL-WPF-201', 30, NULL, '[]', 24, 'active', NOW()),
('Headlight Protection Film', 'UV-resistant film that protects headlights from yellowing and cracking. Maintains light output.', 3, 8000.00, 10000.00, 'HL-PF-202', 25, NULL, '[]', 24, 'active', NOW()),
('Interior Ceramic Coating', 'Protects interior surfaces from UV damage, stains, and wear. Easy to clean and maintain.', 3, 15000.00, 18000.00, 'INT-CC-203', 15, NULL, '[]', 36, 'active', NOW());

-- Insert demo VLT options for the system
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('vlt_options', '["5%", "10%", "15%", "20%", "25%", "30%", "35%", "40%", "45%", "50%", "55%", "60%", "65%", "70%", "75%", "80%", "85%", "90%", "95%"]', 'json', 'Available VLT percentage options for tint products');