/**
 * MonsterAgro Backend Server
 * Express.js API serveris MonsterAgro saimniecības pārvaldības sistēmai
 * 
 * Funkcionalitāte:
 * - Lietotāju autentifikācija un reģistrācija
 * - Saimniecību pārvaldība
 * - Tehnikas uzskaite
 * - Lauku pārvaldība
 * - Kultūraugu sugu un šķirņu pārvaldība
 * - Ražas/novākšanas uzskaite
 * - Degvielas uzskaite
 * - Darbinieku pārvaldība
 * - Lietotāju apstiprināšanas procesi
 */

import express from "express";
import mysql from "mysql2";
import cors from "cors";
import dotenv from "dotenv";
import crypto from "crypto";
import nodemailer from "nodemailer";

// Ielādē vides mainīgos no .env faila
dotenv.config({ path: './pag.env' });

const app = express();

// Starpprogrammatūras konfigurācija
app.use(cors({
  origin: '*',
  methods: ['GET', 'POST', 'PUT', 'DELETE'],
  allowedHeaders: ['Content-Type', 'Accept']
}));
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true }));
app.use(express.static(".")); // Apkalpo statiskos failus no saknes

// Pieprasījumu reģistrēšanas starpprogrammatūra
app.use((req, _res, next) => {
  console.log(`${new Date().toISOString()} - ${req.method} ${req.url}`);
  next();
});

/**
 * Testa galapunkts, lai pārbaudītu, vai serveris darbojas
 */
app.get("/api/test", (req, res) => {
  res.json({ message: "Server is working!", timestamp: new Date() });
});

// Debug datu bāzes konfigurācija
console.log("🔍 Database config:", {
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  database: process.env.DB_NAME,
  password: process.env.DB_PASS ? "***" : "empty"
});

/**
 * Datu bāzes savienojums
 * Izveido savienojumu pulku ar MySQL datu bāzi
 */
const db = mysql.createConnection({
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT) || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASS || '',
  database: process.env.DB_NAME,
  connectTimeout: 10000
});

// Testē datu bāzes savienojumu
db.connect((err) => {
  if (err) {
    console.error("❌ DB connection failed:", err.message);
    console.error("Error details:", {
      code: err.code,
      errno: err.errno,
      syscall: err.syscall,
      address: err.address,
      port: err.port
    });
  } else {
    console.log("✅ Connected to MySQL database");
  }
});

/**
 * E-pasta konfigurācija
 * Konfigurē nodemailer automatizētu e-pasta paziņojumu sūtīšanai
 * Izmanto Gmail SMTP vai pielāgotu e-pasta servisu
 */
const emailTransporter = nodemailer.createTransport({
  service: 'gmail',
  auth: {
    user: process.env.EMAIL_USER || 'your-email@gmail.com',
    pass: process.env.EMAIL_PASS || 'your-app-password'
  }
});

/**
 * Nosūta brīdinājuma e-pastu par degvielas patēriņu
 * Informē saimniecības vadītāju, kad degvielas patēriņš sasniedz 5000L
 */
async function sendFuelAlertEmail(farmManagerEmail, farmName, totalFuelUsed) {
  try {
    const mailOptions = {
      from: process.env.EMAIL_USER || 'noreply@monsteragro.com',
      to: farmManagerEmail,
      subject: '⚠️ Fuel Consumption Alert - 5000L Threshold Reached',
      html: `
        <div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto;">
          <div style="background-color: #f5deb3; padding: 20px; border-radius: 10px; margin-bottom: 20px;">
            <h2 style="color: #28a745; margin: 0;">⚠️ Fuel Consumption Alert</h2>
          </div>
          
          <div style="background-color: #f8f9fa; padding: 20px; border-radius: 10px; margin-bottom: 20px;">
            <p style="margin: 0 0 15px 0; font-size: 16px;">
              Hello,
            </p>
            
            <p style="margin: 0 0 15px 0; font-size: 16px;">
              Your farm <strong>${farmName}</strong> has reached the <strong>5000 liter</strong> fuel consumption threshold.
            </p>
            
            <div style="background-color: white; padding: 15px; border-left: 4px solid #28a745; margin: 20px 0;">
              <p style="margin: 0; font-size: 18px;">
                <strong>Total Fuel Consumed:</strong> <span style="color: #28a745; font-size: 24px;">${totalFuelUsed.toLocaleString()} L</span>
              </p>
            </div>
            
            <p style="margin: 0 0 15px 0; font-size: 14px; color: #666;">
              This is an automated notification from MonsterAgro. Please review your fuel consumption records and consider refueling your equipment if necessary.
            </p>
            
            <p style="margin: 0; font-size: 14px; color: #999;">
              Sent: ${new Date().toLocaleString()}
            </p>
          </div>
          
          <div style="text-align: center; padding-top: 20px; border-top: 1px solid #ddd; color: #999; font-size: 12px;">
            <p>MonsterAgro Farm Management System</p>
          </div>
        </div>
      `
    };

    await emailTransporter.sendMail(mailOptions);
    console.log(`✅ Fuel alert email sent to ${farmManagerEmail}`);
    return true;
  } catch (error) {
    console.error('❌ Failed to send fuel alert email:', error);
    return false;
  }
};

