import { createClient, SupabaseClient } from '@supabase/supabase-js';
import { config } from '../config/index.js';

type OrderRow = {
    id?: string | null;
    order_id?: string | null;
    profile_id?: string | null;
    user_id?: string | null;
    symbol?: string | null;
    action?: string | null;
    status?: string | null;
    trade_id?: string | null;
    created_at?: string | null;
};

type HistoryRow = {
    id?: string | null;
    profile_id?: string | null;
    user_id?: string | null;
    symbol?: string | null;
    trade_id?: string | null;
    created_at?: string | null;
    timestamp?: number | string | null;
};

type GroupKey = string;

const FALLBACK_BATCH_LIMIT = 2000;

function ensureSupabaseClient(): SupabaseClient {
    if (!config.SUPABASE_URL || !config.SUPABASE_KEY) {
        throw new Error('SUPABASE_URL / SUPABASE_KEY must be configured');
    }
    return createClient(config.SUPABASE_URL, config.SUPABASE_KEY);
}

function normalizeToken(raw: string | null | undefined, fallback: string): string {
    const value = String(raw || '').trim();
    if (!value) return fallback;
    return value.replace(/[^A-Za-z0-9]/g, '').slice(0, 24) || fallback;
}

function buildLegacyTradeId(row: { profile_id?: string | null; user_id?: string | null; symbol?: string | null; created_at?: string | null }, sequence: number): string {
    const owner = normalizeToken(row.profile_id || row.user_id, 'global');
    const symbol = normalizeToken(row.symbol, 'asset');
    const created = row.created_at ? new Date(row.created_at).getTime() : Date.now();
    const safeCreated = Number.isFinite(created) && created > 0 ? created : Date.now();
    const seq = String(sequence).padStart(4, '0');
    return `TRD-LEGACY-${owner}-${symbol}-${safeCreated}-${seq}`;
}

function toGroupKey(profileId?: string | null, userId?: string | null, symbol?: string | null): GroupKey {
    return `${profileId || userId || 'global'}::${symbol || 'UNKNOWN'}`;
}

function parseTs(rawCreatedAt?: string | null, rawTimestamp?: number | string | null): number {
    const ts = Number(rawTimestamp);
    if (Number.isFinite(ts) && ts > 0) return ts;
    const createdAtTs = Date.parse(String(rawCreatedAt || ''));
    if (Number.isFinite(createdAtTs) && createdAtTs > 0) return createdAtTs;
    return 0;
}

function normalizeAction(rawAction: string | null | undefined): 'ENTRY' | 'EXIT' | 'UNKNOWN' {
    const action = String(rawAction || '').trim().toUpperCase();
    if (action === 'ENTRY' || action === 'EXIT') return action;
    return 'UNKNOWN';
}

async function fetchOrdersForGroup(client: SupabaseClient, profileId: string | null, userId: string | null, symbol: string | null): Promise<OrderRow[]> {
    let query = client
        .from('orders')
        .select('id,order_id,profile_id,user_id,symbol,action,status,trade_id,created_at')
        .eq('symbol', symbol || '')
        .order('created_at', { ascending: true })
        .limit(FALLBACK_BATCH_LIMIT);

    if (profileId) {
        query = query.eq('profile_id', profileId);
    } else if (userId) {
        query = query.eq('user_id', userId);
    } else {
        query = query.is('profile_id', null);
    }

    const { data, error } = await query;
    if (error) {
        throw new Error(`fetchOrdersForGroup failed (${profileId || userId || 'global'}/${symbol}): ${error.message}`);
    }
    return (data || []) as OrderRow[];
}

async function fetchMissingOrders(client: SupabaseClient): Promise<OrderRow[]> {
    const { data, error } = await client
        .from('orders')
        .select('id,order_id,profile_id,user_id,symbol,action,status,trade_id,created_at')
        .or('trade_id.is.null,trade_id.eq.')
        .order('created_at', { ascending: true })
        .limit(FALLBACK_BATCH_LIMIT);

    if (error) {
        throw new Error(`fetchMissingOrders failed: ${error.message}`);
    }
    return (data || []) as OrderRow[];
}

