-- =====================================================================
-- AkDevz AI Market Intelligence — Enterprise Database Migration
-- Multi-Broker Management, News Intelligence, Autonomous Execution,
-- Risk Control Center, Portfolio Tracking, and AI Provider Registry
-- Migration: 2026_11_01_000001_create_enterprise_trading_and_news_tables.sql
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Ensure 'sector' column exists in 'assets' table
ALTER TABLE `assets` ADD COLUMN IF NOT EXISTS `sector` VARCHAR(32) NOT NULL DEFAULT 'OTHER' AFTER `segment`;

-- 2. Multi-Broker Providers Master Registry
CREATE TABLE IF NOT EXISTS `broker_providers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `provider_code` VARCHAR(32) NOT NULL UNIQUE,
    `name` VARCHAR(64) NOT NULL,
    `official_website` VARCHAR(255) NOT NULL,
    `docs_url` VARCHAR(255) NOT NULL,
    `is_free` TINYINT(1) NOT NULL DEFAULT 1,
    `auth_type` ENUM('OAUTH2', 'TOTP_API_KEY', 'API_KEY_SECRET') NOT NULL DEFAULT 'OAUTH2',
    `rate_limit_per_sec` INT UNSIGNED NOT NULL DEFAULT 5,
    `rate_limit_per_min` INT UNSIGNED NOT NULL DEFAULT 50,
    `rate_limit_per_day` INT UNSIGNED NOT NULL DEFAULT 5000,
    `supports_quotes` TINYINT(1) NOT NULL DEFAULT 1,
    `supports_history` TINYINT(1) NOT NULL DEFAULT 1,
    `supports_depth` TINYINT(1) NOT NULL DEFAULT 1,
    `supports_option_chain` TINYINT(1) NOT NULL DEFAULT 1,
    `supports_orders` TINYINT(1) NOT NULL DEFAULT 1,
    `supports_websocket` TINYINT(1) NOT NULL DEFAULT 1,
    `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `is_execution_broker` 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_bp_enabled` (`is_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Broker Credentials Storage (Encrypted at rest)
CREATE TABLE IF NOT EXISTS `broker_credentials` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `provider_code` VARCHAR(32) NOT NULL UNIQUE,
    `client_id` VARCHAR(128) NULL,
    `encrypted_api_key` TEXT NULL,
    `encrypted_secret_key` TEXT NULL,
    `encrypted_pin_totp` TEXT NULL,
    `redirect_uri` VARCHAR(255) NULL,
    `encrypted_access_token` TEXT NULL,
    `encrypted_refresh_token` TEXT NULL,
    `encrypted_feed_token` TEXT NULL,
    `token_expires_at` DATETIME NULL,
    `auth_status` ENUM('UNCONFIGURED', 'CONFIGURED', 'AUTHENTICATED', 'EXPIRED', 'FAILED') NOT NULL DEFAULT 'UNCONFIGURED',
    `last_tested_at` DATETIME NULL,
    `last_error` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_bc_provider` FOREIGN KEY (`provider_code`) REFERENCES `broker_providers` (`provider_code`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Centralized News Sources Registry
CREATE TABLE IF NOT EXISTS `news_sources` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `source_code` VARCHAR(48) NOT NULL UNIQUE,
    `name` VARCHAR(128) NOT NULL,
    `official_website` VARCHAR(255) NOT NULL,
    `docs_url` VARCHAR(255) NULL,
    `category` ENUM('OFFICIAL_REGULATORY', 'FINANCIAL_MEDIA', 'GLOBAL_MACRO') NOT NULL DEFAULT 'FINANCIAL_MEDIA',
    `feed_type` ENUM('RSS', 'ATOM', 'REST_API') NOT NULL DEFAULT 'RSS',
    `endpoint_url` VARCHAR(512) NOT NULL,
    `encrypted_api_key` TEXT NULL,
    `is_free` TINYINT(1) NOT NULL DEFAULT 1,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `fetch_interval_min` INT UNSIGNED NOT NULL DEFAULT 15,
    `last_sync_at` DATETIME NULL,
    `last_status` VARCHAR(32) NOT NULL DEFAULT 'PENDING',
    `last_error` TEXT NULL,
    `articles_collected_count` INT UNSIGNED 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_ns_active` (`is_active`),
    INDEX `idx_ns_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Ingested News Articles Repository
CREATE TABLE IF NOT EXISTS `news_articles_raw` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `source_id` INT UNSIGNED NULL,
    `guid_hash` VARCHAR(64) NOT NULL UNIQUE,
    `title` VARCHAR(512) NOT NULL,
    `url` VARCHAR(1024) NOT NULL,
    `publisher` VARCHAR(128) NOT NULL,
    `category` VARCHAR(64) NOT NULL DEFAULT 'GENERAL',
    `published_at` DATETIME NOT NULL,
    `summary` TEXT NULL,
    `tickers_detected_json` JSON NULL,
    `sectors_detected_json` JSON NULL,
    `sentiment_score` DECIMAL(4,2) NOT NULL DEFAULT 0.00,
    `sentiment_label` ENUM('BULLISH', 'BEARISH', 'NEUTRAL') NOT NULL DEFAULT 'NEUTRAL',
    `market_impact` ENUM('HIGH', 'MEDIUM', 'LOW') NOT NULL DEFAULT 'LOW',
    `is_official` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_nar_published` (`published_at`),
    INDEX `idx_nar_sentiment` (`sentiment_label`),
    CONSTRAINT `fk_nar_source` FOREIGN KEY (`source_id`) REFERENCES `news_sources` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Live and Paper Trading Execution Orders