// Iegūst visus darbiniekus ar iespēju filtrēt pēc saimniecības vai statusa
app.get("/api/employees", (req, res) => {
  const { farm_id, status } = req.query;
  
  let query = "SELECT id, name, fname, email, status, farm_id FROM users";
  let params = [];
  let conditions = ["active = 1"];
  
  if (farm_id) {
    conditions.push("farm_id = ?");
    params.push(parseInt(farm_id));
  }
  
  if (status) {
    conditions.push("status = ?");
    params.push(parseInt(status));
  }
  
  if (conditions.length > 0) {
    query += " WHERE " + conditions.join(" AND ");
  }
  
  query += " ORDER BY name, fname";
  
  db.query(query, params, (err, results) => {
    if (err) {
      console.error("❌ Database query failed:", err);
      return res.status(500).json({ error: "Database query failed" });
    }
    res.json(results);
  });
});

// Iegūst degvielas uzpildes ierakstus
// Izmanto JOIN, lai saņemtu farm_id no lietotāja
app.get("/api/fuel", (req, res) => {
  const { farm_id } = req.query;
  
  let query = `
    SELECT 
      f.id, 
      f.time, 
      f.equipment_id, 
      f.litres, 
      f.user_id,
      u.farm_id
    FROM fuel_accounting f
    JOIN users u ON f.user_id = u.id
  `;
  let params = [];
  
  if (farm_id) {
    query += " WHERE u.farm_id = ?";
    params.push(parseInt(farm_id));
  }
  
  query += " ORDER BY f.time DESC";
  
  db.query(query, params, (err, results) => {
    if (err) {
      console.error("❌ Database query failed:", err);
      return res.status(500).json({ error: "Database query failed" });
    }
    res.json(results);
  });
});

// Iegūst aprīkojuma ierakstus
app.get("/api/equipment", (req, res) => {
  const { farm_id } = req.query;
  
  let query = "SELECT id, name, fuel_tank, farm_id FROM equipment";
  let params = [];
  
  if (farm_id) {
    query += " WHERE farm_id = ?";
    params.push(parseInt(farm_id));
  }
  
  query += " ORDER BY name";
  
  db.query(query, params, (err, results) => {
    if (err) {
      console.error("❌ Equipment query failed:", err);
      return res.status(500).json({ error: "Database query failed" });
    }
    res.json(results);
  });
});

// Pievieno jaunu aprīkojumu
app.post("/api/equipment", (req, res) => {
  const { name, fuel_tank, farm_id } = req.body;

  if (!name || !fuel_tank || !farm_id) {
    return res.status(400).json({ error: "All fields are required" });
  }

  db.query(
    "INSERT INTO equipment (name, fuel_tank, farm_id) VALUES (?, ?, ?)",
    [name, fuel_tank, farm_id],
    (err, result) => {
      if (err) {
        console.error("❌ Equipment addition failed:", err);
        return res.status(500).json({ error: "Equipment addition failed" });
      }
      res.json({ success: true, id: result.insertId, message: "Equipment added successfully" });
    }
  );
});

// Atjaunina esošu aprīkojumu
app.put("/api/equipment/:id", (req, res) => {
  const { id } = req.params;
  const { name, fuel_tank } = req.body;

  if (!name || !fuel_tank) {
    return res.status(400).json({ error: "All fields are required" });
  }

  db.query(
    "UPDATE equipment SET name = ?, fuel_tank = ? WHERE id = ?",
    [name, fuel_tank, id],
    (err) => {
      if (err) {
        console.error("❌ Equipment update failed:", err);
        return res.status(500).json({ error: "Equipment update failed" });
      }
      res.json({ success: true, message: "Equipment updated successfully" });
    }
  );
});

// Dzēš aprīkojumu
app.delete("/api/equipment/:id", (req, res) => {
  const { id } = req.params;

  db.query("DELETE FROM equipment WHERE id = ?", [id], (err) => {
    if (err) {
      console.error("❌ Equipment deletion failed:", err);
      return res.status(500).json({ error: "Equipment deletion failed" });
    }
    res.json({ success: true, message: "Equipment deleted successfully" });
  });
});

// Iegūst pieejamās saimniecības reģistrācijai
app.get("/api/farms", (req, res) => {
  db.query("SELECT id, name, active FROM farms ORDER BY name", (err, results) => {
    if (err) {
      console.error("❌ Farms query failed:", err);
      return res.json([{ id: 1, name: "Default Farm", active: 0 }]);
    }

    if (results.length === 0) {
      return res.json([
        { id: 1, name: "Green Valley Farm", active: 0 },
        { id: 2, name: "Sunny Farm", active: 0 },
        { id: 3, name: "Golden Harvest Farm", active: 0 },
        { id: 4, name: "Meadow Stream Farm", active: 0 }
      ]);
    }

    res.json(results);
  });
});

