import { Request, Response, NextFunction, RequestHandler } from "express";
import { User } from "../models/User";
import { Subscription } from "../models/Subscription";
import { PricingPlan } from "../models/PricingPlan";
import { userSchema, userSchemaEdit } from "../schema/userSchema";
import bcrypt from "bcryptjs";
import jwt, { JwtPayload } from "jsonwebtoken";
import crypto from "crypto";
import { PartialModelObject } from "objection";
import { renderEmailTemplate } from "../utils/emailTemplate";
import dotenv from "dotenv";
import { sendEmail } from "../services/emailService";

dotenv.config(); // load .env variables
interface TokenPayload extends JwtPayload {
  id: number;
  role: string;
}

const { OAuth2Client } = require("google-auth-library");
const client = new OAuth2Client({
  clientId: process.env.GOOGLE_CLIENT_ID,
  clientSecret: process.env.GOOGLE_CLIENT_SECRET,
  redirectUri: "postmessage", // Match useGoogleLogin auth-code flow
});

export class UsersController {
  private sendError(res: Response, status: number, message: string): void {
    res.status(status).json({ message });
  }

  private sendSuccess(res: Response, message: string, data?: any): void {
    res.status(200).json({ success: true, message, data });
  }

  authUser: RequestHandler = async (
    req: Request,
    res: Response,
    next: NextFunction
  ): Promise<void> => {
    try {
      // Type assertion to include userId
      const session = req.session as typeof req.session & { userId?: number };

      // Check if session and userId exist
      if (!session || session.userId === undefined) {
        res.status(401).json({
          isAuthenticated: false,
          message: "Not logged in",
        });
        return;
      }

      const userId = session.userId;
      console.log("User ID from session:", userId);

      // Fetch user from database
      const user = await User.query().findById(userId);

      if (!user) {
        res.status(401).json({
          isAuthenticated: false,
          message: "User not found",
        });
        return;
      }

      // Return user data
      res.json({
        isAuthenticated: true,
        user: {
          id: user.id,
          email: user.user_details?.email,
          name:
            user.user_details?.first_name + " " + user.user_details.last_name,
          role: user.user_details?.role,
        },
      });
    } catch (error) {
      console.error("Auth user error:", error);
      res.status(500).json({
        isAuthenticated: false,
        message: "Internal server error",
      });
    }
  };

  show: RequestHandler = async (
    req: Request,
    res: Response,
    next: NextFunction
  ): Promise<void> => {
    try {
      const users = await User.query().select(
        "id",
        "user_details",
        "email_verified",
        "is_premium",
        "status"
      );
      this.sendSuccess(res, "Users fetched successfully", users);
    } catch (err) {
      console.error("Error fetching custom pages:", err);
      next(err);
    }
  };

  add: RequestHandler = async (
    req: Request,
    res: Response,
    next: NextFunction
  ): Promise<void> => {
    try {
      const userData = req.body;

      const parsedData = userSchema.parse(userData);

      if (parsedData.password !== parsedData.confirm_password) {
        res.status(400).json({ message: "Passwords do not match" });
        return;
      }
      const existingUsername = await User.query()
        .whereRaw(
          "LOWER(JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.username'))) = ?",
          [parsedData.username.toLowerCase()]
        )
        .first();

      const existingEmail = await User.query()
        .orWhereRaw(
          "LOWER(JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.email'))) = ?",
          [parsedData.email.toLowerCase()]
        )
        .first();

      if (existingUsername && existingEmail) {
        res.status(400).json({ message: "Username or email already exists" });
        return;
      }
      if (existingUsername) {
        res.status(400).json({ message: "Username already exists" });
        return;
      }
      if (existingEmail) {
        res.status(400).json({ message: "Email already exists" });
        return;
      }

      const hashedPassword = await bcrypt.hash(parsedData.password, 10);

      const newUser = await User.query().insert({
        user_details: {
          first_name: parsedData.firstName,
          last_name: parsedData.lastName,
          username: parsedData.username,
          email: parsedData.email,
          role: parsedData.role,
        },
        password: hashedPassword,
        stripe_id: parsedData.stripe_id,
        is_premium: parsedData.is_premium,
        status: parsedData.status,
      });

      res
        .status(201)
        .json({ message: "User created successfully", user: newUser });
    } catch (err: any) {
      if (err.name === "ZodError") {
        res
          .status(400)
          .json({ message: "Validation Error", errors: err.errors });
      } else {
        console.error(err);
        res.status(500).json({ message: "Internal server error" });
      }
    }
  };

