-- Luxury Car Marketplace Database Schema
-- MySQL 5.7+ Compatible
-- Database: patrimony_apitest

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

USE patrimony_apitest;

-- ============================================
-- Phase 1: Core Tables
-- ============================================

-- 1. Users Table
CREATE TABLE IF NOT EXISTS users (
    id CHAR(36) PRIMARY KEY,
    user_id VARCHAR(50) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone_number VARCHAR(20) UNIQUE,
    country VARCHAR(10) DEFAULT 'OM',
    currency VARCHAR(3) DEFAULT 'OMR',
    password_hash VARCHAR(255) NOT NULL,
    date_of_birth DATE,
    profile_image VARCHAR(500),
    role ENUM('admin', 'customer', 'dealer') NOT NULL DEFAULT 'customer',
    is_verified BOOLEAN DEFAULT FALSE,
    is_kyc_verified BOOLEAN DEFAULT FALSE,
    is_status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    is_deleted BOOLEAN DEFAULT FALSE,
    reset_token VARCHAR(255),
    reset_token_expiry TIMESTAMP NULL,
    verification_token VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_email (email),
    INDEX idx_users_phone (phone_number),
    INDEX idx_users_role (role),
    INDEX idx_users_status (is_status),
    INDEX idx_users_country (country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Vehicle Brands Table
CREATE TABLE IF NOT EXISTS vehicle_brands (
    id CHAR(36) PRIMARY KEY,
    brand_name VARCHAR(100) UNIQUE NOT NULL,
    brand_logo VARCHAR(500),
    slug VARCHAR(100) UNIQUE,
    is_luxury BOOLEAN DEFAULT FALSE,
    display_order INT,
    is_status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_brands_status (is_status),
    INDEX idx_brands_luxury (is_luxury)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Vehicle Models Table
CREATE TABLE IF NOT EXISTS vehicle_models (
    id CHAR(36) PRIMARY KEY,
    vehicle_brand_id CHAR(36) NOT NULL,
    model_name VARCHAR(100) NOT NULL,
    model_images JSON,
    slug VARCHAR(150) UNIQUE,
    year_introduced INT,
    is_status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_models_brand (vehicle_brand_id),
    INDEX idx_models_status (is_status),
    FOREIGN KEY (vehicle_brand_id) REFERENCES vehicle_brands(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Extra Features Table
CREATE TABLE IF NOT EXISTS extra_features (
    id CHAR(36) PRIMARY KEY,
    feature_name VARCHAR(100) UNIQUE NOT NULL,
    feature_category ENUM('safety', 'comfort', 'technology', 'performance', 'entertainment'),
    icon VARCHAR(500),
    is_premium BOOLEAN DEFAULT FALSE,
    is_status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Phase 2: Main Features
-- ============================================

-- 5. Car Listings Table
CREATE TABLE IF NOT EXISTS car_listings (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    country VARCHAR(10) NOT NULL DEFAULT 'OM',
    currency VARCHAR(3) NOT NULL DEFAULT 'OMR',
    vehicle_brand_id CHAR(36) NOT NULL,
    vehicle_model_id CHAR(36) NOT NULL,
    listing_name VARCHAR(200) NOT NULL,
    listing_description TEXT,
    vehicle_year INT NOT NULL,
    `condition` ENUM('new', 'used') NOT NULL,
    kilometers INT,
    chassis_number VARCHAR(50) UNIQUE,
    vehicle_version VARCHAR(100),
    engine_type VARCHAR(100),
    fuel_type ENUM('diesel', 'electric', 'gasoline', 'hybrid', 'plug_in_hybrid') NOT NULL,
    transmission_type ENUM('automatic', 'manual', 'semi_automatic') NOT NULL,
    body_type ENUM('convertible', 'estate', 'hatchback', 'mpv', 'pickup', 'sedan', 'small_city_car', 'sports_car', 'coupe', 'suv', 'van', 'bus') NOT NULL,
    power_hp INT,
    consumption DECIMAL(5,2),
    exterior_color VARCHAR(50) NOT NULL,
    interior_type ENUM('cloth', 'leather', 'part_leather', 'velour', 'alcantara', 'other'),
    number_of_seats INT,
    number_of_doors INT,
    number_of_owners INT,
    air_conditioning BOOLEAN DEFAULT FALSE,
    warranty ENUM('yes', 'no', 'not_applicable'),
    warranty_details TEXT,
    source_country VARCHAR(100),
    regional_specs ENUM('american', 'chinese', 'gcc', 'european', 'japanese', 'korean', 'other'),
    location VARCHAR(200) NOT NULL,
    listing_lat DECIMAL(10,8),
    listing_long DECIMAL(11,8),
    price DECIMAL(12,2) NOT NULL,
    payment_type ENUM('cash', 'card', 'both', 'financing') NOT NULL,
    contact_type ENUM('chat', 'call', 'both') NOT NULL,
    listing_images JSON NOT NULL,
    view_count INT DEFAULT 0,
    inquiry_count INT DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    is_verified BOOLEAN DEFAULT FALSE,
    listing_status ENUM('pending', 'active', 'sold', 'expired', 'rejected') DEFAULT 'pending',
    is_deleted BOOLEAN DEFAULT FALSE,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_listings_user (user_id),
    INDEX idx_listings_country (country),
    INDEX idx_listings_currency (currency),
    INDEX idx_listings_brand (vehicle_brand_id),
    INDEX idx_listings_model (vehicle_model_id),
    INDEX idx_listings_status (listing_status),
    INDEX idx_listings_featured (is_featured),
    INDEX idx_listings_location (listing_lat, listing_long),
    INDEX idx_listings_price (price),
    INDEX idx_listings_year (vehicle_year),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (vehicle_brand_id) REFERENCES vehicle_brands(id) ON DELETE CASCADE,
    FOREIGN KEY (vehicle_model_id) REFERENCES vehicle_models(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Listing Features Table (Junction)
CREATE TABLE IF NOT EXISTS listing_features (
    id CHAR(36) PRIMARY KEY,
    listing_id CHAR(36) NOT NULL,
    feature_id CHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_listing_features_listing (listing_id),
    INDEX idx_listing_features_feature (feature_id),
    UNIQUE KEY unique_listing_feature (listing_id, feature_id),
    FOREIGN KEY (listing_id) REFERENCES car_listings(id) ON DELETE CASCADE,
    FOREIGN KEY (feature_id) REFERENCES extra_features(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Favorites/Wishlist Table
CREATE TABLE IF NOT EXISTS favorites (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    listing_id CHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_favorites_user (user_id),
    UNIQUE KEY unique_favorite (user_id, listing_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (listing_id) REFERENCES car_listings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Inquiries Table
CREATE TABLE IF NOT EXISTS inquiries (
    id CHAR(36) PRIMARY KEY,
    listing_id CHAR(36) NOT NULL,
    buyer_id CHAR(36) NOT NULL,
    seller_id CHAR(36) NOT NULL,
    message TEXT NOT NULL,
    contact_method ENUM('chat', 'call', 'email'),
    status ENUM('open', 'responded', 'closed') DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_inquiries_listing (listing_id),
    INDEX idx_inquiries_buyer (buyer_id),
    INDEX idx_inquiries_seller (seller_id),
    FOREIGN KEY (listing_id) REFERENCES car_listings(id) ON DELETE CASCADE,
    FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Phase 3: Business Logic
-- ============================================

-- 9. Dealer Profiles Table
CREATE TABLE IF NOT EXISTS dealer_profiles (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) UNIQUE NOT NULL,
    dealership_name VARCHAR(200) NOT NULL,
    business_license VARCHAR(100),
    trade_license_document VARCHAR(500),
    showroom_address TEXT,
    showroom_lat DECIMAL(10,8),
    showroom_long DECIMAL(11,8),
    website VARCHAR(255),
    description TEXT,
    logo VARCHAR(500),
    cover_image VARCHAR(500),
    total_listings INT DEFAULT 0,
    verification_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_dealers_user (user_id),
    INDEX idx_dealers_verification (verification_status),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. KYC Documents Table
CREATE TABLE IF NOT EXISTS kyc_documents (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    document_type ENUM('national_id', 'passport', 'drivers_license', 'trade_license') NOT NULL,
    document_number VARCHAR(100),
    document_front VARCHAR(500) NOT NULL,
    document_back VARCHAR(500),
    verification_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    rejection_reason TEXT,
    verified_by CHAR(36),
    verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_kyc_user (user_id),
    INDEX idx_kyc_status (verification_status),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Reviews Table
CREATE TABLE IF NOT EXISTS reviews (
    id CHAR(36) PRIMARY KEY,
    reviewer_id CHAR(36) NOT NULL,
    reviewed_user_id CHAR(36) NOT NULL,
    listing_id CHAR(36) NOT NULL,
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    review_text TEXT,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    is_approved BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_reviews_reviewed_user (reviewed_user_id),
    UNIQUE KEY unique_review (reviewer_id, listing_id),
    FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (listing_id) REFERENCES car_listings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Notifications Table
CREATE TABLE IF NOT EXISTS notifications (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('listing_approved', 'inquiry_received', 'message', 'system', 'kyc_approved') NOT NULL,
    related_entity_type VARCHAR(50),
    related_entity_id CHAR(36),
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_notifications_user (user_id),
    INDEX idx_notifications_read (user_id, is_read),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Phase 4: Monetization
-- ============================================

-- 13. Subscriptions Table
CREATE TABLE IF NOT EXISTS subscriptions (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    plan_type ENUM('basic', 'premium', 'elite', 'dealer') NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    featured_listings INT DEFAULT 0,
    total_listings INT DEFAULT 10,
    starts_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    auto_renew BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_subscriptions_user (user_id),
    INDEX idx_subscriptions_active (user_id, is_active),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Payment Transactions Table
CREATE TABLE IF NOT EXISTS payment_transactions (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    subscription_id CHAR(36),
    transaction_id VARCHAR(100) UNIQUE,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'AED',
    payment_method ENUM('card', 'bank_transfer', 'wallet'),
    payment_status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    gateway_response JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_transactions_user (user_id),
    INDEX idx_transactions_status (payment_status),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. Audit Logs Table
CREATE TABLE IF NOT EXISTS audit_logs (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36),
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id CHAR(36),
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_created (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

