-- CADDE AUTO MySQL Database Schema
-- MongoDB'den MySQL'e migration için tablo yapıları

-- 1. Domains Tablosu
CREATE TABLE IF NOT EXISTS domains (
  id INT PRIMARY KEY AUTO_INCREMENT,
  domain VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  theme VARCHAR(50) DEFAULT 'default',
  settings JSON,
  contact JSON,
  branding JSON,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_domain (domain),
  INDEX idx_active (is_active)
);

-- 3. Vehicles Tablosu (Ana araç tablosu)
CREATE TABLE IF NOT EXISTS vehicles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  make VARCHAR(100) NOT NULL,
  model VARCHAR(100) NOT NULL,
  year INT NOT NULL,
  vin VARCHAR(17) UNIQUE,
  license_plate VARCHAR(20) NOT NULL UNIQUE,
  category ENUM('economy', 'compact', 'intermediate', 'standard', 'fullsize', 'premium', 'luxury', 'convertible', 'suv', 'van', 'truck') NOT NULL,
  type ENUM('car', 'motorcycle', 'truck', 'van', 'suv', 'other') NOT NULL,

  -- Specifications
  specifications JSON,

  -- Pricing
  pricing JSON,

  -- Availability
  availability JSON,

  -- Media
  media JSON,

  -- Location
  location JSON,

  -- Analytics
  analytics JSON,

  -- SEO
  seo JSON,

  -- Owner info
  owner_id INT,
  owner_type ENUM('company', 'individual') DEFAULT 'company',
  verification_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',

  -- Status
  status ENUM('available', 'rented', 'sold', 'maintenance', 'unavailable') DEFAULT 'available',
  is_active BOOLEAN DEFAULT true,

  -- Metadata
  created_by INT NOT NULL,
  updated_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_make_model (make, model),
  INDEX idx_category (category),
  INDEX idx_type (type),
  INDEX idx_year (year),
  INDEX idx_status (status),
  INDEX idx_active (is_active),
  INDEX idx_owner (owner_id),
  INDEX idx_created_by (created_by),

  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
);

-- 4. Bookings Tablosu (Rezervasyon tablosu)
CREATE TABLE IF NOT EXISTS bookings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  vehicle_id INT NOT NULL,

  -- Booking details
  type ENUM('rental', 'purchase') NOT NULL,
  status ENUM('pending', 'confirmed', 'active', 'completed', 'cancelled', 'no_show') DEFAULT 'pending',

  -- Dates
  start_date DATE NOT NULL,
  end_date DATE,
  return_date DATE,
  total_days INT,

  -- Locations
  pickup_location VARCHAR(255) NOT NULL,
  dropoff_location VARCHAR(255),

  -- Pricing
  pricing JSON,

  -- Payment
  payment JSON,

  -- Customer details
  customer_details JSON,

  -- Additional services
  extras JSON,

  -- Documents
  documents JSON,

  -- Notifications
  notifications JSON,

  -- Notes
  notes TEXT,

  -- Metadata
  created_by INT NOT NULL,
  approved_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_customer (customer_id),
  INDEX idx_vehicle (vehicle_id),
  INDEX idx_type (type),
  INDEX idx_status (status),
  INDEX idx_dates (start_date, end_date),
  INDEX idx_created_by (created_by),

  FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE RESTRICT,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
);

-- 5. Page Content Tablosu
CREATE TABLE IF NOT EXISTS page_contents (
  id INT PRIMARY KEY AUTO_INCREMENT,
  page_type ENUM('homepage', 'about', 'services', 'contact', 'blog', 'portfolio') NOT NULL UNIQUE,
  sections JSON,
  theme JSON,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_page_type (page_type),
  INDEX idx_active (is_active)
);

-- 7. Payments Tablosu (Ödeme takibi için)
CREATE TABLE IF NOT EXISTS payments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  booking_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'TRY',
  payment_method VARCHAR(50),
  transaction_id VARCHAR(255),
  gateway VARCHAR(50),
  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_booking (booking_id),
  INDEX idx_status (status),
  INDEX idx_transaction (transaction_id),

  FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE RESTRICT
);

-- 8. Files Tablosu (Dosya yönetimi için)
CREATE TABLE IF NOT EXISTS files (
  id INT PRIMARY KEY AUTO_INCREMENT,
  filename VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  path VARCHAR(500) NOT NULL,
  size INT NOT NULL,
  mime_type VARCHAR(100),
  entity_type VARCHAR(50), -- 'vehicle', 'user', 'booking', etc.
  entity_id INT,
  field_name VARCHAR(100), -- 'avatar', 'gallery', 'document', etc.
  is_primary BOOLEAN DEFAULT false,
  uploaded_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_entity (entity_type, entity_id),
  INDEX idx_uploaded_by (uploaded_by),
  INDEX idx_primary (is_primary),

  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
);

-- 9. Analytics Tablosu (İstatistikler için)
CREATE TABLE IF NOT EXISTS analytics (
  id INT PRIMARY KEY AUTO_INCREMENT,
  entity_type VARCHAR(50) NOT NULL, -- 'vehicle', 'booking', 'user', etc.
  entity_id INT NOT NULL,
  event_type VARCHAR(50) NOT NULL, -- 'view', 'click', 'booking', etc.
  data JSON,
  ip_address VARCHAR(45),
  user_agent TEXT,
  user_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_entity (entity_type, entity_id),
  INDEX idx_event (event_type),
  INDEX idx_date (created_at),
  INDEX idx_user (user_id),

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Başlangıç verilerini ekle
INSERT IGNORE INTO domains (domain, name, theme, contact, is_active) VALUES
('localhost', 'CADDE AUTO Local', 'default', '{"phone": "+90 555 123 4567", "email": "info@localhost", "address": "Ankara, Türkiye"}', true),
('demo.caddeauto.com', 'CADDE AUTO Demo', 'default', '{"phone": "+90 555 123 4567", "email": "info@caddeauto.com", "address": "İstanbul, Türkiye"}', true);

-- Performans için ek indeksler
ALTER TABLE vehicles ADD FULLTEXT(make, model);
ALTER TABLE bookings ADD INDEX idx_created_at (created_at);
