import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
import type { ActivityEntry } from './schema';
import { eq, and, desc, asc, sql } from 'drizzle-orm';
export { and };
import { PILOT_TOKEN_ALLOWANCE, PILOT_DURATION_MS } from './constants';

// PostgreSQL connection — uses env vars, with safe dev defaults.
// In production the app fails fast if POSTGRES_DB is not explicitly set.
const isProd = process.env.NODE_ENV === 'production';
const user = process.env.POSTGRES_USER || (isProd ? (() => { throw new Error('POSTGRES_USER is required'); })() : 'postgres');
const password = process.env.POSTGRES_PASSWORD || '';
const host = process.env.POSTGRES_HOST || (isProd ? (() => { throw new Error('POSTGRES_HOST is required'); })() : 'localhost');
const port = process.env.POSTGRES_PORT || '5432';
const database = process.env.POSTGRES_DB || (isProd ? (() => { throw new Error('POSTGRES_DB is required'); })() : 'scriba');
const connectionString = password
  ? `postgres://${user}:${password}@${host}:${port}/${database}`
  : `postgres://${user}@${host}:${port}/${database}`;

const client = postgres(connectionString, { max: 10 });
const db = drizzle(client, { schema });

export { db, schema };

// Database helper functions
export const dbHelpers = {
  // Get all projects
  getAllProjects: async () => {
    const projects = await db.query.projects.findMany({
      orderBy: [desc(schema.projects.createdAt)]
    });
    return projects;
  },

  // Get project by ID
  getProject: async (id: string) => {
    const project = await db.query.projects.findFirst({
      where: eq(schema.projects.id, id)
    });
    return project;
  },

  // Create new project
  createProject: async (project: {
    id: string;
    name: string;
    description: string;
    sourceLanguage: string;
    targetLanguage: string;
    repoUrl?: string;
    tags?: string[];
    team?: schema.TeamMember[];
    userId: string;
    status?: string;
    config?: schema.ProjectConfig;
  }) => {
    const newProject = await db.insert(schema.projects).values({
      id: project.id,
      name: project.name,
      description: project.description,
      sourceLanguage: project.sourceLanguage,
      targetLanguage: project.targetLanguage,
      repoUrl: project.repoUrl,
      tags: project.tags || [],
      team: project.team || [],
      activity: [{
        id: `act-${Date.now()}`,
        timestamp: new Date().toISOString(),
        action: 'Conversion created',
        user: 'System'
      }],
      status: project.status || 'draft',
      config: project.config || {},
      maxReachedStep: 0,
      currentStep: 0,
      userId: project.userId
    }).returning();
    
    return newProject[0];
  },

  // Update project progress
  updateProjectProgress: async (id: string, updates: {
    maxReachedStep?: number;
    currentStep?: number;
    status?: string;
    repoUrl?: string;
    totalFiles?: number;
    totalLines?: number;
    convertedFiles?: number;
    accuracy?: number;
    testCoverage?: number;
    config?: schema.ProjectConfig;
    tokensUsed?: number;
    completedAt?: Date | null;
    approvedEstimatedTokens?: number | null;
    approvedGrossEurCents?: number | null;
    approvedNetEurCents?: number | null;
    approvedCreditsTokensApplied?: number | null;
    costApprovedAt?: Date | null;
  }) => {
    const result = await db.update(schema.projects)
      .set({
        ...updates,
        updatedAt: new Date()
      })
      .where(eq(schema.projects.id, id))
      .returning();
    
    return result[0];
  },

  // Record step progress
  recordStepProgress: async (projectId: string, stepNumber: number, stepName: string, status: string, metadata?: schema.StepMetadata) => {
    // Check if step record exists
    const existing = await db.query.projectSteps.findFirst({
      where: and(
        eq(schema.projectSteps.projectId, projectId),
        eq(schema.projectSteps.stepNumber, stepNumber)
      )
    });
    
    if (existing) {
      const result = await db.update(schema.projectSteps)
        .set({
          status,
          completedAt: status === 'completed' ? new Date() : null,
          metadata: metadata || existing.metadata
        })
        .where(and(
          eq(schema.projectSteps.projectId, projectId),
          eq(schema.projectSteps.stepNumber, stepNumber)
        ))
        .returning();
      return result[0];
    } else {
      const result = await db.insert(schema.projectSteps).values({
        projectId,
        stepNumber,
        stepName,
        status,
        startedAt: new Date(),
        metadata: metadata || {}
      }).returning();
      return result[0];
    }
  },

  // Get step history for a project
  getProjectSteps: async (projectId: string) => {
    const steps = await db.query.projectSteps.findMany({
      where: eq(schema.projectSteps.projectId, projectId),
      orderBy: [asc(schema.projectSteps.stepNumber)]
    });
    return steps;
  },

  // Delete project
  deleteProject: async (id: string) => {
    const result = await db.delete(schema.projects)
      .where(eq(schema.projects.id, id))
      .returning();
    return result[0];
  },

  // Get GitHub OAuth access token for a user
  getGithubToken: async (userId: string) => {
    return db.query.accounts.findFirst({
      where: and(
        eq(schema.accounts.userId, userId),
        eq(schema.accounts.providerId, 'github')
      )
    });
  },

  /**
   * Flatten the JSONB `activity` arrays across all of a user's projects into a
   * single feed, newest-first. Used by the notification panel in the UI.
   *
   * Silently skips malformed entries (missing id/timestamp/action) and logs a
   * warning so data-quality issues surface in server logs without breaking the feed.
   */
  getActivityFeedForUser: async (userId: string, limit = 40) => {
    const projs = await db.query.projects.findMany({
      where: eq(schema.projects.userId, userId),
      columns: { id: true, name: true, activity: true },
    });
    const rows: {
      id: string;
      projectId: string;
      projectName: string;
      action: string;
      detail?: string;
      user?: string;
      timestamp: string;
    }[] = [];
    for (const p of projs) {
      const raw = p.activity;
      const activity = Array.isArray(raw) ? raw : [];
      for (const a of activity) {
        if (!a?.id || !a?.timestamp || !a?.action) {
          console.warn('[db] malformed activity entry skipped', { projectId: p.id, entry: a });
          continue;
        }
        rows.push({
          id: `${p.id}:${a.id}`,
          projectId: p.id,
          projectName: p.name,
          action: a.action,
          detail: a.detail,
          user: a.user,
          timestamp: a.timestamp,
        });
      }
    }
    rows.sort((x, y) => new Date(y.timestamp).getTime() - new Date(x.timestamp).getTime());
    return rows.slice(0, limit);
  },

  /**
   * Append a coarse lifecycle event to a project's JSONB `activity` array.
   * Capped at 50 entries (oldest are dropped). Use `saveLog` for verbose
   * per-line engine output — `addActivity` is for human-readable milestones.
   *
   * @param projectId  Target project
   * @param action     Short label, e.g. "Started Migration" or "Completed Verification"
   * @param detail     Optional longer description shown in the activity feed
   * @param user       Actor label; defaults to 'System' for automated events
   */
  addActivity: async (projectId: string, action: string, detail?: string, user: string = 'System') => {
    const project = await dbHelpers.getProject(projectId);
    if (!project) return;

    const entry: ActivityEntry = {
      id: `act-${Date.now()}`,
      timestamp: new Date().toISOString(),
      action,
      user,
      detail,
    };

    const activity = Array.isArray(project.activity) ? project.activity : [];
    const trimmedActivity = [entry, ...activity].slice(0, 50);

    const result = await db.update(schema.projects)
      .set({
        activity: trimmedActivity,
        updatedAt: new Date()
      })
      .where(eq(schema.projects.id, projectId))
      .returning();

    return result[0];
  },

  /** Activate the free pilot program for a user (one-time). Returns `{ alreadyActivated: true }` if already active. */
  activatePilot: async (userId: string) => {
    const user = await db.query.users.findFirst({
      where: eq(schema.users.id, userId),
      columns: { id: true, pilotActivatedAt: true },
    });
    if (!user) return null;
    if (user.pilotActivatedAt) return { alreadyActivated: true as const };
    const result = await db.update(schema.users)
      .set({ pilotActivatedAt: new Date(), updatedAt: new Date() })
      .where(eq(schema.users.id, userId))
      .returning();
    return result[0];
  },

  /** Return pilot status for a user: token consumption, expiry, days remaining. */
  getPilotStatus: async (userId: string) => {
    const PILOT_ALLOWANCE = PILOT_TOKEN_ALLOWANCE;

    const user = await db.query.users.findFirst({
      where: eq(schema.users.id, userId),
      columns: { pilotActivatedAt: true },
    });

    if (!user?.pilotActivatedAt) {
      return { activated: false, isActive: false, tokensUsed: 0, tokensRemaining: PILOT_ALLOWANCE };
    }

    const activatedAt = user.pilotActivatedAt;
    const expiresAt = new Date(activatedAt.getTime() + PILOT_DURATION_MS);
    const isActive = new Date() < expiresAt;
    const daysRemaining = isActive
      ? Math.max(0, Math.ceil((expiresAt.getTime() - Date.now()) / (1000 * 60 * 60 * 24)))
      : 0;

    const projects = await db.query.projects.findMany({
      where: eq(schema.projects.userId, userId),
      columns: { tokensUsed: true },
    });
    const tokensUsed = projects.reduce((s, p) => s + (p.tokensUsed ?? 0), 0);

    return {
      activated: true,
      isActive,
      activatedAt: activatedAt.toISOString(),
      expiresAt: expiresAt.toISOString(),
      daysRemaining,
      tokensUsed,
      tokensRemaining: Math.max(0, PILOT_ALLOWANCE - tokensUsed),
    };
  },

  /** Increment tokensUsed for a project (additive — call with the delta each migration step). */
  addTokens: async (projectId: string, delta: number) => {
    const result = await db.update(schema.projects)
      .set({ tokensUsed: sql`COALESCE(${schema.projects.tokensUsed}, 0) + ${delta}`, updatedAt: new Date() })
      .where(eq(schema.projects.id, projectId))
      .returning();
    return result[0];
  },

  /**
   * Persist a single engine log line to `project_logs`.
   * Called fire-and-forget from MigrationFlow — errors are swallowed on the
   * client side so a DB hiccup never interrupts the live log stream.
   *
   * `level`   must be 'info' | 'warn' | 'error' | 'debug' (validated at the API layer)
   * `time`    wall-clock string HH:mm:ss.mmm (local time, set by the client)
   * `message` already sanitized by sanitizeEngineLogForDisplay before this call
   */
  saveLog: async (projectId: string, time: string, level: string, message: string) => {
    const result = await db.insert(schema.projectLogs).values({ projectId, time, level, message }).returning();
    return result[0];
  },

  /**
   * Fetch persisted log lines for a project, oldest-first (matches display order).
   * Capped at `limit` rows (default 2000) to prevent unbounded result sets on
   * long-running migrations.
   */
  getProjectLogs: async (projectId: string, limit = 2000) => {
    return db.query.projectLogs.findMany({
      where: eq(schema.projectLogs.projectId, projectId),
      orderBy: [asc(schema.projectLogs.id)],
      limit,
    });
  },

  /** Remove all log lines for a project. Called at the start of each new conversion run. */
  clearProjectLogs: async (projectId: string) => {
    await db.delete(schema.projectLogs).where(eq(schema.projectLogs.projectId, projectId));
  },

  insertNotificationOutboxBatch: async (
    rows: Array<{
      id: string;
      userId: string;
      projectId: string;
      channel: string;
      event: string;
      payload: Record<string, unknown>;
    }>,
  ) => {
    if (rows.length === 0) return 0;
    await db.insert(schema.notificationOutbox).values(
      rows.map((r) => ({
        id: r.id,
        userId: r.userId,
        projectId: r.projectId,
        channel: r.channel,
        event: r.event,
        status: 'pending',
        payload: r.payload,
      })),
    );
    return rows.length;
  },
};

export default db;