import { query } from './db.js';

export interface Idea {
  id: number;
  title: string;
  problem: string;
  solution: string | null;
  category: string;
  author_name: string | null;
  author_city: string | null;
  status: string;
  votes: number;
  comment_count?: number;
  solved_by_product?: string | null;
  created_at: string;
}

export interface ProductSubmission {
  id: number;
  title: string;
  description: string;
  category: string;
  demo_url: string;
  source_url: string | null;
  screenshot_url: string | null;
  solves_ideas: string | null;
  author_name: string;
  author_email: string;
  author_location: string | null;
  author_github: string | null;
  status: string;
  created_at: string;
}

export async function getIdeas(sort: string = 'votes', category?: string): Promise<Idea[]> {
  const orderBy = sort === 'new' ? 'i.created_at DESC' : 'i.votes DESC, i.created_at DESC';
  const categoryFilter = category && category !== 'toate' ? 'AND i.category = $1' : '';
  const params = category && category !== 'toate' ? [category] : [];

  const result = await query<Idea>(`
    SELECT i.*, COALESCE(c.cnt, 0)::int AS comment_count
    FROM platform.ideas i
    LEFT JOIN (SELECT idea_id, COUNT(*) AS cnt FROM platform.comments GROUP BY idea_id) c
      ON c.idea_id = i.id
    WHERE 1=1 ${categoryFilter}
    ORDER BY ${orderBy}
  `, params);
  return result.rows;
}

export async function getIdea(id: number): Promise<Idea | null> {
  const result = await query('SELECT * FROM platform.ideas WHERE id = $1', [id]);
  return result.rows[0] || null;
}

export async function getIdeaComments(ideaId: number) {
  const result = await query(
    'SELECT * FROM platform.comments WHERE idea_id = $1 ORDER BY created_at ASC',
    [ideaId]
  );
  return result.rows;
}

export async function createIdea(data: {
  title: string;
  problem: string;
  solution?: string;
  category?: string;
  author_name?: string;
  author_email?: string;
  author_city?: string;
}): Promise<number> {
  const result = await query(
    `INSERT INTO platform.ideas (title, problem, solution, category, author_name, author_email, author_city)
     VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id`,
    [data.title, data.problem, data.solution || null, data.category || 'general',
     data.author_name || null, data.author_email || null, data.author_city || null]
  );
  return result.rows[0].id;
}

export async function voteIdea(ideaId: number, fingerprint: string): Promise<{ votes: number; alreadyVoted: boolean }> {
  // Try insert vote
  try {
    await query(
      'INSERT INTO platform.votes (idea_id, fingerprint) VALUES ($1, $2)',
      [ideaId, fingerprint]
    );
    // Increment counter
    const result = await query(
      'UPDATE platform.ideas SET votes = votes + 1 WHERE id = $1 RETURNING votes',
      [ideaId]
    );
    return { votes: result.rows[0].votes, alreadyVoted: false };
  } catch {
    // Already voted (unique constraint)
    const result = await query('SELECT votes FROM platform.ideas WHERE id = $1', [ideaId]);
    return { votes: result.rows[0]?.votes || 0, alreadyVoted: true };
  }
}

export async function addComment(ideaId: number, authorName: string, content: string) {
  await query(
    'INSERT INTO platform.comments (idea_id, author_name, content) VALUES ($1, $2, $3)',
    [ideaId, authorName || 'Anonim', content]
  );
}

export async function getStats() {
  const result = await query(`
    SELECT
      (SELECT COUNT(*) FROM platform.ideas)::int AS total_ideas,
      (SELECT COUNT(*) FROM platform.ideas WHERE status = 'in lucru')::int AS in_progress,
      (SELECT COUNT(*) FROM platform.ideas WHERE status = 'live')::int AS live,
      (SELECT SUM(votes) FROM platform.ideas)::int AS total_votes
  `);
  return result.rows[0];
}

export async function getIdeasByVoteThreshold(threshold: number): Promise<Idea[]> {
  const result = await query<Idea>(`
    SELECT i.*, COALESCE(c.cnt, 0)::int AS comment_count
    FROM platform.ideas i
    LEFT JOIN (SELECT idea_id, COUNT(*) AS cnt FROM platform.comments GROUP BY idea_id) c
      ON c.idea_id = i.id
    WHERE i.votes >= $1
    ORDER BY i.votes DESC
  `, [threshold]);
  return result.rows;
}

export async function getTopIdeas(limit: number): Promise<Idea[]> {
  const result = await query<Idea>(`
    SELECT i.*, COALESCE(c.cnt, 0)::int AS comment_count
    FROM platform.ideas i
    LEFT JOIN (SELECT idea_id, COUNT(*) AS cnt FROM platform.comments GROUP BY idea_id) c
      ON c.idea_id = i.id
    ORDER BY i.votes DESC, i.created_at DESC
    LIMIT $1
  `, [limit]);
  return result.rows;
}

export async function getIdeasForProduct(productSlug: string): Promise<Idea[]> {
  const result = await query<Idea>(`
    SELECT i.*, COALESCE(c.cnt, 0)::int AS comment_count
    FROM platform.ideas i
    LEFT JOIN (SELECT idea_id, COUNT(*) AS cnt FROM platform.comments GROUP BY idea_id) c
      ON c.idea_id = i.id
    WHERE i.solved_by_product = $1
    ORDER BY i.votes DESC
  `, [productSlug]);
  return result.rows;
}

export async function submitProductIdea(data: {
  title: string;
  description: string;
  category: string;
  demo_url: string;
  source_url?: string | null;
  screenshot_url?: string | null;
  solves_ideas?: string | null;
  author_name: string;
  author_email: string;
  author_location?: string | null;
  author_github?: string | null;
}): Promise<number> {
  // Create table if not exists (idempotent)
  await query(`
    CREATE TABLE IF NOT EXISTS platform.product_submissions (
      id SERIAL PRIMARY KEY,
      title VARCHAR(200) NOT NULL,
      description TEXT NOT NULL,
      category VARCHAR(50) NOT NULL,
      demo_url TEXT NOT NULL,
      source_url TEXT,
      screenshot_url TEXT,
      solves_ideas TEXT,
      author_name VARCHAR(200) NOT NULL,
      author_email VARCHAR(200) NOT NULL,
      author_location VARCHAR(200),
      author_github VARCHAR(200),
      status VARCHAR(50) DEFAULT 'pending',
      created_at TIMESTAMPTZ DEFAULT NOW()
    )
  `);

  const result = await query(
    `INSERT INTO platform.product_submissions
      (title, description, category, demo_url, source_url, screenshot_url, solves_ideas, author_name, author_email, author_location, author_github)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
     RETURNING id`,
    [
      data.title,
      data.description,
      data.category,
      data.demo_url,
      data.source_url || null,
      data.screenshot_url || null,
      data.solves_ideas || null,
      data.author_name,
      data.author_email,
      data.author_location || null,
      data.author_github || null,
    ]
  );
  return result.rows[0].id;
}
