import { dbQuery, dbGet, dbRun, getDb } from '../config/db.js';

// Ensure DB is initialized before any query
async function ensureDb() {
  await getDb();
}

export async function getProjects(req, res) {
  try {
    await ensureDb();
    const projects = await dbQuery('SELECT * FROM projects ORDER BY id DESC');
    res.status(200).json({ success: true, count: projects.length, data: projects });
  } catch (error) {
    console.error('getProjects error:', error);
    res.status(500).json({ success: false, message: 'Server error retrieving projects' });
  }
}

export async function getProjectById(req, res) {
  try {
    await ensureDb();
    const project = await dbGet('SELECT * FROM projects WHERE id = ?', [req.params.id]);
    if (!project) return res.status(404).json({ success: false, message: 'Project not found' });
    res.status(200).json({ success: true, data: project });
  } catch (error) {
    console.error('getProjectById error:', error);
    res.status(500).json({ success: false, message: 'Server error' });
  }
}

export async function createProject(req, res) {
  try {
    await ensureDb();
    const { title, description, category, status, tech_stack, github_url, demo_url, image_url } = req.body;
    if (!title || !description || !category || !status || !tech_stack) {
      return res.status(400).json({ success: false, message: 'Missing required fields: title, description, category, status, tech_stack' });
    }
    const result = await dbRun(
      `INSERT INTO projects (title, description, category, status, tech_stack, github_url, demo_url, image_url) VALUES (?,?,?,?,?,?,?,?)`,
      [title, description, category, status, tech_stack, github_url || null, demo_url || null, image_url || null]
    );
    const newProject = await dbGet('SELECT * FROM projects WHERE id = ?', [result.lastID]);
    res.status(201).json({ success: true, message: 'Project created', data: newProject });
  } catch (error) {
    console.error('createProject error:', error);
    res.status(500).json({ success: false, message: 'Server error creating project' });
  }
}

export async function updateProject(req, res) {
  try {
    await ensureDb();
    const { id } = req.params;
    const project = await dbGet('SELECT * FROM projects WHERE id = ?', [id]);
    if (!project) return res.status(404).json({ success: false, message: 'Project not found' });

    const { title, description, category, status, tech_stack, github_url, demo_url, image_url } = req.body;
    await dbRun(
      `UPDATE projects SET title=?, description=?, category=?, status=?, tech_stack=?, github_url=?, demo_url=?, image_url=? WHERE id=?`,
      [
        title || project.title,
        description || project.description,
        category || project.category,
        status || project.status,
        tech_stack || project.tech_stack,
        github_url !== undefined ? github_url : project.github_url,
        demo_url !== undefined ? demo_url : project.demo_url,
        image_url !== undefined ? image_url : project.image_url,
        id
      ]
    );
    const updated = await dbGet('SELECT * FROM projects WHERE id = ?', [id]);
    res.status(200).json({ success: true, message: 'Project updated', data: updated });
  } catch (error) {
    console.error('updateProject error:', error);
    res.status(500).json({ success: false, message: 'Server error updating project' });
  }
}

export async function deleteProject(req, res) {
  try {
    await ensureDb();
    const { id } = req.params;
    const project = await dbGet('SELECT * FROM projects WHERE id = ?', [id]);
    if (!project) return res.status(404).json({ success: false, message: 'Project not found' });
    await dbRun('DELETE FROM projects WHERE id = ?', [id]);
    res.status(200).json({ success: true, message: 'Project deleted' });
  } catch (error) {
    console.error('deleteProject error:', error);
    res.status(500).json({ success: false, message: 'Server error deleting project' });
  }
}