/**
 * 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";
import bcrypt from "bcrypt";

// 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() });
});

// Datu bāzes savienojums
console.log("🚀 Server running at http://localhost:3001");
console.log("📋 Database API endpoints available:");
console.log("   GET  http://localhost:3001/api/employees");
console.log("   GET  http://localhost:3001/api/fuel");
console.log("   POST http://localhost:3001/api/employees");
console.log("   GET  http://localhost:3001/api/pending-registrations");
console.log("   POST http://localhost:3001/api/init-harvest-data");

/**
 * 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);
  } 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
 * Šis ir tikai prototips un tas nestrādā
 */
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 WHERE active = 1";
  let params = [];
  
  if (farm_id) {
    query += " AND 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);
  });
});

// Iegūst VISU aprīkojumu (arī neaktīvo) - izmanto vēsturiskajiem ierakstiem
app.get("/api/equipment-all", (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" });
  }

  // Vispirms pārbauda, vai aprīkojums ar šo nosaukumu jau eksistē
  db.query(
    "SELECT id, active FROM equipment WHERE name = ? AND farm_id = ?",
    [name, farm_id],
    (checkErr, checkResults) => {
      if (checkErr) {
        console.error("❌ Equipment check failed:", checkErr);
        return res.status(500).json({ error: "Equipment check failed" });
      }

      // Ja aprīkojums jau eksistē
      if (checkResults.length > 0) {
        const equipment = checkResults[0];
        
        // Ja tas jau ir aktīvs, atgriezt kļūdu
        if (equipment.active === 1) {
          return res.status(400).json({ error: "Equipment with this name already exists" });
        }

        // Ja tas ir neaktīvs, reaktivizēt to
        db.query(
          "UPDATE equipment SET active = 1, fuel_tank = ? WHERE id = ?",
          [fuel_tank, equipment.id],
          (updateErr) => {
            if (updateErr) {
              console.error("❌ Equipment reactivation failed:", updateErr);
              return res.status(500).json({ error: "Equipment reactivation failed" });
            }
            res.json({ success: true, id: equipment.id, message: "Equipment reactivated successfully" });
          }
        );
      } else {
        // Ja aprīkojums neeksistē, izveidot jaunu
        db.query(
          "INSERT INTO equipment (name, fuel_tank, farm_id, active) VALUES (?, ?, ?, 1)",
          [name, fuel_tank, farm_id],
          (insertErr, result) => {
            if (insertErr) {
              console.error("❌ Equipment addition failed:", insertErr);
              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 = ? AND active = 1",
    [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ēst aprīkojumu (soft delete)
app.delete("/api/equipment/:id", (req, res) => {
  const { id } = req.params;

  db.query("UPDATE equipment SET active = 0 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" });
  }

  // Pārbauda, vai saimniecība ar šādu nosaukumu jau pastāv (case-insensitive)
  db.query(
    "SELECT id FROM farms WHERE LOWER(name) = LOWER(?)",
    [name],
    (err, results) => {
      if (err) {
        console.error("❌ Farm check failed:", err);
        return res.status(500).json({ error: "Farm check failed" });
      }

      console.log(`Checking farm name: "${name}", Found: ${results.length}`);

      if (results.length > 0) {
        return res.status(400).json({ error: "A farm with this name already exists" });
      }

      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" });
          }
          console.log(`✅ Farm added: ${name} (ID: ${result.insertId})`);
          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;

  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" });
      }
      res.json({ success: true, message: "Farm status updated successfully" });
    }
  );
});

// Dzēst saimniecību un visus ar to saistītos ierakstus
app.delete("/api/farms/:id", (req, res) => {
  const { id } = req.params;

  // Sāk transakciju, lai nodrošinātu datu integritāti
  db.query("START TRANSACTION", (startErr) => {
    if (startErr) {
      console.error("❌ Transaction start failed:", startErr);
      return res.status(500).json({ error: "Farm deletion failed" });
    }

    try {
      // 1. Dzēš visus degvielas ierakstus, kas pieder šai saimniecībai
      db.query(
        `DELETE FROM fuel_accounting 
         WHERE user_id IN (SELECT id FROM users WHERE farm_id = ?)`,
        [id],
        (fuelErr) => {
          if (fuelErr) {
            console.error("❌ Fuel records deletion failed:", fuelErr);
            return db.query("ROLLBACK", () => {
              res.status(500).json({ error: "Farm deletion failed" });
            });
          }

          // 2. Dzēš visus ražas ierakstus, kas pieder šai saimniecībai
          db.query(
            `DELETE FROM harvest_record 
             WHERE field_id IN (SELECT id FROM fields WHERE farm_id = ?)`,
            [id],
            (harvestErr) => {
              if (harvestErr) {
                console.error("❌ Harvest records deletion failed:", harvestErr);
                return db.query("ROLLBACK", () => {
                  res.status(500).json({ error: "Farm deletion failed" });
                });
              }

              // 3. Dzēš visus laukus, kas pieder šai saimniecībai
              db.query(
                "DELETE FROM fields WHERE farm_id = ?",
                [id],
                (fieldsErr) => {
                  if (fieldsErr) {
                    console.error("❌ Fields deletion failed:", fieldsErr);
                    return db.query("ROLLBACK", () => {
                      res.status(500).json({ error: "Farm deletion failed" });
                    });
                  }

                  // 4. Dzēš visu aprīkojumu, kas pieder šai saimniecībai
                  db.query(
                    "DELETE FROM equipment WHERE farm_id = ?",
                    [id],
                    (equipmentErr) => {
                      if (equipmentErr) {
                        console.error("❌ Equipment deletion failed:", equipmentErr);
                        return db.query("ROLLBACK", () => {
                          res.status(500).json({ error: "Farm deletion failed" });
                        });
                      }

                      // 5. Dzēš visus lietotājus (darbiniekus un vadītājus), kas pieder šai saimniecībai
                      db.query(
                        "DELETE FROM users WHERE farm_id = ?",
                        [id],
                        (usersErr) => {
                          if (usersErr) {
                            console.error("❌ Users deletion failed:", usersErr);
                            return db.query("ROLLBACK", () => {
                              res.status(500).json({ error: "Farm deletion failed" });
                            });
                          }

                          // 6. Dzēš pašu saimniecību
                          db.query(
                            "DELETE FROM farms WHERE id = ?",
                            [id],
                            (farmErr) => {
                              if (farmErr) {
                                console.error("❌ Farm deletion failed:", farmErr);
                                return db.query("ROLLBACK", () => {
                                  res.status(500).json({ error: "Farm deletion failed" });
                                });
                              }

                              // Apstiprina transakciju
                              db.query("COMMIT", (commitErr) => {
                                if (commitErr) {
                                  console.error("❌ Transaction commit failed:", commitErr);
                                  return db.query("ROLLBACK", () => {
                                    res.status(500).json({ error: "Farm deletion failed" });
                                  });
                                }

                                console.log("✅ Farm and all related records deleted successfully:", id);
                                res.json({ 
                                  success: true, 
                                  message: "Farm and all related records deleted successfully" 
                                });
                              });
                            }
                          );
                        }
                      );
                    }
                  );
                }
              );
            }
          );
        }
      );
    } catch (error) {
      console.error("❌ Farm deletion error:", error);
      db.query("ROLLBACK", () => {
        res.status(500).json({ error: "Farm deletion failed" });
      });
    }
  });
});

// Pieslēgšanās galapunkts
app.post("/api/login", (req, res) => {
  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 u.id, u.password, u.name, u.fname, u.status, u.farm_id, u.active, f.active as farm_active FROM users u LEFT JOIN farms f ON u.farm_id = f.id WHERE u.email = ?",
    [email],
    async (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];

        // Pārbauda, vai saimniecība ir aktīva
        if (user.farm_active === 0) {
          console.log("❌ Login attempt for disabled farm:", email);
          return res.json({
            success: false,
            message: "❌ Your farm has been disabled. Please contact the administrator."
          });
        }

        try {
          // Salīdzina ievadīto paroli ar jaucēto paroli datu bāzē
          const passwordMatch = await bcrypt.compare(password, user.password);

          if (passwordMatch) {
            if (user.active === 0) {
              console.log("❌ Login attempt for inactive account:", email);
              
              // Atšķirīgs paziņojums atkarībā no lietotāja lomas
              let pendingMessage = "❌ Your account is pending approval. ";
              if (user.status === 1) {
                // Darbinieks - vadītājs aktivizēs
                pendingMessage += "Please wait for the employer to activate your account.";
              } else if (user.status === 2) {
                // Vadītājs - administrators aktivizēs
                pendingMessage += "Please wait for the administrator to activate your account.";
              } else {
                pendingMessage += "Please wait for the administrator to activate your account.";
              }
              
              return res.json({
                success: false,
                message: pendingMessage
              });
            }

            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"
            });
          }
        } catch (bcryptErr) {
          console.error("❌ Password comparison failed:", bcryptErr);
          return res.status(500).json({
            success: false,
            message: "❌ Authentication error"
          });
        }
      } 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) => {
  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

  try {
    // Jaucē paroli ar bcrypt (salt rounds = 10)
    const hashedPassword = await bcrypt.hash(password, 10);

    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, hashedPassword, 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);

            // Atšķirīgs paziņojums atkarībā no lietotāja lomas
            let message = "✅ Account created! ";
            if (userStatus === 1) {
              // Darbinieks - vadītājs pārskatīs
              message += "Employer will review your registration soon.";
            } else if (userStatus === 2) {
              // Vadītājs - administrators pārskatīs
              message += "Administrator will review your registration soon.";
            } else {
              message += "Administrator will review your registration soon.";
            }

            res.json({
              success: true,
              message: message,
              userId: result.insertId,
              requiresActivation: true
            });
          }
        );
      }
    );
  } catch (hashErr) {
    console.error("❌ Password hashing failed:", hashErr);
    return res.status(500).json({
      success: false,
      message: "❌ Account creation failed"
    });
  }
});