  get: RequestHandler = async (
    req: Request,
    res: Response,
    next: NextFunction
  ): Promise<void> => {
    try {
      const userId = req.params.id;

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

      const user = await User.query().findById(userId);

      if (!user) {
        res.status(404).json({ message: "User not found" });
        return;
      }

      res.status(200).json({
        success: true,
        message: "User fetched successfully",
        data: user,
      });
    } catch (error) {
      console.error("Error fetching user:", error);
      res.status(500).json({ message: "Failed to fetch user" });
    }
  };

  edit: RequestHandler = async (req, res): Promise<void> => {
    try {
      const { id } = req.params;
      const userData = req.body;
      const userSchemaEditPartial = userSchemaEdit._def.schema
        .partial()
        .superRefine((data, ctx) => {
          if (data.password || data.confirm_password) {
            if (data.password !== data.confirm_password) {
              ctx.addIssue({
                code: "custom",
                path: ["confirm_password"],
                message: "Passwords do not match",
              });
            }
          }
        });
      const parsedData = userSchemaEditPartial.parse(userData);

      const existingUser = await User.query().findById(id);
      if (!existingUser) {
        res.status(404).json({ message: "User not found" });
        return;
      }

      // Check uniqueness for username/email
      const usernameExists = await User.query()
        .whereRaw(
          "LOWER(JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.username'))) = ?",
          [parsedData.username?.toLowerCase()]
        )
        .whereNot("id", id)
        .first();

      const emailExists = await User.query()
        .whereRaw(
          "LOWER(JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.email'))) = ?",
          [parsedData.email?.toLowerCase()]
        )
        .whereNot("id", id)
        .first();

      if (usernameExists) {
        res.status(400).json({ message: "Username already exists" });
        return;
      }
      if (emailExists) {
        res.status(400).json({ message: "Email already exists" });
        return;
      }
      const updatedFields: any = {
        user_details: {
          first_name: parsedData.firstName,
          last_name: parsedData.lastName,
          username: parsedData.username,
          email: parsedData.email,
          role: parsedData.role,
        },
        stripe_id: parsedData.stripe_id,
        is_premium: parsedData.is_premium,
        status: parsedData.status,
      };

      if (parsedData.password && parsedData.confirm_password) {
        if (parsedData.password !== parsedData.confirm_password) {
          res.status(400).json({ message: "Passwords do not match" });
          return;
        }

        const hashedPassword = await bcrypt.hash(parsedData.password, 10);
        updatedFields.password = hashedPassword;
      }

      await User.query().findById(id).patch(updatedFields);

      res.json({ message: "User updated successfully" });
      return;
    } catch (err: any) {
      console.error(err);
      res.status(500).json({ message: "Server error" });
      return;
    }
  };

  delete: RequestHandler = async (
    req: Request,
    res: Response,
    next: NextFunction
  ): Promise<void> => {
    try {
      const { id } = req.params;

      if (!id) {
        this.sendError(res, 400, "Missing user ID");
        return;
      }

      const user = await User.query().findById(id);
      if (!user) {
        this.sendError(res, 404, "User not found");
        return;
      }

      await User.query().deleteById(id);

      this.sendSuccess(res, "User deleted successfully");
    } catch (error) {
      console.error("Error deleting user:", error);
      this.sendError(res, 500, "Failed to delete user");
    }
  };

