const { Op } = require("sequelize");
const { Companies, Users, Projects, Subscriptions } = require("../models");
const { buildCheckoutSessionUrl } = require("../utils/stripeCheckout");
const { sendPriceSetEmail } = require("../utils/emailService");

function subscriptionMrr(sub) {
  if (!sub || sub.subscriptionType !== "paid") return 0;
  if (sub.userPrice == null || sub.projectPrice == null) return 0;
  const sds =
    (sub.monthlySDSSaveCount || 0) * (sub.SDSSavePrice || 0) +
    (sub.monthlySDSSaveCount || 0) * (sub.SDSSearchPerSavePrice || 0);
  return (
    (sub.usersCount || 0) * sub.userPrice +
    (sub.projectsCount || 0) * sub.projectPrice +
    sds
  );
}

const dashboard = async (req, res) => {
  try {
    const companies = await Companies.findAll();
    const totalCompanies = companies.length;
    let activeSubscriptions = 0;
    let trialCompanies = 0;
    let expiredCompanies = 0;
    let totalRevenue = 0;
    const now = new Date();
    const in7 = new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000);

    for (const c of companies) {
      const sub = await Subscriptions.findOne({
        where: { CompanyId: c.id },
        order: [["createdAt", "DESC"]],
      });
      if (!sub) continue;
      const ends = sub.subscriptionEndsAt ? new Date(sub.subscriptionEndsAt) : null;
      if (ends && ends < now) {
        expiredCompanies++;
      } else if (sub.subscriptionType === "paid" && ends && ends >= now) {
        activeSubscriptions++;
        totalRevenue += subscriptionMrr(sub);
      } else if (sub.subscriptionType === "free trial") {
        trialCompanies++;
      }
    }

    const pendingRequests = await Subscriptions.count({
      where: { userPrice: { [Op.is]: null } },
    });

    const companiesExpiringSoon = await Subscriptions.findAll({
      where: {
        subscriptionEndsAt: { [Op.between]: [now, in7] },
        subscriptionType: "paid",
      },
      include: [Companies],
      limit: 20,
    });

    const monthlyRevenue = [];
    for (let i = 11; i >= 0; i--) {
      const monthStart = new Date(now.getFullYear(), now.getMonth() - i, 1);
      const monthEnd = new Date(now.getFullYear(), now.getMonth() - i + 1, 0, 23, 59, 59);
      const activePaid = await Subscriptions.findAll({
        where: {
          subscriptionType: "paid",
          userPrice: { [Op.ne]: null },
          subscriptionEndsAt: { [Op.gt]: monthStart },
        },
      });
      let rev = 0;
      for (const s of activePaid) {
        if (new Date(s.createdAt) <= monthEnd) rev += subscriptionMrr(s);
      }
      monthlyRevenue.push({
        month: `${monthStart.getFullYear()}-${String(monthStart.getMonth() + 1).padStart(2, "0")}`,
        revenue: Math.round(rev * 100) / 100,
      });
    }

    const recentCompanies = await Companies.findAll({
      order: [["createdAt", "DESC"]],
      limit: 5,
    });

    res.json({
      totalCompanies,
      activeSubscriptions,
      trialCompanies,
      expiredCompanies,
      totalRevenue: Math.round(totalRevenue * 100) / 100,
      pendingRequests,
      companiesExpiringSoon: companiesExpiringSoon.map((s) => ({
        companyId: s.CompanyId,
        companyName: s.Company?.name,
        subscriptionEndsAt: s.subscriptionEndsAt,
      })),
      monthlyRevenue,
      recentCompanies: await Promise.all(
        recentCompanies.map(async (c) => ({
          id: c.id,
          name: c.name,
          createdAt: c.createdAt,
          subscription: await Subscriptions.findOne({
            where: { CompanyId: c.id },
            order: [["createdAt", "DESC"]],
          }),
        })),
      ),
    });
  } catch (e) {
    console.error(e);
    res.status(500).json({ message: "Dashboard failed" });
  }
};