// Iegūst saimniecības informāciju pēc ID
app.get("/api/farms/:id", (req, res) => {
  const { id } = req.params;

  db.query(
    "SELECT id, name, location, area, phone, email, description FROM farms WHERE id = ?",
    [id],
    (err, results) => {
      if (err) {
        console.error("❌ Farm query failed:", err);
        return res.status(500).json({ error: "Database query failed" });
      }

      if (results.length === 0) {
        return res.status(404).json({ error: "Farm not found" });
      }

      res.json(results[0]);
    }
  );
});

// Pievieno jaunu saimniecību
app.post("/api/farms", (req, res) => {
  const { name } = req.body;

  if (!name) {
    return res.status(400).json({ error: "Farm name is required" });
  }

  db.query(
    "INSERT INTO farms (name) VALUES (?)",
    [name],
    (err, result) => {
      if (err) {
        console.error("❌ Farm addition failed:", err);
        return res.status(500).json({ error: "Farm addition failed" });
      }
      res.json({ success: true, id: result.insertId, message: "Farm added successfully" });
    }
  );
});

// Atjaunina saimniecības informāciju
app.put("/api/farms/:id", (req, res) => {
  const { id } = req.params;
  const { name } = req.body;

  if (!name) {
    return res.status(400).json({ error: "Farm name is required" });
  }

  db.query(
    "UPDATE farms SET name = ? WHERE id = ?",
    [name, id],
    (err) => {
      if (err) {
        console.error("❌ Farm update failed:", err);
        return res.status(500).json({ error: "Farm update failed" });
      }
      res.json({ success: true, message: "Farm updated successfully" });
    }
  );
});

// Pārslēdz saimniecības aktīvo statusu
app.put("/api/farms/:id/toggle", (req, res) => {
  const { id } = req.params;
  const { active } = req.body;

  console.log(`🔄 Toggling farm ${id} to active=${active}`);

  if (active === undefined || active === null) {
    return res.status(400).json({ error: "Active status is required" });
  }

  db.query(
    "UPDATE farms SET active = ? WHERE id = ?",
    [active, id],
    (err, result) => {
      if (err) {
        console.error("❌ Farm status toggle failed:", err);
        return res.status(500).json({ error: "Farm status toggle failed" });
      }
      console.log(`✅ Farm ${id} toggled successfully. Affected rows: ${result.affectedRows}`);
      res.json({ success: true, message: "Farm status updated successfully" });
    }
  );
});

// Dzēst saimniecību
app.delete("/api/farms/:id", (req, res) => {
  const { id } = req.params;

  db.query(
    "DELETE FROM farms WHERE id = ?",
    [id],
    (err) => {
      if (err) {
        console.error("❌ Farm deletion failed:", err);
        return res.status(500).json({ error: "Farm deletion failed" });
      }
      res.json({ success: true, message: "Farm deleted successfully" });
    }
  );
});

// Pieslēgšanās galapunkts
app.post("/api/login", (req, res) => {
  console.log("🔍 Login request received:", req.body);

  const { email, password } = req.body || {};

  if (!email || !password) {
    console.log("❌ Email or password missing");
    return res.status(400).json({
      success: false,
      message: "❌ Please enter email and password"
    });
  }

  db.query(
    "SELECT id, password, name, fname, status, farm_id, active FROM users WHERE email = ?",
    [email],
    (err, results) => {
      if (err) {
        console.error("❌ Login query failed:", err);
        return res.status(500).json({
          success: false,
          message: "❌ Database error"
        });
      }

      if (results.length === 1) {
        const user = results[0];

        if (user.password === password) {
          if (user.active === 0) {
            console.log("❌ Login attempt for inactive account:", email);
            return res.json({
              success: false,
              message: "❌ Your account is pending approval. Please wait for the administrator to activate your account."
            });
          }

          console.log("✅ Successful login:", email);

          db.query(
            "UPDATE users SET session = NOW() WHERE id = ?",
            [user.id],
            (updateErr) => {
              if (updateErr) console.error("❌ Session update failed:", updateErr);
            }
          );

          return res.json({
            success: true,
            message: `✅ Welcome, ${user.name}!`,
            user: {
              id: user.id,
              name: user.name,
              fname: user.fname,
              status: user.status,
              farm_id: user.farm_id
            }
          });
        } else {
          console.log("❌ Incorrect password:", email);
          return res.json({
            success: false,
            message: "❌ Incorrect password"
          });
        }
      } else {
        console.log("❌ User not found:", email);
        return res.json({
          success: false,
          message: "❌ User not found"
        });
      }
    }
  );
});

