PRAGMA foreign_keys = ON;

-- 1. Users table
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(255) NULL,
    role VARCHAR(50) NOT NULL DEFAULT 'client',
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    avatar VARCHAR(255) NULL,
    email_verified_at DATETIME NULL,
    password VARCHAR(255) NOT NULL,
    remember_token VARCHAR(100) NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

-- 2. Password reset tokens
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    email VARCHAR(255) PRIMARY KEY,
    token VARCHAR(255) NOT NULL,
    created_at DATETIME NULL
);

-- 3. Sessions
CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(255) PRIMARY KEY,
    user_id INTEGER NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    payload TEXT NOT NULL,
    last_activity INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS sessions_user_id_index ON sessions (user_id);
CREATE INDEX IF NOT EXISTS sessions_last_activity_index ON sessions (last_activity);

-- 4. Cache tables
CREATE TABLE IF NOT EXISTS cache (
    key VARCHAR(255) PRIMARY KEY,
    value MEDIUMTEXT NOT NULL,
    expiration INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS cache_expiration_index ON cache (expiration);

CREATE TABLE IF NOT EXISTS cache_locks (
    key VARCHAR(255) PRIMARY KEY,
    owner VARCHAR(255) NOT NULL,
    expiration INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS cache_locks_expiration_index ON cache_locks (expiration);

-- 5. Jobs & Failed Jobs
CREATE TABLE IF NOT EXISTS jobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    queue VARCHAR(255) NOT NULL,
    payload LONGTEXT NOT NULL,
    attempts INTEGER NOT NULL,
    reserved_at INTEGER NULL,
    available_at INTEGER NOT NULL,
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS jobs_queue_index ON jobs (queue);

CREATE TABLE IF NOT EXISTS job_batches (
    id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    total_jobs INTEGER NOT NULL,
    pending_jobs INTEGER NOT NULL,
    failed_jobs INTEGER NOT NULL,
    failed_job_ids TEXT NOT NULL,
    options TEXT NULL,
    cancelled_at INTEGER NULL,
    created_at INTEGER NOT NULL,
    finished_at INTEGER NULL
);

CREATE TABLE IF NOT EXISTS failed_jobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uuid VARCHAR(255) NOT NULL UNIQUE,
    connection TEXT NOT NULL,
    queue TEXT NOT NULL,
    payload LONGTEXT NOT NULL,
    exception LONGTEXT NOT NULL,
    failed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 6. Media table
CREATE TABLE IF NOT EXISTS media (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(255) NOT NULL,
    alt_text VARCHAR(255) NOT NULL,
    path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(255) NOT NULL,
    size INTEGER NOT NULL,
    is_public BOOLEAN NOT NULL DEFAULT 0,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

-- 7. Services table
CREATE TABLE IF NOT EXISTS services (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    name_ar VARCHAR(255) NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    short_description TEXT NOT NULL,
    short_description_ar TEXT NULL,
    full_description LONGTEXT NULL,
    starting_price NUMERIC(10,2) NOT NULL DEFAULT 0,
    icon VARCHAR(255) NULL,
    badge VARCHAR(255) NULL,
    features TEXT NULL,
    media_id INTEGER NULL REFERENCES media(id) ON DELETE SET NULL,
    gallery_media_ids TEXT NULL,
    is_active BOOLEAN NOT NULL DEFAULT 1,
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

-- 8. Service Prices table
CREATE TABLE IF NOT EXISTS service_prices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    service_id INTEGER NOT NULL REFERENCES services(id) ON DELETE CASCADE,
    package_name VARCHAR(255) NOT NULL,
    vehicle_category VARCHAR(255) NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    notes VARCHAR(255) NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

-- 9. Clients table
CREATE TABLE IF NOT EXISTS clients (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL,
    whatsapp VARCHAR(255) NULL,
    email VARCHAR(255) NULL,
    address TEXT NULL,
    city VARCHAR(255) NOT NULL DEFAULT 'Abu Dhabi',
    notes TEXT NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

-- 10. Orders table (with vehicle_photos)
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_number VARCHAR(255) NOT NULL UNIQUE,
    client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    service_id INTEGER NULL REFERENCES services(id) ON DELETE SET NULL,
    service_title VARCHAR(255) NOT NULL,
    vehicle_make VARCHAR(255) NOT NULL,
    vehicle_model VARCHAR(255) NOT NULL,
    vehicle_year VARCHAR(255) NULL,
    vehicle_plate VARCHAR(255) NULL,
    vehicle_category VARCHAR(255) NOT NULL DEFAULT 'Sedan',
    current_color VARCHAR(255) NULL,
    target_color VARCHAR(255) NULL,
    package_type VARCHAR(255) NULL,
    pickup_required BOOLEAN NOT NULL DEFAULT 0,
    pickup_location TEXT NULL,
    preferred_date DATE NULL,
    preferred_time VARCHAR(255) NULL,
    preferred_contact_method VARCHAR(255) NOT NULL DEFAULT 'WhatsApp',
    status VARCHAR(255) NOT NULL DEFAULT 'pending_review',
    estimated_cost NUMERIC(10,2) NULL,
    final_cost NUMERIC(10,2) NULL,
    payment_status VARCHAR(255) NOT NULL DEFAULT 'pending',
    customer_notes TEXT NULL,
    vehicle_photos TEXT NULL,
    internal_notes TEXT NULL,
    assigned_technician_id INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

-- 11. Order Status Logs
CREATE TABLE IF NOT EXISTS order_status_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    from_status VARCHAR(255) NULL,
    to_status VARCHAR(255) NOT NULL,
    note TEXT NULL,
    updated_by INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

-- 12. Settings table
CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    module VARCHAR(255) NOT NULL,
    key VARCHAR(255) NOT NULL UNIQUE,
    value TEXT NULL,
    type VARCHAR(255) NOT NULL DEFAULT 'text',
    label VARCHAR(255) NULL,
    description VARCHAR(255) NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
);

-- 13. Migrations Tracker table
CREATE TABLE IF NOT EXISTS migrations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    migration VARCHAR(255) NOT NULL,
    batch INTEGER NOT NULL
);

INSERT OR IGNORE INTO migrations (migration, batch) VALUES 
('0001_01_01_000000_create_users_table', 1),
('0001_01_01_000001_create_cache_table', 1),
('0001_01_01_000002_create_jobs_table', 1),
('2026_09_22_195107_create_media_table', 1),
('2026_09_23_000001_create_auto_care_tables', 1),
('2026_09_23_000002_add_media_id_to_services_table', 1),
('2026_09_23_000003_add_gallery_media_ids_to_services_table', 1),
('2026_09_23_000004_add_vehicle_photos_to_orders_table', 1);