async function fetchMissingHistory(client: SupabaseClient): Promise<HistoryRow[]> {
    const { data, error } = await client
        .from('trade_history')
        .select('id,profile_id,user_id,symbol,trade_id,created_at,timestamp')
        .or('trade_id.is.null,trade_id.eq.')
        .order('created_at', { ascending: true })
        .limit(FALLBACK_BATCH_LIMIT);

    if (error) {
        throw new Error(`fetchMissingHistory failed: ${error.message}`);
    }
    return (data || []) as HistoryRow[];
}

async function updateOrderTradeId(client: SupabaseClient, row: OrderRow, tradeId: string): Promise<void> {
    const hasPrimaryId = !!row.id;
    if (hasPrimaryId) {
        const { error } = await client
            .from('orders')
            .update({ trade_id: tradeId })
            .eq('id', row.id);
        if (error) throw new Error(`update order id=${row.id} failed: ${error.message}`);
        return;
    }

    if (!row.order_id) {
        throw new Error('order row missing both id and order_id');
    }
    const { error } = await client
        .from('orders')
        .update({ trade_id: tradeId })
        .eq('order_id', row.order_id);
    if (error) throw new Error(`update order order_id=${row.order_id} failed: ${error.message}`);
}

async function updateHistoryTradeId(client: SupabaseClient, row: HistoryRow, tradeId: string): Promise<void> {
    if (!row.id) {
        throw new Error('history row missing id');
    }
    const { error } = await client
        .from('trade_history')
        .update({ trade_id: tradeId })
        .eq('id', row.id);
    if (error) throw new Error(`update history id=${row.id} failed: ${error.message}`);
}

function deriveOrderBackfillPlan(rows: OrderRow[]): Map<string, string> {
    const assignments = new Map<string, string>();

    const grouped = new Map<GroupKey, OrderRow[]>();
    for (const row of rows) {
        const key = toGroupKey(row.profile_id || null, row.user_id || null, row.symbol || null);
        const list = grouped.get(key) || [];
        list.push(row);
        grouped.set(key, list);
    }

    for (const groupRows of grouped.values()) {
        const orderedRows = [...groupRows].sort((a, b) => parseTs(a.created_at) - parseTs(b.created_at));
        let currentTradeId: string | null = null;
        let sequence = 0;

        for (const row of orderedRows) {
            const existingTradeId = String(row.trade_id || '').trim();
            if (existingTradeId) {
                currentTradeId = existingTradeId;
                continue;
            }

            const action = normalizeAction(row.action);
            let assignedTradeId: string | null = currentTradeId;
            if (!assignedTradeId || action === 'ENTRY') {
                sequence++;
                assignedTradeId = buildLegacyTradeId(row, sequence);
                currentTradeId = assignedTradeId;
            }

            const key = String(row.id || row.order_id || '');
            if (!key) continue;
            if (!assignedTradeId) continue;
            assignments.set(key, assignedTradeId);

            const status = String(row.status || '').toLowerCase();
            if (action === 'EXIT' && status === 'filled') {
                currentTradeId = null;
            }
        }
    }

    return assignments;
}

function findNearestTradeIdForHistory(historyRow: HistoryRow, orderRows: OrderRow[]): string | null {
    const historyTs = parseTs(historyRow.created_at, historyRow.timestamp);
    if (!(historyTs > 0)) return null;

    let candidateTradeId: string | null = null;
    let candidateTs = 0;

    for (const order of orderRows) {
        const tradeId = String(order.trade_id || '').trim();
        if (!tradeId) continue;
        const orderTs = parseTs(order.created_at);
        if (!(orderTs > 0)) continue;
        if (orderTs <= historyTs && orderTs >= candidateTs) {
            candidateTs = orderTs;
            candidateTradeId = tradeId;
        }
    }

    return candidateTradeId;
}

