import { NextResponse } from 'next/server';
import { query, execute } from '@/lib/db';
import { getAdminSession } from '@/lib/auth';

let schemaReady = false;

async function ensureSchema() {
  if (schemaReady) return;
  await execute(`
    CREATE TABLE IF NOT EXISTS Conversation (
      id            TEXT PRIMARY KEY,
      visitor_email TEXT NOT NULL,
      visitor_name  TEXT NOT NULL,
      subject       TEXT DEFAULT '',
      status        TEXT DEFAULT 'open',
      created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `).catch(() => {});
  await execute(`
    CREATE TABLE IF NOT EXISTS ConversationMessage (
      id              INTEGER PRIMARY KEY AUTOINCREMENT,
      conversation_id TEXT NOT NULL,
      sender_type     TEXT NOT NULL CHECK(sender_type IN ('visitor', 'admin')),
      content         TEXT NOT NULL,
      ai_draft        TEXT DEFAULT NULL,
      status          TEXT DEFAULT 'sent',
      created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (conversation_id) REFERENCES Conversation(id)
    )
  `).catch(() => {});
  await execute(`CREATE INDEX IF NOT EXISTS idx_conv_msg_conv_id ON ConversationMessage(conversation_id)`).catch(() => {});
  await execute(`CREATE INDEX IF NOT EXISTS idx_conv_visitor_email ON Conversation(visitor_email)`).catch(() => {});
  await execute(`CREATE INDEX IF NOT EXISTS idx_conv_status ON Conversation(status)`).catch(() => {});
  schemaReady = true;
}

// GET /api/admin/conversations — List all conversations with latest message preview
export async function GET() {
  const session = await getAdminSession();
  if (!session) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
  }

  await ensureSchema();

  try {
    const conversations = await query(`
      SELECT 
        c.id,
        c.visitor_email,
        c.visitor_name,
        c.subject,
        c.status,
        c.created_at,
        c.updated_at,
        (SELECT content FROM ConversationMessage WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1) AS last_message,
        (SELECT created_at FROM ConversationMessage WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1) AS last_message_at,
        (SELECT COUNT(*) FROM ConversationMessage WHERE conversation_id = c.id AND sender_type = 'visitor' AND status = 'sent') AS unread_count
      FROM Conversation c
      ORDER BY c.updated_at DESC
    `) as any[];

    return NextResponse.json({ conversations });
  } catch (err: any) {
    console.error('[conversations] GET error:', err?.message ?? err);
    return NextResponse.json({ error: 'Failed to load conversations.' }, { status: 500 });
  }
}

// POST /api/admin/conversations — Create a new conversation (from existing ContactMessage or manually)
export async function POST(request: Request) {
  const session = await getAdminSession();
  if (!session) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
  }

  await ensureSchema();

  try {
    const body = await request.json();
    const { visitor_email, visitor_name, subject, initial_message } = body;

    if (!visitor_email || !visitor_name) {
      return NextResponse.json({ error: 'Visitor email and name are required.' }, { status: 400 });
    }

    const convId = `conv_${Date.now()}_${Math.random().toString(36).slice(2, 9)}`;

    await execute(
      'INSERT INTO Conversation (id, visitor_email, visitor_name, subject) VALUES (?, ?, ?, ?)',
      [convId, visitor_email, visitor_name, subject || '']
    );

    if (initial_message) {
      await execute(
        'INSERT INTO ConversationMessage (conversation_id, sender_type, content) VALUES (?, ?, ?)',
        [convId, 'visitor', initial_message]
      );
    }

    return NextResponse.json({ success: true, conversation: { id: convId } });
  } catch (err: any) {
    console.error('[conversations] POST error:', err?.message ?? err);
    return NextResponse.json({ error: 'Failed to create conversation.' }, { status: 500 });
  }
}