podzahr/lib/db.ts

import { Database } from "bun:sqlite";
import path from "path";
import { albums as initialAlbums } from "./data";
import { Album, Purchase } from "./types";

// Database path
const dbPath = path.join(process.cwd(), "data", "parsa.db");

// Initialize database
let db: any;

function createDatabase() {
  // Use Bun's native SQLite
  const { Database } = require("bun:sqlite");
  return new Database(dbPath, { create: true });
}

export function getDatabase(): any {
  if (!db) {
    // Create data directory if it doesn't exist
    const fs = require("fs");
    const dataDir = path.join(process.cwd(), "data");
    if (!fs.existsSync(dataDir)) {
      fs.mkdirSync(dataDir, { recursive: true });
    }

    db = createDatabase();
    db.exec("PRAGMA journal_mode = WAL");
    initializeDatabase();
  }
  return db;
}

function initializeDatabase() {
  // Create albums table
  db.exec(`
    CREATE TABLE IF NOT EXISTS albums (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      coverImage TEXT NOT NULL,
      year INTEGER NOT NULL,
      genre TEXT NOT NULL,
      description TEXT NOT NULL,
      price REAL NOT NULL,
      tag TEXT NOT NULL DEFAULT 'Album',
      format TEXT NOT NULL DEFAULT 'mp3',
      bitrate TEXT NOT NULL DEFAULT '320kbps',
      songs TEXT NOT NULL,
      createdAt INTEGER DEFAULT (strftime('%s', 'now')),
      updatedAt INTEGER DEFAULT (strftime('%s', 'now'))
    )
  `);

  // Create purchases table
  db.exec(`
    CREATE TABLE IF NOT EXISTS purchases (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      albumId TEXT NOT NULL,
      transactionId TEXT NOT NULL UNIQUE,
      customerName TEXT,
      email TEXT,
      phoneNumber TEXT,
      txReceipt TEXT,
      purchaseDate INTEGER NOT NULL,
      approvalStatus TEXT DEFAULT 'pending',
      paymentMethod TEXT DEFAULT 'card-to-card',
      createdAt INTEGER DEFAULT (strftime('%s', 'now')),
      FOREIGN KEY (albumId) REFERENCES albums(id) ON DELETE CASCADE
    )
  `);

  // Create payment authorities table for ZarinPal tracking
  db.exec(`
    CREATE TABLE IF NOT EXISTS payment_authorities (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      authority TEXT NOT NULL UNIQUE,
      albumId TEXT NOT NULL,
      amount INTEGER NOT NULL,
      customerName TEXT,
      email TEXT,
      phoneNumber TEXT,
      status TEXT DEFAULT 'pending',
      refId TEXT,
      cardPan TEXT,
      fee INTEGER,
      createdAt INTEGER DEFAULT (strftime('%s', 'now')),
      verifiedAt INTEGER,
      FOREIGN KEY (albumId) REFERENCES albums(id) ON DELETE CASCADE
    )
  `);

  // Add columns if they don't exist (migration)
  try {
    db.exec(`ALTER TABLE purchases ADD COLUMN approvalStatus TEXT DEFAULT 'pending'`);
  } catch (e) {
    // Column already exists
  }
  try {
    db.exec(`ALTER TABLE purchases ADD COLUMN paymentMethod TEXT DEFAULT 'card-to-card'`);
  } catch (e) {
    // Column already exists
  }

  // Create indexes
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_purchases_albumId ON purchases(albumId);
    CREATE INDEX IF NOT EXISTS idx_purchases_transactionId ON purchases(transactionId);
    CREATE INDEX IF NOT EXISTS idx_purchases_approvalStatus ON purchases(approvalStatus);
    CREATE INDEX IF NOT EXISTS idx_payment_authorities_authority ON payment_authorities(authority);
    CREATE INDEX IF NOT EXISTS idx_payment_authorities_status ON payment_authorities(status);
  `);

  // Seed initial data if albums table is empty
  const count = db.prepare("SELECT COUNT(*) as count FROM albums").get() as {
    count: number;
  };
  if (count.count === 0) {
    seedInitialData();
  }
}

function seedInitialData() {
  const insert = db.prepare(`
    INSERT INTO albums (id, title, coverImage, year, genre, description, price, tag, format, bitrate, songs)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  const insertMany = db.transaction((albums: Album[]) => {
    for (const album of albums) {
      insert.run(
        album.id,
        album.title,
        album.coverImage,
        album.year,
        album.genre,
        album.description,
        album.price,
        album.tag,
        album.format,
        album.bitrate,
        JSON.stringify(album.songs),
      );
    }
  });

  insertMany(initialAlbums);
}