CREATE TABLE IF NOT EXISTS `trading_orders` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `idempotency_key` VARCHAR(64) NOT NULL UNIQUE,
    `broker_order_id` VARCHAR(128) NULL,
    `provider_code` VARCHAR(32) NOT NULL DEFAULT 'PAPER',
    `symbol` VARCHAR(64) NOT NULL,
    `exchange` VARCHAR(16) NOT NULL DEFAULT 'NSE',
    `segment` VARCHAR(16) NOT NULL DEFAULT 'EQUITY',
    `direction` ENUM('BUY', 'SELL') NOT NULL,
    `order_type` ENUM('MARKET', 'LIMIT', 'SL', 'SL-M') NOT NULL DEFAULT 'MARKET',
    `product_type` ENUM('INTRADAY', 'MARGIN', 'CNC') NOT NULL DEFAULT 'INTRADAY',
    `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `trigger_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `quantity` INT UNSIGNED NOT NULL,
    `filled_quantity` INT UNSIGNED NOT NULL DEFAULT 0,
    `average_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('PENDING', 'SUBMITTED', 'OPEN', 'COMPLETED', 'CANCELLED', 'REJECTED') NOT NULL DEFAULT 'PENDING',
    `status_reason` TEXT NULL,
    `is_paper` TINYINT(1) NOT NULL DEFAULT 1,
    `strategy_id` INT UNSIGNED NULL,
    `signal_id` BIGINT UNSIGNED NULL,
    `stop_loss` DECIMAL(12,2) NULL,
    `target_price` DECIMAL(12,2) NULL,
    `placed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_to_symbol` (`symbol`),
    INDEX `idx_to_status` (`status`),
    INDEX `idx_to_paper` (`is_paper`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Active and Closed Trading Positions
CREATE TABLE IF NOT EXISTS `trading_positions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `provider_code` VARCHAR(32) NOT NULL DEFAULT 'PAPER',
    `symbol` VARCHAR(64) NOT NULL,
    `product_type` VARCHAR(16) NOT NULL DEFAULT 'INTRADAY',
    `quantity` INT NOT NULL,
    `buy_qty` INT UNSIGNED NOT NULL DEFAULT 0,
    `sell_qty` INT UNSIGNED NOT NULL DEFAULT 0,
    `buy_avg_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `sell_avg_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `current_ltp` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `unrealized_pnl` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `realized_pnl` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `stop_loss` DECIMAL(12,2) NULL,
    `target_1` DECIMAL(12,2) NULL,
    `target_2` DECIMAL(12,2) NULL,
    `trailing_stop_loss` DECIMAL(12,2) NULL,
    `status` ENUM('OPEN', 'CLOSED', 'PARTIALLY_CLOSED') NOT NULL DEFAULT 'OPEN',
    `is_paper` TINYINT(1) NOT NULL DEFAULT 1,
    `opened_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `closed_at` DATETIME NULL,
    `last_synced_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_tp_status` (`status`),
    INDEX `idx_tp_paper` (`is_paper`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Portfolio Telemetry & Snapshot Register
CREATE TABLE IF NOT EXISTS `portfolio_snapshots` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `provider_code` VARCHAR(32) NOT NULL DEFAULT 'PAPER',
    `snapshot_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `available_funds` DECIMAL(16,2) NOT NULL DEFAULT 1000000.00,
    `used_margin` DECIMAL(16,2) NOT NULL DEFAULT 0.00,
    `collateral_value` DECIMAL(16,2) NOT NULL DEFAULT 0.00,
    `total_realized_pnl` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `total_unrealized_pnl` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `daily_pnl` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `weekly_pnl` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `monthly_pnl` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `brokerage_charges` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `taxes_stt` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `is_paper` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_ps_time` (`snapshot_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Central Risk Control Configuration