// Pievienot jaunu darbinieku
app.post("/api/employees", async (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" });
  }

  try {
    // Jaucē paroli ar bcrypt (salt rounds = 10)
    const hashedPassword = await bcrypt.hash(password, 10);

    db.query(
      "INSERT INTO users (name, fname, email, password, status, farm_id, active, session) VALUES (?, ?, ?, ?, ?, ?, 1, NOW())",
      [name, fname, email, hashedPassword, 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" });
      }
    );
  } catch (hashErr) {
    console.error("❌ Password hashing failed:", hashErr);
    return res.status(500).json({ error: "Employee addition failed" });
  }
});

// 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", async (req, res) => {
  const { id } = req.params;
  const { currentPassword, newPassword } = req.body;

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

  try {
    db.query(
      "SELECT password FROM users WHERE id = ?",
      [id],
      async (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" });
        }

        // Salīdzina ievadīto pašreizējo paroli ar jaucēto paroli datu bāzē
        const passwordMatch = await bcrypt.compare(currentPassword, results[0].password);

        if (!passwordMatch) {
          return res.status(400).json({ error: "Current password is incorrect" });
        }

        // Jaucē jauno paroli
        const hashedNewPassword = await bcrypt.hash(newPassword, 10);

        db.query(
          "UPDATE users SET password = ? WHERE id = ?",
          [hashedNewPassword, 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" });
          }
        );
      }
    );
  } catch (bcryptErr) {
    console.error("❌ Password hashing failed:", bcryptErr);
    return res.status(500).json({ error: "Password change failed" });
  }
});

