-- Migration 0004: supply_accounts
-- Stores per-account credentials and metadata used for platform API access.
-- Replaces the one-row account_routing_states pattern with a proper multi-account table.

CREATE TABLE IF NOT EXISTS supply_intelligence_supply_accounts (
    account_id    BIGINT PRIMARY KEY,
    platform      TEXT NOT NULL,          -- 'openai' | 'anthropic'
    api_key       TEXT NOT NULL DEFAULT '', -- encrypted in production; here stored raw
    consumer_tag  TEXT NOT NULL DEFAULT '', -- gateway consumer that owns this account
    status        TEXT NOT NULL DEFAULT 'active', -- 'active' | 'suspended'
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_supply_accounts_platform ON supply_intelligence_supply_accounts(platform);
CREATE INDEX IF NOT EXISTS idx_supply_accounts_status  ON supply_intelligence_supply_accounts(status);

-- Migrate existing account data from account_routing_states if rows exist
INSERT INTO supply_intelligence_supply_accounts (account_id, platform, api_key, consumer_tag, status)
SELECT account_id, platform, '', '', 'active'
FROM supply_intelligence_account_routing_states
ON CONFLICT (account_id) DO NOTHING;