  authWebUser: RequestHandler = async (req, res) => {
    try {
      const token = req.cookies.token;

      if (!token) {
        res.status(401).json({
          isAuthenticated: false,
          message: "No token provided",
        });
        return;
      }

      // Verify JWT
      let decoded: TokenPayload;
      try {
        decoded = jwt.verify(
          token,
          process.env.JWT_SECRET_FRONTEND as string
        ) as TokenPayload;
      } catch (err) {
        res.status(401).json({
          isAuthenticated: false,
          message: "Invalid or expired token",
        });
        return;
      }

      // Fetch user with subscription → query + pricingPlans
      const user = await User.query()
        .findById(decoded.id)
        .withGraphFetched("subscription.[query, pricingPlans]");

      if (!user) {
        res.status(401).json({
          isAuthenticated: false,
          message: "User not found",
        });
        return;
      }

      if (!user.email_verified) {
        res.status(403).json({
          isAuthenticated: false,
          message: "Email not verified",
        });
        return;
      }

      const role = user.user_details?.role;
      if (role !== "admin" && role !== "web_user") {
        res.status(403).json({
          isAuthenticated: false,
          message: "Access denied. Invalid role.",
        });
        return;
      }

      res.json({
        isAuthenticated: true,
        user: {
          id: user.id,
          email: user.user_details?.email,
          name: `${user.user_details?.first_name} ${user.user_details?.last_name}`,
          username: user.user_details?.username,
          role: user.user_details?.role,
          status: user.status,
          login_via: user.login_via,
          subscription: user.subscription
            ? {
                ...user.subscription,
                query: user.subscription.query || null,
                // pricingPlan: user.subscription.pricingPlans || null,
              }
            : null,
        },
      });
    } catch (error) {
      console.error("Auth user error:", error);
      res.status(500).json({
        isAuthenticated: false,
        message: "Internal server error",
      });
    }
  };

  login: RequestHandler = async (req, res, next) => {
    try {
      const { email, password } = req.body;

      // Find user
      const user = await User.query()
        .whereRaw(`JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.email')) = ?`, [
          email,
        ])
        .first();

      if (!user) {
        res.status(400).json({ success: false, message: "Invalid email" });
        return;
      }

      // If email not verified
      if (!user.email_verified) {
        res
          .status(403)
          .json({ success: false, message: "Email is not Verified" });
        return;
      }

      // Compare password
      const isMatch = await bcrypt.compare(password, user.password);
      if (!isMatch) {
        res.status(400).json({ success: false, message: "Invalid password" });
        return;
      }

      // Generate token
      const token = jwt.sign(
        { id: user.id, role: user.user_details.role },
        process.env.JWT_SECRET_FRONTEND as string,
        { expiresIn: "7d" }
      );

      await User.query()
        .patch({ is_premium: "logged_in" })
        .whereRaw(`JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.email')) = ?`, [
          email,
        ]);

      res.cookie("token", token, {
        httpOnly: true, // cannot be accessed by JS
        secure: process.env.NODE_ENV === "production", // only https in production
        sameSite: "strict",
        maxAge: 7 * 24 * 60 * 60 * 1000, // 7 days
      });

      res.status(200).json({
        success: true,
        message: "Login successful",
        token,
        user: {
          id: user.id,
          ...user.user_details,
          is_premium: user.is_premium,
          status: user.status,
        },
      });
    } catch (error) {
      console.error(error);
      res.status(500).json({ success: false, message: "Login failed" });
    }
  };

  logout: RequestHandler = async (req, res, next) => {
    // Clear the cookie
    res.clearCookie("token", {
      httpOnly: true,
      secure: process.env.NODE_ENV === "production", // true on prod
      sameSite: "lax",
      path: "/",
    });

    // Respond with not authenticated
    res.json({
      isAuthenticated: false,
      user: null,
      message: "Logged out successfully",
    });
  };

