-- ====================================================================
-- ASHOK SERVICES - DATABASE SCHEMA
-- Engine: InnoDB | Charset: utf8mb4
-- ====================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ====================================================================
-- 1. USERS
-- ====================================================================
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mobile VARCHAR(15) NOT NULL UNIQUE,
  name VARCHAR(150) DEFAULT NULL,
  email VARCHAR(150) DEFAULT NULL,
  address TEXT DEFAULT NULL,
  city VARCHAR(100) DEFAULT NULL,
  state VARCHAR(100) DEFAULT NULL,
  pincode VARCHAR(10) DEFAULT NULL,
  profile_image VARCHAR(255) DEFAULT NULL,
  fcm_token VARCHAR(255) DEFAULT NULL,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  is_verified TINYINT(1) NOT NULL DEFAULT 0,
  last_login_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_users_mobile (mobile),
  INDEX idx_users_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- OTP table (separate, so OTP logic never touches users table directly)
CREATE TABLE IF NOT EXISTS otp_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  mobile VARCHAR(15) NOT NULL,
  otp VARCHAR(10) NOT NULL,
  purpose ENUM('login','register') NOT NULL DEFAULT 'login',
  is_used TINYINT(1) NOT NULL DEFAULT 0,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_otp_mobile (mobile)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 2. ADMINS (role-ready architecture)
