-- Enhanced Schema with Provider Exchange Mappings
-- Connect to trading_bot database
\c trading_bot;

-- First, rename is_active to active in existing tables
ALTER TABLE IF EXISTS exchanges RENAME COLUMN is_active TO active;
ALTER TABLE IF EXISTS symbols RENAME COLUMN is_active TO active;

-- Provider Exchange Mappings Table
-- Maps provider-specific exchange codes to our master exchanges
CREATE TABLE IF NOT EXISTS provider_exchange_mappings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    provider VARCHAR(50) NOT NULL,              -- 'qm', 'eod', 'ib', etc.
    provider_exchange_code VARCHAR(50) NOT NULL, -- Provider's exchange code: 'NYE', 'US', 'NASDAQ'
    provider_exchange_name VARCHAR(255),        -- Provider's exchange name
    master_exchange_id UUID REFERENCES exchanges(id),
    country_code CHAR(2),                       -- Provider's country code
    currency CHAR(3),                           -- Provider's currency
    confidence DECIMAL(3,2) DEFAULT 0.8,       -- Mapping confidence (0.0 to 1.0)
    active BOOLEAN DEFAULT false,               -- Manual activation flag
    verified BOOLEAN DEFAULT false,             -- Manually verified flag
    auto_mapped BOOLEAN DEFAULT true,           -- Was this auto-created by sync?
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(provider, provider_exchange_code)
);

-- Create indexes for provider exchange mappings
CREATE INDEX IF NOT EXISTS idx_provider_exchange_mappings_provider ON provider_exchange_mappings(provider);
CREATE INDEX IF NOT EXISTS idx_provider_exchange_mappings_master ON provider_exchange_mappings(master_exchange_id);
CREATE INDEX IF NOT EXISTS idx_provider_exchange_mappings_active ON provider_exchange_mappings(provider, active);

-- Update existing exchanges to be inactive by default (for new syncs)
-- But preserve any existing active status
-- This only affects future INSERTs, not existing data

-- Add some useful views for management
CREATE OR REPLACE VIEW exchange_provider_summary AS
SELECT 
    e.code as master_code,
    e.name as master_name,
    e.country,
    e.currency,
    e.active as master_active,
    COUNT(pem.id) as provider_mappings,
    COUNT(CASE WHEN pem.active = true THEN 1 END) as active_mappings,
    COUNT(CASE WHEN pem.verified = true THEN 1 END) as verified_mappings,
    STRING_AGG(DISTINCT pem.provider, ', ') as providers
FROM exchanges e
LEFT JOIN provider_exchange_mappings pem ON e.id = pem.master_exchange_id
GROUP BY e.id, e.code, e.name, e.country, e.currency, e.active
ORDER BY e.code;

CREATE OR REPLACE VIEW provider_exchange_details AS
SELECT 
    pem.provider,
    pem.provider_exchange_code,
    pem.provider_exchange_name,
    pem.country_code,
    pem.currency,
    pem.active,
    pem.verified,
    pem.auto_mapped,
    pem.confidence,
    e.code as master_exchange_code,
    e.name as master_exchange_name,
    e.active as master_active
FROM provider_exchange_mappings pem
JOIN exchanges e ON pem.master_exchange_id = e.id
ORDER BY pem.provider, pem.provider_exchange_code;

-- Show what we created
SELECT 'Enhanced provider exchange mapping schema created' as status;