// Album operations
export const albumDb = {
  getAll(): Album[] {
    const db = getDatabase();
    const rows = db.prepare("SELECT * FROM albums ORDER BY year DESC").all();
    return rows.map((row: any) => ({
      ...row,
      songs: JSON.parse(row.songs),
    }));
  },

  getById(id: string): Album | null {
    const db = getDatabase();
    const row = db.prepare("SELECT * FROM albums WHERE id = ?").get(id) as any;
    if (!row) return null;
    return {
      ...row,
      songs: JSON.parse(row.songs),
    };
  },

  create(album: Album): void {
    const db = getDatabase();
    db.prepare(
      `
      INSERT INTO albums (id, title, coverImage, year, genre, description, price, tag, format, bitrate, songs)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `,
    ).run(
      album.id,
      album.title,
      album.coverImage,
      album.year,
      album.genre,
      album.description,
      album.price,
      album.tag,
      album.format,
      album.bitrate,
      JSON.stringify(album.songs),
    );
  },

  update(id: string, album: Album): void {
    const db = getDatabase();
    db.prepare(
      `
      UPDATE albums
      SET title = ?, coverImage = ?, year = ?, genre = ?, description = ?, price = ?, tag = ?, format = ?, bitrate = ?, songs = ?, updatedAt = strftime('%s', 'now')
      WHERE id = ?
    `,
    ).run(
      album.title,
      album.coverImage,
      album.year,
      album.genre,
      album.description,
      album.price,
      album.tag,
      album.format,
      album.bitrate,
      JSON.stringify(album.songs),
      id,
    );
  },

  delete(id: string): void {
    const db = getDatabase();
    db.prepare("DELETE FROM albums WHERE id = ?").run(id);
  },
};

// Purchase operations
export const purchaseDb = {
  getAll(): Purchase[] {
    const db = getDatabase();
    const rows = db
      .prepare("SELECT * FROM purchases ORDER BY purchaseDate DESC")
      .all();
    return rows.map((row: any) => ({
      id: row.id,
      albumId: row.albumId,
      transactionId: row.transactionId,
      customerName: row.customerName,
      email: row.email,
      phoneNumber: row.phoneNumber,
      txReceipt: row.txReceipt,
      purchaseDate: new Date(row.purchaseDate),
      approvalStatus: row.approvalStatus,
      paymentMethod: row.paymentMethod,
    }));
  },

  getByAlbumId(albumId: string): Purchase[] {
    const db = getDatabase();
    const rows = db
      .prepare(
        "SELECT * FROM purchases WHERE albumId = ? ORDER BY purchaseDate DESC",
      )
      .all(albumId);
    return rows.map((row: any) => ({
      id: row.id,
      albumId: row.albumId,
      transactionId: row.transactionId,
      customerName: row.customerName,
      email: row.email,
      phoneNumber: row.phoneNumber,
      txReceipt: row.txReceipt,
      purchaseDate: new Date(row.purchaseDate),
      approvalStatus: row.approvalStatus,
      paymentMethod: row.paymentMethod,
    }));
  },

  getByTransactionId(transactionId: string): Purchase | null {
    const db = getDatabase();
    const row = db
      .prepare("SELECT * FROM purchases WHERE transactionId = ?")
      .get(transactionId) as any;
    if (!row) return null;
    return {
      id: row.id,
      albumId: row.albumId,
      transactionId: row.transactionId,
      customerName: row.customerName,
      email: row.email,
      phoneNumber: row.phoneNumber,
      txReceipt: row.txReceipt,
      purchaseDate: new Date(row.purchaseDate),
      approvalStatus: row.approvalStatus,
      paymentMethod: row.paymentMethod,
    };
  },

  create(purchase: Omit<Purchase, "id">): Purchase {
    const db = getDatabase();
    const result = db
      .prepare(
        `
      INSERT INTO purchases (albumId, transactionId, customerName, email, phoneNumber, txReceipt, purchaseDate, approvalStatus, paymentMethod)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `,
      )
      .run(
        purchase.albumId,
        purchase.transactionId,
        purchase.customerName || null,
        purchase.email || null,
        purchase.phoneNumber || null,
        purchase.txReceipt || null,
        purchase.purchaseDate instanceof Date
          ? purchase.purchaseDate.getTime()
          : purchase.purchaseDate,
        purchase.approvalStatus || 'pending',
        purchase.paymentMethod || 'card-to-card',
      );

    return {
      id: result.lastInsertRowid as number,
      ...purchase,
    };
  },

  delete(id: number): void {
    const db = getDatabase();
    db.prepare("DELETE FROM purchases WHERE id = ?").run(id);
  },
};