-- =====================================================================
-- AkDevz AI Market Intelligence — Database Migration: Market Behaviour Intelligence
-- Migration: 2026_10_31_000001_create_market_behaviour_tables.sql
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Market Behaviour Snapshots
CREATE TABLE IF NOT EXISTS `market_behaviour_snapshots` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `snapshot_timestamp` DATETIME NOT NULL,
    `total_configured` INT UNSIGNED NOT NULL DEFAULT 0,
    `valid_scanned` INT UNSIGNED NOT NULL DEFAULT 0,
    `coverage_pct` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `advances` INT UNSIGNED NOT NULL DEFAULT 0,
    `declines` INT UNSIGNED NOT NULL DEFAULT 0,
    `unchanged` INT UNSIGNED NOT NULL DEFAULT 0,
    `bullish_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `bearish_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `neutral_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `long_buildup_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `short_buildup_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `short_covering_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `long_unwinding_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `market_regime` VARCHAR(64) NOT NULL DEFAULT 'ROTATIONAL_CHOP',
    `flow_data_json` LONGTEXT NULL,
    `ai_summary_json` TEXT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_mbs_timestamp` (`snapshot_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Market Behaviour Scans (Individual Instrument Records)
CREATE TABLE IF NOT EXISTS `market_behaviour_scans` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `snapshot_id` INT UNSIGNED NULL,
    `symbol` VARCHAR(64) NOT NULL,
    `display_name` VARCHAR(128) NOT NULL,
    `segment` VARCHAR(32) NOT NULL DEFAULT 'EQUITY',
    `sector` VARCHAR(32) NOT NULL DEFAULT 'OTHER',
    `is_fo` TINYINT(1) NOT NULL DEFAULT 0,
    `ltp` DECIMAL(12,2) NOT NULL,
    `change_abs` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `change_pct` DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    `volume` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `rvol` DECIMAL(6,2) NOT NULL DEFAULT 1.00,
    `vol_acceleration` DECIMAL(6,2) NOT NULL DEFAULT 1.00,
    `oi` BIGINT UNSIGNED NULL,
    `oi_change_pct` DECIMAL(6,2) NULL,
    `bias` ENUM('BULLISH', 'BEARISH', 'NEUTRAL') NOT NULL DEFAULT 'NEUTRAL',
    `classification` VARCHAR(64) NOT NULL DEFAULT 'CONSOLIDATION',
    `classification_label` VARCHAR(64) NOT NULL DEFAULT 'Consolidation',
    `signal_score` DECIMAL(5,2) NOT NULL DEFAULT 50.00,
    `vwap` DECIMAL(12,2) NOT NULL,
    `vwap_deviation_pct` DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    `rsi` DECIMAL(5,2) NOT NULL DEFAULT 50.00,
    `adx` DECIMAL(5,2) NOT NULL DEFAULT 20.00,
    `atr` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `supertrend` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `supertrend_direction` ENUM('BULLISH', 'BEARISH') NOT NULL DEFAULT 'BULLISH',
    `setup_state` VARCHAR(64) NOT NULL DEFAULT 'WATCHLIST',
    `setup_type` VARCHAR(64) NOT NULL DEFAULT 'NONE',
    `entry_trigger` DECIMAL(12,2) NULL,
    `stop_loss` DECIMAL(12,2) NULL,
    `target_1` DECIMAL(12,2) NULL,
    `target_2` DECIMAL(12,2) NULL,
    `risk_reward` VARCHAR(16) NULL,
    `is_unusual` TINYINT(1) NOT NULL DEFAULT 0,
    `data_freshness` VARCHAR(16) NOT NULL DEFAULT 'LIVE',
    `scanned_timestamp` INT UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_mbscans_symbol` (`symbol`),
    INDEX `idx_mbscans_classification` (`classification`),
    INDEX `idx_mbscans_bias` (`bias`),
    INDEX `idx_mbscans_created` (`created_at`),
    CONSTRAINT `fk_mbscans_snapshot` FOREIGN KEY (`snapshot_id`) REFERENCES `market_behaviour_snapshots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;