const express = require('express');
const { Pool } = require('pg');
const redis = require('redis');
const path = require('path');
const multer = require('multer');
const upload = multer({ storage: multer.memoryStorage(), limits: { fileSize: 10 * 1024 * 1024 } });
const jwt = require('jsonwebtoken');
const bcryptjs = require('bcryptjs');
const bcrypt = bcryptjs;

const app = express();
const PORT = process.env.PORT || 3000;
const JWT_SECRET = process.env.JWT_SECRET || 'hireflow-secret-key-change-in-production';
const JWT_REFRESH_SECRET = process.env.JWT_REFRESH_SECRET || 'hireflow-refresh-secret-change-in-production';

app.use(express.json());

// PostgreSQL connection
const pool = new Pool({
  host: process.env.POSTGRES_HOST || 'postgres',
  port: process.env.POSTGRES_PORT || 5432,
  user: process.env.POSTGRES_USER || 'postgres',
  password: process.env.POSTGRES_PASSWORD || 'postgres',
  database: process.env.POSTGRES_DB || 'postgres'
});

// Redis connection
const redisClient = redis.createClient({
  socket: {
    host: process.env.REDIS_HOST || 'redis',
    port: process.env.REDIS_PORT || 6379
  }
});

redisClient.connect().catch(console.error);
redisClient.on('connect', () => console.log('Redis connected'));
redisClient.on('error', (err) => console.error('Redis error:', err));

