import { Database } from "bun:sqlite";
import { mkdirSync } from "node:fs";
import { dirname } from "node:path";
import type { ReportBucket, ReportCoverageStatus, ReportMetricUnit } from "@overdeck/report-contract";

const STORE_SCHEMA_VERSION = 1;
export const DEFAULT_REPORT_HISTORY_RETENTION_DAYS = 400;

interface StoredRow {
  rowid: number;
  payload_json: string;
}

export interface ReportBucketReadResult {
  buckets: ReportBucket[];
  quarantinedRows: number;
}

function isRecord(value: unknown): value is Record<string, unknown> {
  return typeof value === "object" && value !== null && !Array.isArray(value);
}

function isCoverageStatus(value: unknown): value is ReportCoverageStatus {
  return value === "complete" || value === "partial" || value === "stale" || value === "unavailable";
}

function isMetricUnit(value: unknown): value is ReportMetricUnit {
  return value === "count" || value === "percent" || value === "milliseconds" || value === "tokens" || value === "currency";
}

function validTimestamp(value: unknown): value is string {
  return typeof value === "string" && Number.isFinite(Date.parse(value));
}

function parseBucket(raw: string): ReportBucket | undefined {
  let value: unknown;
  try {
    value = JSON.parse(raw);
  } catch {
    return undefined;
  }
  if (!isRecord(value) || !isRecord(value.coverage) || !isRecord(value.sourceWatermark)) return undefined;
  if (!validTimestamp(value.bucketStart) || !validTimestamp(value.bucketEnd) || !validTimestamp(value.generatedAt)) return undefined;
  if (Date.parse(value.bucketStart) >= Date.parse(value.bucketEnd)) return undefined;
  if (typeof value.metricKey !== "string" || value.metricKey.length === 0 || value.metricKey.length > 160) return undefined;
  if (!Number.isInteger(value.metricVersion) || (value.metricVersion as number) < 1) return undefined;
  if (typeof value.label !== "string" || value.label.length === 0 || value.label.length > 160) return undefined;
  if (!isMetricUnit(value.unit) || typeof value.dimensionKey !== "string" || value.dimensionKey.length > 500) return undefined;
  if (typeof value.value !== "number" || !Number.isFinite(value.value)) return undefined;
  if (!isCoverageStatus(value.coverage.status) || typeof value.coverage.key !== "string" || !Array.isArray(value.coverage.gaps)) return undefined;
  if (typeof value.backfilled !== "boolean") return undefined;
  return value as unknown as ReportBucket;
}

function canonicalBucket(bucket: ReportBucket): string {
  return JSON.stringify({
    bucketStart: bucket.bucketStart,
    bucketEnd: bucket.bucketEnd,
    metricKey: bucket.metricKey,
    metricVersion: bucket.metricVersion,
    label: bucket.label,
    unit: bucket.unit,
    dimensionKey: bucket.dimensionKey,
    value: bucket.value,
    coverage: bucket.coverage,
    sourceWatermark: Object.fromEntries(Object.entries(bucket.sourceWatermark).sort(([left], [right]) => left.localeCompare(right))),
    generatedAt: bucket.generatedAt,
    backfilled: bucket.backfilled,
  });
}

export class ReportHistoryStore {
  private readonly db: Database;
  private readonly retentionMs: number;
  readonly retentionDays: number;