// Reģistrācijas galapunkts ar aktivāciju
app.post("/api/register", async (req, res) => {
  console.log("🔍 Registration request received:", req.body);

  const { email, firstName, lastName, password, farmId, status } = req.body || {};

  if (!email || !firstName || !lastName || !password || !farmId) {
    console.log("❌ Required fields missing");
    return res.status(400).json({
      success: false,
      message: "❌ All fields are required"
    });
  }

  const userStatus = status || 1; // Noklusējums: darbinieks (1), ja nav norādīts

  db.query(
    "SELECT id FROM users WHERE email = ?",
    [email],
    async (err, results) => {
      if (err) {
        console.error("❌ Email check query failed:", err);
        return res.status(500).json({
          success: false,
          message: "❌ Database error"
        });
      }

      if (results.length > 0) {
        console.log("❌ Email already exists:", email);
        return res.json({
          success: false,
          message: "❌ Email already registered"
        });
      }

      db.query(
        "INSERT INTO users (name, fname, email, password, status, farm_id, active, session) VALUES (?, ?, ?, ?, ?, ?, 0, NOW())",
        [firstName, lastName, email, password, userStatus, farmId],
        async (insertErr, result) => {
          if (insertErr) {
            console.error("❌ User creation failed:", insertErr);
            return res.status(500).json({
              success: false,
              message: "❌ Account creation failed"
            });
          }

          console.log("✅ User registered (pending activation):", email);

          res.json({
            success: true,
            message: "✅ Account created! Administrator will review your registration soon.",
            userId: result.insertId,
            requiresActivation: true
          });
        }
      );
    }
  );
});

// Pievienot jaunu darbinieku
app.post("/api/employees", (req, res) => {
  const { name, fname, email, password, status, farm_id } = req.body;

  if (!name || !fname || !email || !password) {
    return res.status(400).json({ error: "All fields are required" });
  }

  db.query(
    "INSERT INTO users (name, fname, email, password, status, farm_id, active, session) VALUES (?, ?, ?, ?, ?, ?, 1, NOW())",
    [name, fname, email, password, status || 1, farm_id || 1],
    (err, result) => {
      if (err) {
        console.error("❌ Employee addition failed:", err);
        return res.status(500).json({ error: "Employee addition failed" });
      }
      res.json({ success: true, id: result.insertId, message: "Employee added successfully" });
    }
  );
});

// Atjaunināt darbinieka datus
app.put("/api/employees/:id", (req, res) => {
  const { id } = req.params;
  const { name, fname, email, status } = req.body;

  db.query(
    "UPDATE users SET name = ?, fname = ?, email = ?, status = ? WHERE id = ?",
    [name, fname, email, status, id],
    (err) => {
      if (err) {
        console.error("❌ Employee update failed:", err);
        return res.status(500).json({ error: "Employee update failed" });
      }
      res.json({ success: true, message: "Employee updated successfully" });
    }
  );
});

// Atjaunināt lietotāja profilu
app.put("/api/users/:id", (req, res) => {
  const { id } = req.params;
  const { name, fname, email } = req.body;

  if (!name || !fname || !email) {
    return res.status(400).json({ error: "All fields are required" });
  }

  db.query(
    "SELECT id FROM users WHERE email = ? AND id != ?",
    [email, id],
    (err, results) => {
      if (err) {
        console.error("❌ Email check failed:", err);
        return res.status(500).json({ error: "Database error" });
      }

      if (results.length > 0) {
        return res.status(400).json({ error: "Email is already in use" });
      }

      db.query(
        "UPDATE users SET name = ?, fname = ?, email = ? WHERE id = ?",
        [name, fname, email, id],
        (updateErr) => {
          if (updateErr) {
            console.error("❌ User update failed:", updateErr);
            return res.status(500).json({ error: "Profile update failed" });
          }
          res.json({ success: true, message: "Profile updated successfully" });
        }
      );
    }
  );
});

// Mainīt paroli
app.put("/api/users/:id/password", (req, res) => {
  const { id } = req.params;
  const { currentPassword, newPassword } = req.body;

  if (!currentPassword || !newPassword) {
    return res.status(400).json({ error: "All fields are required" });
  }

  db.query(
    "SELECT password FROM users WHERE id = ?",
    [id],
    (err, results) => {
      if (err) {
        console.error("❌ Password check failed:", err);
        return res.status(500).json({ error: "Database error" });
      }

      if (results.length === 0) {
        return res.status(404).json({ error: "User not found" });
      }

      if (results[0].password !== currentPassword) {
        return res.status(400).json({ error: "Current password is incorrect" });
      }

      db.query(
        "UPDATE users SET password = ? WHERE id = ?",
        [newPassword, id],
        (updateErr) => {
          if (updateErr) {
            console.error("❌ Password change failed:", updateErr);
            return res.status(500).json({ error: "Password change failed" });
          }
          res.json({ success: true, message: "Password changed successfully" });
        }
      );
    }
  );
});

// Dzēst darbinieku
app.delete("/api/employees/:id", (req, res) => {
  const { id } = req.params;

  // Vispirms pārbaudīt, vai lietotājs eksistē
  db.query(
    "SELECT id FROM users WHERE id = ?",
    [id],
    (err, results) => {
      if (err) {
        console.error("❌ User lookup failed:", err);
        return res.status(500).json({ error: "User lookup failed" });
      }

      if (results.length === 0) {
        return res.status(404).json({ error: "User not found" });
      }

      // Dzēst lietotāju (saistītie ieraksti tiks saglabāti ar user_id = NULL)
      db.query("DELETE FROM users WHERE id = ?", [id], (deleteErr) => {
        if (deleteErr) {
          console.error("❌ Employee deletion failed:", deleteErr);
          return res.status(500).json({ error: "Employee deletion failed" });
        }
        res.json({ success: true, message: "Employee deleted successfully. Related records have been preserved." });
      });
    }
  );
});