-- ====================================================================
CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('superadmin','admin','staff') NOT NULL DEFAULT 'admin',
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  last_login_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activity_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_type ENUM('admin','user','system') NOT NULL DEFAULT 'admin',
  actor_id INT UNSIGNED DEFAULT NULL,
  action VARCHAR(150) NOT NULL,
  module VARCHAR(100) DEFAULT NULL,
  reference_id INT UNSIGNED DEFAULT NULL,
  description TEXT DEFAULT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_log_actor (actor_type, actor_id),
  INDEX idx_log_module (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 3. SERVICE CATEGORIES & SERVICES (dynamic, admin managed)
-- ====================================================================
CREATE TABLE IF NOT EXISTS categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(170) NOT NULL UNIQUE,
  icon VARCHAR(255) DEFAULT NULL,
  description VARCHAR(500) DEFAULT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS services (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NOT NULL,
  name VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  image VARCHAR(255) DEFAULT NULL,
  description TEXT DEFAULT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  discount_price DECIMAL(10,2) DEFAULT NULL,
  required_documents TEXT DEFAULT NULL COMMENT 'JSON array of document names',
  processing_time VARCHAR(100) DEFAULT NULL,
  form_fields TEXT DEFAULT NULL COMMENT 'JSON schema for dynamic form',
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_services_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
  INDEX idx_services_category (category_id),
  INDEX idx_services_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 4. SERVICE REQUESTS (applications) + submitted form data
-- ====================================================================
CREATE TABLE IF NOT EXISTS service_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_code VARCHAR(30) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  service_id INT UNSIGNED NOT NULL,
  status ENUM('pending','under_review','documents_required','processing','completed','rejected','cancelled') NOT NULL DEFAULT 'pending',
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  admin_notes TEXT DEFAULT NULL,
  rejection_reason VARCHAR(500) DEFAULT NULL,
  final_document VARCHAR(255) DEFAULT NULL,
  assigned_admin_id INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_sr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_sr_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE RESTRICT,
  CONSTRAINT fk_sr_admin FOREIGN KEY (assigned_admin_id) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_sr_user (user_id),
  INDEX idx_sr_status (status),
  INDEX idx_sr_service (service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_forms (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  service_request_id INT UNSIGNED NOT NULL,
  field_key VARCHAR(150) NOT NULL,
  field_label VARCHAR(200) DEFAULT NULL,
  field_value TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sf_request FOREIGN KEY (service_request_id) REFERENCES service_requests(id) ON DELETE CASCADE,
  INDEX idx_sf_request (service_request_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 5. DOCUMENTS (polymorphic - attach to any request type)
-- ====================================================================
CREATE TABLE IF NOT EXISTS documents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  owner_type ENUM('service_request','insurance_request','vehicle','profile') NOT NULL,
  owner_id INT UNSIGNED DEFAULT NULL,
  doc_type VARCHAR(100) DEFAULT NULL,
  file_path VARCHAR(255) NOT NULL,
  file_name VARCHAR(255) DEFAULT NULL,
  file_size INT UNSIGNED DEFAULT NULL,
  mime_type VARCHAR(100) DEFAULT NULL,
  uploaded_by ENUM('user','admin') NOT NULL DEFAULT 'user',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_doc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_doc_owner (owner_type, owner_id),
  INDEX idx_doc_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 6. PAYMENTS (polymorphic across request types)
-- ====================================================================
CREATE TABLE IF NOT EXISTS payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transaction_id VARCHAR(100) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  owner_type ENUM('service_request','flight_request','train_request','bus_request','taxi_request','insurance_request') NOT NULL,
  owner_id INT UNSIGNED NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  gateway VARCHAR(50) DEFAULT NULL,
  gateway_payment_id VARCHAR(150) DEFAULT NULL,
  status ENUM('pending','successful','failed','refunded') NOT NULL DEFAULT 'pending',
  refund_status ENUM('none','requested','processed') NOT NULL DEFAULT 'none',
  receipt_path VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_pay_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_pay_owner (owner_type, owner_id),
  INDEX idx_pay_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 7. GPS TRACKING
-- ====================================================================
CREATE TABLE IF NOT EXISTS gps_devices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_imei VARCHAR(50) NOT NULL UNIQUE,
  sim_number VARCHAR(20) DEFAULT NULL,
  device_model VARCHAR(100) DEFAULT NULL,
  status ENUM('unassigned','assigned','inactive') NOT NULL DEFAULT 'unassigned',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  gps_device_id INT UNSIGNED DEFAULT NULL,
  vehicle_number VARCHAR(20) NOT NULL,
  vehicle_name VARCHAR(150) DEFAULT NULL,
  vehicle_type VARCHAR(50) DEFAULT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_veh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_veh_device FOREIGN KEY (gps_device_id) REFERENCES gps_devices(id) ON DELETE SET NULL,
  INDEX idx_veh_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS gps_locations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vehicle_id INT UNSIGNED NOT NULL,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  speed DECIMAL(6,2) DEFAULT 0.00,
  is_online TINYINT(1) NOT NULL DEFAULT 1,
  recorded_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_loc_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE CASCADE,
  INDEX idx_loc_vehicle_time (vehicle_id, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 8. BOOKING REQUESTS (flight / train / bus / taxi / tour)
-- ====================================================================
CREATE TABLE IF NOT EXISTS flight_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_code VARCHAR(30) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  from_city VARCHAR(100) NOT NULL,
  to_city VARCHAR(100) NOT NULL,
  journey_date DATE NOT NULL,
  passenger_count INT UNSIGNED NOT NULL DEFAULT 1,
  passenger_details TEXT DEFAULT NULL COMMENT 'JSON array',
  contact_number VARCHAR(15) NOT NULL,
  status ENUM('pending','under_review','processing','completed','cancelled') NOT NULL DEFAULT 'pending',
  admin_notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_fr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_fr_user (user_id), INDEX idx_fr_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS train_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_code VARCHAR(30) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  from_city VARCHAR(100) NOT NULL,
  to_city VARCHAR(100) NOT NULL,
  journey_date DATE NOT NULL,
  passenger_count INT UNSIGNED NOT NULL DEFAULT 1,
  passenger_details TEXT DEFAULT NULL,
  contact_number VARCHAR(15) NOT NULL,
  status ENUM('pending','under_review','processing','completed','cancelled') NOT NULL DEFAULT 'pending',
  admin_notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_tr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_tr_user (user_id), INDEX idx_tr_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bus_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_code VARCHAR(30) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  from_city VARCHAR(100) NOT NULL,
  to_city VARCHAR(100) NOT NULL,
  journey_date DATE NOT NULL,
  passenger_count INT UNSIGNED NOT NULL DEFAULT 1,
  passenger_details TEXT DEFAULT NULL,
  contact_number VARCHAR(15) NOT NULL,
  status ENUM('pending','under_review','processing','completed','cancelled') NOT NULL DEFAULT 'pending',
  admin_notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_br_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_br_user (user_id), INDEX idx_br_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS taxi_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_code VARCHAR(30) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  pickup_location VARCHAR(255) NOT NULL,
  drop_location VARCHAR(255) NOT NULL,
  journey_datetime DATETIME NOT NULL,
  vehicle_type VARCHAR(50) DEFAULT NULL,
  contact_number VARCHAR(15) NOT NULL,
  status ENUM('pending','under_review','processing','completed','cancelled') NOT NULL DEFAULT 'pending',
  admin_notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_taxi_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_taxi_user (user_id), INDEX idx_taxi_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tour_packages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  image VARCHAR(255) DEFAULT NULL,
  description TEXT DEFAULT NULL,
  duration VARCHAR(100) DEFAULT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tour_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_code VARCHAR(30) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  tour_package_id INT UNSIGNED NOT NULL,
  travel_date DATE NOT NULL,
  traveler_count INT UNSIGNED NOT NULL DEFAULT 1,
  contact_number VARCHAR(15) NOT NULL,
  status ENUM('pending','under_review','processing','completed','cancelled') NOT NULL DEFAULT 'pending',
  admin_notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_tourreq_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_tourreq_pkg FOREIGN KEY (tour_package_id) REFERENCES tour_packages(id) ON DELETE RESTRICT,
  INDEX idx_tourreq_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 9. VEHICLE INSURANCE
-- ====================================================================
CREATE TABLE IF NOT EXISTS insurance_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_code VARCHAR(30) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  insurance_type ENUM('car','bike') NOT NULL,
  vehicle_number VARCHAR(20) NOT NULL,
  vehicle_model VARCHAR(150) DEFAULT NULL,
  previous_policy_number VARCHAR(100) DEFAULT NULL,
  status ENUM('pending','processing','completed','rejected') NOT NULL DEFAULT 'pending',
  policy_document VARCHAR(255) DEFAULT NULL,
  admin_notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_ins_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_ins_user (user_id), INDEX idx_ins_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 10. NOTIFICATIONS
-- ====================================================================
CREATE TABLE IF NOT EXISTS notifications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL = broadcast to all',
  title VARCHAR(200) NOT NULL,
  message TEXT NOT NULL,
  type ENUM('application','payment','document','booking','offer','general') NOT NULL DEFAULT 'general',
  reference_type VARCHAR(50) DEFAULT NULL,
  reference_id INT UNSIGNED DEFAULT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  sent_by_admin_id INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_notif_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 11. BANNERS / OFFERS
-- ====================================================================
CREATE TABLE IF NOT EXISTS banners (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) DEFAULT NULL,
  image VARCHAR(255) NOT NULL,
  link_type VARCHAR(50) DEFAULT NULL,
  link_value VARCHAR(255) DEFAULT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS offers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  description VARCHAR(500) DEFAULT NULL,
  code VARCHAR(50) DEFAULT NULL,
  discount_type ENUM('flat','percent') NOT NULL DEFAULT 'flat',
  discount_value DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  valid_from DATE DEFAULT NULL,
  valid_till DATE DEFAULT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 12. SUPPORT
-- ====================================================================
CREATE TABLE IF NOT EXISTS support_tickets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_code VARCHAR(30) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  subject VARCHAR(200) NOT NULL,
  message TEXT NOT NULL,
  status ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
  admin_reply TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_ticket_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_ticket_user (user_id), INDEX idx_ticket_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- 13. SETTINGS (key-value)
-- ====================================================================
CREATE TABLE IF NOT EXISTS settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT DEFAULT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ====================================================================
-- DEFAULT DATA
-- ====================================================================

-- Default super admin: email admin@ashokservices.com / password: Admin@123
INSERT INTO admins (name, email, password, role, status) VALUES
('Super Admin', 'admin@ashokservices.com', '$2y$10$059EuZq0Vwo19FS.6jDcPOCIJPcbKmCG.OEEl/DoGOiJJuTYqdobO', 'superadmin', 'active');
-- NOTE: hash above corresponds to password "Admin@123" (bcrypt). Change immediately after first login.

-- Default settings
INSERT INTO settings (setting_key, setting_value) VALUES
('app_name', 'Ashok Services'),
('app_logo', ''),
('contact_number', ''),
('whatsapp_number', ''),
('support_email', ''),
('privacy_policy', ''),
('terms_conditions', ''),
('payment_gateway', 'razorpay'),
('payment_key_id', ''),
('payment_key_secret', ''),
('otp_expiry_minutes', '5');

-- Default categories
INSERT INTO categories (name, slug, sort_order, status) VALUES
('GPS Vehicle Tracking', 'gps-vehicle-tracking', 1, 'active'),
('Flight Ticket', 'flight-ticket', 2, 'active'),
('Vehicle Insurance', 'vehicle-insurance', 3, 'active'),
('Government & Digital Services', 'government-digital-services', 4, 'active'),
('Vehicle Services', 'vehicle-services', 5, 'active'),
('Train/Bus Booking', 'train-bus-booking', 6, 'active'),
('Taxi & Tour', 'taxi-tour', 7, 'active'),
('Document Services', 'document-services', 8, 'active');