-- AUTO TESTER Database Setup Script
-- Complete database schema for vehicle inspection system

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS autotester_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE autotester_db;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'supervisor', 'inspector', 'client') NOT NULL,
    area VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_active (is_active)
);

-- Inspection centers
CREATE TABLE IF NOT EXISTS inspection_centers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    email VARCHAR(255),
    governorate VARCHAR(100),
    area VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_governorate (governorate),
    INDEX idx_active (is_active)
);

-- Inspection types
CREATE TABLE IF NOT EXISTS inspection_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    points INT DEFAULT 0,
    estimated_time INT DEFAULT 30,
    price DECIMAL(10,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_active (is_active)
);

-- Inspections
CREATE TABLE IF NOT EXISTS inspections (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    inspector_id INT,
    center_id INT,
    inspection_type_id INT NOT NULL,
    vehicle_info JSON,
    status ENUM('pending', 'assigned', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    result_data JSON,
    report_url VARCHAR(500),
    total_price DECIMAL(10,2),
    paid_amount DECIMAL(10,2) DEFAULT 0.00,
    payment_status ENUM('pending', 'partial', 'paid', 'refunded') DEFAULT 'pending',
    scheduled_date DATETIME,
    completed_date DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES users(id),
    FOREIGN KEY (inspector_id) REFERENCES users(id),
    FOREIGN KEY (center_id) REFERENCES inspection_centers(id),
    FOREIGN KEY (inspection_type_id) REFERENCES inspection_types(id),
    INDEX idx_client (client_id),
    INDEX idx_inspector (inspector_id),
    INDEX idx_status (status),
    INDEX idx_scheduled (scheduled_date)
);

-- Inspection checklist items
CREATE TABLE IF NOT EXISTS inspection_checklist_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    inspection_type_id INT NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    description TEXT,
    is_required BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (inspection_type_id) REFERENCES inspection_types(id),
    INDEX idx_type_category (inspection_type_id, category)
);

-- Inspection results
CREATE TABLE IF NOT EXISTS inspection_results (
    id INT PRIMARY KEY AUTO_INCREMENT,
    inspection_id INT NOT NULL,
    checklist_item_id INT NOT NULL,
    status ENUM('pass', 'fail', 'na') NOT NULL,
    notes TEXT,
    images JSON,
    inspector_notes TEXT,
    FOREIGN KEY (inspection_id) REFERENCES inspections(id),
    FOREIGN KEY (checklist_item_id) REFERENCES inspection_checklist_items(id),
    INDEX idx_inspection (inspection_id),
    INDEX idx_status (status)
);

-- WhatsApp logs
CREATE TABLE IF NOT EXISTS whatsapp_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phone_number VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    message_type ENUM('text', 'image', 'document') DEFAULT 'text',
    status ENUM('pending', 'sent', 'delivered', 'failed') DEFAULT 'pending',
    error_message TEXT,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_phone (phone_number),
    INDEX idx_status (status),
    INDEX idx_sent_at (sent_at)
);

-- System settings
CREATE TABLE IF NOT EXISTS system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(255) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_key (setting_key)
);

-- Activity logs
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(255) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
);

-- Insert default inspection types
INSERT INTO inspection_types (name, description, points, estimated_time, price) VALUES
('200-Point Inspection', 'Comprehensive vehicle inspection covering all major systems', 200, 90, 1500.00),
('Uber Inspection', 'Ride-hailing vehicle verification and safety check', 50, 30, 500.00),
('Computer Diagnostics', 'OBD2 and electronic system analysis', 30, 20, 300.00),
('Brakes & Suspension', 'Braking system and suspension components check', 40, 25, 400.00),
('Engine Analysis', 'Engine performance and diagnostics', 35, 30, 350.00),
('Electrical Systems', 'Wiring, sensors, and electrical components', 25, 20, 250.00),
('ECU & Sensors', 'Computer systems and sensor diagnostics', 20, 15, 200.00);

-- Insert default system settings
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('company_name', 'AUTO TESTER', 'Company name'),
('company_phone', '01018518599', 'Company phone number'),
('company_email', 'info@autotestersystem.com', 'Company email'),
('company_address', 'Egypt - New Cairo - Toman Bay Street', 'Company address'),
('whatsapp_enabled', 'true', 'Enable WhatsApp notifications'),
('default_inspection_price', '500.00', 'Default inspection price'),
('currency', 'EGP', 'Currency code');

-- Insert default admin user (password: admin123)
INSERT INTO users (full_name, email, phone, password_hash, role, area) VALUES
('System Administrator', 'admin@autotester.com', '01018518599', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'Cairo');

-- Create indexes for better performance
CREATE INDEX idx_inspections_status_date ON inspections(status, scheduled_date);
CREATE INDEX idx_users_role_active ON users(role, is_active);
CREATE INDEX idx_activity_logs_user_date ON activity_logs(user_id, created_at);

-- Create view for active inspections
CREATE VIEW active_inspections AS
SELECT 
    i.id,
    i.status,
    i.scheduled_date,
    u.full_name as client_name,
    u.phone as client_phone,
    it.name as inspection_type,
    ic.name as center_name,
    ins.full_name as inspector_name
FROM inspections i
JOIN users u ON i.client_id = u.id
JOIN inspection_types it ON i.inspection_type_id = it.id
JOIN inspection_centers ic ON i.center_id = ic.id
LEFT JOIN users ins ON i.inspector_id = ins.id
WHERE i.status IN ('pending', 'assigned', 'in_progress');

-- Create view for inspector statistics
CREATE VIEW inspector_stats AS
SELECT 
    u.id,
    u.full_name,
    COUNT(i.id) as total_inspections,
    SUM(CASE WHEN i.status = 'completed' THEN 1 ELSE 0 END) as completed_inspections,
    AVG(CASE WHEN i.status = 'completed' THEN 
        (SELECT COUNT(*) FROM inspection_results ir WHERE ir.inspection_id = i.id AND ir.status = 'pass') * 100.0 / 
        (SELECT COUNT(*) FROM inspection_results ir WHERE ir.inspection_id = i.id)
    END) as avg_pass_rate
FROM users u
LEFT JOIN inspections i ON u.id = i.inspector_id
WHERE u.role = 'inspector'
GROUP BY u.id, u.full_name;