// Pievienot degvielas uzskaites ierakstu – farm_id tiek noteikts automātiski no lietotāja
app.post("/api/fuel", (req, res) => {
  const { time, equipment_id, litres, user_id } = req.body;

  // Vispirms iegūt lietotāja farm_id
  db.query(
    "SELECT farm_id FROM users WHERE id = ?",
    [user_id],
    (err, userResults) => {
      if (err) {
        console.error("❌ User lookup failed:", err);
        return res.status(500).json({ error: "User lookup failed" });
      }

      if (userResults.length === 0) {
        return res.status(404).json({ error: "User not found" });
      }

      const farm_id = userResults[0].farm_id;

      // Ievietot degvielas uzskaites ierakstu
      db.query(
        "INSERT INTO fuel_accounting (time, equipment_id, litres, user_id) VALUES (?, ?, ?, ?)",
        [time || new Date(), equipment_id, litres, user_id],
        (insertErr, result) => {
          if (insertErr) {
            console.error("❌ Fuel record insertion failed:", insertErr);
            return res.status(500).json({ error: "Fuel record insertion failed" });
          }

          // Pārbaudīt, vai kopējais degvielas patēriņš saimniecībā ir sasniedzis 5000L
          db.query(
            "SELECT SUM(litres) as total_fuel FROM fuel_accounting fa JOIN users u ON fa.user_id = u.id WHERE u.farm_id = ?",
            [farm_id],
            (checkErr, checkResults) => {
              if (checkErr) {
                console.error("❌ Fuel total check failed:", checkErr);
                return res.json({ success: true, id: result.insertId, message: "Fuel record added successfully" });
              }

              const totalFuel = checkResults[0]?.total_fuel || 0;

              // Ja kopējais degvielas patēriņš ≥ 5000L, nosūtīt brīdinājuma e-pastu saimniecības vadītājam
              if (totalFuel >= 5000) {
                // Iegūt saimniecības vadītāja e-pastu
                db.query(
                  "SELECT u.email, f.name as farm_name FROM users u JOIN farms f ON u.farm_id = f.id WHERE u.farm_id = ? AND u.status = 2 AND u.active = 1 LIMIT 1",
                  [farm_id],
                  (managerErr, managerResults) => {
                    if (!managerErr && managerResults.length > 0) {
                      const { email, farm_name } = managerResults[0];
                      // Nosūtīt e-pastu asinhroni (negaidot atbildi)
                      sendFuelAlertEmail(email, farm_name, totalFuel);
                    }
                  }
                );
              }

              res.json({ 
                success: true, 
                id: result.insertId, 
                message: "Fuel record added successfully",
                totalFuelUsed: totalFuel,
                alertTriggered: totalFuel >= 5000
              });
            }
          );
        }
      );
    }
  );
});

// Dzēst degvielas ierakstu
app.delete("/api/fuel/:id", (req, res) => {
  const { id } = req.params;
  const { user_id } = req.query;

  if (!user_id) {
    return res.status(400).json({ error: "User ID is required" });
  }

  db.query(
    "SELECT user_id FROM fuel_accounting WHERE id = ?",
    [id],
    (err, results) => {
      if (err) {
        console.error("❌ Fuel record check failed:", err);
        return res.status(500).json({ error: "Fuel record check failed" });
      }

      if (results.length === 0) {
        return res.status(404).json({ error: "Fuel record not found" });
      }

      // Pārbaudīt, vai ieraksts pieder pašreizējam lietotājam
      if (results[0].user_id !== parseInt(user_id)) {
        return res.status(403).json({ error: "You can only delete your own records" });
      }

      db.query("DELETE FROM fuel_accounting WHERE id = ?", [id], (deleteErr) => {
        if (deleteErr) {
          console.error("❌ Fuel record deletion failed:", deleteErr);
          return res.status(500).json({ error: "Fuel record deletion failed" });
        }
        res.json({ success: true, message: "Fuel record deleted successfully" });
      });
    }
  );
});

// Iegūt neapstiprinātās reģistrācijas (tikai neaktīvi darbinieki – statuss 1)
app.get("/api/pending-registrations", (req, res) => {
  const { farm_id } = req.query;

  if (!farm_id) {
    return res.status(400).json({ error: "Farm ID is required" });
  }

  db.query(
    "SELECT id, name, fname, email, session as registration_date FROM users WHERE farm_id = ? AND active = 0 AND status = 1 ORDER BY session DESC",
    [farm_id],
    (err, results) => {
      if (err) {
        console.error("❌ Pending registrations query failed:", err);
        return res.status(500).json({ error: "Database query failed" });
      }
      res.json(results);
    }
  );
});

