-- Abalı Oto Tamir Servisi - Veritabanı Şeması
-- Oluşturma Tarihi: 2025-12-27

CREATE DATABASE IF NOT EXISTS tectonco_abloto CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE tectonco_abloto;

-- QR Kodlar Tablosu (Sabit QR kodlar)
CREATE TABLE qr_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    qr_hash VARCHAR(64) UNIQUE NOT NULL,
    qr_url VARCHAR(255) NOT NULL,
    is_assigned TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_qr_hash (qr_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Müşteriler Tablosu
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone (phone),
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Araçlar Tablosu
CREATE TABLE vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    qr_code_id INT UNIQUE,
    plate VARCHAR(20) NOT NULL UNIQUE,
    brand VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year YEAR,
    color VARCHAR(30),
    chassis_number VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (qr_code_id) REFERENCES qr_codes(id) ON DELETE SET NULL,
    INDEX idx_plate (plate),
    INDEX idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Servis Kayıtları Tablosu
CREATE TABLE service_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vehicle_id INT NOT NULL,
    technician_id INT NOT NULL,
    service_date DATE NOT NULL,
    km_reading INT,
    description TEXT,
    total_amount DECIMAL(10,2) DEFAULT 0.00,
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
    FOREIGN KEY (technician_id) REFERENCES users(id),
    INDEX idx_vehicle (vehicle_id),
    INDEX idx_date (service_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Servis İşlemleri Detay Tablosu
CREATE TABLE service_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_record_id INT NOT NULL,
    item_type ENUM('labor', 'part', 'material') NOT NULL,
    description TEXT NOT NULL,
    quantity DECIMAL(10,2) DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (service_record_id) REFERENCES service_records(id) ON DELETE CASCADE,
    INDEX idx_service (service_record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ödemeler Tablosu
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_record_id INT NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('cash', 'credit_card', 'bank_transfer', 'other') NOT NULL,
    payment_date DATE NOT NULL,
    notes TEXT,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (service_record_id) REFERENCES service_records(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_service (service_record_id),
    INDEX idx_customer (customer_id),
    INDEX idx_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Borç/Alacak Özet View
CREATE VIEW customer_balance AS
SELECT
    c.id AS customer_id,
    c.name AS customer_name,
    c.phone,
    COALESCE(d.total_debt, 0) AS total_debt,
    COALESCE(p.total_paid, 0) AS total_paid,
    COALESCE(d.total_debt, 0) - COALESCE(p.total_paid, 0) AS balance
FROM customers c
LEFT JOIN (
    SELECT v.customer_id, SUM(sr.total_amount) AS total_debt
    FROM vehicles v
    JOIN service_records sr ON sr.vehicle_id = v.id
    GROUP BY v.customer_id
) d ON d.customer_id = c.id
LEFT JOIN (
    SELECT customer_id, SUM(amount) AS total_paid
    FROM payments
    GROUP BY customer_id
) p ON p.customer_id = c.id;

-- Örnek QR Kodlar (10 adet)
INSERT INTO qr_codes (qr_hash, qr_url) VALUES
(MD5(CONCAT('QR_', UUID())), 'https://abali.online/servis/'),
(MD5(CONCAT('QR_', UUID())), 'https://abali.online/servis/'),
(MD5(CONCAT('QR_', UUID())), 'https://abali.online/servis/'),
(MD5(CONCAT('QR_', UUID())), 'https://abali.online/servis/'),
(MD5(CONCAT('QR_', UUID())), 'https://abali.online/servis/'),
(MD5(CONCAT('QR_', UUID())), 'https://abali.online/servis/'),
(MD5(CONCAT('QR_', UUID())), 'https://abali.online/servis/'),
(MD5(CONCAT('QR_', UUID())), 'https://abali.online/servis/'),
(MD5(CONCAT('QR_', UUID())), 'https://abali.online/servis/'),
(MD5(CONCAT('QR_', UUID())), 'https://abali.online/servis/');
