/**
 * Advanced Analytics & Insights Library
 * Hotspot detection, export metrics, custom dashboards
 */

import { pgTable, text, timestamp, integer, real, jsonb, boolean } from "drizzle-orm/pg-core";
import { getDatabase, schema } from "@/db";
import { eq, and, gte, lte, sql, desc } from "drizzle-orm";
import { logger } from "./logger";
import { repositories } from "@/db/schema/repositories";
import { users } from "@/db/schema/users";
import { compareBranches } from "@/lib/git";
import { resolveRepoPath } from "@/lib/git-storage";

// ============================================================================
// SCHEMA
// ============================================================================

export const fileHotspots = pgTable("file_hotspots", {
    id: text("id").primaryKey(),
    repositoryId: text("repository_id")
        .notNull()
        .references(() => repositories.id, { onDelete: "cascade" }),
    filePath: text("file_path").notNull(),
    changeCount: integer("change_count").default(0),
    bugCount: integer("bug_count").default(0),
    reviewCount: integer("review_count").default(0),
    complexityScore: real("complexity_score"),
    lastModified: timestamp("last_modified"),
    calculatedAt: timestamp("calculated_at").notNull().defaultNow(),
});

export const customDashboards = pgTable("custom_dashboards", {
    id: text("id").primaryKey(),
    userId: text("user_id")
        .notNull()
        .references(() => users.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    description: text("description"),
    isPublic: boolean("is_public").default(false),
    layout: jsonb("layout").$type<DashboardLayout>(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const dashboardWidgets = pgTable("dashboard_widgets", {
    id: text("id").primaryKey(),
    dashboardId: text("dashboard_id")
        .notNull()
        .references(() => customDashboards.id, { onDelete: "cascade" }),
    widgetType: text("widget_type").notNull(),
    title: text("title").notNull(),
    config: jsonb("config").$type<WidgetConfig>(),
    position: jsonb("position").$type<{ x: number; y: number; w: number; h: number }>(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
});

export const metricSnapshots = pgTable("metric_snapshots", {
    id: text("id").primaryKey(),
    repositoryId: text("repository_id")
        .references(() => repositories.id, { onDelete: "cascade" }),
    organizationId: text("organization_id"),
    metricType: text("metric_type").notNull(),
    value: real("value").notNull(),
    dimensions: jsonb("dimensions").$type<Record<string, string>>(),
    timestamp: timestamp("timestamp").notNull().defaultNow(),
});

export type FileHotspot = typeof fileHotspots.$inferSelect;
export type CustomDashboard = typeof customDashboards.$inferSelect;
export type MetricSnapshot = typeof metricSnapshots.$inferSelect;

interface DashboardLayout {
    columns: number;
    rows: number;
}

interface WidgetConfig {
    repositoryId?: string;
    timeRange?: string;
    metrics?: string[];
    filters?: Record<string, string>;
}

// ============================================================================
// HOTSPOT FILE DETECTION
// ============================================================================

export async function calculateFileHotspots(repositoryId: string): Promise<FileHotspot[]> {
    const db = getDatabase();
    const repository = await db.query.repositories.findFirst({
        where: eq(schema.repositories.id, repositoryId),
    });
    if (!repository) return [];

    const repoPath = await resolveRepoPath(repository.diskPath);

    // Get all PRs for this repository
    const prs = await db.query.pullRequests.findMany({
        where: eq(schema.pullRequests.repositoryId, repositoryId),
    });

    const fileStats: Map<string, {
        changeCount: number;
        bugCount: number;
        reviewCount: number;
        lastModified: Date;
    }> = new Map();

    // Replace previous snapshot for this repository
    // @ts-expect-error - Drizzle multi-db union type issue
    await db.delete(schema.fileHotspots).where(eq(schema.fileHotspots.repositoryId, repositoryId));

    // Analyze each PR's changed files
    for (const pr of prs) {
        let changedFiles: string[] = [];
        try {
            const { diffs } = await compareBranches(repoPath, pr.baseBranch, pr.headBranch);
            changedFiles = diffs.map((d) => d.file).filter(Boolean);
        } catch (error) {
            logger.warn({ repositoryId, prId: pr.id, error }, "Failed to load changed files for hotspot analysis");
            continue;
        }
        const isBugFix = pr.title?.toLowerCase().includes("fix") ||
            pr.title?.toLowerCase().includes("bug");

        for (const file of changedFiles) {
            const current = fileStats.get(file) || {
                changeCount: 0,
                bugCount: 0,
                reviewCount: 0,
                lastModified: new Date(0),
            };

            current.changeCount++;
            if (isBugFix) current.bugCount++;
            current.reviewCount += pr.reviewCount || 0;
            if (pr.mergedAt && pr.mergedAt > current.lastModified) {
                current.lastModified = pr.mergedAt;
            }

            fileStats.set(file, current);
        }
    }

    // Calculate complexity scores and save hotspots
    const hotspots: FileHotspot[] = [];

    for (const [filePath, stats] of fileStats) {
        // Hotspot score = changes * 0.5 + bugs * 2 + reviews * 0.3
        const complexityScore = stats.changeCount * 0.5 + stats.bugCount * 2;

        const hotspot = {
            id: crypto.randomUUID(),
            repositoryId,
            filePath,
            changeCount: stats.changeCount,
            bugCount: stats.bugCount,
            reviewCount: stats.reviewCount,
            complexityScore,
            lastModified: stats.lastModified,
            calculatedAt: new Date(),
        };

        // @ts-expect-error - Drizzle multi-db union type issue
        await db.insert(schema.fileHotspots).values(hotspot);
        hotspots.push(hotspot as FileHotspot);
    }

    // Sort by complexity score descending
    hotspots.sort((a, b) => (b.complexityScore || 0) - (a.complexityScore || 0));

    logger.info({ repoId: repositoryId, hotspotCount: hotspots.length }, "Hotspots calculated");

    return hotspots.slice(0, 50); // Return top 50
}

export async function getFileHotspots(repositoryId: string, limit = 20): Promise<FileHotspot[]> {
    const db = getDatabase();

    try {
        return await db.query.fileHotspots?.findMany({
            where: eq(schema.fileHotspots.repositoryId, repositoryId),
            orderBy: (h, { desc }) => [desc(h.complexityScore)],
            limit,
        }) || [];
    } catch {
        return [];
    }
}

// ============================================================================
// METRICS EXPORT
// ============================================================================

export type ExportFormat = "json" | "csv" | "prometheus";

export interface MetricExportOptions {
    repositoryId?: string;
    organizationId?: string;
    metrics: string[];
    startDate: Date;
    endDate: Date;
    format: ExportFormat;
    groupBy?: "day" | "week" | "month";
}

export async function exportMetrics(options: MetricExportOptions): Promise<string> {
    const db = getDatabase();

    const snapshots = await db.query.metricSnapshots?.findMany({
        where: and(
            options.repositoryId
                ? eq(schema.metricSnapshots.repositoryId, options.repositoryId)
                : undefined,
            gte(schema.metricSnapshots.timestamp, options.startDate),
            lte(schema.metricSnapshots.timestamp, options.endDate)
        ),
        orderBy: (m, { asc }) => [asc(m.timestamp)],
    }) || [];

    // Filter by metric types
    const filtered = snapshots.filter(s => options.metrics.includes(s.metricType));

    switch (options.format) {
        case "json":
            return exportAsJSON(filtered);
        case "csv":
            return exportAsCSV(filtered);
        case "prometheus":
            return exportAsPrometheus(filtered);
        default:
            return exportAsJSON(filtered);
    }
}

function exportAsJSON(snapshots: MetricSnapshot[]): string {
    return JSON.stringify(snapshots, null, 2);
}

function exportAsCSV(snapshots: MetricSnapshot[]): string {
    const headers = ["timestamp", "metricType", "value", "repositoryId"];
    const rows = snapshots.map(s => [
        s.timestamp.toISOString(),
        s.metricType,
        s.value.toString(),
        s.repositoryId || "",
    ]);

    return [headers.join(","), ...rows.map(r => r.join(","))].join("\n");
}

function exportAsPrometheus(snapshots: MetricSnapshot[]): string {
    const lines: string[] = [];

    for (const snapshot of snapshots) {
        const labels = snapshot.dimensions
            ? Object.entries(snapshot.dimensions)
                .map(([k, v]) => `${k}="${v}"`)
                .join(",")
            : "";

        const metricName = snapshot.metricType.replace(/[^a-zA-Z0-9_]/g, "_");
        lines.push(`# TYPE ${metricName} gauge`);
        lines.push(`${metricName}{${labels}} ${snapshot.value} ${snapshot.timestamp.getTime()}`);
    }

    return lines.join("\n");
}

// ============================================================================
// CUSTOM DASHBOARDS
// ============================================================================

export async function createDashboard(options: {
    userId: string;
    name: string;
    description?: string;
    isPublic?: boolean;
}): Promise<CustomDashboard> {
    const db = getDatabase();

    const dashboard = {
        id: crypto.randomUUID(),
        userId: options.userId,
        name: options.name,
        description: options.description || null,
        isPublic: options.isPublic ?? false,
        layout: { columns: 12, rows: 8 },
        createdAt: new Date(),
        updatedAt: new Date(),
    };

    // @ts-expect-error - Drizzle multi-db union type issue
    await db.insert(schema.customDashboards).values(dashboard);

    return dashboard as CustomDashboard;
}

export async function addWidget(options: {
    dashboardId: string;
    widgetType: string;
    title: string;
    config?: WidgetConfig;
    position?: { x: number; y: number; w: number; h: number };
}): Promise<typeof dashboardWidgets.$inferSelect> {
    const db = getDatabase();

    const widget = {
        id: crypto.randomUUID(),
        dashboardId: options.dashboardId,
        widgetType: options.widgetType,
        title: options.title,
        config: options.config || {},
        position: options.position || { x: 0, y: 0, w: 4, h: 2 },
        createdAt: new Date(),
    };

    // @ts-expect-error - Drizzle multi-db union type issue
    await db.insert(schema.dashboardWidgets).values(widget);

    return widget;
}

export const WIDGET_TYPES = {
    pr_velocity: {
        name: "PR Velocity",
        description: "PRs merged over time",
        defaultConfig: { timeRange: "30d" },
    },
    review_time: {
        name: "Review Time",
        description: "Average time to first review",
        defaultConfig: { timeRange: "30d" },
    },
    cycle_time: {
        name: "Cycle Time",
        description: "Time from PR open to merge",
        defaultConfig: { timeRange: "30d" },
    },
    hotspot_files: {
        name: "Hotspot Files",
        description: "Most frequently changed files",
        defaultConfig: { limit: 10 },
    },
    team_workload: {
        name: "Team Workload",
        description: "Open PRs per team member",
        defaultConfig: {},
    },
    ci_success_rate: {
        name: "CI Success Rate",
        description: "Percentage of passing builds",
        defaultConfig: { timeRange: "7d" },
    },
    deploy_frequency: {
        name: "Deploy Frequency",
        description: "Deployments per day/week",
        defaultConfig: { timeRange: "30d" },
    },
    issue_burndown: {
        name: "Issue Burndown",
        description: "Issues opened vs closed",
        defaultConfig: { timeRange: "30d" },
    },
} as const;

export async function getDashboard(dashboardId: string): Promise<{
    dashboard: CustomDashboard;
    widgets: (typeof dashboardWidgets.$inferSelect)[];
} | null> {
    const db = getDatabase();

    const dashboard = await db.query.customDashboards?.findFirst({
        where: eq(schema.customDashboards.id, dashboardId),
    });

    if (!dashboard) return null;

    const widgets = await db.query.dashboardWidgets?.findMany({
        where: eq(schema.dashboardWidgets.dashboardId, dashboardId),
    }) || [];

    return { dashboard, widgets };
}

// ============================================================================
// AGGREGATED METRICS
// ============================================================================

export async function recordMetric(options: {
    repositoryId?: string;
    organizationId?: string;
    metricType: string;
    value: number;
    dimensions?: Record<string, string>;
}): Promise<void> {
    const db = getDatabase();

    // @ts-expect-error - Drizzle multi-db union type issue
    await db.insert(schema.metricSnapshots).values({
        id: crypto.randomUUID(),
        repositoryId: options.repositoryId || null,
        organizationId: options.organizationId || null,
        metricType: options.metricType,
        value: options.value,
        dimensions: options.dimensions || null,
        timestamp: new Date(),
    });
}

export async function getMetricTimeSeries(options: {
    repositoryId?: string;
    metricType: string;
    startDate: Date;
    endDate: Date;
    granularity?: "hour" | "day" | "week";
}): Promise<{ timestamp: Date; value: number }[]> {
    const db = getDatabase();

    const snapshots = await db.query.metricSnapshots?.findMany({
        where: and(
            options.repositoryId
                ? eq(schema.metricSnapshots.repositoryId, options.repositoryId)
                : undefined,
            eq(schema.metricSnapshots.metricType, options.metricType),
            gte(schema.metricSnapshots.timestamp, options.startDate),
            lte(schema.metricSnapshots.timestamp, options.endDate)
        ),
        orderBy: (m, { asc }) => [asc(m.timestamp)],
    }) || [];

    return snapshots.map(s => ({
        timestamp: s.timestamp,
        value: s.value,
    }));
}

// ============================================================================
// DORA METRICS
// ============================================================================

export async function getDORAMetrics(options: {
    repositoryId?: string;
    organizationId?: string;
    startDate: Date;
    endDate: Date;
}): Promise<{
    deploymentFrequency: number; // per day
    leadTimeForChanges: number; // hours
    changeFailureRate: number; // percentage
    timeToRestoreService: number; // hours
}> {
    const db = getDatabase();

    // Get deployments
    const deployments = await db.query.deployments?.findMany({
        where: and(
            gte(schema.deployments.createdAt, options.startDate),
            lte(schema.deployments.createdAt, options.endDate)
        ),
    }) || [];

    const daysDiff = (options.endDate.getTime() - options.startDate.getTime()) / (1000 * 60 * 60 * 24);

    // Deployment Frequency
    const deploymentFrequency = deployments.length / daysDiff;

    // Lead Time for Changes (time from commit to deploy)
    const leadTimes = deployments
        .filter(d => d.startedAt && d.completedAt)
        .map(d => (d.completedAt!.getTime() - d.startedAt!.getTime()) / (1000 * 60 * 60));

    const leadTimeForChanges = leadTimes.length > 0
        ? leadTimes.reduce((a, b) => a + b, 0) / leadTimes.length
        : 0;

    // Change Failure Rate
    const failedDeploys = deployments.filter(d => d.status === "failed").length;
    const changeFailureRate = deployments.length > 0
        ? (failedDeploys / deployments.length) * 100
        : 0;

    // Time to Restore (time between failure and next success)
    const timeToRestoreService = 0; // Would need incident tracking

    return {
        deploymentFrequency,
        leadTimeForChanges,
        changeFailureRate,
        timeToRestoreService,
    };
}

// ============================================================================
// DEVELOPER WORKLOAD INSIGHTS
// ============================================================================

export interface DeveloperWorkload {
    userId: string;
    userName: string;
    openPRs: number;
    pendingReviews: number;
    assignedIssues: number;
    recentCommits: number;
    avgReviewTime: number; // hours
    workloadScore: number; // 0-100
    trend: "increasing" | "stable" | "decreasing";
}

export async function getDeveloperWorkloads(options: {
    repositoryId?: string;
    organizationId?: string;
    days?: number;
}): Promise<DeveloperWorkload[]> {
    const db = getDatabase();
    const startDate = new Date(Date.now() - (options.days || 30) * 24 * 60 * 60 * 1000);

    // Get all users
    const users = await db.query.users.findMany({}) || [];

    const workloads: DeveloperWorkload[] = [];

    for (const user of users) {
        // Count open PRs authored
        const openPRs = await db.query.pullRequests.findMany({
            where: and(
                eq(schema.pullRequests.authorId, user.id),
                eq(schema.pullRequests.state, "open")
            ),
        }) || [];

        // Count pending reviews requested (simplified - get all assigned reviews)
        const pendingReviews = await db.query.pullRequestReviewers?.findMany({
            where: eq(schema.pullRequestReviewers.userId, user.id),
        }) || [];

        // Count assigned issues
        const assignedIssues = await db.query.issueAssignees?.findMany({
            where: eq(schema.issueAssignees.userId, user.id),
        }) || [];

        // Calculate workload score (weighted)
        const workloadScore = Math.min(100,
            openPRs.length * 10 +
            pendingReviews.length * 15 +
            assignedIssues.length * 5
        );

        // Determine trend (compare to previous period)
        const trend = workloadScore > 70 ? "increasing" :
            workloadScore < 30 ? "decreasing" : "stable";

        workloads.push({
            userId: user.id,
            userName: user.displayName || user.username,
            openPRs: openPRs.length,
            pendingReviews: pendingReviews.length,
            assignedIssues: assignedIssues.length,
            recentCommits: 0, // Would need commit data
            avgReviewTime: 0, // Would need review timing data
            workloadScore,
            trend,
        });
    }

    // Sort by workload score descending
    workloads.sort((a, b) => b.workloadScore - a.workloadScore);

    return workloads;
}

export async function getTeamBalanceReport(organizationId: string): Promise<{
    totalWorkload: number;
    averageWorkload: number;
    maxWorkload: number;
    minWorkload: number;
    imbalanceScore: number; // 0-100, higher = more imbalanced
    recommendations: string[];
}> {
    const workloads = await getDeveloperWorkloads({ organizationId });

    if (workloads.length === 0) {
        return {
            totalWorkload: 0,
            averageWorkload: 0,
            maxWorkload: 0,
            minWorkload: 0,
            imbalanceScore: 0,
            recommendations: [],
        };
    }

    const scores = workloads.map(w => w.workloadScore);
    const totalWorkload = scores.reduce((a, b) => a + b, 0);
    const averageWorkload = totalWorkload / scores.length;
    const maxWorkload = Math.max(...scores);
    const minWorkload = Math.min(...scores);

    // Calculate standard deviation for imbalance
    const variance = scores.reduce((sum, s) => sum + Math.pow(s - averageWorkload, 2), 0) / scores.length;
    const stdDev = Math.sqrt(variance);

    // Imbalance score based on coefficient of variation
    const imbalanceScore = averageWorkload > 0
        ? Math.min(100, (stdDev / averageWorkload) * 100)
        : 0;

    // Generate recommendations
    const recommendations: string[] = [];

    if (imbalanceScore > 50) {
        recommendations.push("Consider redistributing work more evenly across team members");
    }

    const overloaded = workloads.filter(w => w.workloadScore > 80);
    if (overloaded.length > 0) {
        recommendations.push(`${overloaded.length} team member(s) appear overloaded`);
    }

    const underutilized = workloads.filter(w => w.workloadScore < 20);
    if (underutilized.length > 0 && overloaded.length > 0) {
        recommendations.push("Redistribute reviews from overloaded to underutilized members");
    }

    return {
        totalWorkload,
        averageWorkload,
        maxWorkload,
        minWorkload,
        imbalanceScore,
        recommendations,
    };
}

export async function suggestReviewerAssignment(
    pullRequestId: string,
    excludeUserIds: string[] = []
): Promise<{ userId: string; userName: string; reason: string }[]> {
    const db = getDatabase();

    const pr = await db.query.pullRequests.findFirst({
        where: eq(schema.pullRequests.id, pullRequestId),
    });

    if (!pr) return [];

    // Get workloads excluding the PR author and excluded users
    const workloads = await getDeveloperWorkloads({ repositoryId: pr.repositoryId });

    const candidates = workloads
        .filter(w => w.userId !== pr.authorId && !excludeUserIds.includes(w.userId))
        .filter(w => w.workloadScore < 80) // Not overloaded
        .slice(0, 3); // Top 3 suggestions

    return candidates.map(c => ({
        userId: c.userId,
        userName: c.userName,
        reason: c.workloadScore < 30
            ? "Has low current workload"
            : c.pendingReviews < 3
                ? "Has few pending reviews"
                : "Balanced workload",
    }));
}