CREATE TABLE IF NOT EXISTS `risk_configuration` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `live_execution_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `paper_trading_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `selected_execution_broker` VARCHAR(32) NOT NULL DEFAULT 'FYERS',
    `max_daily_loss` DECIMAL(12,2) NOT NULL DEFAULT 15000.00,
    `max_loss_per_trade` DECIMAL(12,2) NOT NULL DEFAULT 3000.00,
    `max_open_positions` INT UNSIGNED NOT NULL DEFAULT 3,
    `max_order_value` DECIMAL(14,2) NOT NULL DEFAULT 100000.00,
    `max_daily_trades` INT UNSIGNED NOT NULL DEFAULT 10,
    `kill_switch_active` TINYINT(1) NOT NULL DEFAULT 0,
    `trailing_sl_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `trailing_sl_points` DECIMAL(8,2) NOT NULL DEFAULT 15.00,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. AI Provider Configuration Registry
CREATE TABLE IF NOT EXISTS `ai_provider_configs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `provider_code` VARCHAR(32) NOT NULL UNIQUE,
    `name` VARCHAR(64) NOT NULL,
    `model_name` VARCHAR(64) NOT NULL,
    `encrypted_api_key` TEXT NULL,
    `is_free` TINYINT(1) NOT NULL DEFAULT 1,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `max_requests_per_min` INT UNSIGNED NOT NULL DEFAULT 30,
    `daily_token_limit` BIGINT UNSIGNED NOT NULL DEFAULT 500000,
    `used_tokens_today` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `last_tested_at` DATETIME NULL,
    `last_status` VARCHAR(32) NOT NULL DEFAULT 'READY',
    `last_error` TEXT 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;

-- SEED DATA: Broker Providers
INSERT INTO `broker_providers` (`provider_code`, `name`, `official_website`, `docs_url`, `is_free`, `auth_type`, `rate_limit_per_sec`, `rate_limit_per_min`, `rate_limit_per_day`, `supports_quotes`, `supports_history`, `supports_depth`, `supports_option_chain`, `supports_orders`, `supports_websocket`, `is_enabled`, `is_execution_broker`) VALUES
('FYERS', 'FYERS API V3', 'https://fyers.in/products/api', 'https://fyers.in/api-docs/', 1, 'OAUTH2', 5, 50, 5000, 1, 1, 1, 1, 1, 1, 1, 1),
('ANGEL_ONE', 'Angel One SmartAPI', 'https://smartapi.angelbroking.com', 'https://smartapi.angelbroking.com/docs', 1, 'TOTP_API_KEY', 10, 60, 10000, 1, 1, 1, 1, 1, 1, 1, 0),
('UPSTOX', 'Upstox Developer API', 'https://upstox.com/developer/api-documentation', 'https://upstox.com/developer/api-documentation/', 1, 'OAUTH2', 10, 60, 10000, 1, 1, 1, 1, 1, 1, 1, 0),
('SHOONYA', 'Shoonya Finvasia API', 'https://shoonya.com', 'https://prism.shoonya.com/api', 1, 'TOTP_API_KEY', 10, 60, 10000, 1, 1, 1, 1, 1, 1, 1, 0)
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

-- SEED DATA: Default Broker Credentials (FYERS populated from existing env)
INSERT INTO `broker_credentials` (`provider_code`, `client_id`, `auth_status`) VALUES
('FYERS', 'SPXXXXE7-100', 'UNCONFIGURED'),
('ANGEL_ONE', '', 'UNCONFIGURED'),
('UPSTOX', '', 'UNCONFIGURED'),
('SHOONYA', '', 'UNCONFIGURED')
ON DUPLICATE KEY UPDATE `provider_code`=VALUES(`provider_code`);

