-- ============================================
-- ALGORITHMIC TRADING SYSTEM - DATABASE SCHEMA
-- ============================================
-- Version: 2.0
-- Date: 2026-01-25
-- ============================================

-- ============================================
-- 1. CANDLE STATE PERSISTENCE (Auto-Created by System)
-- ============================================
-- Note: This table is automatically created by MysqlDriver if MySQL storage is selected
-- No manual creation needed unless you want to pre-create it

CREATE TABLE IF NOT EXISTS `market_persistence` (
    `key_id` VARCHAR(191) PRIMARY KEY COMMENT 'Unique key for stored state (e.g., candle_BTCUSD_15)',
    `value` TEXT COMMENT 'JSON-encoded candle or tick data',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update time'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Stores in-progress candle state for restart safety';

-- Index for cleanup queries
CREATE INDEX idx_updated_at ON `market_persistence`(`updated_at`);

-- ============================================
-- 2. TELEGRAM SUBSCRIBERS
-- ============================================

CREATE TABLE IF NOT EXISTS `telegram_subscribers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `chat_id` VARCHAR(255) UNIQUE NOT NULL COMMENT 'Telegram chat ID',
    `username` VARCHAR(255) DEFAULT NULL COMMENT 'Telegram username',
    `is_active` TINYINT(1) DEFAULT 1 COMMENT '1 = subscribed, 0 = unsubscribed',
    `is_admin` TINYINT(1) DEFAULT 0 COMMENT '1 = admin, 0 = regular user',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Telegram bot subscribers and admin users';

-- Indexes
CREATE INDEX idx_chat_id ON `telegram_subscribers`(`chat_id`);
CREATE INDEX idx_is_active ON `telegram_subscribers`(`is_active`);
CREATE INDEX idx_is_admin ON `telegram_subscribers`(`is_admin`);

-- ============================================
-- 3. TELEGRAM-BROKER MAPPING
-- ============================================

CREATE TABLE IF NOT EXISTS `telegram_subscriber_broker_mapping` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `chat_id` VARCHAR(255) NOT NULL COMMENT 'Telegram chat ID',
    `broker_account_id` INT NOT NULL COMMENT 'Broker account ID from broker_accounts table',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_mapping` (`chat_id`, `broker_account_id`),
    FOREIGN KEY (`broker_account_id`) REFERENCES `broker_accounts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Maps Telegram users to broker accounts for order notifications';

-- Indexes
CREATE INDEX idx_chat_id_mapping ON `telegram_subscriber_broker_mapping`(`chat_id`);
CREATE INDEX idx_broker_account_id ON `telegram_subscriber_broker_mapping`(`broker_account_id`);

-- ============================================
-- NOTES
-- ============================================

-- 1. CANDLE PERSISTENCE:
--    - Table is auto-created by the system if MySQL driver is selected
--    - Stores only in-progress candles (auto-deleted on candle close)
--    - Typical size: ~1 KB per symbol (5 timeframes)
--    - No manual maintenance needed

-- 2. TELEGRAM TABLES:
--    - Run migration: php bin/migrate_telegram_tables.php
--    - Or manually execute the CREATE TABLE statements above

-- 3. BROKER ACCOUNTS:
--    - Assumes broker_accounts table already exists
--    - Foreign key constraint ensures data integrity

-- ============================================
-- VERIFICATION QUERIES
-- ============================================

-- Check candle persistence storage
-- SELECT key_id, LENGTH(value) as bytes, updated_at 
-- FROM market_persistence 
-- ORDER BY updated_at DESC;

-- Check Telegram subscribers
-- SELECT chat_id, username, is_active, is_admin 
-- FROM telegram_subscribers 
-- ORDER BY created_at DESC;

-- Check Telegram-Broker mappings
-- SELECT m.chat_id, m.broker_account_id, s.username, b.broker_code
-- FROM telegram_subscriber_broker_mapping m
-- JOIN telegram_subscribers s ON m.chat_id = s.chat_id
-- JOIN broker_accounts b ON m.broker_account_id = b.id;

-- ============================================
-- CLEANUP QUERIES (Optional)
-- ============================================

-- Clear old candle persistence data (if needed)
-- DELETE FROM market_persistence WHERE updated_at < DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- Remove inactive subscribers (if needed)
-- DELETE FROM telegram_subscribers WHERE is_active = 0 AND updated_at < DATE_SUB(NOW(), INTERVAL 30 DAY);