  register: RequestHandler = async (req, res) => {
    try {
      const { email, first_name, last_name, username, password } = req.body;

      // Check if email already exists
      const existingEmail = await User.query()
        .whereRaw(`JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.email')) = ?`, [
          email,
        ])
        .first();

      if (existingEmail) {
        res.status(400).json({
          success: false,
          message: "Email already exists",
        });
        return;
      }

      // Check if username already exists
      const existingUsername = await User.query()
        .whereRaw(
          `JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.username')) = ?`,
          [username]
        )
        .first();

      if (existingUsername) {
        res.status(400).json({
          success: false,
          message: "Username already exists",
        });
        return;
      }

      const hashedPassword = await bcrypt.hash(password, 10);

      const userDetails = {
        first_name,
        last_name,
        username,
        email,
        role: "web_user",
      };

      const token = crypto.randomBytes(32).toString("hex");
      const expiry = new Date(Date.now() + 5 * 60 * 1000); // 5 minutes from now
      const verificationUrl = `${process.env.FRONTEND_DOMAIN}/verificar-correo?token=${token}`;

      const htmlContent = renderEmailTemplate("verify-email.html", {
        first_name,
        last_name,
        verification_url: verificationUrl,
      });

      try {
        await sendEmail({
          to: [
            {
              email,
              name: `${first_name} ${last_name}`,
            },
          ],
          subject: "Verificación de correo electrónico",
          htmlContent,
        });
        console.log("Email sent successfully");
      } catch (emailError) {
        console.error(
          "Failed to send verification email:",
          JSON.stringify(emailError, null, 2)
        );
        res.status(500).json({
          success: false,
          message: "Failed to send verification email. User not created.",
        });
        return;
      }

      const newUserData = {
        user_details: userDetails,
        password: hashedPassword,
        email_verified: null,
        stripe_id: "",
        is_premium: "guest",
        status: "active",
        verification_token: token,
        verification_token_expiry: expiry,
      } satisfies PartialModelObject<User>;

      const newUser = await User.query().insert(newUserData);

      res.status(201).json({
        success: true,
        message: "User registered successfully. Verification email sent.",
        data: newUser,
      });
    } catch (error) {
      console.error("General error:", JSON.stringify(error, null, 2));
      res.status(500).json({ success: false, message: "Registration failed" });
    }
  };