// Initialize database schema
async function initDb() {
  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS candidates (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255),
        phone VARCHAR(100),
        skills TEXT[],
        experience_years INTEGER,
        education TEXT,
        summary TEXT,
        raw_cv TEXT,
        created_at TIMESTAMP DEFAULT NOW()
      );

      CREATE TABLE IF NOT EXISTS jobs (
        id SERIAL PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        requirements TEXT,
        location VARCHAR(255),
        salary_min INTEGER,
        salary_max INTEGER,
        channels TEXT[],
        status VARCHAR(50) DEFAULT 'draft',
        posted_at TIMESTAMP,
        created_at TIMESTAMP DEFAULT NOW()
      );

      CREATE TABLE IF NOT EXISTS scorecards (
        id SERIAL PRIMARY KEY,
        job_id INTEGER REFERENCES jobs(id),
        candidate_id INTEGER REFERENCES candidates(id),
        interviewer VARCHAR(255) NOT NULL,
        criteria JSONB,
        total_score INTEGER,
        comments TEXT,
        recommendation VARCHAR(50),
        created_at TIMESTAMP DEFAULT NOW()
      );

      CREATE TABLE IF NOT EXISTS job_applications (
        id SERIAL PRIMARY KEY,
        job_id INTEGER REFERENCES jobs(id),
        candidate_id INTEGER REFERENCES candidates(id),
        status VARCHAR(50) DEFAULT 'applied',
        stage VARCHAR(50) DEFAULT 'screening',
        created_at TIMESTAMP DEFAULT NOW(),
        UNIQUE(job_id, candidate_id)
      );

      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        email VARCHAR(255) UNIQUE NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        role VARCHAR(50) NOT NULL DEFAULT 'recruiter',
        agency_id INTEGER,
        is_active BOOLEAN DEFAULT true,
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW(),
        deleted_at TIMESTAMP
      );

      CREATE TABLE IF NOT EXISTS refresh_tokens (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id),
        token_hash VARCHAR(255) NOT NULL,
        expires_at TIMESTAMP NOT NULL,
        revoked_at TIMESTAMP,
        created_at TIMESTAMP DEFAULT NOW()
      );
    `);
    console.log('Database schema initialized');
  } catch (err) {
    console.error('DB init error:', err.message);
  }
}

pool.query('SELECT NOW()', (err, res) => {
  if (err) {
    console.error('PostgreSQL connection error:', err);
  } else {
    console.log('PostgreSQL connected at:', res.rows[0].now);
    initDb();
  }
});

// Simple CV parser using regex patterns
function parseCV(text) {
  const emailMatch = text.match(/[\w.-]+@[\w.-]+\.\w+/);
  const phoneMatch = text.match(/(\+?[\d\s()-]{7,})/);

  // Extract name (first line or after common headers)
  const lines = text.split('\n').filter(l => l.trim());
  const name = lines[0]?.trim() || 'Unknown';

  // Extract skills
  const skillKeywords = ['JavaScript', 'TypeScript', 'React', 'Node.js', 'Python', 'Java', 'SQL',
    'PostgreSQL', 'MongoDB', 'Redis', 'Docker', 'AWS', 'Git', 'HTML', 'CSS', 'REST', 'GraphQL',
    'Vue', 'Angular', 'PHP', 'Ruby', 'Go', 'Rust', 'Swift', 'Kotlin', 'C++', 'C#', 'Agile', 'Scrum'];
  const skills = skillKeywords.filter(skill =>
    text.toLowerCase().includes(skill.toLowerCase())
  );

  // Extract experience years
  const expMatch = text.match(/(\d+)\s*(?:\+\s*)?years?\s*(?:of\s*)?(?:experience|exp)/i);
  const experienceYears = expMatch ? parseInt(expMatch[1]) : null;

  // Extract education
  const eduKeywords = ['Bachelor', 'Master', 'PhD', 'BSc', 'MSc', 'MBA', 'University', 'College'];
  const eduLines = lines.filter(l => eduKeywords.some(k => l.includes(k)));
  const education = eduLines.slice(0, 2).join('; ') || null;

  return {
    name,
    email: emailMatch ? emailMatch[0] : null,
    phone: phoneMatch ? phoneMatch[0].trim() : null,
    skills: skills.length > 0 ? skills : ['General'],
    experience_years: experienceYears,
    education,
    summary: lines.slice(1, 4).join(' ').substring(0, 500) || null
  };
}

// Auth middleware
function authenticateToken(req, res, next) {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'Access token required' });
  try {
    const user = jwt.verify(token, JWT_SECRET);
    req.user = user;
    next();
  } catch (err) {
    return res.status(401).json({ error: 'Invalid or expired token' });
  }
}

function requireRole(...roles) {
  return (req, res, next) => {
    if (!req.user || !roles.includes(req.user.role)) {
      return res.status(403).json({ error: 'Insufficient permissions' });
    }
    next();
  };
}

// POST /auth/register
app.post('/auth/register', async (req, res) => {
  try {
    const { email, password, role } = req.body;
    if (!email || !password) return res.status(400).json({ error: 'Email and password required' });
    const allowedRoles = ['admin', 'recruiter', 'hiring_manager'];
    const userRole = allowedRoles.includes(role) ? role : 'recruiter';
    const passwordHash = await bcrypt.hash(password, 12);
    const result = await pool.query(
      'INSERT INTO users (email, password_hash, role) VALUES ($1, $2, $3) RETURNING id, email, role, created_at',
      [email.toLowerCase(), passwordHash, userRole]
    );
    res.status(201).json({ success: true, user: result.rows[0] });
  } catch (err) {
    if (err.code === '23505') return res.status(409).json({ error: 'Email already registered' });
    res.status(500).json({ error: err.message });
  }
});

// POST /auth/login
app.post('/auth/login', async (req, res) => {
  try {
    const { email, password } = req.body;
    if (!email || !password) return res.status(400).json({ error: 'Email and password required' });
    const result = await pool.query('SELECT * FROM users WHERE email = $1 AND deleted_at IS NULL', [email.toLowerCase()]);
    if (!result.rows.length) return res.status(401).json({ error: 'Invalid credentials' });
    const user = result.rows[0];
    if (!user.is_active) return res.status(401).json({ error: 'Account is disabled' });
    const valid = await bcrypt.compare(password, user.password_hash);
    if (!valid) return res.status(401).json({ error: 'Invalid credentials' });
    const accessToken = jwt.sign({ id: user.id, email: user.email, role: user.role }, JWT_SECRET, { expiresIn: '15m' });
    const refreshToken = jwt.sign({ id: user.id }, JWT_REFRESH_SECRET, { expiresIn: '7d' });
    const refreshHash = await bcrypt.hash(refreshToken, 10);
    const expiresAt = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000);
    await pool.query('INSERT INTO refresh_tokens (user_id, token_hash, expires_at) VALUES ($1, $2, $3)', [user.id, refreshHash, expiresAt]);
    res.json({ success: true, accessToken, refreshToken, user: { id: user.id, email: user.email, role: user.role } });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// POST /auth/refresh
app.post('/auth/refresh', async (req, res) => {
  try {
    const { refreshToken } = req.body;
    if (!refreshToken) return res.status(401).json({ error: 'Refresh token required' });
    let payload;
    try { payload = jwt.verify(refreshToken, JWT_REFRESH_SECRET); } catch { return res.status(401).json({ error: 'Invalid refresh token' }); }
    const tokens = await pool.query('SELECT * FROM refresh_tokens WHERE user_id = $1 AND revoked_at IS NULL AND expires_at > NOW()', [payload.id]);
    let validToken = null;
    for (const t of tokens.rows) {
      if (await bcrypt.compare(refreshToken, t.token_hash)) { validToken = t; break; }
    }
    if (!validToken) return res.status(401).json({ error: 'Invalid or expired refresh token' });
    const user = await pool.query('SELECT id, email, role FROM users WHERE id = $1 AND is_active = true AND deleted_at IS NULL', [payload.id]);
    if (!user.rows.length) return res.status(401).json({ error: 'User not found' });
    const accessToken = jwt.sign({ id: user.rows[0].id, email: user.rows[0].email, role: user.rows[0].role }, JWT_SECRET, { expiresIn: '15m' });
    res.json({ success: true, accessToken, user: user.rows[0] });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// POST /auth/logout
app.post('/auth/logout', authenticateToken, async (req, res) => {
  try {
    await pool.query('UPDATE refresh_tokens SET revoked_at = NOW() WHERE user_id = $1 AND revoked_at IS NULL', [req.user.id]);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /auth/me
app.get('/auth/me', authenticateToken, async (req, res) => {
  try {
    const result = await pool.query('SELECT id, email, role, agency_id, is_active, created_at FROM users WHERE id = $1 AND deleted_at IS NULL', [req.user.id]);
    if (!result.rows.length) return res.status(404).json({ error: 'User not found' });
    res.json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /api/users (admin only)
app.get('/api/users', authenticateToken, requireRole('admin'), async (req, res) => {
  try {
    const result = await pool.query('SELECT id, email, role, agency_id, is_active, created_at FROM users WHERE deleted_at IS NULL ORDER BY created_at DESC');
    res.json({ users: result.rows });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// PATCH /api/users/:id/role (admin only)
app.patch('/api/users/:id/role', authenticateToken, requireRole('admin'), async (req, res) => {
  try {
    const { role } = req.body;
    const allowedRoles = ['admin', 'recruiter', 'hiring_manager'];
    if (!allowedRoles.includes(role)) return res.status(400).json({ error: 'Invalid role' });
    const result = await pool.query('UPDATE users SET role = $1, updated_at = NOW() WHERE id = $2 AND deleted_at IS NULL RETURNING id, email, role', [role, req.params.id]);
    if (!result.rows.length) return res.status(404).json({ error: 'User not found' });
    res.json({ success: true, user: result.rows[0] });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Add dashboard endpoints
app.get('/api/dashboard/metrics', authenticateToken, async (req, res) => {
  try {
    const [candidates, jobs, applications, interviews] = await Promise.all([
      pool.query('SELECT COUNT(*) FROM candidates'),
      pool.query("SELECT COUNT(*) FROM jobs WHERE status = 'posted'"),
      pool.query('SELECT COUNT(*) FROM job_applications'),
      pool.query("SELECT COUNT(*) FROM scorecards WHERE created_at >= NOW() - INTERVAL '7 days'"),
    ]);
    res.json({
      totalCandidates: parseInt(candidates.rows[0].count),
      activeJobs: parseInt(jobs.rows[0].count),
      totalApplications: parseInt(applications.rows[0].count),
      interviewsThisWeek: parseInt(interviews.rows[0].count),
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.get('/api/dashboard/pipeline-summary', authenticateToken, async (req, res) => {
  try {
    const result = await pool.query('SELECT stage, COUNT(*) as count FROM job_applications GROUP BY stage');
    const stages = { applied: 0, screening: 0, interview: 0, offer: 0, hired: 0 };
    result.rows.forEach(r => { if (stages.hasOwnProperty(r.stage)) stages[r.stage] = parseInt(r.count); });
    res.json(stages);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.get('/api/dashboard/recent-activity', authenticateToken, async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 'candidate_added' as type, c.name as title, c.email as subtitle, c.created_at as timestamp
      FROM candidates c
      UNION ALL
      SELECT 'job_posted' as type, j.title, j.location as subtitle, j.created_at as timestamp
      FROM jobs j
      UNION ALL
      SELECT 'application' as type, c.name as title, jo.title as subtitle, ja.created_at as timestamp
      FROM job_applications ja JOIN candidates c ON ja.candidate_id = c.id JOIN jobs jo ON ja.job_id = jo.id
      ORDER BY timestamp DESC LIMIT 10
    `);
    res.json({ activities: result.rows });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Health check
