-- ExpertTraders.in Production MySQL Schema
-- Database ID / User: expe_experttraders
-- Import directly into phpMyAdmin or MySQL CLI

-- ============================================================================
-- Expert Traders (experttraders.in) — Production SQL Database Schema
-- Compatible with MySQL 8.0+ / MariaDB 10.5+ (phpMyAdmin / cPanel Import Ready)
-- Stores:
--   1. leads: Direct callback/popup/package inquiries (5-sec Ask an Expert modal)
--   2. visitor_market_interests: Automated Share Market & Trading intent scraping
--   3. tool_usage_signals: Calculator inputs & segment/capital interest signals
--   4. compliance_complaints: Grievance & Complaint Board submissions
-- ============================================================================

SET NAMES utf8mb4;
SET time_zone = '+05:30';

CREATE TABLE IF NOT EXISTS `leads` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `lead_uuid` VARCHAR(48) NOT NULL,
  `full_name` VARCHAR(140) NOT NULL,
  `mobile` VARCHAR(24) NOT NULL,
  `email` VARCHAR(160) DEFAULT '',
  `segment` VARCHAR(80) NOT NULL DEFAULT 'Nifty / BankNifty Options',
  `trading_capital` VARCHAR(80) NOT NULL DEFAULT '₹1,00,000 – ₹3,00,000',
  `package_selected` VARCHAR(120) DEFAULT 'General Callback Inquiry',
  `package_amount` DECIMAL(12,2) DEFAULT 0.00,
  `source_trigger` VARCHAR(64) DEFAULT '5sec_popup',
  `interested_symbols` VARCHAR(255) DEFAULT 'NIFTY50,BANKNIFTY',
  `tools_used` VARCHAR(255) DEFAULT '',
  `intent_score` INT NOT NULL DEFAULT 75,
  `page_url` VARCHAR(255) DEFAULT '/',
  `referrer` VARCHAR(255) DEFAULT '',
  `utm_source` VARCHAR(80) DEFAULT '',
  `utm_medium` VARCHAR(80) DEFAULT '',
  `utm_campaign` VARCHAR(120) DEFAULT '',
  `device_type` VARCHAR(40) DEFAULT 'desktop',
  `user_agent` VARCHAR(255) DEFAULT '',
  `ip_address` VARCHAR(64) DEFAULT '',
  `consent_agreed` TINYINT(1) NOT NULL DEFAULT 1,
  `lead_status` VARCHAR(32) NOT NULL DEFAULT 'NEW',
  `notes` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_lead_uuid` (`lead_uuid`),
  KEY `idx_mobile` (`mobile`),
  KEY `idx_segment` (`segment`),
  KEY `idx_capital` (`trading_capital`),
  KEY `idx_intent_score` (`intent_score`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `visitor_market_interests` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visitor_hash` VARCHAR(64) NOT NULL,
  `inferred_segment` VARCHAR(80) NOT NULL DEFAULT 'Options & Derivatives',
  `inferred_capital_tier` VARCHAR(80) DEFAULT '₹1,00,000 – ₹3,00,000',
  `symbols_researched` VARCHAR(255) DEFAULT 'NIFTY50,BANKNIFTY',
  `calculators_used` VARCHAR(255) DEFAULT '',
  `packages_viewed` VARCHAR(255) DEFAULT '',
  `partial_mobile` VARCHAR(24) DEFAULT '',
  `partial_email` VARCHAR(160) DEFAULT '',
  `partial_name` VARCHAR(140) DEFAULT '',
  `page_views_count` INT NOT NULL DEFAULT 1,
  `seconds_engaged` INT NOT NULL DEFAULT 0,
  `max_scroll_pct` INT NOT NULL DEFAULT 0,
  `intent_score` INT NOT NULL DEFAULT 40,
  `device_model` VARCHAR(80) DEFAULT '',
  `city_region` VARCHAR(80) DEFAULT 'India',
  `referrer_source` VARCHAR(180) DEFAULT 'direct',
  `last_page` VARCHAR(255) DEFAULT '/index.php',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_visitor_hash` (`visitor_hash`),
  KEY `idx_inferred_segment` (`inferred_segment`),
  KEY `idx_intent_score` (`intent_score`),
  KEY `idx_updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tool_usage_signals` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `visitor_hash` VARCHAR(64) NOT NULL,
  `tool_slug` VARCHAR(120) NOT NULL,
  `tool_category` VARCHAR(64) NOT NULL DEFAULT 'Trading',
  `symbol_focused` VARCHAR(40) DEFAULT 'NIFTY50',
  `capital_input_bracket` VARCHAR(80) DEFAULT '',
  `leverage_or_lots` VARCHAR(64) DEFAULT '',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tool_slug` (`tool_slug`),
  KEY `idx_visitor` (`visitor_hash`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `compliance_complaints` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_no` VARCHAR(32) NOT NULL,
  `client_name` VARCHAR(140) NOT NULL,
  `mobile` VARCHAR(24) NOT NULL,
  `email` VARCHAR(160) NOT NULL,
  `category` VARCHAR(80) NOT NULL,
  `description` TEXT NOT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'RECEIVED',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ticket_no` (`ticket_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed initial realistic market leads & trader intent rows for immediate CRM verification
INSERT INTO `leads` (`lead_uuid`, `full_name`, `mobile`, `email`, `segment`, `trading_capital`, `package_selected`, `package_amount`, `source_trigger`, `interested_symbols`, `tools_used`, `intent_score`, `page_url`, `device_type`, `lead_status`, `created_at`) VALUES
('lead_901a8b2c3d', 'Rajesh Sharma', '+91 98204 51289', 'rajesh.sharma.trades@gmail.com', 'Nifty / BankNifty Options', '₹3,00,000 – ₹5,00,000', 'Premium Quarterly (BEST) - ₹43,750/qtr', 43750.00, 'package_enquire_btn', 'NIFTY50,BANKNIFTY,RELIANCE', 'options-profit-calculator,black-scholes-greeks-calculator', 96, '/packages.php', 'iPhone / iOS', 'HOT_LEAD', '2026-10-31 10:15:22'),
('lead_902b7c4d5e', 'Vikramaditya Deshmukh', '+91 97692 84110', 'vikram.deshmukh@outlook.com', 'Stock Futures & Options', 'Above ₹10,00,000 (HNI)', 'Premium Yearly (ELITE) - ₹1,50,000/yr', 150000.00, '5sec_popup', 'BANKNIFTY,HDFCBANK,ICICIBANK,LT', 'multi-leg-straddle-strangle-calculator,intraday-position-margin-calculator', 98, '/index.php', 'iPhone / iOS', 'HOT_LEAD', '2026-10-31 11:42:09'),
('lead_903c6d5e6f', 'Priya Nair', '+91 98193 64021', 'priya.nair88@yahoo.co.in', 'Equity Intraday', '₹1,00,000 – ₹3,00,000', 'Standard Monthly - ₹12,500/mo', 12500.00, '5sec_popup', 'TCS,INFY,TATAMOTORS', 'pivot-point-calculator,position-size-calculator', 84, '/market-dashboard.php', 'Android Mobile', 'NEW', '2026-10-31 13:08:45'),
('lead_904d5e6f7a', 'Amitabh Patel', '+91 99250 77314', 'amitabh.patel.fin@gmail.com', 'Nifty / BankNifty Options', '₹5,00,000 – ₹10,00,000', 'Premium Half Yearly (PRO) - ₹87,500/hy', 87500.00, 'ask_expert_header', 'NIFTY50,BANKNIFTY,SBIN', 'option-chain-pcr-analyzer,gann-square-of-9-calculator', 92, '/tools.php', 'Desktop', 'CONTACTED', '2026-10-31 14:27:30');

INSERT INTO `visitor_market_interests` (`visitor_hash`, `inferred_segment`, `inferred_capital_tier`, `symbols_researched`, `calculators_used`, `packages_viewed`, `page_views_count`, `seconds_engaged`, `max_scroll_pct`, `intent_score`, `device_model`, `city_region`, `referrer_source`, `last_page`) VALUES
('v_84f19a20b3c1', 'Expiry-Day Index Options (Nifty/BankNifty)', '₹3,00,000 – ₹5,00,000', 'NIFTY50,BANKNIFTY', 'black-scholes-greeks-calculator,option-chain-pcr-analyzer,options-profit-calculator', 'Premium Quarterly (BEST),Premium Monthly (HOT)', 7, 340, 92, 94, 'iPhone 16 Pro (iOS)', 'Mumbai, MH', 'google.com (organic: nifty options research)', '/packages.php'),
('v_73e28b19c4d2', 'HNI F&O Positional & Hedging', 'Above ₹10,00,000 (HNI)', 'RELIANCE,HDFCBANK,ICICIBANK,LT', 'multi-leg-straddle-strangle-calculator,historical-var-calculator', 'Premium Yearly (ELITE),Standard Yearly', 9, 485, 100, 97, 'MacBook / Desktop', 'Navi Mumbai, MH', 'perplexity.ai (llm_referral)', '/multi-leg-straddle-strangle-calculator.php'),
('v_62d37c08d5e3', 'Intraday Equity & Momentum Scalping', '₹1,00,000 – ₹3,00,000', 'TATAMOTORS,SBIN,BHARTIARTL', 'pivot-point-calculator,intraday-position-margin-calculator,gann-square-of-9-calculator', 'Standard Monthly,Standard Quarterly', 5, 215, 85, 81, 'iPhone 15 (iOS)', 'Pune, MH', 'chatgpt.com (llm_referral)', '/pivot-point-calculator.php');
