-- =====================================================================
-- AkDevz AI Market Intelligence — Database Schema (MySQL 8+ / MariaDB 10.4+)
-- Complete Normalized Schema with Foreign Keys, Indexes & Initial Seeds
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Users Table
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(64) NOT NULL UNIQUE,
    `email` VARCHAR(191) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'researcher', 'viewer') NOT NULL DEFAULT 'admin',
    `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,
    INDEX `idx_users_email` (`email`),
    INDEX `idx_users_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. User Settings
DROP TABLE IF EXISTS `user_settings`;
CREATE TABLE `user_settings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `setting_key` VARCHAR(64) NOT NULL,
    `setting_value` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_user_setting` (`user_id`, `setting_key`),
    CONSTRAINT `fk_user_settings_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Sessions (Database-backed session store option)
DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
    `id` VARCHAR(128) PRIMARY KEY,
    `user_id` INT UNSIGNED NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(255) NULL,
    `payload` MEDIUMTEXT NOT NULL,
    `last_activity` INT UNSIGNED NOT NULL,
    INDEX `idx_sessions_user` (`user_id`),
    INDEX `idx_sessions_activity` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Assets (Trading Universe Master)
DROP TABLE IF EXISTS `assets`;
CREATE TABLE `assets` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `symbol` VARCHAR(64) NOT NULL UNIQUE,
    `display_name` VARCHAR(128) NOT NULL,
    `exchange` VARCHAR(16) NOT NULL DEFAULT 'NSE',
    `segment` VARCHAR(16) NOT NULL DEFAULT 'INDEX', -- INDEX, EQUITY, FO
    `lot_size` INT UNSIGNED NOT NULL DEFAULT 1,
    `tick_size` DECIMAL(8,4) NOT NULL DEFAULT 0.05,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `is_watchlist_default` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_assets_exchange_segment` (`exchange`, `segment`),
    INDEX `idx_assets_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Watchlists
DROP TABLE IF EXISTS `watchlists`;
CREATE TABLE `watchlists` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(64) NOT NULL,
    `description` VARCHAR(255) NULL,
    `is_system_default` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_watchlists_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Watchlist Assets (Junction)
DROP TABLE IF EXISTS `watchlist_assets`;
CREATE TABLE `watchlist_assets` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `watchlist_id` INT UNSIGNED NOT NULL,
    `asset_id` INT UNSIGNED NOT NULL,
    `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_watchlist_asset` (`watchlist_id`, `asset_id`),
    CONSTRAINT `fk_wa_watchlist` FOREIGN KEY (`watchlist_id`) REFERENCES `watchlists` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_wa_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Instrument Master (Authoritative FYERS Symbol Directory)
DROP TABLE IF EXISTS `instrument_master`;
CREATE TABLE `instrument_master` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `fytoken` VARCHAR(32) NOT NULL UNIQUE,
    `symbol` VARCHAR(64) NOT NULL UNIQUE,
    `description` VARCHAR(255) NULL,
    `exchange` VARCHAR(16) NOT NULL,
    `segment` VARCHAR(16) NOT NULL,
    `instrument_type` VARCHAR(16) NOT NULL, -- EQ, INDEX, FUT, CE, PE
    `underlying_symbol` VARCHAR(64) NULL,
    `strike_price` DECIMAL(12,2) NULL,
    `option_type` ENUM('CE', 'PE') NULL,
    `expiry_date` DATE NULL,
    `lot_size` INT UNSIGNED NOT NULL DEFAULT 1,
    `tick_size` DECIMAL(8,4) NOT NULL DEFAULT 0.05,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `synced_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_im_underlying_expiry` (`underlying_symbol`, `expiry_date`, `strike_price`),
    INDEX `idx_im_symbol` (`symbol`),
    INDEX `idx_im_type` (`instrument_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Instrument Master Versions