app.get('/health', async (req, res) => {
  try {
    const pgResult = await pool.query('SELECT NOW()');
    await redisClient.ping();
    res.json({ status: 'healthy', postgres: 'connected', redis: 'connected', timestamp: pgResult.rows[0].now });
  } catch (error) {
    res.status(500).json({ status: 'unhealthy', error: error.message });
  }
});

// --- Candidates API ---

// Get all candidates
app.get('/api/candidates', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM candidates ORDER BY created_at DESC');
    res.json({ candidates: result.rows });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Get single candidate
app.get('/api/candidates/:id', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM candidates WHERE id = $1', [req.params.id]);
    if (!result.rows.length) return res.status(404).json({ error: 'Not found' });
    res.json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Upload and parse CV
app.post('/api/candidates/parse-cv', upload.single('cv'), async (req, res) => {
  try {
    if (!req.file && !req.body.text) {
      return res.status(400).json({ error: 'No CV file or text provided' });
    }

    const cvText = req.file ? req.file.buffer.toString('utf-8') : req.body.text;
    const parsed = parseCV(cvText);

    // Save to database
    const result = await pool.query(
      `INSERT INTO candidates (name, email, phone, skills, experience_years, education, summary, raw_cv)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *`,
      [parsed.name, parsed.email, parsed.phone, parsed.skills, parsed.experience_years,
       parsed.education, parsed.summary, cvText.substring(0, 5000)]
    );

    res.json({ success: true, candidate: result.rows[0], parsed });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Create candidate manually
app.post('/api/candidates', async (req, res) => {
  try {
    const { name, email, phone, skills, experience_years, education, summary } = req.body;
    const result = await pool.query(
      `INSERT INTO candidates (name, email, phone, skills, experience_years, education, summary)
       VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *`,
      [name, email, phone, skills || [], experience_years, education, summary]
    );
    res.json({ success: true, candidate: result.rows[0] });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// --- Jobs API ---

// Get all jobs
app.get('/api/jobs', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM jobs ORDER BY created_at DESC');
    res.json({ jobs: result.rows });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Get single job
app.get('/api/jobs/:id', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM jobs WHERE id = $1', [req.params.id]);
    if (!result.rows.length) return res.status(404).json({ error: 'Not found' });
    res.json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Create job
app.post('/api/jobs', async (req, res) => {
  try {
    const { title, description, requirements, location, salary_min, salary_max, channels } = req.body;
    if (!title) return res.status(400).json({ error: 'Title required' });

    const result = await pool.query(
      `INSERT INTO jobs (title, description, requirements, location, salary_min, salary_max, channels)
       VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *`,
      [title, description, requirements, location, salary_min, salary_max, channels || []]
    );
    res.json({ success: true, job: result.rows[0] });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Post job to channels (multi-channel posting simulation)
app.post('/api/jobs/:id/post', async (req, res) => {
  try {
    const { channels } = req.body;
    if (!channels || !channels.length) {
      return res.status(400).json({ error: 'At least one channel required' });
    }

    // Simulate posting to each channel
    const postingResults = {};
    const availableChannels = {
      'linkedin': 'LinkedIn Jobs',
      'indeed': 'Indeed',
      'arbetsformedlingen': 'Arbetsförmedlingen',
      'company_site': 'Company Career Page',
      'glassdoor': 'Glassdoor'
    };

    for (const channel of channels) {
      // Simulate API call to each platform
      postingResults[channel] = {
        status: 'posted',
        platform: availableChannels[channel] || channel,
        url: `https://${channel}.example.com/jobs/${req.params.id}`,
        postedAt: new Date().toISOString()
      };
    }

    await pool.query(
      `UPDATE jobs SET channels = $1, status = 'posted', posted_at = NOW() WHERE id = $2`,
      [channels, req.params.id]
    );

    const job = await pool.query('SELECT * FROM jobs WHERE id = $1', [req.params.id]);
    res.json({ success: true, postingResults, job: job.rows[0] });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// --- Applications API ---

// Get applications for a job
app.get('/api/jobs/:id/applications', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT ja.*, c.name, c.email, c.skills, c.experience_years
       FROM job_applications ja JOIN candidates c ON ja.candidate_id = c.id
       WHERE ja.job_id = $1 ORDER BY ja.created_at DESC`,
      [req.params.id]
    );
    res.json({ applications: result.rows });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Apply candidate to job
app.post('/api/jobs/:jobId/apply/:candidateId', async (req, res) => {
  try {
    const result = await pool.query(
      `INSERT INTO job_applications (job_id, candidate_id) VALUES ($1, $2)
       ON CONFLICT (job_id, candidate_id) DO NOTHING RETURNING *`,
      [req.params.jobId, req.params.candidateId]
    );
    res.json({ success: true, application: result.rows[0] });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// --- Scorecards API ---

// Get scorecards for a candidate
app.get('/api/scorecards', async (req, res) => {
  try {
    const { job_id, candidate_id } = req.query;
    let query = 'SELECT * FROM scorecards WHERE 1=1';
    const params = [];
    if (job_id) { params.push(job_id); query += ` AND job_id = $${params.length}`; }
    if (candidate_id) { params.push(candidate_id); query += ` AND candidate_id = $${params.length}`; }
    query += ' ORDER BY created_at DESC';

    const result = await pool.query(query, params);
    res.json({ scorecards: result.rows });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Create scorecard
app.post('/api/scorecards', async (req, res) => {
  try {
    const { job_id, candidate_id, interviewer, criteria, comments, recommendation } = req.body;
    if (!job_id || !candidate_id || !interviewer) {
      return res.status(400).json({ error: 'job_id, candidate_id, and interviewer are required' });
    }

    // Calculate total score from criteria
    let total_score = 0;
    if (criteria && typeof criteria === 'object') {
      const scores = Object.values(criteria).map(c => c.score || 0);
      total_score = scores.length > 0 ? Math.round(scores.reduce((a, b) => a + b, 0) / scores.length) : 0;
    }

    const result = await pool.query(
      `INSERT INTO scorecards (job_id, candidate_id, interviewer, criteria, total_score, comments, recommendation)
       VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *`,
      [job_id, candidate_id, interviewer, JSON.stringify(criteria || {}), total_score, comments, recommendation]
    );
    res.json({ success: true, scorecard: result.rows[0] });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Get aggregate scorecard for a candidate on a job
app.get('/api/scorecards/summary/:jobId/:candidateId', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT
        COUNT(*) as total_interviewers,
        AVG(total_score) as avg_score,
        json_agg(json_build_object(
          'interviewer', interviewer,
          'score', total_score,
          'recommendation', recommendation,
          'comments', comments
        )) as interviews
       FROM scorecards
       WHERE job_id = $1 AND candidate_id = $2`,
      [req.params.jobId, req.params.candidateId]
    );
    res.json(result.rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// --- Stats API ---
app.get('/api/stats', async (req, res) => {
  try {
    const [candidates, jobs, scorecards, applications] = await Promise.all([
      pool.query('SELECT COUNT(*) FROM candidates'),
      pool.query('SELECT COUNT(*) FROM jobs'),
      pool.query('SELECT COUNT(*) FROM scorecards'),
      pool.query('SELECT COUNT(*) FROM job_applications'),
    ]);
    res.json({
      candidates: parseInt(candidates.rows[0].count),
      jobs: parseInt(jobs.rows[0].count),
      scorecards: parseInt(scorecards.rows[0].count),
      applications: parseInt(applications.rows[0].count),
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Serve React frontend
const clientDist = path.join(__dirname, 'client', 'dist');
app.use(express.static(clientDist));
app.get('*', (req, res, next) => {
  if (req.path.startsWith('/api/') || req.path === '/health') return next();
  res.sendFile(path.join(clientDist, 'index.html'));
});

app.listen(PORT, '0.0.0.0', () => {
  console.log(`HireFlow server running on port ${PORT}`);
});

module.exports = { app, pool, redisClient };