// Apstiprināt neapstiprinātu reģistrāciju (aktivizēt lietotāju)
app.post("/api/accept-registration/:id", (req, res) => {
  const { id } = req.params;

  db.query(
    "UPDATE users SET active = 1 WHERE id = ? AND active = 0",
    [id],
    (err, result) => {
      if (err) {
        console.error("❌ Registration acceptance failed:", err);
        return res.status(500).json({ error: "Registration acceptance failed" });
      }

      if (result.affectedRows === 0) {
        return res.status(404).json({ error: "Registration not found or already accepted" });
      }

      res.json({ success: true, message: "Registration accepted successfully" });
    }
  );
});

// Noraidīt neapstiprinātu reģistrāciju (dzēst lietotāju)
app.delete("/api/decline-registration/:id", (req, res) => {
  const { id } = req.params;

  db.query(
    "DELETE FROM users WHERE id = ? AND active = 0",
    [id],
    (err, result) => {
      if (err) {
        console.error("❌ Registration decline failed:", err);
        return res.status(500).json({ error: "Registration decline failed" });
      }

      if (result.affectedRows === 0) {
        return res.status(404).json({ error: "Registration not found or already accepted" });
      }

      res.json({ success: true, message: "Registration declined successfully" });
    }
  );
});

// Iegūt visus gaidošos apstiprinājumus administratoram (neaktīvi lietotāji ar statusu 2 - vadītāji)
app.get("/api/pending-approvals", (req, res) => {
  db.query(
    `SELECT u.id, u.name, u.fname, u.email, u.status, u.session as registration_date, f.name as farm_name 
     FROM users u 
     LEFT JOIN farms f ON u.farm_id = f.id 
     WHERE u.active = 0 AND u.status = 2 
     ORDER BY u.session DESC`,
    (err, results) => {
      if (err) {
        console.error("❌ Pending approvals query failed:", err);
        return res.status(500).json({ error: "Database query failed" });
      }
      res.json(results);
    }
  );
});

// Apstiprināt lietotāju (aktivizēt vadītāju)
app.post("/api/approve-user/:id", (req, res) => {
  const { id } = req.params;

  db.query(
    "UPDATE users SET active = 1 WHERE id = ? AND active = 0 AND status = 2",
    [id],
    (err, result) => {
      if (err) {
        console.error("❌ User approval failed:", err);
        return res.status(500).json({ error: "User approval failed" });
      }

      if (result.affectedRows === 0) {
        return res.status(404).json({ error: "User not found or already approved" });
      }

      res.json({ success: true, message: "User approved successfully" });
    }
  );
});

// Noraidīt lietotāju (dzēst vadītāju)
app.delete("/api/decline-user/:id", (req, res) => {
  const { id } = req.params;

  db.query(
    "DELETE FROM users WHERE id = ? AND active = 0 AND status = 2",
    [id],
    (err, result) => {
      if (err) {
        console.error("❌ User decline failed:", err);
        return res.status(500).json({ error: "User decline failed" });
      }

      if (result.affectedRows === 0) {
        return res.status(404).json({ error: "User not found or already approved" });
      }

      res.json({ success: true, message: "User declined successfully" });
    }
  );
});

// Konta aktivizācijas endpoint
app.post("/api/activate-account", async (req, res) => {
  const { userId, activatedBy } = req.body;

  if (!userId || !activatedBy) {
    return res.status(400).json({ error: "User ID and activator ID are required" });
  }

  // Pārbaudīt, vai aktivizētājs ir vadītājs vai administrators
  db.query(
    "SELECT status FROM users WHERE id = ?",
    [activatedBy],
    (err, results) => {
      if (err || results.length === 0) {
        return res.status(403).json({ error: "Unauthorized" });
      }

      if (results[0].status < 2) {
        return res.status(403).json({ error: "Only managers and administrators can activate accounts" });
      }

      // Aktivizēt lietotāja kontu
      db.query(
        "UPDATE users SET active = 1 WHERE id = ?",
        [userId],
        (updateErr) => {
          if (updateErr) {
            console.error("❌ Account activation failed:", updateErr);
            return res.status(500).json({ error: "Account activation failed" });
          }

          res.json({ success: true, message: "Account activated successfully" });
        }
      );
    }
  );
});

// Saņemt laukus konkrētai saimniecībai (novākšanas formai)
app.get("/api/fields", (req, res) => {
  const { farm_id } = req.query;

  if (!farm_id) {
    return res.status(400).json({ error: "Farm ID is required" });
  }

  db.query(
    `SELECT f.id, f.name, f.area, f.breed_id, cb.name as breed_name, cs.name as species_name
     FROM fields f
     LEFT JOIN crop_breed cb ON f.breed_id = cb.id
     LEFT JOIN crop_species cs ON cb.species_id = cs.id
     WHERE f.farm_id = ?
     ORDER BY f.name`,
    [farm_id],
    (err, results) => {
      if (err) {
        console.error("❌ Fields query failed:", err);
        return res.status(500).json({ error: "Database query failed" });
      }
      res.json(results);
    }
  );
});