const listCompanies = async (req, res) => {
  try {
    const page = Math.max(1, parseInt(req.query.page, 10) || 1);
    const limit = Math.min(100, Math.max(5, parseInt(req.query.limit, 10) || 20));
    const offset = (page - 1) * limit;
    const search = (req.query.search || "").trim();

    const where = search
      ? {
          [Op.or]: [
            { name: { [Op.like]: `%${search}%` } },
            { email: { [Op.like]: `%${search}%` } },
          ],
        }
      : {};

    const { rows, count } = await Companies.findAndCountAll({
      where,
      limit,
      offset,
      order: [["createdAt", "DESC"]],
    });

    const out = [];
    for (const c of rows) {
      const sub = await Subscriptions.findOne({
        where: { CompanyId: c.id },
        order: [["createdAt", "DESC"]],
      });
      const usersCount = await Users.count({ where: { CompanyId: c.id } });
      const projectsCount = await Projects.count({ where: { CompanyId: c.id } });
      const ends = sub?.subscriptionEndsAt ? new Date(sub.subscriptionEndsAt) : null;
      const now = new Date();
      let status = "trial";
      if (ends && ends < now) status = "expired";
      else if (sub?.subscriptionType === "paid" && ends && ends >= now) status = "active";

      out.push({
        id: c.id,
        name: c.name,
        email: c.email,
        country: c.country,
        phoneNumber: c.phoneNumber,
        orgNumber: c.orgNumber,
        subscriptionStatus: status,
        subscriptionEndsAt: sub?.subscriptionEndsAt,
        usersCount,
        projectsCount,
        usersLimit: sub?.usersCount,
        projectsLimit: sub?.projectsCount,
        userPrice: sub?.userPrice,
        projectPrice: sub?.projectPrice,
        SDSSavePrice: sub?.SDSSavePrice,
        SDSSearchPerSavePrice: sub?.SDSSearchPerSavePrice,
        createdAt: c.createdAt,
      });
    }

    res.json({ data: out, total: count, page, limit });
  } catch (e) {
    console.error(e);
    res.status(500).json({ message: "Failed to list companies" });
  }
};

const getCompany = async (req, res) => {
  try {
    const company = await Companies.findByPk(req.params.id);
    if (!company) return res.status(404).json({ message: "Not found" });

    const users = await Users.findAll({
      where: { CompanyId: company.id },
      attributes: { exclude: ["password"] },
      order: [["createdAt", "DESC"]],
    });
    const projects = await Projects.findAll({ where: { CompanyId: company.id } });
    const sub = await Subscriptions.findOne({
      where: { CompanyId: company.id },
      order: [["createdAt", "DESC"]],
    });

    res.json({
      company,
      subscription: sub,
      users,
      projects,
      usage: sub
        ? {
            usersUsed: users.length,
            projectsUsed: projects.length,
            monthlySDSSearchUsage: sub.monthlySDSSearchUsage,
            monthlySDSSaveUsage: sub.monthlySDSSaveUsage,
          }
        : null,
    });
  } catch (e) {
    console.error(e);
    res.status(500).json({ message: "Failed to load company" });
  }
};

const listRequests = async (req, res) => {
  try {
    const rows = await Subscriptions.findAll({
      where: { userPrice: { [Op.is]: null } },
      include: [Companies],
      order: [["createdAt", "DESC"]],
    });
    res.json(
      rows.map((s) => ({
        id: s.id,
        companyId: s.CompanyId,
        companyName: s.Company?.name,
        companyEmail: s.Company?.email,
        usersCount: s.usersCount,
        projectsCount: s.projectsCount,
        monthlySDSSearchCount: s.monthlySDSSearchCount,
        monthlySDSSaveCount: s.monthlySDSSaveCount,
        createdAt: s.createdAt,
      })),
    );
  } catch (e) {
    console.error(e);
    res.status(500).json({ message: "Failed to load requests" });
  }
};