async function main() {
    const apply = process.argv.includes('--apply');
    const client = ensureSupabaseClient();

    console.log(`[BackfillTradeIds] Starting in ${apply ? 'APPLY' : 'DRY-RUN'} mode`);

    const missingOrders = await fetchMissingOrders(client);
    console.log(`[BackfillTradeIds] Missing order trade_id rows: ${missingOrders.length}`);

    const groupCache = new Map<GroupKey, OrderRow[]>();
    const missingOrderGroups = new Map<GroupKey, { profileId: string | null; userId: string | null; symbol: string | null }>();
    for (const row of missingOrders) {
        const profileId = row.profile_id || null;
        const userId = row.user_id || null;
        const symbol = row.symbol || null;
        const key = toGroupKey(profileId, userId, symbol);
        missingOrderGroups.set(key, { profileId, userId, symbol });
    }

    for (const [groupKey, group] of missingOrderGroups.entries()) {
        const rows = await fetchOrdersForGroup(client, group.profileId, group.userId, group.symbol);
        groupCache.set(groupKey, rows);
    }

    let orderUpdates = 0;
    for (const [groupKey, rows] of groupCache.entries()) {
        const plan = deriveOrderBackfillPlan(rows);
        const keyToRow = new Map<string, OrderRow>();
        for (const row of rows) {
            const key = String(row.id || row.order_id || '');
            if (key) keyToRow.set(key, row);
        }

        for (const [rowKey, tradeId] of plan.entries()) {
            const row = keyToRow.get(rowKey);
            if (!row) continue;
            if (!String(row.trade_id || '').trim()) {
                if (apply) {
                    await updateOrderTradeId(client, row, tradeId);
                }
                orderUpdates++;
            }
        }

        // Refresh cache with newly assigned trade ids for history matching.
        if (apply) {
            const groupMeta = missingOrderGroups.get(groupKey)!;
            const refreshed = await fetchOrdersForGroup(client, groupMeta.profileId, groupMeta.userId, groupMeta.symbol);
            groupCache.set(groupKey, refreshed);
        } else {
            const updatedRows = rows.map((row) => {
                const rowKey = String(row.id || row.order_id || '');
                const planned = plan.get(rowKey);
                if (!planned || String(row.trade_id || '').trim()) return row;
                return { ...row, trade_id: planned };
            });
            groupCache.set(groupKey, updatedRows);
        }
    }

    const missingHistory = await fetchMissingHistory(client);
    console.log(`[BackfillTradeIds] Missing history trade_id rows: ${missingHistory.length}`);

    let historyUpdates = 0;
    let fallbackHistoryIds = 0;

    for (const row of missingHistory) {
        const key = toGroupKey(row.profile_id || null, row.user_id || null, row.symbol || null);
        let groupOrders = groupCache.get(key);
        if (!groupOrders) {
            groupOrders = await fetchOrdersForGroup(client, row.profile_id || null, row.user_id || null, row.symbol || null);
            groupCache.set(key, groupOrders);
        }

        const matchedTradeId = findNearestTradeIdForHistory(row, groupOrders);
        const tradeId = matchedTradeId || buildLegacyTradeId(
            {
                profile_id: row.profile_id || null,
                user_id: row.user_id || null,
                symbol: row.symbol || null,
                created_at: row.created_at || null
            },
            1
        );
        if (!matchedTradeId) fallbackHistoryIds++;

        if (apply) {
            await updateHistoryTradeId(client, row, tradeId);
        }
        historyUpdates++;
    }

    console.log(`[BackfillTradeIds] Planned/Applied order updates: ${orderUpdates}`);
    console.log(`[BackfillTradeIds] Planned/Applied history updates: ${historyUpdates}`);
    console.log(`[BackfillTradeIds] History fallback synthetic IDs: ${fallbackHistoryIds}`);

    if (!apply) {
        console.log('[BackfillTradeIds] Dry-run complete. Re-run with --apply to persist changes.');
    }
}

main().catch((error) => {
    console.error('[BackfillTradeIds] Failed:', error);
    process.exit(1);
});
