-- MobileDukaan Backend Schema
-- Import via phpMyAdmin or: mysql -u user -p mobiledukaan < database/schema.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('super_admin','seller','buyer') NOT NULL DEFAULT 'seller',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME 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 api_tokens (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  token CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ MASTER DATA (dynamic) ============

CREATE TABLE IF NOT EXISTS brands (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  slug VARCHAR(80) NOT NULL UNIQUE,
  logo_url VARCHAR(500) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS phone_models (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(150) NOT NULL,
  release_year SMALLINT UNSIGNED NULL,
  sort_order INT NOT NULL DEFAULT 0,
  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,
  FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  UNIQUE KEY uq_brand_model (brand_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS model_variants (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  phone_model_id INT UNSIGNED NOT NULL,
  name VARCHAR(80) NOT NULL,
  storage VARCHAR(20) NULL,
  ram VARCHAR(20) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  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,
  FOREIGN KEY (phone_model_id) REFERENCES phone_models(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS colors (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL UNIQUE,
  hex_code VARCHAR(7) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cities (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  state VARCHAR(80) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_city_state (name, state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS phone_conditions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(40) NOT NULL UNIQUE,
  slug VARCHAR(40) NOT NULL UNIQUE,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscription_plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  slug VARCHAR(60) NOT NULL UNIQUE,
  price_monthly DECIMAL(10,2) NOT NULL DEFAULT 0,
  price_yearly DECIMAL(10,2) NULL,
  device_limit INT NOT NULL DEFAULT 25,
  features JSON NULL,
  is_popular TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ SHOPS (admin managed) ============

CREATE TABLE IF NOT EXISTS shops (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(150) NOT NULL UNIQUE,
  owner_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(20) NULL,
  gstin VARCHAR(20) NULL,
  pan VARCHAR(20) NULL,
  address TEXT NULL,
  city_id INT UNSIGNED NULL,
  city_name VARCHAR(80) NULL,
  logo_url VARCHAR(500) NULL,
  cover_url VARCHAR(500) NULL,
  description TEXT NULL,
  plan_id INT UNSIGNED NULL,
  status ENUM('active','trial','suspended') NOT NULL DEFAULT 'trial',
  verified TINYINT(1) NOT NULL DEFAULT 0,
  whatsapp_enabled TINYINT(1) NOT NULL DEFAULT 0,
  whatsapp_number VARCHAR(20) NULL,
  devices_count INT NOT NULL DEFAULT 0,
  portal_listings_count INT NOT NULL DEFAULT 0,
  total_sales INT NOT NULL DEFAULT 0,
  revenue DECIMAL(14,2) NOT NULL DEFAULT 0,
  mrr DECIMAL(10,2) NOT NULL DEFAULT 0,
  employees_count INT NOT NULL DEFAULT 0,
  subscription_ends_at DATE NULL,
  last_active_at DATETIME NULL,
  joined_at DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE SET NULL,
  FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ CMS ============

CREATE TABLE IF NOT EXISTS cms_settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  page_key VARCHAR(40) NOT NULL DEFAULT 'seller_landing',
  meta_title VARCHAR(200) NULL,
  meta_description TEXT NULL,
  og_title VARCHAR(200) NULL,
  og_description TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_page (page_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_hero (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  page_key VARCHAR(40) NOT NULL DEFAULT 'seller_landing',
  badge_text VARCHAR(200) NULL,
  headline VARCHAR(300) NOT NULL,
  headline_highlight VARCHAR(100) NULL,
  subheadline TEXT NULL,
  cta_primary_text VARCHAR(80) NULL,
  cta_primary_url VARCHAR(300) NULL,
  cta_secondary_text VARCHAR(80) NULL,
  cta_secondary_url VARCHAR(300) NULL,
  dashboard_image_url VARCHAR(500) NULL,
  floating_phone_image_url VARCHAR(500) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_ticker_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  text VARCHAR(300) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_transformation_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tag VARCHAR(20) NULL,
  title VARCHAR(120) NOT NULL,
  description TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_feature_stories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tag VARCHAR(40) NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NOT NULL,
  link_text VARCHAR(80) NULL,
  link_url VARCHAR(300) NULL,
  image_url VARCHAR(500) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_marketplace (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  headline VARCHAR(300) NULL,
  subheadline TEXT NULL,
  stat_value VARCHAR(40) NULL,
  stat_label VARCHAR(80) NULL,
  map_image_url VARCHAR(500) NULL,
  cta_text VARCHAR(80) NULL,
  cta_url VARCHAR(300) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_analytics_kpis (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(80) NOT NULL,
  value VARCHAR(40) NOT NULL,
  trend VARCHAR(40) NULL,
  progress_width VARCHAR(10) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_analytics_banner (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  section_title VARCHAR(200) NULL,
  section_highlight VARCHAR(100) NULL,
  network_gmv_label VARCHAR(100) NULL,
  network_gmv_value VARCHAR(40) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_ecosystem_nodes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_testimonials (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  shop_label VARCHAR(150) NULL,
  quote TEXT NOT NULL,
  avatar_url VARCHAR(500) NULL,
  before_value VARCHAR(60) NULL,
  after_value VARCHAR(60) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_pricing_plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  price_display VARCHAR(40) NOT NULL,
  price_monthly DECIMAL(10,2) NOT NULL DEFAULT 0,
  description TEXT NULL,
  cta_text VARCHAR(60) NULL,
  cta_url VARCHAR(300) NULL,
  is_featured TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_pricing_features (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_id INT UNSIGNED NOT NULL,
  feature_text VARCHAR(200) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  FOREIGN KEY (plan_id) REFERENCES cms_pricing_plans(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_final_cta (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  headline VARCHAR(300) NOT NULL,
  headline_highlight VARCHAR(100) NULL,
  subheadline TEXT NULL,
  cta_text VARCHAR(80) NULL,
  cta_url VARCHAR(300) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_stats (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  value VARCHAR(40) NOT NULL,
  label VARCHAR(80) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_nav_links (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(60) NOT NULL,
  url VARCHAR(300) NOT NULL,
  location ENUM('header','footer') NOT NULL DEFAULT 'header',
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cms_floating_cards (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  card_type ENUM('whatsapp','revenue','instagram','kpi') NOT NULL,
  title VARCHAR(100) NULL,
  subtitle VARCHAR(150) NULL,
  body_text TEXT NULL,
  value_display VARCHAR(60) NULL,
  trend_text VARCHAR(60) NULL,
  image_url VARCHAR(500) NULL,
  chart_data JSON NULL,
  position ENUM('left','right') NOT NULL DEFAULT 'left',
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ ADMIN ============

CREATE TABLE IF NOT EXISTS platform_settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(80) NOT NULL UNIQUE,
  setting_value TEXT NULL,
  setting_type ENUM('string','number','boolean','json') NOT NULL DEFAULT 'string',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_alerts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type ENUM('warning','info','critical','success') NOT NULL DEFAULT 'info',
  title VARCHAR(200) NOT NULL,
  message TEXT NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_activity_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor VARCHAR(120) NOT NULL,
  action VARCHAR(200) NOT NULL,
  target VARCHAR(200) NOT NULL,
  category ENUM('shop','user','billing','system','support','cms') NOT NULL DEFAULT 'system',
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS verification_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id INT UNSIGNED NOT NULL,
  request_type ENUM('shop_verification','listing_review','document_update') NOT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  notes TEXT NULL,
  documents JSON NULL,
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_at DATETIME NULL,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS support_tickets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_code VARCHAR(20) NOT NULL UNIQUE,
  shop_id INT UNSIGNED NOT NULL,
  subject VARCHAR(200) NOT NULL,
  description TEXT NOT NULL,
  priority ENUM('high','medium','low') NOT NULL DEFAULT 'medium',
  status ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
  assigned_to VARCHAR(120) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS support_messages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  sender_name VARCHAR(120) NOT NULL,
  message TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS portal_listings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id INT UNSIGNED NOT NULL,
  title VARCHAR(200) NOT NULL,
  brand_name VARCHAR(80) NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('pending','approved','flagged','rejected') NOT NULL DEFAULT 'pending',
  flags JSON NULL,
  views INT NOT NULL DEFAULT 0,
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS platform_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(20) NULL,
  city_name VARCHAR(80) NULL,
  purchases INT NOT NULL DEFAULT 0,
  total_spent DECIMAL(12,2) NOT NULL DEFAULT 0,
  inquiries INT NOT NULL DEFAULT 0,
  status ENUM('active','blocked','flagged') NOT NULL DEFAULT 'active',
  joined_at DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS revenue_snapshots (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  month_key CHAR(7) NOT NULL,
  mrr DECIMAL(14,2) NOT NULL DEFAULT 0,
  arr DECIMAL(14,2) NOT NULL DEFAULT 0,
  new_shops INT NOT NULL DEFAULT 0,
  churned_shops INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_month (month_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ SELLER APP ============

CREATE TABLE IF NOT EXISTS seller_devices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id INT UNSIGNED NOT NULL,
  brand_id INT UNSIGNED NOT NULL,
  phone_model_id INT UNSIGNED NOT NULL,
  model_variant_id INT UNSIGNED NULL,
  color_id INT UNSIGNED NULL,
  phone_condition_id INT UNSIGNED NULL,
  brand_name VARCHAR(80) NOT NULL,
  model_name VARCHAR(120) NOT NULL,
  variant_name VARCHAR(80) NULL,
  color_name VARCHAR(60) NULL,
  storage VARCHAR(20) NULL,
  ram VARCHAR(20) NULL,
  imei VARCHAR(20) NOT NULL,
  serial_number VARCHAR(80) NULL,
  battery_health TINYINT UNSIGNED NULL,
  condition_slug ENUM('excellent','good','fair','refurbished') NOT NULL DEFAULT 'good',
  purchase_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  asking_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('in_stock','listed','sold','reserved') NOT NULL DEFAULT 'in_stock',
  images JSON NULL,
  specs JSON NULL,
  warranty VARCHAR(120) NULL,
  listed_on_portal TINYINT(1) NOT NULL DEFAULT 0,
  has_box TINYINT(1) NOT NULL DEFAULT 0,
  has_bill TINYINT(1) NOT NULL DEFAULT 0,
  invoice_url VARCHAR(500) NULL,
  description TEXT NULL,
  source_seller_name VARCHAR(120) NULL,
  source_seller_phone VARCHAR(20) NULL,
  source_aadhaar_last4 CHAR(4) NULL,
  source_id_proof_type ENUM('aadhaar','pan','voter','dl') NULL,
  source_address TEXT NULL,
  inquiries INT NOT NULL DEFAULT 0,
  added_at DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
  FOREIGN KEY (brand_id) REFERENCES brands(id),
  FOREIGN KEY (phone_model_id) REFERENCES phone_models(id),
  FOREIGN KEY (model_variant_id) REFERENCES model_variants(id) ON DELETE SET NULL,
  FOREIGN KEY (color_id) REFERENCES colors(id) ON DELETE SET NULL,
  FOREIGN KEY (phone_condition_id) REFERENCES phone_conditions(id) ON DELETE SET NULL,
  UNIQUE KEY uq_shop_imei (shop_id, imei),
  INDEX idx_shop_status (shop_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seller_customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(190) NULL,
  city VARCHAR(80) NULL,
  total_purchases INT NOT NULL DEFAULT 0,
  total_spent DECIMAL(12,2) NOT NULL DEFAULT 0,
  last_contact DATE NULL,
  temperature ENUM('hot','warm','cold') NOT NULL DEFAULT 'warm',
  tags JSON NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
  INDEX idx_shop_phone (shop_id, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seller_leads (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NULL,
  device_id INT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  message TEXT NULL,
  temperature ENUM('hot','warm','cold') NOT NULL DEFAULT 'warm',
  status ENUM('new','contacted','negotiating','won','lost') NOT NULL DEFAULT 'new',
  source ENUM('whatsapp','call','portal','walk_in','other') NOT NULL DEFAULT 'other',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES seller_customers(id) ON DELETE SET NULL,
  FOREIGN KEY (device_id) REFERENCES seller_devices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seller_repair_tickets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id INT UNSIGNED NOT NULL,
  ticket_code VARCHAR(20) NOT NULL,
  customer_name VARCHAR(120) NOT NULL,
  customer_phone VARCHAR(20) NOT NULL,
  device_name VARCHAR(150) NOT NULL,
  issue TEXT NOT NULL,
  status ENUM('received','diagnosing','repairing','ready','delivered') NOT NULL DEFAULT 'received',
  estimated_cost DECIMAL(10,2) NOT NULL DEFAULT 0,
  advance_paid DECIMAL(10,2) NOT NULL DEFAULT 0,
  technician VARCHAR(120) NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
  UNIQUE KEY uq_ticket_code (ticket_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seller_invoices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id INT UNSIGNED NOT NULL,
  invoice_no VARCHAR(30) NOT NULL,
  type ENUM('purchase','sale','repair') NOT NULL,
  customer_name VARCHAR(120) NOT NULL,
  customer_phone VARCHAR(20) NULL,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  gst DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  due DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('paid','partial','unpaid') NOT NULL DEFAULT 'unpaid',
  invoice_date DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
  UNIQUE KEY uq_shop_invoice (shop_id, invoice_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seller_invoice_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT UNSIGNED NOT NULL,
  description VARCHAR(300) NOT NULL,
  qty INT NOT NULL DEFAULT 1,
  rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (invoice_id) REFERENCES seller_invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seller_marketplace_listings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id INT UNSIGNED NOT NULL,
  device_id INT UNSIGNED NOT NULL,
  title VARCHAR(200) NOT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('active','draft','sold') NOT NULL DEFAULT 'draft',
  views INT NOT NULL DEFAULT 0,
  clicks INT NOT NULL DEFAULT 0,
  whatsapp_leads INT NOT NULL DEFAULT 0,
  listed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
  FOREIGN KEY (device_id) REFERENCES seller_devices(id) ON DELETE CASCADE,
  UNIQUE KEY uq_device_listing (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS seller_activity_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id INT UNSIGNED NOT NULL,
  type VARCHAR(40) NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
  INDEX idx_shop_created (shop_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;