// 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) {
                console.log(`⚠️ Fuel threshold reached (${totalFuel}L) for farm ${farm_id}`);
                // 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) {
                      console.error("❌ Failed to find farm manager:", managerErr);
                    } else if (managerResults.length > 0) {
                      const { email, farm_name } = managerResults[0];
                      console.log(`📧 Sending alert email to manager: ${email}`);
                      // Nosūtīt e-pastu asinhroni (negaidot atbildi)
                      sendFuelAlertEmail(email, farm_name, totalFuel);
                    } else {
                      console.log(`⚠️ No active farm manager found for farm ${farm_id}`);
                    }
                  }
                );
              }

              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 WHERE active = 1 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 WHERE active = 1 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" });
  }

  // Vispirms pārbauda, vai suga ar šo nosaukumu jau eksistē
  db.query(
    "SELECT id, active FROM crop_species WHERE name = ?",
    [name],
    (checkErr, checkResults) => {
      if (checkErr) {
        console.error("❌ Species check failed:", checkErr);
        return res.status(500).json({ error: "Species check failed" });
      }

      // Ja suga jau eksistē
      if (checkResults.length > 0) {
        const species = checkResults[0];
        
        // Ja tā jau ir aktīva, atgriezt kļūdu
        if (species.active === 1) {
          return res.status(400).json({ error: "Species with this name already exists" });
        }

        // Ja tā ir neaktīva, reaktivizēt to
        db.query(
          "UPDATE crop_species SET active = 1 WHERE id = ?",
          [species.id],
          (updateErr) => {
            if (updateErr) {
              console.error("❌ Species reactivation failed:", updateErr);
              return res.status(500).json({ error: "Species reactivation failed" });
            }
            res.json({ success: true, id: species.id, message: "Species reactivated successfully" });
          }
        );
      } else {
        // Ja suga neeksistē, izveidot jaunu
        db.query(
          "INSERT INTO crop_species (name, active) VALUES (?, 1)",
          [name],
          (insertErr, result) => {
            if (insertErr) {
              console.error("❌ Species addition failed:", insertErr);
              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" });
  }

  // Vispirms pārbauda, vai šķirne ar šo nosaukumu jau eksistē šai sugai
  db.query(
    "SELECT id, active FROM crop_breed WHERE name = ? AND species_id = ?",
    [name, species_id],
    (checkErr, checkResults) => {
      if (checkErr) {
        console.error("❌ Breed check failed:", checkErr);
        return res.status(500).json({ error: "Breed check failed" });
      }

      // Ja šķirne jau eksistē
      if (checkResults.length > 0) {
        const breed = checkResults[0];
        
        // Ja tā jau ir aktīva, atgriezt kļūdu
        if (breed.active === 1) {
          return res.status(400).json({ error: "Breed with this name already exists for this species" });
        }

        // Ja tā ir neaktīva, reaktivizēt to
        db.query(
          "UPDATE crop_breed SET active = 1 WHERE id = ?",
          [breed.id],
          (updateErr) => {
            if (updateErr) {
              console.error("❌ Breed reactivation failed:", updateErr);
              return res.status(500).json({ error: "Breed reactivation failed" });
            }
            res.json({ success: true, id: breed.id, message: "Breed reactivated successfully" });
          }
        );
      } else {
        // Ja šķirne neeksistē, izveidot jaunu
        db.query(
          "INSERT INTO crop_breed (name, species_id, active) VALUES (?, ?, 1)",
          [name, species_id],
          (insertErr, result) => {
            if (insertErr) {
              console.error("❌ Breed addition failed:", insertErr);
              return res.status(500).json({ error: "Breed addition failed" });
            }
            res.json({ success: true, id: result.insertId, message: "Breed added successfully" });
          }
        );
      }
    }
  );
});

// Deaktivizē kultūraugu sugu (soft delete)
// Pārbauda, vai suga tiek izmantota laukos, pirms dzēšanas
// Ja suga tiek dzēsta, arī visas tās šķirnes tiek deaktivizētas
app.delete("/api/crop-species/:id", (req, res) => {
  const { id } = req.params;

  // Vispirms pārbauda, vai šī suga tiek izmantota kādā laukā
  db.query(
    `SELECT COUNT(*) as count FROM fields f 
     JOIN crop_breed cb ON f.breed_id = cb.id 
     WHERE cb.species_id = ?`,
    [id],
    (checkErr, checkResults) => {
      if (checkErr) {
        console.error("❌ Species usage check failed:", checkErr);
        return res.status(500).json({ error: "Database query failed" });
      }

      // Ja suga tiek izmantota, atgriezt kļūdu
      if (checkResults[0].count > 0) {
        return res.status(400).json({ 
          error: "Cannot delete this species because it is being used in fields. Please remove it from all fields first." 
        });
      }

      // Ja suga netiek izmantota, deaktivizēt sugu un visas tās šķirnes
      db.query(
        "UPDATE crop_species SET active = 0 WHERE id = ?",
        [id],
        (speciesErr) => {
          if (speciesErr) {
            console.error("❌ Species deletion failed:", speciesErr);
            return res.status(500).json({ error: "Species deletion failed" });
          }

          // Deaktivizēt visas šķirnes, kas pieder šai sugai
          db.query(
            "UPDATE crop_breed SET active = 0 WHERE species_id = ?",
            [id],
            (breedsErr) => {
              if (breedsErr) {
                console.error("❌ Breeds deactivation failed:", breedsErr);
                return res.status(500).json({ error: "Breeds deactivation failed" });
              }

              res.json({ 
                success: true, 
                message: "Species and all its breeds deleted successfully" 
              });
            }
          );
        }
      );
    }
  );
});

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

  db.query("UPDATE crop_breed SET active = 0 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`);
});