const setRequestPrices = async (req, res) => {
  try {
    const { subscriptionId } = req.params;
    const { userPrice, projectPrice, SDSSavePrice, SDSSearchPerSavePrice } = req.body;

    if (
      userPrice == null ||
      projectPrice == null ||
      SDSSavePrice == null ||
      SDSSearchPerSavePrice == null
    ) {
      return res.status(400).json({ message: "All price fields are required" });
    }

    const sub = await Subscriptions.findByPk(subscriptionId, { include: [Companies] });
    if (!sub) return res.status(404).json({ message: "Subscription not found" });

    await Subscriptions.update(
      {
        userPrice: Number(userPrice),
        projectPrice: Number(projectPrice),
        SDSSavePrice: Number(SDSSavePrice),
        SDSSearchPerSavePrice: Number(SDSSearchPerSavePrice),
      },
      { where: { id: subscriptionId } },
    );

    const admin = await Users.findOne({
      where: { CompanyId: sub.CompanyId, role: "admin" },
    });
    const paymentUrl = await buildCheckoutSessionUrl(sub.CompanyId);

    const updated = await Subscriptions.findByPk(subscriptionId);

    if (admin?.email) {
      await sendPriceSetEmail({
        to: admin.email,
        adminName: admin.fullname || admin.email,
        companyName: sub.Company?.name || "Your company",
        subscription: updated,
        paymentUrl,
      });
    }

    res.json({ message: "Prices saved and email sent", paymentUrl });
  } catch (e) {
    console.error(e);
    res.status(500).json({ message: e.message || "Failed to set prices" });
  }
};

const extendSubscription = async (req, res) => {
  try {
    const { id } = req.params;
    const months = Math.min(36, Math.max(1, parseInt(req.body.months, 10) || 1));

    const company = await Companies.findByPk(id);
    if (!company) return res.status(404).json({ message: "Company not found" });

    const sub = await Subscriptions.findOne({ where: { CompanyId: id }, order: [["createdAt", "DESC"]] });
    if (!sub) return res.status(404).json({ message: "Subscription not found" });

    const base = sub.subscriptionEndsAt && new Date(sub.subscriptionEndsAt) > new Date()
      ? new Date(sub.subscriptionEndsAt)
      : new Date();
    base.setMonth(base.getMonth() + months);

    await Subscriptions.update({ subscriptionEndsAt: base }, { where: { id: sub.id } });
    await Companies.update({ subscriptionEndsAt: base }, { where: { id } });

    res.json({ message: "Subscription extended", subscriptionEndsAt: base });
  } catch (e) {
    console.error(e);
    res.status(500).json({ message: "Failed to extend" });
  }
};

const suspendCompany = async (req, res) => {
  try {
    const { id } = req.params;
    const past = new Date(Date.now() - 24 * 60 * 60 * 1000);

    const sub = await Subscriptions.findOne({ where: { CompanyId: id }, order: [["createdAt", "DESC"]] });
    if (sub) await Subscriptions.update({ subscriptionEndsAt: past }, { where: { id: sub.id } });
    await Companies.update({ subscriptionEndsAt: past }, { where: { id } });

    res.json({ message: "Company suspended" });
  } catch (e) {
    console.error(e);
    res.status(500).json({ message: "Failed to suspend" });
  }
};

const analytics = async (req, res) => {
  try {
    const paid = await Subscriptions.findAll({
      where: { subscriptionType: "paid", userPrice: { [Op.ne]: null } },
      include: [Companies],
    });

    const byCompany = paid.map((s) => ({
      companyId: s.CompanyId,
      companyName: s.Company?.name,
      mrr: subscriptionMrr(s),
    }));
    byCompany.sort((a, b) => b.mrr - a.mrr);
    const top5 = byCompany.slice(0, 5);

    const trial = await Subscriptions.count({ where: { subscriptionType: "free trial" } });
    const paidCount = await Subscriptions.count({ where: { subscriptionType: "paid" } });

    const now = new Date();
    const monthlyRevenue = [];
    for (let i = 11; i >= 0; i--) {
      const monthStart = new Date(now.getFullYear(), now.getMonth() - i, 1);
      const activePaid = await Subscriptions.findAll({
        where: {
          subscriptionType: "paid",
          userPrice: { [Op.ne]: null },
          subscriptionEndsAt: { [Op.gt]: monthStart },
        },
      });
      let rev = 0;
      for (const s of activePaid) rev += subscriptionMrr(s);
      monthlyRevenue.push({
        month: `${monthStart.getFullYear()}-${String(monthStart.getMonth() + 1).padStart(2, "0")}`,
        revenue: Math.round(rev * 100) / 100,
      });
    }

    res.json({
      monthlyRevenue,
      topCompaniesByRevenue: top5,
      subscriptionBreakdown: { trial, paid: paidCount },
    });
  } catch (e) {
    console.error(e);
    res.status(500).json({ message: "Analytics failed" });
  }
};

module.exports = {
  dashboard,
  listCompanies,
  getCompany,
  listRequests,
  setRequestPrices,
  extendSubscription,
  suspendCompany,
  analytics,
};