DROP TABLE IF EXISTS `instrument_master_versions`;
CREATE TABLE `instrument_master_versions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `segment` VARCHAR(16) NOT NULL,
    `file_checksum` VARCHAR(64) NOT NULL,
    `total_records` INT UNSIGNED NOT NULL,
    `synced_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_imv_segment` (`segment`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. FYERS Credentials (Encrypted At Rest)
DROP TABLE IF EXISTS `fyers_credentials`;
CREATE TABLE `fyers_credentials` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `app_id` VARCHAR(64) NOT NULL,
    `encrypted_secret_key` TEXT NOT NULL,
    `redirect_uri` VARCHAR(255) NOT NULL,
    `encrypted_access_token` TEXT NULL,
    `token_expires_at` DATETIME NULL,
    `last_authenticated_at` DATETIME NULL,
    `status` ENUM('unconfigured', 'connected', 'expired', 'error') NOT NULL DEFAULT 'unconfigured',
    `error_message` VARCHAR(255) 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 COLLATE=utf8mb4_unicode_ci;

-- 10. FYERS Auth Logs
DROP TABLE IF EXISTS `fyers_auth_logs`;
CREATE TABLE `fyers_auth_logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `event_type` VARCHAR(32) NOT NULL, -- login_attempt, token_granted, token_expired, refresh_failed
    `status` VARCHAR(16) NOT NULL, -- success, failure
    `ip_address` VARCHAR(45) NULL,
    `details` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_fal_event_created` (`event_type`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. FYERS API Usage Logs (Centralized Leaky Bucket Monitor)
DROP TABLE IF EXISTS `fyers_api_usage_logs`;
CREATE TABLE `fyers_api_usage_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `endpoint` VARCHAR(128) NOT NULL,
    `http_method` VARCHAR(8) NOT NULL DEFAULT 'GET',
    `response_code` INT NOT NULL,
    `response_time_ms` INT UNSIGNED NOT NULL,
    `request_date` DATE NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_faul_date_endpoint` (`request_date`, `endpoint`),
    INDEX `idx_faul_code` (`response_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. FYERS Capabilities Register
DROP TABLE IF EXISTS `fyers_capabilities`;
CREATE TABLE `fyers_capabilities` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `feature_name` VARCHAR(64) NOT NULL UNIQUE,
    `endpoint` VARCHAR(128) NOT NULL,
    `plan_availability` ENUM('standard', 'prime', 'none') NOT NULL DEFAULT 'standard',
    `is_verified` TINYINT(1) NOT NULL DEFAULT 1,
    `rate_limit_info` VARCHAR(128) NULL,
    `last_verified_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. FYERS Health Logs
DROP TABLE IF EXISTS `fyers_health_logs`;
CREATE TABLE `fyers_health_logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ping_latency_ms` INT UNSIGNED NULL,
    `status` ENUM('healthy', 'degraded', 'down') NOT NULL,
    `active_websocket_streams` INT UNSIGNED NOT NULL DEFAULT 0,
    `daily_request_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_fhl_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Market Snapshots (Point-in-Time Analytical State)
DROP TABLE IF EXISTS `market_snapshots`;
CREATE TABLE `market_snapshots` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `asset_symbol` VARCHAR(64) NOT NULL,
    `ltp` DECIMAL(12,2) NOT NULL,
    `change_abs` DECIMAL(12,2) NOT NULL,
    `change_pct` DECIMAL(6,2) NOT NULL,
    `open_price` DECIMAL(12,2) NOT NULL,
    `high_price` DECIMAL(12,2) NOT NULL,
    `low_price` DECIMAL(12,2) NOT NULL,
    `prev_close` DECIMAL(12,2) NOT NULL,
    `volume` BIGINT UNSIGNED NOT NULL,
    `vwap` DECIMAL(12,2) NULL,
    `oi` BIGINT UNSIGNED NULL,
    `spread` DECIMAL(8,4) NULL,
    `market_regime` VARCHAR(32) NOT NULL DEFAULT 'UNCERTAIN',
    `data_freshness` ENUM('LIVE', 'DELAYED', 'STALE', 'OFF-MARKET') NOT NULL DEFAULT 'LIVE',
    `source_timestamp` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_ms_symbol_created` (`asset_symbol`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. Raw Market Responses (Auditable Provider Payloads)
DROP TABLE IF EXISTS `raw_market_responses`;
CREATE TABLE `raw_market_responses` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `endpoint` VARCHAR(128) NOT NULL,
    `request_hash` VARCHAR(64) NOT NULL,
    `payload_json` MEDIUMTEXT NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_rmr_hash` (`request_hash`),
    INDEX `idx_rmr_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. Historical Candles (Normalized OHLCV)
DROP TABLE IF EXISTS `historical_candles`;
CREATE TABLE `historical_candles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `symbol` VARCHAR(64) NOT NULL,
    `timeframe` VARCHAR(8) NOT NULL, -- 1m, 3m, 5m, 15m, 60m, D
    `candle_timestamp` DATETIME NOT NULL,
    `open` DECIMAL(12,2) NOT NULL,
    `high` DECIMAL(12,2) NOT NULL,
    `low` DECIMAL(12,2) NOT NULL,
    `close` DECIMAL(12,2) NOT NULL,
    `volume` BIGINT UNSIGNED NOT NULL,
    `oi` BIGINT UNSIGNED NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_candle` (`symbol`, `timeframe`, `candle_timestamp`),
    INDEX `idx_hc_symbol_tf_ts` (`symbol`, `timeframe`, `candle_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 17. Realtime Ticks (Pruned retention buffer)