-- SEED DATA: Official Indian Regulatory & Financial News Sources
INSERT INTO `news_sources` (`source_code`, `name`, `official_website`, `category`, `feed_type`, `endpoint_url`, `is_free`, `is_active`, `fetch_interval_min`) VALUES
('NSE_INDIA', 'NSE India Press Releases', 'https://www.nseindia.com', 'OFFICIAL_REGULATORY', 'RSS', 'https://nsearchives.nseindia.com/content/press_releases.xml', 1, 1, 15),
('BSE_INDIA', 'BSE India Corporate Announcements', 'https://www.bseindia.com', 'OFFICIAL_REGULATORY', 'RSS', 'https://www.bseindia.com/corporates/ann.html', 1, 1, 15),
('SEBI_PRESS', 'SEBI Official Press Releases', 'https://www.sebi.gov.in', 'OFFICIAL_REGULATORY', 'RSS', 'https://www.sebi.gov.in/sebirss.xml', 1, 1, 30),
('RBI_BULLETIN', 'Reserve Bank of India Press Releases', 'https://www.rbi.org.in', 'OFFICIAL_REGULATORY', 'RSS', 'https://rbi.org.in/scripts/BS_PressReleaseDisplay.aspx', 1, 1, 30),
('PIB_FINANCE', 'PIB Ministry of Finance', 'https://pib.gov.in', 'OFFICIAL_REGULATORY', 'RSS', 'https://pib.gov.in/RssMain.aspx?ModId=2&Lang=1', 1, 1, 30),
('MONEYCONTROL', 'Moneycontrol Top Markets Feed', 'https://www.moneycontrol.com', 'FINANCIAL_MEDIA', 'RSS', 'https://www.moneycontrol.com/rss/marketreports.xml', 1, 1, 15),
('ET_MARKETS', 'Economic Times Markets', 'https://economictimes.indiatimes.com', 'FINANCIAL_MEDIA', 'RSS', 'https://economictimes.indiatimes.com/markets/rssfeeds/1977021501.cms', 1, 1, 15),
('BUSINESS_STANDARD', 'Business Standard Markets', 'https://www.business-standard.com', 'FINANCIAL_MEDIA', 'RSS', 'https://www.business-standard.com/rss/markets-106.rss', 1, 1, 15),
('MINT_MARKETS', 'Mint Markets Feed', 'https://www.livemint.com', 'FINANCIAL_MEDIA', 'RSS', 'https://www.livemint.com/rss/markets', 1, 1, 15),
('FED_RESERVE', 'US Federal Reserve Monetary Policy', 'https://www.federalreserve.gov', 'GLOBAL_MACRO', 'RSS', 'https://www.federalreserve.gov/feeds/press_monetary.xml', 1, 1, 60)
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

-- SEED DATA: Default Risk Controls
INSERT INTO `risk_configuration` (`id`, `live_execution_enabled`, `paper_trading_enabled`, `selected_execution_broker`, `max_daily_loss`, `max_loss_per_trade`, `max_open_positions`, `max_order_value`, `max_daily_trades`, `kill_switch_active`, `trailing_sl_enabled`, `trailing_sl_points`) VALUES
(1, 0, 1, 'FYERS', 15000.00, 3000.00, 3, 100000.00, 10, 0, 1, 15.00)
ON DUPLICATE KEY UPDATE `selected_execution_broker`=VALUES(`selected_execution_broker`);

-- SEED DATA: AI Providers
INSERT INTO `ai_provider_configs` (`provider_code`, `name`, `model_name`, `is_free`, `is_active`) VALUES
('GROQ', 'Groq Fast LPU Inference', 'openai/gpt-oss-120b', 1, 1),
('GEMINI', 'Google Gemini AI Studio', 'models/gemini-3.8-flash', 1, 1),
('OLLAMA', 'Local Ollama LLM (Zero Cost)', 'llama3.2:latest', 1, 0)
ON DUPLICATE KEY UPDATE `model_name`=VALUES(`model_name`);

SET FOREIGN_KEY_CHECKS = 1;