  googleLogin: RequestHandler = async (req, res) => {
    try {
      const { code } = req.body;

      if (!code) {
        res
          .status(400)
          .json({ success: false, message: "Authorization code is required" });
        return;
      }

      // Debug environment variables
      console.log("GOOGLE_CLIENT_ID:", process.env.GOOGLE_CLIENT_ID);
      console.log(
        "GOOGLE_CLIENT_SECRET:",
        process.env.GOOGLE_CLIENT_SECRET ? "Set" : "Not set"
      );
      console.log("GOOGLE_REDIRECT_URI:", "postmessage");

      // Exchange authorization code for tokens
      const tokenResponse = await client.getToken({
        code,
        clientId: process.env.GOOGLE_CLIENT_ID,
        clientSecret: process.env.GOOGLE_CLIENT_SECRET,
        redirectUri: "postmessage",
      });

      console.log("Token Response:", tokenResponse.tokens);

      const { id_token } = tokenResponse.tokens;
      if (!id_token) {
        res
          .status(401)
          .json({ success: false, message: "Failed to retrieve ID token" });
        return;
      }

      // Verify the ID token
      const ticket = await client.verifyIdToken({
        idToken: id_token,
        audience: process.env.GOOGLE_CLIENT_ID,
      });

      const payload = ticket.getPayload();
      if (!payload) {
        res
          .status(401)
          .json({ success: false, message: "Invalid Google token" });
        return;
      }

      const { email, given_name, family_name, sub: googleId } = payload;

      if (!email) {
        res
          .status(400)
          .json({ success: false, message: "Google account has no email" });
        return;
      }

      // Check if user exists by email
      let user = await User.query()
        .whereRaw(`JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.email')) = ?`, [
          email,
        ])
        .first();

      if (!user) {
        const randomPassword = Math.random().toString(36).slice(-8); // Generate random 8-char string
        const hashedPassword = await bcrypt.hash(randomPassword, 10); // Hash with salt round 10
        // Create new user (skip email verification)
        const userDetails = {
          first_name: given_name,
          last_name: family_name,
          username: email.split("@")[0],
          email,
          role: "web_user",
          google_id: googleId,
        };

        const newUserData = {
          user_details: userDetails,
          email_verified: new Date(), // auto-verified
          stripe_id: "",
          is_premium: "logged_in",
          status: "active",
          verification_token: null,
          verification_token_expiry: null,
          login_via: "google",
          password: hashedPassword,
        } satisfies PartialModelObject<User>;

        user = await User.query().insert(newUserData);
      }

      // Generate JWT
      const authToken = jwt.sign(
        { id: user.id, role: user.user_details.role },
        process.env.JWT_SECRET_FRONTEND as string,
        { expiresIn: "7d" }
      );

      // Save as cookie
      res.cookie("token", authToken, {
        httpOnly: true,
        secure: process.env.NODE_ENV === "production",
        sameSite: "strict",
        maxAge: 7 * 24 * 60 * 60 * 1000,
      });

      const existingTrialSubscription = await Subscription.query()
        .where("subscriptions.user_id", user.id)
        .whereNull("subscriptions.deleted_at")
        .joinRelated("pricingPlans")
        .where("pricingPlans.is_trial", 1)
        .first();

      if (existingTrialSubscription) {
        res.status(200).json({
          success: true,
          message: "Google login successful",
          token: authToken,
          user: {
            id: user.id,
            ...user.user_details,
            is_premium: user.is_premium,
            status: user.status,
          },
        });
        return;
      }

      // Find trial plan
      const trialPlan = await PricingPlan.query()
        .findOne({ is_trial: 1 })
        .withGraphFetched("query") // eager load query relation
        .modifyGraph(
          "query",
          (builder: any) =>
            builder.select(
              "id",
              "query_name",
              "total_token",
              "total_characters"
            ) // only get needed fields
        );

      if (!trialPlan) {
        res
          .status(400)
          .json({ success: false, message: "No trial plan available" });
        return;
      }

      let expiryDate: Date;

      switch (trialPlan.duration) {
        case "weekly":
          expiryDate = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000); // +7 days
          break;
        case "monthly":
          expiryDate = new Date();
          expiryDate.setMonth(expiryDate.getMonth() + 1); // +1 month
          break;
        case "yearly":
          expiryDate = new Date();
          expiryDate.setFullYear(expiryDate.getFullYear() + 1); // +1 year
          break;
        default:
          expiryDate = new Date(); // fallback → now
      }
      // Create subscription for the user with the trial plan
      await Subscription.query().insert({
        user_id: user.id,
        plan_id: trialPlan.id,
        queries_id: trialPlan.queries_id,
        used_token: 0,
        left_token: trialPlan.query.total_token,
        used_words: 0,
        left_words: trialPlan.query.total_characters,
        expiry_date: expiryDate, // ✅ Date auto-converts to TIMESTAMP
      });

      res.status(200).json({
        success: true,
        message: "Google login successful",
        token: authToken,
        user: {
          id: user.id,
          ...user.user_details,
          is_premium: user.is_premium,
          status: user.status,
        },
      });
    } catch (error) {
      console.error("Google Login Error:", error);
      res.status(500).json({ success: false, message: "Google login failed" });
    }
  };

  forgetPassword: RequestHandler = async (req, res) => {
    try {
      const { email } = req.body;

      const user = await User.query()
        .whereRaw(`JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.email')) = ?`, [
          email,
        ])
        .first();

      if (!user) {
        res.status(400).json({ success: false, message: "Email not found" });
        return;
      }

      if (user.login_via === "google") {
        res.status(400).json({
          success: false,
          message:
            "You signed up with Google. Please login using Google instead.",
        });
        return;
      }

      const resetToken = jwt.sign(
        { id: user.id, email },
        process.env.JWT_SECRET_FRONTEND as string,
        { expiresIn: "1h" }
      );

      const resetUrl = `${process.env.FRONTEND_DOMAIN}/restablecer-contrasena?token=${resetToken}`;

      const htmlContent = renderEmailTemplate("reset-password.html", {
        first_name: user.user_details.first_name,
        last_name: user.user_details.last_name,
        reset_url: resetUrl,
      });

      await sendEmail({
        to: [
          {
            email,
            name: `${user.user_details.first_name} ${user.user_details.last_name}`,
          },
        ],
        subject: "¿Olvidaste tu contraseña?",
        htmlContent,
      });

      res
        .status(200)
        .json({ success: true, message: "Password reset link sent to email" });
    } catch (error) {
      console.error("ForgetPassword Error:", error);
      res
        .status(500)
        .json({ success: false, message: "Failed to process request" });
    }
  };

  resetPassword: RequestHandler = async (req, res) => {
    try {
      const { token, newPassword } = req.body;

      if (!token || !newPassword) {
        res
          .status(400)
          .json({ success: false, message: "Token and new password required" });
        return;
      }

      // Verify token
      let decoded: any;
      try {
        decoded = jwt.verify(token, process.env.JWT_SECRET_FRONTEND as string);
      } catch (err) {
        res
          .status(400)
          .json({ success: false, message: "Invalid or expired token" });
        return;
      }

      const userId = decoded.id;

      // Find user
      const user = await User.query().findById(userId);
      if (!user) {
        res.status(404).json({ success: false, message: "User not found" });
        return;
      }

      // Optional: disallow Google-only accounts
      if (user.login_via === "google") {
        res.status(400).json({
          success: false,
          message:
            "You signed up with Google. Please login using Google instead.",
        });
        return;
      }

      // Hash new password
      const hashedPassword = await bcrypt.hash(newPassword, 10);

      // Update password
      await User.query().patchAndFetchById(userId, {
        password: hashedPassword,
      });

      res.status(200).json({
        success: true,
        message:
          "Password reset successful. You can now login with your new password.",
      });
      return;
    } catch (error) {
      console.error("Reset Password Error:", error);
      res
        .status(500)
        .json({ success: false, message: "Failed to reset password" });
      return;
    }
  };

  changePassword: RequestHandler = async (req, res) => {
    try {
      const { userId, currentPassword, newPassword } = req.body;

      if (!currentPassword || !newPassword) {
        res.status(400).json({
          success: false,
          message: "Current password and new password are required",
        });
        return;
      }

      if (!userId) {
        res.status(401).json({ success: false, message: "Unauthorized" });
        return;
      }

      // Find user
      const user = await User.query().findById(userId);
      if (!user) {
        res.status(404).json({ success: false, message: "User not found" });
        return;
      }

      // Block Google login accounts
      if (user.login_via === "google") {
        res.status(400).json({
          success: false,
          message:
            "You signed up with Google. Please change your password in Google account settings.",
        });
        return;
      }

      // Check current password
      const isMatch = await bcrypt.compare(currentPassword, user.password);
      if (!isMatch) {
        res
          .status(400)
          .json({ success: false, message: "Incorrect current password" });
        return;
      }

      // Hash new password
      const hashedPassword = await bcrypt.hash(newPassword, 10);

      // Update password
      await User.query().patchAndFetchById(userId, {
        password: hashedPassword,
      });

      res.status(200).json({
        success: true,
        message: "Password changed successfully.",
      });
    } catch (error) {
      console.error("Change Password Error:", error);
      res
        .status(500)
        .json({ success: false, message: "Failed to change password" });
    }
  };

  verifyEmail: RequestHandler = async (
    req: Request,
    res: Response,
    next: NextFunction
  ): Promise<void> => {
    try {
      const { token } = req.query;

      if (
        !token ||
        typeof token !== "string" ||
        token === "null" ||
        token === "undefined"
      ) {
        res.status(400).json({ success: false, message: "Token is required" });
        return;
      }

      const user = await User.query().findOne({ verification_token: token });

      if (!user) {
        res.status(400).json({ success: false, message: "Invalid token" });
        return;
      }

      // Check expiry date
      if (
        user.verification_token_expiry &&
        new Date(user.verification_token_expiry) < new Date()
      ) {
        res.status(400).json({ success: false, message: "Token has expired" });
        return;
      }

      await User.query()
        .patch({
          email_verified: new Date(),
          verification_token: null,
          verification_token_expiry: null,
        })
        .where("id", user.id);

      // Check if user already has an active trial subscription
      const existingTrialSubscription = await Subscription.query()
        .where("subscriptions.user_id", user.id)
        .whereNull("subscriptions.deleted_at")
        .joinRelated("pricingPlans")
        .where("pricingPlans.is_trial", 1)
        .first();

      if (existingTrialSubscription) {
        res
          .status(200)
          .json({ success: true, message: "Email verified successfully" });
        return;
      }

      // Find trial plan
      const trialPlan = await PricingPlan.query()
        .findOne({ is_trial: 1 })
        .withGraphFetched("query") // eager load query relation
        .modifyGraph(
          "query",
          (builder: any) =>
            builder.select(
              "id",
              "query_name",
              "total_token",
              "total_characters"
            ) // only get needed fields
        );

      if (!trialPlan) {
        res
          .status(400)
          .json({ success: false, message: "No trial plan available" });
        return;
      }

      let expiryDate: Date;

      switch (trialPlan.duration) {
        case "weekly":
          expiryDate = new Date(Date.now() + 7 * 24 * 60 * 60 * 1000); // +7 days
          break;
        case "monthly":
          expiryDate = new Date();
          expiryDate.setMonth(expiryDate.getMonth() + 1); // +1 month
          break;
        case "yearly":
          expiryDate = new Date();
          expiryDate.setFullYear(expiryDate.getFullYear() + 1); // +1 year
          break;
        default:
          expiryDate = new Date(); // fallback → now
      }
      // Create subscription for the user with the trial plan
      await Subscription.query().insert({
        user_id: user.id,
        plan_id: trialPlan.id,
        queries_id: trialPlan.queries_id,
        used_token: 0,
        left_token: trialPlan.query.total_token,
        used_words: 0,
        left_words: trialPlan.query.total_characters,
        expiry_date: expiryDate, // ✅ Date auto-converts to TIMESTAMP
      });

      res
        .status(200)
        .json({ success: true, message: "Email verified successfully" });
    } catch (error) {
      console.error(error);
      res
        .status(500)
        .json({ success: false, message: "Email verification failed" });
    }
  };
  resendEmail: RequestHandler = async (req, res) => {
    try {
      const { email } = req.body;
      if (!email) {
        res.status(400).json({ success: false, message: "Email is required" });
        return;
      }

      const user = await User.query()
        .whereRaw(`JSON_UNQUOTE(JSON_EXTRACT(user_details, '$.email')) = ?`, [
          email,
        ])
        .first();

      if (!user) {
        res.status(404).json({ success: false, message: "User not found" });
        return;
      }

      if (user.email_verified) {
        res
          .status(400)
          .json({ success: false, message: "Email already verified" });
        return;
      }

      const token = crypto.randomBytes(32).toString("hex");
      const expiry = new Date(Date.now() + 5 * 60 * 1000);

      await User.query()
        .patch({
          verification_token: token,
          verification_token_expiry: expiry,
        })
        .where("id", user.id);

      const verificationUrl = `${process.env.FRONTEND_DOMAIN}/verificar-correo?token=${token}`;

      const htmlContent = renderEmailTemplate("resend-verify-email.html", {
        first_name: user.user_details.first_name,
        last_name: user.user_details.last_name,
        verification_url: verificationUrl,
      });

      await sendEmail({
        to: [
          {
            email,
            name: `${user.user_details.first_name} ${user.user_details.last_name}`,
          },
        ],
        subject: "Verifica tu correo electrónico",
        htmlContent,
      });

      res.status(200).json({
        success: true,
        message: "Verification email resent successfully",
      });
    } catch (error) {
      console.error("ResendEmail Error:", error);
      res
        .status(500)
        .json({ success: false, message: "Resend verification failed" });
    }
  };
}