DROP TABLE IF EXISTS `realtime_ticks`;
CREATE TABLE `realtime_ticks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `symbol` VARCHAR(64) NOT NULL,
    `ltp` DECIMAL(12,2) NOT NULL,
    `last_qty` INT UNSIGNED NOT NULL,
    `volume` BIGINT UNSIGNED NOT NULL,
    `tick_timestamp` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_rt_symbol_ts` (`symbol`, `tick_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 18. Market Depth Snapshots
DROP TABLE IF EXISTS `market_depth_snapshots`;
CREATE TABLE `market_depth_snapshots` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `symbol` VARCHAR(64) NOT NULL,
    `total_buy_qty` BIGINT UNSIGNED NOT NULL,
    `total_sell_qty` BIGINT UNSIGNED NOT NULL,
    `depth_json` TEXT NOT NULL,
    `snapshot_timestamp` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_mds_symbol_created` (`symbol`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 19. Technical Indicators (Computed and Cached)
DROP TABLE IF EXISTS `technical_indicators`;
CREATE TABLE `technical_indicators` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `symbol` VARCHAR(64) NOT NULL,
    `timeframe` VARCHAR(8) NOT NULL,
    `indicator_timestamp` DATETIME NOT NULL,
    `ema_9` DECIMAL(12,2) NULL,
    `ema_20` DECIMAL(12,2) NULL,
    `ema_50` DECIMAL(12,2) NULL,
    `ema_200` DECIMAL(12,2) NULL,
    `vwap` DECIMAL(12,2) NULL,
    `rsi_14` DECIMAL(6,2) NULL,
    `adx_14` DECIMAL(6,2) NULL,
    `atr_14` DECIMAL(8,2) NULL,
    `supertrend` DECIMAL(12,2) NULL,
    `supertrend_direction` TINYINT(1) NULL, -- 1=bullish, -1=bearish
    `bb_upper` DECIMAL(12,2) NULL,
    `bb_middle` DECIMAL(12,2) NULL,
    `bb_lower` DECIMAL(12,2) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_indicators` (`symbol`, `timeframe`, `indicator_timestamp`),
    INDEX `idx_ti_symbol_timeframe` (`symbol`, `timeframe`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20. Option Chain Snapshots
DROP TABLE IF EXISTS `option_chain_snapshots`;
CREATE TABLE `option_chain_snapshots` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `underlying_symbol` VARCHAR(64) NOT NULL,
    `underlying_price` DECIMAL(12,2) NOT NULL,
    `expiry_date` DATE NOT NULL,
    `pcr` DECIMAL(6,3) NULL,
    `max_pain` DECIMAL(12,2) NULL,
    `atm_strike` DECIMAL(12,2) NOT NULL,
    `india_vix` DECIMAL(6,2) NULL,
    `snapshot_timestamp` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_ocs_underlying_expiry` (`underlying_symbol`, `expiry_date`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 21. Option Contracts
DROP TABLE IF EXISTS `option_contracts`;
CREATE TABLE `option_contracts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `snapshot_id` BIGINT UNSIGNED NOT NULL,
    `contract_symbol` VARCHAR(64) NOT NULL,
    `strike_price` DECIMAL(12,2) NOT NULL,
    `option_type` ENUM('CE', 'PE') NOT NULL,
    `ltp` DECIMAL(12,2) NOT NULL,
    `bid` DECIMAL(12,2) NOT NULL,
    `ask` DECIMAL(12,2) NOT NULL,
    `spread_pct` DECIMAL(6,2) NOT NULL,
    `volume` BIGINT UNSIGNED NOT NULL,
    `oi` BIGINT UNSIGNED NOT NULL,
    `oi_change` BIGINT NOT NULL DEFAULT 0,
    `iv` DECIMAL(6,2) NULL,
    `delta` DECIMAL(6,4) NULL,
    `gamma` DECIMAL(8,6) NULL,
    `theta` DECIMAL(8,4) NULL,
    `vega` DECIMAL(8,4) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_oc_snapshot` (`snapshot_id`),
    INDEX `idx_oc_contract` (`contract_symbol`),
    CONSTRAINT `fk_oc_snapshot` FOREIGN KEY (`snapshot_id`) REFERENCES `option_chain_snapshots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 22. Option Metrics
DROP TABLE IF EXISTS `option_metrics`;
CREATE TABLE `option_metrics` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `underlying_symbol` VARCHAR(64) NOT NULL,
    `metric_key` VARCHAR(64) NOT NULL,
    `metric_value` DECIMAL(14,4) NOT NULL,
    `calculation_time` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_om_symbol_time` (`underlying_symbol`, `calculation_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 23. Global Market Snapshots
DROP TABLE IF EXISTS `global_market_snapshots`;
CREATE TABLE `global_market_snapshots` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `instrument_name` VARCHAR(64) NOT NULL, -- GIFT Nifty, Dow Jones, Crude Oil, etc.
    `price` DECIMAL(12,2) NOT NULL,
    `change_pct` DECIMAL(6,2) NOT NULL,
    `source` VARCHAR(64) NOT NULL,
    `data_status` ENUM('LIVE', 'DELAYED', 'PREV_CLOSE') NOT NULL DEFAULT 'DELAYED',
    `observed_at` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_gms_instrument` (`instrument_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 24. News Articles
DROP TABLE IF EXISTS `news_articles`;
CREATE TABLE `news_articles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `headline` VARCHAR(255) NOT NULL,
    `publisher` VARCHAR(128) NOT NULL,
    `article_url` VARCHAR(512) NULL,
    `published_at` DATETIME NOT NULL,
    `sentiment_score` DECIMAL(4,2) NOT NULL DEFAULT 0.00, -- -1.00 (bearish) to +1.00 (bullish)
    `reliability_assessment` ENUM('official', 'high', 'moderate', 'low') NOT NULL DEFAULT 'high',
    `category` VARCHAR(64) NOT NULL DEFAULT 'market',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_na_published` (`published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 25. News Asset Mapping
DROP TABLE IF EXISTS `news_asset_mapping`;
CREATE TABLE `news_asset_mapping` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `news_id` BIGINT UNSIGNED NOT NULL,
    `asset_symbol` VARCHAR(64) NOT NULL,
    CONSTRAINT `fk_nam_news` FOREIGN KEY (`news_id`) REFERENCES `news_articles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 26. Market Regimes
DROP TABLE IF EXISTS `market_regimes`;
CREATE TABLE `market_regimes` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `symbol` VARCHAR(64) NOT NULL,
    `regime_name` VARCHAR(32) NOT NULL, -- STRONG_BULLISH, SIDEWAYS, CHOPPY, etc.
    `confidence_pct` DECIMAL(5,2) NOT NULL,
    `reasons_json` TEXT NOT NULL,
    `detected_at` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_mr_symbol_detected` (`symbol`, `detected_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 27. Support & Resistance Zones
DROP TABLE IF EXISTS `support_resistance_zones`;
CREATE TABLE `support_resistance_zones` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `symbol` VARCHAR(64) NOT NULL,
    `zone_type` ENUM('SUPPORT', 'RESISTANCE', 'PIVOT') NOT NULL,
    `price_low` DECIMAL(12,2) NOT NULL,
    `price_high` DECIMAL(12,2) NOT NULL,
    `touch_count` INT UNSIGNED NOT NULL DEFAULT 1,
    `strength_score` DECIMAL(4,2) NOT NULL DEFAULT 5.0, -- 1.0 to 10.0
    `calculation_method` VARCHAR(64) NOT NULL, -- PIVOT, SWING, VOLUME_CLUSTER
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_srz_symbol_active` (`symbol`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 28. Strategy Definitions
DROP TABLE IF EXISTS `strategy_definitions`;
CREATE TABLE `strategy_definitions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `strategy_code` VARCHAR(32) NOT NULL UNIQUE,
    `name` VARCHAR(128) NOT NULL,
    `description` TEXT NOT NULL,
    `timeframe` VARCHAR(8) NOT NULL DEFAULT '5m',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 29. Strategy Versions
DROP TABLE IF EXISTS `strategy_versions`;
CREATE TABLE `strategy_versions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `strategy_id` INT UNSIGNED NOT NULL,
    `version_number` VARCHAR(16) NOT NULL,
    `parameters_json` TEXT NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_sv_strategy` FOREIGN KEY (`strategy_id`) REFERENCES `strategy_definitions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 30. Strategy Signals
DROP TABLE IF EXISTS `strategy_signals`;
CREATE TABLE `strategy_signals` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `strategy_id` INT UNSIGNED NOT NULL,
    `symbol` VARCHAR(64) NOT NULL,
    `signal_type` ENUM('BUY_CE', 'BUY_PE', 'WAIT', 'NO_TRADE') NOT NULL,
    `trigger_price` DECIMAL(12,2) NOT NULL,
    `stop_loss` DECIMAL(12,2) NOT NULL,
    `target_1` DECIMAL(12,2) NOT NULL,
    `target_2` DECIMAL(12,2) NOT NULL,
    `risk_reward_ratio` DECIMAL(5,2) NOT NULL,
    `confluence_score` DECIMAL(4,2) NOT NULL,
    `signal_timestamp` DATETIME NOT NULL,
    `status` ENUM('FORMING', 'TRIGGERED', 'INVALIDATED', 'EXPIRED') NOT NULL DEFAULT 'FORMING',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_ss_symbol_status` (`symbol`, `status`),
    CONSTRAINT `fk_ss_strategy` FOREIGN KEY (`strategy_id`) REFERENCES `strategy_definitions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 31. Strategy Backtests
DROP TABLE IF EXISTS `strategy_backtests`;
CREATE TABLE `strategy_backtests` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `strategy_id` INT UNSIGNED NOT NULL,
    `symbol` VARCHAR(64) NOT NULL,
    `timeframe` VARCHAR(8) NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `total_trades` INT UNSIGNED NOT NULL DEFAULT 0,
    `winning_trades` INT UNSIGNED NOT NULL DEFAULT 0,
    `losing_trades` INT UNSIGNED NOT NULL DEFAULT 0,
    `win_rate_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `gross_pnl` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `net_pnl` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `profit_factor` DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    `max_drawdown_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `config_json` TEXT NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_sb_strategy` FOREIGN KEY (`strategy_id`) REFERENCES `strategy_definitions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 32. Backtest Trades
DROP TABLE IF EXISTS `backtest_trades`;
CREATE TABLE `backtest_trades` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `backtest_id` BIGINT UNSIGNED NOT NULL,
    `entry_time` DATETIME NOT NULL,
    `exit_time` DATETIME NOT NULL,
    `direction` ENUM('BUY_CE', 'BUY_PE') NOT NULL,
    `entry_price` DECIMAL(12,2) NOT NULL,
    `exit_price` DECIMAL(12,2) NOT NULL,
    `pnl_points` DECIMAL(12,2) NOT NULL,
    `slippage_points` DECIMAL(8,2) NOT NULL DEFAULT 0.50,
    `exit_reason` ENUM('TARGET_1', 'TARGET_2', 'STOP_LOSS', 'TIME_EXIT') NOT NULL,
    CONSTRAINT `fk_bt_backtest` FOREIGN KEY (`backtest_id`) REFERENCES `strategy_backtests` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 33. Analysis Jobs
DROP TABLE IF EXISTS `analysis_jobs`;
CREATE TABLE `analysis_jobs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `job_type` VARCHAR(32) NOT NULL,
    `symbol` VARCHAR(64) NOT NULL,
    `status` ENUM('queued', 'running', 'completed', 'failed') NOT NULL DEFAULT 'queued',
    `scheduled_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `completed_at` DATETIME NULL,
    `error_message` TEXT NULL,
    INDEX `idx_aj_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 34. Job Logs
DROP TABLE IF EXISTS `job_logs`;
CREATE TABLE `job_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `job_id` BIGINT UNSIGNED NOT NULL,
    `log_level` VARCHAR(16) NOT NULL DEFAULT 'info',
    `message` TEXT NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_jl_job` FOREIGN KEY (`job_id`) REFERENCES `analysis_jobs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 35. AI Reports (Google Gemini Research Outputs)
DROP TABLE IF EXISTS `ai_reports`;
CREATE TABLE `ai_reports` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `symbol` VARCHAR(64) NOT NULL,
    `decision` ENUM('BUY_CE', 'BUY_PE', 'WAIT', 'NO_TRADE') NOT NULL,
    `market_bias` ENUM('BULLISH', 'BEARISH', 'NEUTRAL', 'UNCERTAIN') NOT NULL,
    `regime` VARCHAR(32) NOT NULL,
    `executive_summary` TEXT NOT NULL,
    `hinglish_explanation` TEXT NULL,
    `supporting_reasons_json` TEXT NOT NULL,
    `contradicting_reasons_json` TEXT NOT NULL,
    `why_wait_or_avoid` TEXT NOT NULL,
    `what_must_happen_next` TEXT NOT NULL,
    `invalidation_condition` TEXT NOT NULL,
    `option_candidate_symbol` VARCHAR(64) NULL,
    `entry_trigger` VARCHAR(128) NOT NULL,
    `stop_loss` VARCHAR(64) NOT NULL,
    `target_1` VARCHAR(64) NOT NULL,
    `target_2` VARCHAR(64) NOT NULL,
    `risk_reward` VARCHAR(32) NOT NULL,
    `data_freshness_status` VARCHAR(32) NOT NULL DEFAULT 'VERIFIED_LIVE',
    `python_validation_status` ENUM('APPROVED', 'REJECTED') NOT NULL DEFAULT 'APPROVED',
    `raw_ai_response` MEDIUMTEXT NOT NULL,
    `model_name` VARCHAR(64) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_ar_symbol_decision` (`symbol`, `decision`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 36. AI Report Versions
DROP TABLE IF EXISTS `ai_report_versions`;
CREATE TABLE `ai_report_versions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `report_id` BIGINT UNSIGNED NOT NULL,
    `prompt_version` VARCHAR(16) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_arv_report` FOREIGN KEY (`report_id`) REFERENCES `ai_reports` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 37. AI Model Usage
DROP TABLE IF EXISTS `ai_model_usage`;
CREATE TABLE `ai_model_usage` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `model_name` VARCHAR(64) NOT NULL,
    `tokens_prompt` INT UNSIGNED NOT NULL DEFAULT 0,
    `tokens_completion` INT UNSIGNED NOT NULL DEFAULT 0,
    `cost_estimate_usd` DECIMAL(8,6) NOT NULL DEFAULT 0.000000,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 38. Trade Candidates
DROP TABLE IF EXISTS `trade_candidates`;
CREATE TABLE `trade_candidates` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `symbol` VARCHAR(64) NOT NULL,
    `setup_name` VARCHAR(64) NOT NULL,
    `action` ENUM('BUY_CE', 'BUY_PE', 'WAIT', 'NO_TRADE') NOT NULL,
    `option_contract` VARCHAR(64) NULL,
    `trigger_level` DECIMAL(12,2) NOT NULL,
    `stop_level` DECIMAL(12,2) NOT NULL,
    `target_1` DECIMAL(12,2) NOT NULL,
    `target_2` DECIMAL(12,2) NOT NULL,
    `status` ENUM('FORMING', 'TRIGGERED', 'INVALIDATED', 'EXPIRED') NOT NULL DEFAULT 'FORMING',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_tc_symbol_status` (`symbol`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 39. Candidate Evidence
DROP TABLE IF EXISTS `candidate_evidence`;
CREATE TABLE `candidate_evidence` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `candidate_id` BIGINT UNSIGNED NOT NULL,
    `evidence_category` VARCHAR(32) NOT NULL, -- TREND, VWAP, VOLUME, OI, GREEKS, REGIME
    `is_supporting` TINYINT(1) NOT NULL, -- 1=supporting, 0=contradicting
    `description` VARCHAR(255) NOT NULL,
    `observed_value` VARCHAR(64) NOT NULL,
    CONSTRAINT `fk_ce_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `trade_candidates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 40. Candidate Validation (Python Gate Results)
DROP TABLE IF EXISTS `candidate_validation`;
CREATE TABLE `candidate_validation` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `candidate_id` BIGINT UNSIGNED NOT NULL,
    `is_passed` TINYINT(1) NOT NULL,
    `validation_rule` VARCHAR(64) NOT NULL,
    `details` VARCHAR(255) NOT NULL,
    `checked_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_cv_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `trade_candidates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 41. Hypothetical Setups (Paper Trades)
DROP TABLE IF EXISTS `hypothetical_setups`;
CREATE TABLE `hypothetical_setups` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `candidate_id` BIGINT UNSIGNED NOT NULL,
    `symbol` VARCHAR(64) NOT NULL,
    `contract_symbol` VARCHAR(64) NULL,
    `direction` ENUM('BUY_CE', 'BUY_PE') NOT NULL,
    `hypothetical_entry_price` DECIMAL(12,2) NOT NULL,
    `stop_loss_price` DECIMAL(12,2) NOT NULL,
    `target_1_price` DECIMAL(12,2) NOT NULL,
    `target_2_price` DECIMAL(12,2) NOT NULL,
    `triggered_at` DATETIME NULL,
    `status` ENUM('PENDING_TRIGGER', 'OPEN', 'TARGET_HIT', 'STOP_HIT', 'INVALIDATED', 'EXPIRED') NOT NULL DEFAULT 'PENDING_TRIGGER',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_hs_symbol_status` (`symbol`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 42. Setup Outcomes (Forward Simulated Results)
DROP TABLE IF EXISTS `setup_outcomes`;
CREATE TABLE `setup_outcomes` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `setup_id` BIGINT UNSIGNED NOT NULL,
    `final_status` ENUM('TARGET_1_HIT', 'TARGET_2_HIT', 'STOPPED_OUT', 'INVALIDATED', 'TIME_EXPIRED') NOT NULL,
    `exit_price` DECIMAL(12,2) NOT NULL,
    `pnl_points` DECIMAL(12,2) NOT NULL,
    `pnl_pct` DECIMAL(6,2) NOT NULL,
    `max_favorable_excursion` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `max_adverse_excursion` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `holding_time_minutes` INT UNSIGNED NOT NULL DEFAULT 0,
    `recorded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_so_setup` FOREIGN KEY (`setup_id`) REFERENCES `hypothetical_setups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 43. Model Versions
DROP TABLE IF EXISTS `model_versions`;
CREATE TABLE `model_versions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `component_name` VARCHAR(64) NOT NULL, -- regime_classifier, confidence_calibrator
    `version_string` VARCHAR(32) NOT NULL,
    `description` VARCHAR(255) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 44. Model Evaluations
DROP TABLE IF EXISTS `model_evaluations`;
CREATE TABLE `model_evaluations` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `model_version_id` INT UNSIGNED NOT NULL,
    `test_sample_count` INT UNSIGNED NOT NULL,
    `accuracy_score` DECIMAL(5,2) NOT NULL,
    `notes` TEXT NULL,
    `evaluated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_me_version` FOREIGN KEY (`model_version_id`) REFERENCES `model_versions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 45. Learning Jobs (Idle-Time Research)
DROP TABLE IF EXISTS `learning_jobs`;
CREATE TABLE `learning_jobs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `job_name` VARCHAR(128) NOT NULL,
    `parameters_tested_json` TEXT NOT NULL,
    `results_summary` TEXT NOT NULL,
    `status` ENUM('running', 'completed', 'failed') NOT NULL DEFAULT 'completed',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 46. Notifications
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `type` VARCHAR(32) NOT NULL, -- SETUP_FORMING, TRIGGERED, INVALIDATED, DATA_STALE, SYSTEM
    `severity` ENUM('info', 'success', 'warning', 'danger') NOT NULL DEFAULT 'info',
    `title` VARCHAR(128) NOT NULL,
    `message` TEXT NOT NULL,
    `link_url` VARCHAR(255) NULL,
    `is_read` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_notif_read_created` (`is_read`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 47. Audit Logs
DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NULL,
    `action` VARCHAR(64) NOT NULL,
    `ip_address` VARCHAR(45) NULL,
    `details` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_al_action_created` (`action`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 48. System Health Logs
DROP TABLE IF EXISTS `system_health_logs`;
CREATE TABLE `system_health_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `component` VARCHAR(64) NOT NULL,
    `status` ENUM('healthy', 'degraded', 'error') NOT NULL,
    `message` VARCHAR(255) NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_shl_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- INITIAL SEEDS: Essential Assets, Strategies, and Default Admin
-- =====================================================================

-- Default Admin User (Password: "Admin@123456" - Bcrypt Hashed)
INSERT INTO `users` (`id`, `username`, `email`, `password_hash`, `role`)
VALUES (1, 'akdevz_admin', 'admin@akdevz.local', '$2y$12$e0q9eG196U6g3E9L6O5cXeN.0lP6hV2eR0Kj8X0h3T0.8Kz2wWp6e', 'admin')
ON DUPLICATE KEY UPDATE `username`=`username`;

-- Default Trading Universe (Core NSE Indices & Selected Liquid Stocks)
INSERT INTO `assets` (`symbol`, `display_name`, `exchange`, `segment`, `lot_size`, `tick_size`, `is_watchlist_default`)
VALUES 
('NSE:NIFTY50-INDEX', 'NIFTY 50', 'NSE', 'INDEX', 25, 0.05, 1),
('NSE:BANKNIFTY-INDEX', 'BANK NIFTY', 'NSE', 'INDEX', 15, 0.05, 1),
('NSE:FINNIFTY-INDEX', 'NIFTY FINANCIAL', 'NSE', 'INDEX', 25, 0.05, 1),
('NSE:MIDCPNIFTY-INDEX', 'NIFTY MIDCAP SELECT', 'NSE', 'INDEX', 50, 0.05, 1),
('NSE:RELIANCE-EQ', 'RELIANCE INDUSTRIES', 'NSE', 'EQUITY', 1, 0.05, 1),
('NSE:HDFCBANK-EQ', 'HDFC BANK', 'NSE', 'EQUITY', 1, 0.05, 1),
('NSE:ICICIBANK-EQ', 'ICICI BANK', 'NSE', 'EQUITY', 1, 0.05, 1),
('NSE:INFY-EQ', 'INFOSYS', 'NSE', 'EQUITY', 1, 0.05, 1),
('NSE:TCS-EQ', 'TATA CONSULTANCY SERVICES', 'NSE', 'EQUITY', 1, 0.05, 1),
('NSE:SBIN-EQ', 'STATE BANK OF INDIA', 'NSE', 'EQUITY', 1, 0.05, 1)
ON DUPLICATE KEY UPDATE `display_name`=VALUES(`display_name`);

-- Default System Watchlist
INSERT INTO `watchlists` (`id`, `user_id`, `name`, `description`, `is_system_default`)
VALUES (1, 1, 'Core Indices & Leaders', 'Primary intraday monitoring universe', 1)
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

INSERT INTO `watchlist_assets` (`watchlist_id`, `asset_id`, `sort_order`)
VALUES (1, 1, 1), (1, 2, 2), (1, 3, 3), (1, 4, 4), (1, 5, 5), (1, 6, 6)
ON DUPLICATE KEY UPDATE `sort_order`=VALUES(`sort_order`);

-- Codified Strategies (12 Core Strategies)
INSERT INTO `strategy_definitions` (`id`, `strategy_code`, `name`, `description`, `timeframe`)
VALUES
(1, 'ORB_15M', 'Opening Range Breakout', '15-minute high/low breakout with volume surge and VWAP confirmation', '5m'),
(2, 'VWAP_PULLBACK', 'VWAP Pullback & Rejection', 'Pullback to rising/falling session VWAP with candle rejection and RSI reset', '5m'),
(3, 'EMA_PULLBACK', 'EMA Stack Pullback (9/20 EMA)', 'Retracement into value zone of 9 and 20 EMAs with trend continuation', '5m'),
(4, 'MOMENTUM_BO', 'Momentum Breakout', 'Bollinger squeeze expansion with rising ADX and volume surge', '5m'),
(5, 'BREAKOUT_RETEST', 'Breakout Retest & Confirmation', 'Breach of key horizontal pivot with successful retest and continuation', '15m'),
(6, 'SUPPORT_REJECT', 'Support Zone Rejection', 'Rebound from verified multi-touch support zone with bullish wick confirmation', '5m'),
(7, 'RESIST_REJECT', 'Resistance Zone Rejection', 'Rejection from verified multi-touch resistance zone with bearish wick confirmation', '5m'),
(8, 'FAILED_BO_REVERSAL', 'Failed Breakout Reversal (Trap)', 'Immediate reversal after liquidity sweep beyond PDH/PDL back inside range', '5m'),
(9, 'MEAN_REVERSION', 'Mean Reversion to 20 EMA', 'Extreme extension beyond 2.5 SD Bollinger bands with RSI divergence', '5m'),
(10, 'RVOL_EXPANSION', 'Relative Volume Surge', 'RVol > 2.5x normal intraday pace accompanied by range expansion', '5m'),
(11, 'OI_CONFIRMED', 'Options OI Confluence Setup', 'Heavy ATM/ITM put writing + call unwinding for CE, or vice versa for PE', '15m'),
(12, 'TREND_CONTINUATION', 'Multi-Timeframe Trend Alignment', 'Unified Daily, 60m, 15m, 5m trend alignment on 15m pullback', '15m')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

SET FOREIGN_KEY_CHECKS = 1;