-- ============================================
-- AUTO TESTER - ملف تثبيت قاعدة البيانات (SQL فقط)
-- ============================================

USE `digitals_autotestersystem`;

-- ============================================
-- 1. إنشاء جدول أنواع الفحوصات
-- ============================================
CREATE TABLE IF NOT EXISTS inspection_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name_ar VARCHAR(100) NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    duration_minutes INT,
    description TEXT,
    points_count INT,
    icon VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- إدخال البيانات
INSERT INTO inspection_types (name_ar, name_en, price, duration_minutes, points_count, icon, sort_order) VALUES
('فحص شامل', 'comprehensive', 850, 90, 200, 'fa-clipboard-check', 1),
('فحص داينو', 'dyno', 1200, 45, 50, 'fa-tachometer-alt', 2),
('فرامل وعفشة', 'brakes', 600, 60, 75, 'fa-car-crash', 3),
('فحص محرك', 'engine', 750, 60, 85, 'fa-oil-can', 4),
('فحص كهرباء', 'electrical', 500, 45, 60, 'fa-bolt', 5),
('فحص استلام', 'pickup', 400, 30, 40, 'fa-clipboard-list', 6),
('فحص كمبيوتر', 'computer', 650, 40, 55, 'fa-microchip', 7),
('فحص تطبيقات', 'apps', 550, 45, 65, 'fa-taxi', 8);

-- ============================================
-- 2. إنشاء جدول طلبات الفحص
-- ============================================
CREATE TABLE IF NOT EXISTS inspection_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_number VARCHAR(50) NOT NULL UNIQUE,
    client_id INT NOT NULL,
    car_id INT NOT NULL,
    inspection_type_id INT NOT NULL,
    inspector_id INT NULL,
    status ENUM('pending','confirmed','assigned','in_progress','completed','cancelled') DEFAULT 'pending',
    payment_status ENUM('pending','paid','refunded') DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL,
    deposit_amount DECIMAL(10,2) DEFAULT 1000.00,
    scheduled_date DATE,
    scheduled_time TIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES customers(id),
    FOREIGN KEY (car_id) REFERENCES vehicles(id),
    FOREIGN KEY (inspection_type_id) REFERENCES inspection_types(id)
);

-- ============================================
-- 3. إنشاء Trigger لرقم الطلب
-- ============================================
DELIMITER $$
CREATE TRIGGER before_insert_inspection_requests
BEFORE INSERT ON inspection_requests
FOR EACH ROW
BEGIN
    DECLARE year_month VARCHAR(6);
    DECLARE next_number INT;
    
    SET year_month = DATE_FORMAT(NOW(), '%Y%m');
    
    SELECT COALESCE(MAX(CAST(SUBSTRING(request_number, -4) AS UNSIGNED)), 0) + 1
    INTO next_number
    FROM inspection_requests
    WHERE request_number LIKE CONCAT('REQ-', year_month, '%');
    
    SET NEW.request_number = CONCAT('REQ-', year_month, '-', LPAD(next_number, 4, '0'));
END$$
DELIMITER ;

-- عرض النتيجة
SELECT '✅ تم التثبيت بنجاح' AS result;