Database Schema, Turso Integration, and Vector Storage

-- Database Schema for TenderCheck AI

-- 1. Users table
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    name TEXT NOT NULL,
    company TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. Tenders (Analyses) table
CREATE TABLE IF NOT EXISTS tenders (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    title TEXT NOT NULL,
    status TEXT NOT NULL, -- 'PENDING', 'COMPLETED', 'FAILED'
    document_url TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 3. Requirements table (linked to a Tender)
CREATE TABLE IF NOT EXISTS requirements (
    id TEXT PRIMARY KEY,
    tender_id TEXT NOT NULL,
    text TEXT NOT NULL,
    type TEXT NOT NULL, -- 'MANDATORY', 'OPTIONAL'
    confidence REAL DEFAULT 1.0,
    keywords TEXT, -- Store as JSON string or comma-separated
    page_number INTEGER,
    snippet TEXT,
    embedding BLOB, -- Vector embedding for semantic search (768 dimensions)
    FOREIGN KEY (tender_id) REFERENCES tenders(id) ON DELETE CASCADE
);

-- 4. Validation Results (linked to a Tender)
CREATE TABLE IF NOT EXISTS validation_results (
    id TEXT PRIMARY KEY,
    tender_id TEXT NOT NULL,
    status TEXT NOT NULL, -- 'MET', 'NOT_MET'
    message TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tender_id) REFERENCES tenders(id) ON DELETE CASCADE
);

-- 5. Decision Log (Audit Trail)
CREATE TABLE IF NOT EXISTS decision_logs (
    id TEXT PRIMARY KEY,
    tender_id TEXT NOT NULL,
    requirement_id TEXT NOT NULL,
    prompt_used TEXT,
    ai_response TEXT,
    model_version TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tender_id) REFERENCES tenders(id) ON DELETE CASCADE
);

-- 6. Industry Presets for Scope Validation
CREATE TABLE IF NOT EXISTS industry_presets (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    positive_keywords TEXT NOT NULL, -- JSON array
    negative_keywords TEXT NOT NULL, -- JSON array
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

// ITenderRepository.ts
export interface ITenderRepository {
  save(tender: TenderAnalysis): Promise<void>;
  findById(id: string): Promise<TenderAnalysis | null>;
  findByUserId(userId: string): Promise<TenderAnalysis[]>;
  delete(id: string): Promise<void>;
}

// UserRepository.ts
export interface UserRepository {
  findByEmail(email: string): Promise<User | null>;
  save(user: User): Promise<void>;
  findById(id: string): Promise<User | null>;
}

// Serialize Float32Array → Buffer for SQLite BLOB
serializeEmbedding(embedding: Float32Array): Buffer {
  return Buffer.from(embedding.buffer);
}

// Deserialize Buffer from SQLite → Float32Array
deserializeEmbedding(buffer: Buffer): Float32Array {
  if (!buffer || !buffer.buffer || buffer.length === 0) {
    return new Float32Array(this.dimensions);
  }
  return new Float32Array(
    buffer.buffer,
    buffer.byteOffset || 0,
    buffer.length / Float32Array.BYTES_PER_ELEMENT,
  );
}

// TursoDatabase.ts
export class TursoDatabase {
  private static instance: Client | null = null;

  static getInstance(): Client {
    if (!TursoDatabase.instance) {
      const url = process.env.TURSO_DB_URL;
      const authToken = process.env.TURSO_AUTH_TOKEN;

      if (!url) {
        throw new Error(
          "🚨 TURSO_DB_URL is missing. Please check your .env file.",
        );
      }

      if (url.startsWith("libsql://") && !authToken) {
        throw new Error("🚨 TURSO_AUTH_TOKEN is required for remote Turso DB.");
      }

      // Force HTTP for serverless stability — WebSocket can be unstable
      const safeUrl = url.replace("libsql://", "https://");

      TursoDatabase.instance = createClient({
        url: safeUrl,
        ...(authToken ? { authToken } : {}),
      } as any);
    }
    return TursoDatabase.instance;
  }
}