CREATE DATABASE IF NOT EXISTS `CCW`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `CCW`;

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(120) NULL,
    loyalty_points INT NOT NULL DEFAULT 0,
    customer_group ENUM('member', 'guest') NOT NULL DEFAULT 'member',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_customers_phone (phone),
    INDEX idx_customers_group (customer_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cars (
    car_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    plate_number VARCHAR(30) NOT NULL,
    province VARCHAR(80) NULL,
    vehicle_type VARCHAR(40) NULL,
    brand VARCHAR(80) NULL,
    model VARCHAR(100) NULL,
    color VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_cars_plate (plate_number),
    INDEX idx_cars_customer (customer_id),
    CONSTRAINT fk_cars_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS service_categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(60) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_service_categories_name (category_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS service_groups (
    group_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    main_service_id INT NULL,
    group_name VARCHAR(80) NOT NULL,
    show_window_film_details TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_service_group_main (main_service_id, group_name),
    INDEX idx_service_groups_category_id (category_id),
    INDEX idx_service_groups_main_service_id (main_service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS services (
    service_id INT AUTO_INCREMENT PRIMARY KEY,
    service_name VARCHAR(120) NOT NULL,
    service_type ENUM('main_service', 'sub_service', 'package') NOT NULL DEFAULT 'sub_service',
    category VARCHAR(60) NOT NULL DEFAULT 'Detailing',
    category_id INT NULL,
    main_service_id INT NULL,
    group_id INT NULL,
    package_vehicle_scope ENUM('member', 'car') NOT NULL DEFAULT 'member',
    is_quick_service TINYINT(1) NOT NULL DEFAULT 0,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    duration_minutes INT NOT NULL DEFAULT 0,
    description TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_services_active (is_active),
    INDEX idx_services_category (category),
    INDEX idx_services_category_id (category_id),
    INDEX idx_services_main_service_id (main_service_id),
    INDEX idx_services_group_id (group_id),
    INDEX idx_services_service_type (service_type),
    INDEX idx_services_quick_service (is_quick_service)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS service_package_items (
    package_item_id INT AUTO_INCREMENT PRIMARY KEY,
    package_service_id INT NOT NULL,
    sub_service_id INT NOT NULL,
    included_uses INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_package_service_sub_service (package_service_id, sub_service_id),
    INDEX idx_package_items_package_service (package_service_id),
    INDEX idx_package_items_sub_service (sub_service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS positions (
    position_id INT AUTO_INCREMENT PRIMARY KEY,
    position_name VARCHAR(80) NOT NULL,
    role_key VARCHAR(40) NOT NULL,
    branch_scope ENUM('all', 'assigned') NOT NULL DEFAULT 'assigned',
    permissions TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_positions_role_key (role_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    position_id INT NULL,
    name VARCHAR(120) NOT NULL,
    position VARCHAR(80) NULL,
    phone VARCHAR(30) NULL,
    username VARCHAR(80) NULL,
    password_hash VARCHAR(255) NULL,
    branch_scope ENUM('all', 'assigned') NOT NULL DEFAULT 'assigned',
    default_start_page VARCHAR(40) NULL,
    last_login_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_employees_username (username),
    INDEX idx_employees_position_id (position_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_branch_access (
    employee_id INT NOT NULL,
    branch_name VARCHAR(120) NOT NULL,
    PRIMARY KEY (employee_id, branch_name),
    INDEX idx_employee_branch_name (branch_name),
    CONSTRAINT fk_employee_branch_access_employee
        FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    bill_number VARCHAR(30) NULL,
    customer_id INT NOT NULL,
    car_id INT NOT NULL,
    employee_id INT NULL,
    branch_name VARCHAR(120) NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    scheduled_at DATETIME NULL,
    slide_send_at DATETIME NULL,
    service_at DATETIME NULL,
    return_at DATETIME NULL,
    order_source ENUM('booking', 'quick_service') NOT NULL DEFAULT 'booking',
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    cancelled_by_employee_id INT NULL,
    cancellation_reason TEXT NULL,
    reopened_from_order_id INT NULL,
    status ENUM('waiting', 'washing', 'done', 'cancelled') NOT NULL DEFAULT 'waiting',
    payment_status ENUM('unpaid', 'partial', 'paid') NOT NULL DEFAULT 'unpaid',
    total_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    deposit_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    deposit_method VARCHAR(30) NULL,
    admin_deposit_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    admin_deposit_method VARCHAR(30) NULL,
    branch_deposit_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    branch_deposit_method VARCHAR(30) NULL,
    notes TEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_orders_status (status),
    INDEX idx_orders_payment_status (payment_status),
    INDEX idx_orders_order_date (order_date),
    INDEX idx_orders_customer (customer_id),
    INDEX idx_orders_car (car_id),
    INDEX idx_orders_branch_name (branch_name),
    INDEX idx_orders_return_at (return_at),
    INDEX idx_orders_order_source (order_source),
    INDEX idx_orders_cancelled_at (cancelled_at),
    INDEX idx_orders_cancelled_by_employee (cancelled_by_employee_id),
    INDEX idx_orders_reopened_from (reopened_from_order_id),
    INDEX idx_orders_deposit_method (deposit_method),
    INDEX idx_orders_admin_deposit_method (admin_deposit_method),
    INDEX idx_orders_branch_deposit_method (branch_deposit_method),
    UNIQUE KEY uniq_orders_bill_number (bill_number),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_orders_car
        FOREIGN KEY (car_id) REFERENCES cars(car_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_orders_employee
        FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_details (
    detail_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    service_id INT NOT NULL,
    service_name_snapshot VARCHAR(120) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    duration_minutes INT NOT NULL DEFAULT 0,
    service_options TEXT NULL,
    INDEX idx_order_details_order (order_id),
    INDEX idx_order_details_service (service_id),
    CONSTRAINT fk_order_details_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_order_details_service
        FOREIGN KEY (service_id) REFERENCES services(service_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_packages (
    customer_package_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    car_id INT NULL,
    vehicle_scope ENUM('member', 'car') NOT NULL DEFAULT 'member',
    package_service_id INT NOT NULL,
    order_id INT NOT NULL,
    package_name_snapshot VARCHAR(120) NOT NULL,
    status ENUM('active', 'cancelled') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    cancelled_at DATETIME NULL,
    INDEX idx_customer_packages_customer (customer_id),
    INDEX idx_customer_packages_car (car_id),
    INDEX idx_customer_packages_order (order_id),
    INDEX idx_customer_packages_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_package_items (
    customer_package_item_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_package_id INT NOT NULL,
    sub_service_id INT NOT NULL,
    service_name_snapshot VARCHAR(120) NOT NULL,
    included_uses INT NOT NULL DEFAULT 1,
    used_uses INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_customer_package_items_package (customer_package_id),
    INDEX idx_customer_package_items_sub_service (sub_service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_package_usages (
    usage_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    customer_package_item_id INT NOT NULL,
    service_id INT NOT NULL,
    service_name_snapshot VARCHAR(120) NOT NULL,
    used_uses INT NOT NULL DEFAULT 1,
    is_reversed TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at DATETIME NULL,
    INDEX idx_order_package_usages_order (order_id),
    INDEX idx_order_package_usages_package_item (customer_package_item_id),
    INDEX idx_order_package_usages_service (service_id),
    INDEX idx_order_package_usages_reversed (is_reversed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS queue (
    queue_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    queue_number INT NOT NULL,
    status ENUM('waiting', 'washing', 'done', 'cancelled') NOT NULL DEFAULT 'waiting',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    called_at DATETIME NULL,
    completed_at DATETIME NULL,
    INDEX idx_queue_order (order_id),
    INDEX idx_queue_day (created_at, queue_number),
    CONSTRAINT fk_queue_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    payment_method ENUM('cash', 'transfer', 'qr', 'card') NOT NULL DEFAULT 'cash',
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    received_amount DECIMAL(10,2) NULL,
    change_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    note VARCHAR(255) NULL,
    slip_file_path VARCHAR(255) NULL,
    slip_file_name VARCHAR(255) NULL,
    slip_mime_type VARCHAR(100) NULL,
    slip_uploaded_at DATETIME NULL,
    payment_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_payments_order (order_id),
    INDEX idx_payments_date (payment_date),
    CONSTRAINT fk_payments_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    audit_log_id INT AUTO_INCREMENT PRIMARY KEY,
    module_key VARCHAR(40) NOT NULL,
    action_key VARCHAR(40) NOT NULL,
    entity_type VARCHAR(40) NOT NULL,
    entity_id INT NULL,
    order_id INT NULL,
    payment_id INT NULL,
    customer_id INT NULL,
    car_id INT NULL,
    actor_employee_id INT NULL,
    actor_name VARCHAR(120) NULL,
    branch_name VARCHAR(120) NULL,
    bill_number VARCHAR(30) NULL,
    amount DECIMAL(10,2) NULL,
    message VARCHAR(255) NULL,
    before_json LONGTEXT NULL,
    after_json LONGTEXT NULL,
    meta_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_logs_created_at (created_at),
    INDEX idx_audit_logs_module_key (module_key),
    INDEX idx_audit_logs_action_key (action_key),
    INDEX idx_audit_logs_order_id (order_id),
    INDEX idx_audit_logs_payment_id (payment_id),
    INDEX idx_audit_logs_actor_employee_id (actor_employee_id),
    INDEX idx_audit_logs_branch_name (branch_name),
    INDEX idx_audit_logs_bill_number (bill_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_maintenance_logs (
    maintenance_log_id INT AUTO_INCREMENT PRIMARY KEY,
    maintenance_type VARCHAR(40) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'completed',
    actor_employee_id INT NULL,
    actor_name VARCHAR(120) NULL,
    branch_name VARCHAR(120) NULL,
    snapshot_path VARCHAR(255) NULL,
    filters_json LONGTEXT NULL,
    preview_json LONGTEXT NULL,
    result_json LONGTEXT NULL,
    note VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_system_maintenance_created_at (created_at),
    INDEX idx_system_maintenance_type (maintenance_type),
    INDEX idx_system_maintenance_actor (actor_employee_id),
    INDEX idx_system_maintenance_branch (branch_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS loyalty_transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_id INT NULL,
    points INT NOT NULL,
    transaction_type ENUM('earn', 'redeem', 'adjust') NOT NULL DEFAULT 'earn',
    description VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_loyalty_order_type (order_id, transaction_type),
    INDEX idx_loyalty_customer (customer_id),
    CONSTRAINT fk_loyalty_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_loyalty_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(120) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    cost DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(60) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL DEFAULT 'staff',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO service_categories (category_name)
SELECT 'Wash'
WHERE NOT EXISTS (SELECT 1 FROM service_categories WHERE category_name = 'Wash');

INSERT INTO service_categories (category_name)
SELECT 'Detailing'
WHERE NOT EXISTS (SELECT 1 FROM service_categories WHERE category_name = 'Detailing');

INSERT INTO service_categories (category_name)
SELECT 'Interior'
WHERE NOT EXISTS (SELECT 1 FROM service_categories WHERE category_name = 'Interior');

INSERT INTO service_categories (category_name)
SELECT 'Coating'
WHERE NOT EXISTS (SELECT 1 FROM service_categories WHERE category_name = 'Coating');

INSERT INTO service_categories (category_name)
SELECT 'Protection'
WHERE NOT EXISTS (SELECT 1 FROM service_categories WHERE category_name = 'Protection');

INSERT INTO service_categories (category_name)
SELECT 'Add-on'
WHERE NOT EXISTS (SELECT 1 FROM service_categories WHERE category_name = 'Add-on');

INSERT INTO services (service_name, service_type, category, price, duration_minutes, description)
SELECT 'Standard Wash', 'main_service', 'Wash', 550.00, 0, 'Exterior wash, interior vacuum and quick finish.'
WHERE NOT EXISTS (SELECT 1 FROM services WHERE service_name = 'Standard Wash');

INSERT INTO services (service_name, service_type, category, price, duration_minutes, description)
SELECT 'Premium Polish', 'main_service', 'Detailing', 2500.00, 0, 'Paint correction, gloss recovery and wax protection.'
WHERE NOT EXISTS (SELECT 1 FROM services WHERE service_name = 'Premium Polish');

INSERT INTO services (service_name, service_type, category, price, duration_minutes, description)
SELECT 'Interior Deep Clean', 'main_service', 'Interior', 3200.00, 0, 'Seat, carpet, panel and odor treatment for cabin care.'
WHERE NOT EXISTS (SELECT 1 FROM services WHERE service_name = 'Interior Deep Clean');

INSERT INTO services (service_name, service_type, category, price, duration_minutes, description)
SELECT 'Glass Coating', 'main_service', 'Coating', 12900.00, 0, 'Nano-ceramic glass coating with surface prep.'
WHERE NOT EXISTS (SELECT 1 FROM services WHERE service_name = 'Glass Coating');

INSERT INTO services (service_name, service_type, category, price, duration_minutes, description)
SELECT 'Ceramic Pro Gold Package', 'main_service', 'Coating', 37900.00, 0, 'Multi-stage ceramic coating package with premium finish.'
WHERE NOT EXISTS (SELECT 1 FROM services WHERE service_name = 'Ceramic Pro Gold Package');

INSERT INTO services (service_name, service_type, category, price, duration_minutes, description)
SELECT 'Paint Protection Film', 'main_service', 'Protection', 45000.00, 0, 'Full body PPF installation and finishing inspection.'
WHERE NOT EXISTS (SELECT 1 FROM services WHERE service_name = 'Paint Protection Film');

INSERT INTO services (service_name, service_type, category, price, duration_minutes, description)
SELECT 'Engine Cleaning', 'main_service', 'Add-on', 800.00, 0, 'Engine bay cleaning and light dressing.'
WHERE NOT EXISTS (SELECT 1 FROM services WHERE service_name = 'Engine Cleaning');

INSERT INTO services (service_name, service_type, category, price, duration_minutes, description)
SELECT 'AC System Ozone', 'main_service', 'Add-on', 500.00, 0, 'Ozone treatment for air conditioning and cabin odor.'
WHERE NOT EXISTS (SELECT 1 FROM services WHERE service_name = 'AC System Ozone');

INSERT INTO positions (position_name, role_key, branch_scope, permissions)
SELECT 'Administrator', 'admin', 'all', '["dashboard","booking","queue","queue_bill_control","queue_cancel_closed","queue_cancel_open","queue_edit_open","services","member","report_service","sale_report","void_report","loyalty","employees"]'
WHERE NOT EXISTS (SELECT 1 FROM positions WHERE role_key = 'admin');

INSERT INTO positions (position_name, role_key, branch_scope, permissions)
SELECT 'Branch Manager', 'manager', 'assigned', '["dashboard","booking","queue","services","member","report_service","sale_report","void_report","loyalty"]'
WHERE NOT EXISTS (SELECT 1 FROM positions WHERE role_key = 'manager');

INSERT INTO positions (position_name, role_key, branch_scope, permissions)
SELECT 'Service Advisor', 'advisor', 'assigned', '["dashboard","booking","queue","member","loyalty"]'
WHERE NOT EXISTS (SELECT 1 FROM positions WHERE role_key = 'advisor');

INSERT INTO positions (position_name, role_key, branch_scope, permissions)
SELECT 'Accounting', 'accounting', 'assigned', '["dashboard","report_service","sale_report","void_report"]'
WHERE NOT EXISTS (SELECT 1 FROM positions WHERE role_key = 'accounting');

INSERT INTO positions (position_name, role_key, branch_scope, permissions)
SELECT 'Viewer', 'viewer', 'assigned', '["dashboard","report_service","sale_report","void_report"]'
WHERE NOT EXISTS (SELECT 1 FROM positions WHERE role_key = 'viewer');

INSERT INTO employees (position_id, name, position, phone, username, password_hash, branch_scope)
SELECT p.position_id, 'System Administrator', 'Administrator', '', 'admin', '$2y$10$phnLrk3h/qe2sh7H5i5rwOLB78Xf6PjggCYpwgFTI4vHTd6aDv.rS', 'all'
FROM positions p
WHERE p.role_key = 'admin'
    AND NOT EXISTS (SELECT 1 FROM employees WHERE username = 'admin');

INSERT INTO employees (position_id, name, position, phone, branch_scope)
SELECT p.position_id, 'Service Advisor', 'Service Advisor', '', 'assigned'
FROM positions p
WHERE p.role_key = 'advisor'
    AND NOT EXISTS (SELECT 1 FROM employees WHERE name = 'Service Advisor');