// Pievienot jaunu lauku
app.post("/api/fields", (req, res) => {
  const { name, area, breed_id, farm_id } = req.body;

  if (!name || !area || !breed_id || !farm_id) {
    return res.status(400).json({ error: "All fields are required" });
  }

  db.query(
    "INSERT INTO fields (name, area, breed_id, farm_id) VALUES (?, ?, ?, ?)",
    [name, area, breed_id, farm_id],
    (err, result) => {
      if (err) {
        console.error("❌ Field addition failed:", err);
        return res.status(500).json({ error: "Field addition failed" });
      }
      res.json({ success: true, id: result.insertId, message: "Field added successfully" });
    }
  );
});

// Atjaunināt lauku
app.put("/api/fields/:id", (req, res) => {
  const { id } = req.params;
  const { name, area, breed_id } = req.body;

  if (!name || !area || !breed_id) {
    return res.status(400).json({ error: "All fields are required" });
  }

  db.query(
    "UPDATE fields SET name = ?, area = ?, breed_id = ? WHERE id = ?",
    [name, area, breed_id, id],
    (err) => {
      if (err) {
        console.error("❌ Field update failed:", err);
        return res.status(500).json({ error: "Field update failed" });
      }
      res.json({ success: true, message: "Field updated successfully" });
    }
  );
});

// Dzēst lauku
app.delete("/api/fields/:id", (req, res) => {
  const { id } = req.params;

  db.query("DELETE FROM fields WHERE id = ?", [id], (err) => {
    if (err) {
      console.error("❌ Field deletion failed:", err);
      return res.status(500).json({ error: "Field deletion failed" });
    }
    res.json({ success: true, message: "Field deleted successfully" });
  });
});

// Saņemt kultūraugu šķirnes
app.get("/api/crop-breeds", (req, res) => {
  db.query(
    "SELECT id, name, species_id FROM crop_breed ORDER BY name",
    (err, results) => {
      if (err) {
        console.error("❌ Crop breeds query failed:", err);
        return res.status(500).json({ error: "Database query failed" });
      }
      res.json(results);
    }
  );
});

// Saņemt kultūraugu sugas
app.get("/api/crop-species", (req, res) => {
  db.query(
    "SELECT id, name FROM crop_species ORDER BY name",
    (err, results) => {
      if (err) {
        console.error("❌ Crop species query failed:", err);
        return res.status(500).json({ error: "Database query failed" });
      }
      res.json(results);
    }
  );
});

// Pievienot jaunu kultūraugu sugu
app.post("/api/crop-species", (req, res) => {
  const { name } = req.body;

  if (!name) {
    return res.status(400).json({ error: "Species name is required" });
  }

  db.query(
    "INSERT INTO crop_species (name) VALUES (?)",
    [name],
    (err, result) => {
      if (err) {
        console.error("❌ Species addition failed:", err);
        return res.status(500).json({ error: "Species addition failed" });
      }
      res.json({ success: true, id: result.insertId, message: "Species added successfully" });
    }
  );
});

// Pievienot jaunu kultūraugu šķirni
app.post("/api/crop-breeds", (req, res) => {
  const { name, species_id } = req.body;

  if (!name || !species_id) {
    return res.status(400).json({ error: "All fields are required" });
  }

  db.query(
    "INSERT INTO crop_breed (name, species_id) VALUES (?, ?)",
    [name, species_id],
    (err, result) => {
      if (err) {
        console.error("❌ Breed addition failed:", err);
        return res.status(500).json({ error: "Breed addition failed" });
      }
      res.json({ success: true, id: result.insertId, message: "Breed added successfully" });
    }
  );
});

// Dzēst kultūraugu sugu
app.delete("/api/crop-species/:id", (req, res) => {
  const { id } = req.params;

  db.query("DELETE FROM crop_species WHERE id = ?", [id], (err) => {
    if (err) {
      console.error("❌ Species deletion failed:", err);
      return res.status(500).json({ error: "Species deletion failed" });
    }
    res.json({ success: true, message: "Species deleted successfully" });
  });
});

// Dzēst kultūraugu šķirni
app.delete("/api/crop-breeds/:id", (req, res) => {
  const { id } = req.params;

  db.query("DELETE FROM crop_breed WHERE id = ?", [id], (err) => {
    if (err) {
      console.error("❌ Breed deletion failed:", err);
      return res.status(500).json({ error: "Breed deletion failed" });
    }
    res.json({ success: true, message: "Breed deleted successfully" });
  });
});

// Pievienot novākšanas ierakstu
app.post("/api/harvest", (req, res) => {
  const { field_id, full_weight, empty_weight, moisture, user_id } = req.body;

  if (!field_id || !full_weight || !empty_weight || !moisture || !user_id) {
    return res.status(400).json({ error: "All fields are required" });
  }

  // Aprēķināt tīro svaru
  const weight = full_weight - empty_weight;

  if (weight <= 0) {
    return res.status(400).json({ error: "Net weight must be positive (full weight > empty weight)" });
  }

  // Iegūt šķirnes ID no lauka
  db.query(
    "SELECT breed_id FROM fields WHERE id = ?",
    [field_id],
    (err, results) => {
      if (err) {
        console.error("❌ Field lookup failed:", err);
        return res.status(500).json({ error: "Field lookup failed" });
      }

      if (results.length === 0) {
        return res.status(404).json({ error: "Field not found" });
      }

      const breed_id = results[0].breed_id;
      const time = new Date();

      // Ievietot novākšanas ierakstu
      db.query(
        "INSERT INTO harvest_record (time, field_id, breed_id, weight, moisture, user_id) VALUES (?, ?, ?, ?, ?, ?)",
        [time, field_id, breed_id, weight, moisture, user_id],
        (insertErr, result) => {
          if (insertErr) {
            console.error("❌ Harvest record insertion failed:", insertErr);
            return res.status(500).json({ error: "Harvest record insertion failed" });
          }
          res.json({
            success: true,
            id: result.insertId,
            message: "Harvest record added successfully",
            data: {
              fullWeight: full_weight,
              emptyWeight: empty_weight,
              netWeight: weight,
              moisture: moisture
            }
          });
        }
      );
    }
  );
});

