import path from "node:path";
import { fileURLToPath } from "node:url";
import dotenv from "dotenv";
import pg from "pg";
import { Kysely, PostgresDialect, sql, type ColumnType } from "kysely";

const __dirname = path.dirname(fileURLToPath(import.meta.url));
dotenv.config({ path: path.resolve(__dirname, "../../../.env"), quiet: true });

const { Pool } = pg;

type Generated<T> = ColumnType<T, T | undefined, T>;
type Defaulted<T> = ColumnType<T, T | undefined, T>;

export interface Database {
  users: {
    id: Generated<string>;
    email: string;
    password_hash: string;
    created_at: Generated<Date>;
  };
  invites: {
    id: Generated<string>;
    email: string;
    token_hash: string;
    accepted_at: Defaulted<Date | null>;
    expires_at: Date;
    created_by: Defaulted<string | null>;
    created_at: Generated<Date>;
  };
  password_resets: {
    id: Generated<string>;
    user_id: string;
    token_hash: string;
    used_at: Defaulted<Date | null>;
    expires_at: Date;
    created_at: Generated<Date>;
  };
  passkey_credentials: {
    id: Generated<string>;
    user_id: string;
    credential_id: string;
    public_key: Buffer;
    counter: Defaulted<string>;
    transports: Defaulted<string[]>;
    device_type: Defaulted<string | null>;
    backed_up: Defaulted<boolean>;
    created_at: Generated<Date>;
    last_used_at: Defaulted<Date | null>;
  };
  passkey_challenges: {
    id: Generated<string>;
    user_id: Defaulted<string | null>;
    challenge: string;
    type: "registration" | "authentication";
    expires_at: Date;
    created_at: Generated<Date>;
  };
  sessions: {
    id: Generated<string>;
    user_id: string;
    token_hash: string;
    expires_at: Date;
    created_at: Generated<Date>;
  };
  locations: {
    id: Generated<string>;
    name: string;
    kind: Defaulted<"warehouse" | "truck" | "other">;
    priority: Defaulted<number>;
    active: Defaulted<boolean>;
    created_at: Generated<Date>;
  };
  parts: {
    id: Generated<string>;
    name: string;
    sku: Defaulted<string | null>;
    type: "base" | "partial" | "full";
    average_cost: Defaulted<string>;
    active: Defaulted<boolean>;
    created_at: Generated<Date>;
  };
  part_aliases: {
    id: Generated<string>;
    part_id: string;
    code: string;
    label: Defaulted<string | null>;
    created_at: Generated<Date>;
  };
  bom_versions: {
    id: Generated<string>;
    assembly_part_id: string;
    name: Defaulted<string>;
    version: number;
    active: Defaulted<boolean>;
    created_at: Generated<Date>;
  };
  bom_lines: {
    id: Generated<string>;
    bom_version_id: string;
    component_part_id: string;
    quantity_required: string;
  };
  inventory_balances: {
    part_id: string;
    location_id: string;
    quantity: Defaulted<string>;
    updated_at: Generated<Date>;
  };
  stock_thresholds: {
    part_id: string;
    location_id: string;
    min_quantity: Defaulted<string>;
    reorder_quantity: Defaulted<string>;
  };
  inventory_lots: {
    id: Generated<string>;
    part_id: string;
    location_id: string;
    received_quantity: string;
    remaining_quantity: string;
    unit_cost: string;
    vendor_name: Defaulted<string | null>;
    reference: Defaulted<string | null>;
    received_at: Generated<Date>;
  };
  inventory_operations: {
    id: Generated<string>;
    idempotency_key: string;
    type: "receive" | "transfer" | "assemble" | "disassemble" | "adjust" | "cycle_count";
    status: Defaulted<"pending" | "committed" | "conflict" | "void">;
    user_id: Defaulted<string | null>;
    note: Defaulted<string | null>;
    metadata: Defaulted<unknown>;
    created_at: Generated<Date>;
  };
  inventory_ledger: {
    id: Generated<string>;
    operation_id: string;
    part_id: string;
    location_id: string;
    quantity_delta: string;
    unit_cost_snapshot: Defaulted<string>;
    action: "receive" | "transfer" | "assemble" | "disassemble" | "adjust" | "cycle_count";
    created_at: Generated<Date>;
  };
  sync_events: {
    id: Generated<number>;
    topic: string;
    payload: unknown;
    created_at: Generated<Date>;
  };
}

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL ?? "postgres://inventory:inventory_dev_password@localhost:5432/inventory2026"
});

export const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool })
});

export async function emitSyncEvent(topic: string, payload: unknown) {
  await db
    .insertInto("sync_events")
    .values({ topic, payload: sql`${JSON.stringify(payload)}::jsonb` })
    .execute();
}

export function toNumber(value: string | number | null | undefined) {
  return Number(value ?? 0);
}