  constructor(path: string, options: { retentionDays?: number } = {}) {
    mkdirSync(dirname(path), { recursive: true });
    this.db = new Database(path, { create: true });
    const requestedRetentionDays = options.retentionDays ?? DEFAULT_REPORT_HISTORY_RETENTION_DAYS;
    this.retentionDays = Number.isFinite(requestedRetentionDays) && requestedRetentionDays > 0
      ? Math.floor(requestedRetentionDays)
      : DEFAULT_REPORT_HISTORY_RETENTION_DAYS;
    this.retentionMs = this.retentionDays * 24 * 60 * 60 * 1_000;
    this.db.exec("PRAGMA journal_mode = WAL");
    this.db.exec("PRAGMA synchronous = FULL");
    const schemaVersion = this.db.query<{ user_version: number }, []>("PRAGMA user_version").get()?.user_version ?? 0;
    if (schemaVersion > STORE_SCHEMA_VERSION) {
      this.db.close();
      throw new Error("Report history uses a newer schema version.");
    }
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS report_buckets (
        bucket_start TEXT NOT NULL,
        bucket_end TEXT NOT NULL,
        metric_key TEXT NOT NULL,
        metric_version INTEGER NOT NULL,
        dimension_key TEXT NOT NULL,
        payload_json TEXT NOT NULL,
        PRIMARY KEY (bucket_start, metric_key, metric_version, dimension_key)
      );
      CREATE INDEX IF NOT EXISTS report_buckets_range
        ON report_buckets(bucket_start, bucket_end, metric_key);
      CREATE TABLE IF NOT EXISTS report_bucket_quarantine (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        original_rowid INTEGER,
        reason TEXT NOT NULL,
        quarantined_at TEXT NOT NULL,
        raw_json TEXT NOT NULL
      );
    `);
    if (schemaVersion < STORE_SCHEMA_VERSION) this.db.exec(`PRAGMA user_version = ${STORE_SCHEMA_VERSION}`);
  }

  writeReportBucket(bucket: ReportBucket): "inserted" | "unchanged" {
    const parsed = parseBucket(canonicalBucket(bucket));
    if (!parsed) throw new Error("Report bucket is invalid.");
    const payload = canonicalBucket(parsed);
    return this.db.transaction(() => {
      const existing = this.db.query<{ payload_json: string }, [string, string, number, string]>(`
        SELECT payload_json FROM report_buckets
        WHERE bucket_start = ? AND metric_key = ? AND metric_version = ? AND dimension_key = ?
      `).get(parsed.bucketStart, parsed.metricKey, parsed.metricVersion, parsed.dimensionKey);
      if (existing) {
        if (existing.payload_json !== payload) throw new Error("A different immutable report bucket already exists.");
        return "unchanged" as const;
      }
      this.db.query(`
        INSERT INTO report_buckets
          (bucket_start, bucket_end, metric_key, metric_version, dimension_key, payload_json)
        VALUES (?, ?, ?, ?, ?, ?)
      `).run(parsed.bucketStart, parsed.bucketEnd, parsed.metricKey, parsed.metricVersion, parsed.dimensionKey, payload);
      return "inserted" as const;
    })();
  }

  readReportBuckets(from: string, to: string): ReportBucketReadResult {
    const rows = this.db.query<StoredRow, [string, string]>(`
      SELECT rowid, payload_json FROM report_buckets
      WHERE bucket_start >= ? AND bucket_end <= ?
      ORDER BY bucket_start ASC, metric_key ASC, metric_version ASC, dimension_key ASC
    `).all(from, to);
    const buckets: ReportBucket[] = [];
    const corrupt: StoredRow[] = [];
    for (const row of rows) {
      const bucket = parseBucket(row.payload_json);
      if (bucket) buckets.push(bucket);
      else corrupt.push(row);
    }
    if (corrupt.length > 0) {
      const quarantinedAt = new Date().toISOString();
      this.db.transaction(() => {
        const insert = this.db.query("INSERT INTO report_bucket_quarantine (original_rowid, reason, quarantined_at, raw_json) VALUES (?, ?, ?, ?)");
        const remove = this.db.query("DELETE FROM report_buckets WHERE rowid = ?");
        for (const row of corrupt) {
          insert.run(row.rowid, "Stored report bucket failed schema validation.", quarantinedAt, row.payload_json);
          remove.run(row.rowid);
        }
      })();
    }
    return { buckets, quarantinedRows: corrupt.length };
  }

  earliestAvailable(): string | undefined {
    return this.db.query<{ earliest: string | null }, []>("SELECT MIN(bucket_start) AS earliest FROM report_buckets").get()?.earliest ?? undefined;
  }

  prune(nowMs: number): number {
    const cutoff = new Date(nowMs - this.retentionMs).toISOString();
    return Number(this.db.query("DELETE FROM report_buckets WHERE bucket_end < ?").run(cutoff).changes);
  }

  quarantineCount(): number {
    return this.db.query<{ count: number }, []>("SELECT COUNT(*) AS count FROM report_bucket_quarantine").get()?.count ?? 0;
  }

  close(): void {
    this.db.close();
  }
}