// Iegūt ražas statistiku pa saimniecību (ar darbinieku filtrēšanu)
app.get("/api/yield-stats", (req, res) => {
  const { farm_id, user_id, user_status } = req.query;

  if (!farm_id) {
    return res.status(400).json({ error: "Farm ID is required" });
  }

  // Darbinieki (status 1) redz tikai savus ierakstus
  // Menedžeri (status 2) un administratori (status 3) redz visus ierakstus
  const isEmployee = parseInt(user_status) === 1;
  const userFilter = isEmployee ? `AND hr.user_id = ${parseInt(user_id)}` : '';

  // Iegūt sugas ar to šķirnēm un statistiku
  const query = `
    SELECT 
      cs.id as species_id,
      cs.name as species_name,
      cb.id as breed_id,
      cb.name as breed_name,
      COUNT(hr.id) as delivery_count,
      SUM(hr.weight) as total_weight,
      AVG(hr.moisture) as avg_moisture,
      MIN(hr.time) as first_harvest,
      MAX(hr.time) as last_harvest
    FROM crop_species cs
    LEFT JOIN crop_breed cb ON cs.id = cb.species_id
    LEFT JOIN harvest_record hr ON cb.id = hr.breed_id
    LEFT JOIN fields f ON hr.field_id = f.id
    WHERE (f.farm_id = ? OR f.farm_id IS NULL) ${userFilter}
    GROUP BY cs.id, cs.name, cb.id, cb.name
    ORDER BY cs.name, cb.name
  `;

  db.query(query, [farm_id], (err, results) => {
    if (err) {
      console.error("❌ Yield statistics query failed:", err);
      return res.status(500).json({ error: "Database query failed" });
    }

    // Sakārtot datus pa sugām
    const speciesMap = {};
    
    results.forEach(row => {
      if (!speciesMap[row.species_id]) {
        speciesMap[row.species_id] = {
          id: row.species_id,
          name: row.species_name,
          breeds: []
        };
      }
      
      if (row.breed_id) {
        speciesMap[row.species_id].breeds.push({
          id: row.breed_id,
          name: row.breed_name,
          deliveryCount: row.delivery_count || 0,
          totalWeight: row.total_weight || 0,
          avgMoisture: row.avg_moisture ? parseFloat(row.avg_moisture).toFixed(1) : 0,
          firstHarvest: row.first_harvest,
          lastHarvest: row.last_harvest
        });
      }
    });

    res.json(Object.values(speciesMap));
  });
});

// Iegūt detalizētus ražas ierakstus pa šķirni (ar darbinieku filtrēšanu)
app.get("/api/harvest-records/:breed_id", (req, res) => {
  const { breed_id } = req.params;
  const { farm_id, user_id, user_status } = req.query;

  if (!farm_id) {
    return res.status(400).json({ error: "Farm ID is required" });
  }

  // Darbinieki redz tikai savus ierakstus
  const isEmployee = parseInt(user_status) === 1;
  const userFilter = isEmployee ? `AND hr.user_id = ${parseInt(user_id)}` : '';

  const query = `
    SELECT 
      hr.id,
      hr.time,
      hr.weight,
      hr.moisture,
      u.name,
      u.fname,
      f.name as field_name
    FROM harvest_record hr
    JOIN users u ON hr.user_id = u.id
    JOIN fields f ON hr.field_id = f.id
    WHERE hr.breed_id = ? AND f.farm_id = ? ${userFilter}
    ORDER BY hr.time DESC
  `;

  db.query(query, [breed_id, farm_id], (err, results) => {
    if (err) {
      console.error("❌ Harvest records query failed:", err);
      return res.status(500).json({ error: "Database query failed" });
    }
    res.json(results);
  });
});

// Servera palaišana
const PORT = process.env.PORT || 3001;
app.listen(PORT, () => {
  console.log(`🚀 Server running at http://localhost:${PORT}`);
  console.log(`📋 Database API endpoints available:`);
  console.log(`   GET  http://localhost:${PORT}/api/employees`);
  console.log(`   GET  http://localhost:${PORT}/api/fuel`);
  console.log(`   POST http://localhost:${PORT}/api/employees`);
  console.log(`   GET  http://localhost:${PORT}/api/pending-registrations`);
  console.log(`   POST http://localhost:${PORT}/api/init-harvest-data`);
});