import crypto from "node:crypto";
import fs from "node:fs/promises";
import path from "node:path";
import mysql, {
  type Pool,
  type ResultSetHeader,
  type RowDataPacket,
} from "mysql2/promise";

type SqlValue = unknown;

type D1Result<T> = {
  results: T[];
  success: boolean;
  meta: {
    changes: number;
    last_row_id: number;
  };
};

class MariaStatement {
  constructor(
    private readonly pool: Pool,
    readonly sql: string,
    readonly values: SqlValue[] = [],
  ) {}

  bind(...values: SqlValue[]) {
    return new MariaStatement(this.pool, this.sql, values);
  }

  async first<T = Record<string, unknown>>(column?: string): Promise<T | null> {
    const [rows] = await this.pool.execute<RowDataPacket[]>(normalizeSql(this.sql), sqlValues(this.values));
    const row = rows[0] as Record<string, unknown> | undefined;
    if (!row) return null;
    return (column ? row[column] : row) as T;
  }

  async all<T = Record<string, unknown>>(): Promise<D1Result<T>> {
    const [rows] = await this.pool.execute<RowDataPacket[]>(normalizeSql(this.sql), sqlValues(this.values));
    return {
      results: rows as T[],
      success: true,
      meta: { changes: 0, last_row_id: 0 },
    };
  }

  async run(): Promise<D1Result<never>> {
    const [result] = await this.pool.execute<ResultSetHeader>(normalizeSql(this.sql), sqlValues(this.values));
    return {
      results: [],
      success: true,
      meta: {
        changes: result.affectedRows ?? 0,
        last_row_id: result.insertId ?? 0,
      },
    };
  }
}

class MariaDatabase {
  private readonly pool = mysql.createPool({
    host: requiredEnv("DB_HOST", "localhost"),
    port: Number(process.env.DB_PORT || 3306),
    ...(process.env.DB_SOCKET ? { socketPath: process.env.DB_SOCKET } : {}),
    // Build-time route analysis does not have production secrets. Connections
    // are only opened when a request actually executes a query.
    database: process.env.DB_NAME || "pyramidcom",
    user: process.env.DB_USER || "root",
    password: process.env.DB_PASSWORD || "",
    charset: "utf8mb4",
    connectionLimit: Number(process.env.DB_POOL_SIZE || 8),
    enableKeepAlive: true,
    timezone: "Z",
  });

  prepare(sql: string) {
    return new MariaStatement(this.pool, sql);
  }

  async batch(statements: MariaStatement[]) {
    const connection = await this.pool.getConnection();
    try {
      await connection.beginTransaction();
      const results: D1Result<never>[] = [];
      for (const statement of statements) {
        const [result] = await connection.execute<ResultSetHeader>(
          normalizeSql(statement.sql),
          sqlValues(statement.values),
        );
        results.push({
          results: [],
          success: true,
          meta: {
            changes: result.affectedRows ?? 0,
            last_row_id: result.insertId ?? 0,
          },
        });
      }
      await connection.commit();
      return results;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
}

type StoredMetadata = {
  httpMetadata?: { contentType?: string };
  customMetadata?: Record<string, string>;
};

class LocalObject {
  readonly httpEtag: string;

  constructor(
    readonly body: ArrayBuffer,
    readonly httpMetadata: StoredMetadata["httpMetadata"],
    readonly customMetadata: StoredMetadata["customMetadata"],
  ) {
    this.httpEtag = `"${crypto.createHash("sha256").update(new Uint8Array(body)).digest("hex")}"`;
  }

  writeHttpMetadata(headers: Headers) {
    if (this.httpMetadata?.contentType) {
      headers.set("content-type", this.httpMetadata.contentType);
    }
  }
}

class LocalBucket {
  private readonly root = path.resolve(
    /* turbopackIgnore: true */
    requiredEnv("UPLOAD_DIR", path.join(process.cwd(), "data", "uploads")),
  );

  async put(
    key: string,
    value: ReadableStream | ArrayBuffer | Uint8Array,
    metadata: StoredMetadata = {},
  ) {
    const target = this.resolveKey(key);
    const sidecar = `${target}.metadata.json`;
    const temporary = `${target}.${crypto.randomUUID()}.tmp`;
    const bytes = await toBytes(value);
    await fs.mkdir(path.dirname(target), { recursive: true });
    await fs.writeFile(temporary, bytes, { flag: "wx", mode: 0o640 });
    await fs.rename(temporary, target);
    await fs.writeFile(sidecar, JSON.stringify(metadata), { encoding: "utf8", mode: 0o640 });
  }

  async get(key: string) {
    const target = this.resolveKey(key);
    try {
      const [body, metadata] = await Promise.all([
        fs.readFile(target),
        fs.readFile(`${target}.metadata.json`, "utf8")
          .then((value) => JSON.parse(value) as StoredMetadata)
          .catch(() => ({} as StoredMetadata)),
      ]);
      const arrayBuffer = body.buffer.slice(body.byteOffset, body.byteOffset + body.byteLength) as ArrayBuffer;
      return new LocalObject(arrayBuffer, metadata.httpMetadata, metadata.customMetadata);
    } catch (error) {
      if (isMissingFile(error)) return null;
      throw error;
    }
  }

  async delete(key: string) {
    const target = this.resolveKey(key);
    await Promise.all([
      fs.rm(target, { force: true }),
      fs.rm(`${target}.metadata.json`, { force: true }),
    ]);
  }

  private resolveKey(key: string) {
    const normalized = key.replaceAll("\\", "/").replace(/^\/+/, "");
    if (!normalized || normalized.split("/").some((segment) => segment === "..")) {
      throw new Error("Invalid storage key.");
    }
    const target = path.resolve(this.root, normalized);
    if (target !== this.root && !target.startsWith(`${this.root}${path.sep}`)) {
      throw new Error("Invalid storage key.");
    }
    return target;
  }
}

function normalizeSql(sql: string) {
  return sql.replace(/\bINSERT\s+OR\s+IGNORE\b/gi, "INSERT IGNORE");
}

function sqlValues(values: unknown[]) {
  return values.map((value): string | number | boolean | null | Date | Buffer => {
    if (value == null) return null;
    if (typeof value === "string" || typeof value === "number" ||
        typeof value === "boolean" || value instanceof Date || Buffer.isBuffer(value)) return value;
    if (value instanceof Uint8Array) return Buffer.from(value);
    return String(value);
  });
}

function requiredEnv(name: string, fallback?: string) {
  const value = process.env[name] || fallback;
  if (!value) throw new Error(`Missing required environment variable: ${name}`);
  return value;
}

async function toBytes(value: ReadableStream | ArrayBuffer | Uint8Array) {
  if (value instanceof Uint8Array) return value;
  if (value instanceof ArrayBuffer) return new Uint8Array(value);
  return new Uint8Array(await new Response(value).arrayBuffer());
}

function isMissingFile(error: unknown): error is NodeJS.ErrnoException {
  return error instanceof Error && "code" in error && error.code === "ENOENT";
}

export const env = {
  DB: new MariaDatabase(),
  MEDIA: new LocalBucket(),
};