import { db } from './db';
import { eq, and, between, asc } from 'drizzle-orm';
import { tickers, timeframes, candles } from './schema';
import { count } from 'drizzle-orm';
import { sql } from 'drizzle-orm';
import { logger } from './utils/logger';

// Cache for ticker symbols and their IDs
const tickerCache = new Map<string, number>();
// Cache for timeframe names and their IDs
const timeframeCache = new Map<string, number>();

// Custom error for not found
export class NotFoundError extends Error {
    constructor(message: string) {
        super(message);
        this.name = 'NotFoundError';
    }
}

// Shared insert helper for ticker/timeframe
async function insertUnique(
    cache: Map<string, number>,
    table: any,
    value: string,
    valueField: string,
    loggerContext: Record<string, any>
): Promise<number> {
    try {
        const inserted = await db.insert(table).values({ [valueField]: value }).returning();
        if (inserted && inserted.length > 0) {
            cache.set(value, inserted[0].id);
            return inserted[0].id;
        }
    } catch (err) {
        logger.warn({ ...loggerContext, err }, `Error inserting ${valueField}, trying to fetch`);
    }
    // Fallback: try to find it one more time in case of concurrent inserts
    const refetch = await db.select().from(table).where(eq(table[valueField], value)).limit(1);
    if (refetch.length > 0) {
        cache.set(value, refetch[0].id);
        return refetch[0].id;
    }
    throw new Error(`Failed to insert ${valueField} ${value}`);
}

export async function upsertTicker(ticker: string): Promise<number> {
    try {
        return await getTickerId(ticker);
    } catch (err: any) {
        if (err instanceof NotFoundError) {
            return insertUnique(tickerCache, tickers, ticker, 'symbol', { ticker });
        }
        throw err;
    }
}

export async function upsertTimeframe(timeframe: string): Promise<number> {
    try {
        return await getTimeframeId(timeframe);
    } catch (err: any) {
        if (err instanceof NotFoundError) {
            return insertUnique(timeframeCache, timeframes, timeframe, 'name', { timeframe });
        }
        throw err;
    }
}

export async function upsertCandles(tickerId: number, timeframeId: number, data: [number, number, number, number, number, number][]): Promise<number> {
    if (!data.length) return 0;

    try {
        // Map to Drizzle format with proper type conversion
        const rows = data.map(row => ({
            tickerId,
            timeframeId,
            timestamp: row[0],
            open: String(row[1]),
            high: String(row[2]),
            close: String(row[3]),
            low: String(row[4]),
            volume: String(row[5]),
        }));

        // Process in batches to avoid overwhelming the database
        const batchSize = 500;
        let insertedCount = 0;

        for (let i = 0; i < rows.length; i += batchSize) {
            const batch = rows.slice(i, i + batchSize);

            await db.insert(candles).values(batch).onConflictDoUpdate({
                target: [candles.tickerId, candles.timeframeId, candles.timestamp],
                set: {
                    open: candles.open,
                    high: candles.high,
                    close: candles.close,
                    low: candles.low,
                    volume: candles.volume,
                },
            });

            insertedCount += batch.length;
            logger.debug(`Inserted/updated batch of ${batch.length} candles (${i + batch.length}/${rows.length})`);
        }

        return insertedCount;
    } catch (err) {
        logger.error({ err, tickerId, timeframeId, dataLength: data.length }, 'Error upserting candles');
        throw err;
    }
}

// Readonly lookup helpers: get existing ticker and timeframe IDs, throw if missing
export async function getTickerId(ticker: string): Promise<number> {
    if (!tickerCache.has(ticker)) {
        const found = await db.select().from(tickers).where(eq(tickers.symbol, ticker)).limit(1);
        if (found.length > 0) {
            tickerCache.set(ticker, found[0].id);
        } else {
            throw new NotFoundError(`Ticker not found: ${ticker}`);
        }
    }
    return tickerCache.get(ticker)!;
}

export async function getTimeframeId(timeframe: string): Promise<number> {
    if (!timeframeCache.has(timeframe)) {
        const found = await db.select().from(timeframes).where(eq(timeframes.name, timeframe)).limit(1);
        if (found.length > 0) {
            timeframeCache.set(timeframe, found[0].id);
        } else {
            throw new NotFoundError(`Timeframe not found: ${timeframe}`);
        }
    }
    return timeframeCache.get(timeframe)!;
}

export async function fetchCandles(ticker: string, timeframe: string, from: number, to: number, limit: number, offset: number = 0) {
    try {
        const tickerId = await getTickerId(ticker);
        const timeframeId = await getTimeframeId(timeframe);

        const result = await db.select({
            timestamp: candles.timestamp,
            open: candles.open,
            high: candles.high,
            close: candles.close,
            low: candles.low,
            volume: candles.volume,
        })
            .from(candles)
            .where(and(
                eq(candles.tickerId, tickerId),
                eq(candles.timeframeId, timeframeId),
                between(candles.timestamp, from, to)
            ))
            .orderBy(asc(candles.timestamp))
            .limit(limit)
            .offset(offset);

        // Convert numeric values directly to JavaScript numbers
        return result.map(row => [
            row.timestamp,
            Number(row.open),
            Number(row.high),
            Number(row.close),
            Number(row.low),
            Number(row.volume)
        ]);
    } catch (err) {
        logger.error({ err, ticker, timeframe, from, to }, 'Error fetching candles');
        throw err;
    }
}

export async function fetchCandlesCount(ticker: string, timeframe: string, from: number, to: number): Promise<number> {
    try {
        const tickerId = await getTickerId(ticker);
        const timeframeId = await getTimeframeId(timeframe);
        const result = await db.select({ count: count() })
            .from(candles)
            .where(and(
                eq(candles.tickerId, tickerId),
                eq(candles.timeframeId, timeframeId),
                between(candles.timestamp, from, to)
            ));
        return Number(result[0]?.count ?? 0);
    } catch (err) {
        logger.error({ err, ticker, timeframe, from, to }, 'Error counting candles');
        throw err;
    }
}

export async function fetchTimestampRange(ticker: string, timeframe: string): Promise<{
    min: number | null,
    max: number | null,
    count: number | null
}> {
    try {
        const tickerId = await getTickerId(ticker);
        const timeframeId = await getTimeframeId(timeframe);

        // Using the SQL query: SELECT min("timestamp"), max("timestamp") FROM public.candles WHERE ticker_id = ? AND timeframe_id = ?
        const result = await db.select({
            min: sql`MIN(${candles.timestamp})`,
            max: sql`MAX(${candles.timestamp})`,
            count: sql`COUNT(*)`
        })
            .from(candles)
            .where(and(
                eq(candles.tickerId, tickerId),
                eq(candles.timeframeId, timeframeId)
            ));

        return {
            min: result[0]?.min !== null ? Number(result[0].min) : null,
            max: result[0]?.max !== null ? Number(result[0].max) : null,
            count: result[0]?.count !== null ? Number(result[0].count) : null
        };
    } catch (err) {
        logger.error({ err, ticker, timeframe }, 'Error fetching timestamp range');
        throw err;
    }
}

export async function fetchAllTickers(): Promise<string[]> {
    const rows = await db.select({ symbol: tickers.symbol }).from(tickers);
    return rows.map(r => r.symbol);
}

export async function fetchAllTimeframes(): Promise<string[]> {
    const rows = await db.select({ name: timeframes.name }).from(timeframes);
    return rows.map(r => r.name);
}