import db from "../db";
import { TEXT_MESSAGES } from "../const";

export class UserModel {
  static async initialize() {
    try {
      await db.query(`
        CREATE TABLE IF NOT EXISTS users (
          id INT AUTO_INCREMENT PRIMARY KEY,
          username VARCHAR(255) NOT NULL,
          email VARCHAR(255) UNIQUE NOT NULL,
          password VARCHAR(255) NOT NULL,
          createdAt DATETIME NOT NULL,
          UNIQUE (username)
        )
      `);

      await db.query(`
        CREATE TABLE IF NOT EXISTS basicinfo (
          userId INT PRIMARY KEY,
          firstName VARCHAR(255) NOT NULL DEFAULT 'Unknown',
          lastName VARCHAR(255) NOT NULL DEFAULT 'Unknown',
          country VARCHAR(100),
          address VARCHAR(255),
          pincode VARCHAR(20),
          companyName VARCHAR(255),
          phoneNumber VARCHAR(20),
          userType ENUM('superadmin', 'admin', 'customer') NOT NULL DEFAULT 'customer',
          approval BOOLEAN NOT NULL DEFAULT false,
          FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
        )
      `);

      await db.query(`
        CREATE TABLE IF NOT EXISTS kyc (
          id INT AUTO_INCREMENT PRIMARY KEY,
          userId INT NOT NULL,
          sessionId VARCHAR(255) NOT NULL UNIQUE,
          provider VARCHAR(100) NOT NULL DEFAULT 'didit',
          status ENUM('Pending','Approved','Rejected','Cancelled') NOT NULL DEFAULT 'Pending',
          raw TEXT NULL,
          createdAt DATETIME NOT NULL,
          updatedAt DATETIME NOT NULL,
          FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
        )
      `);

      console.info("Users, BasicInfo and KYC tables initialized successfully");
    } catch (error) {
      console.error("Error initializing tables:", error);
    }
  }

  static async migrate() {
    try {
      const [rows] = await db.query(
        `SELECT COUNT(*) AS count 
         FROM information_schema.statistics 
         WHERE table_schema = DATABASE() AND table_name = 'users' AND index_name = 'username'`
      );
      if ((rows as any)[0].count === 0) {
        await db.query(`ALTER TABLE users ADD UNIQUE (username)`);
      }

      const [columns] = await db.query(`SHOW COLUMNS FROM basicinfo LIKE 'address'`);
      if ((columns as any).length === 0) {
        await db.query(`ALTER TABLE basicinfo ADD COLUMN address VARCHAR(255) AFTER country`);
      }

      const [approvalColumn] = await db.query(`SHOW COLUMNS FROM basicinfo LIKE 'approval'`);
      if ((approvalColumn as any).length === 0) {
        await db.query(`ALTER TABLE basicinfo ADD COLUMN approval BOOLEAN NOT NULL DEFAULT false`);
      }

      // KYC migrations (if you later need to add columns, sniff & ALTER here)

      console.info(TEXT_MESSAGES.USER_INFO);
    } catch (error) {
      console.error("Error migrating tables:", error);
    }
  }

  static async findByEmail(email: string) {
    const [rows] = await db.query(
      `
      SELECT u.*, b.firstName, b.lastName, b.country, b.address, b.pincode, b.companyName, b.phoneNumber, b.userType , b.approval
      FROM users u
      LEFT JOIN basicinfo b ON u.id = b.userId
      WHERE u.email = ?
      `,
      [email]
    );
    return (rows as any)[0];
  }

  static async findAll() {
    const [rows] = await db.query(
      `
      SELECT u.*, b.firstName, b.lastName, b.country, b.address, b.pincode, b.companyName, b.phoneNumber, b.userType, b.approval
      FROM users u
      LEFT JOIN basicinfo b ON u.id = b.userId
      `
    );
    return rows as any[];
  }

  static async findAllPaginated({
    page = 1,
    perPage = 10,
    searchQuery = "",
  }: {
    page?: number;
    perPage?: number;
    searchQuery?: string;
  }) {
    const offset = Math.max(0, (page - 1) * perPage);
    const like = `%${searchQuery?.trim() || ""}%`;

    if (searchQuery && searchQuery.trim()) {
      const [rows] = await db.query(
        `
        SELECT u.*, b.firstName, b.lastName, b.country, b.address, b.pincode, b.companyName, b.phoneNumber, b.userType, b.approval
        FROM users u
        LEFT JOIN basicinfo b ON u.id = b.userId
        WHERE (
          u.username LIKE ? OR u.email LIKE ? OR
          b.firstName LIKE ? OR b.lastName LIKE ? OR b.companyName LIKE ? OR b.phoneNumber LIKE ?
        )
        ORDER BY u.createdAt DESC
        LIMIT ? OFFSET ?
        `,
        [like, like, like, like, like, like, perPage, offset]
      );
      return rows as any[];
    }

    const [rows] = await db.query(
      `
      SELECT u.*, b.firstName, b.lastName, b.country, b.address, b.pincode, b.companyName, b.phoneNumber, b.userType, b.approval
      FROM users u
      LEFT JOIN basicinfo b ON u.id = b.userId
      ORDER BY u.createdAt DESC
      LIMIT ? OFFSET ?
      `,
      [perPage, offset]
    );
    return rows as any[];
  }

  static async countAll({ searchQuery = "" }: { searchQuery?: string }) {
    if (searchQuery && searchQuery.trim()) {
      const like = `%${searchQuery.trim()}%`;
      const [rows] = await db.query(
        `
        SELECT COUNT(*) as cnt
        FROM users u
        LEFT JOIN basicinfo b ON u.id = b.userId
        WHERE (
          u.username LIKE ? OR u.email LIKE ? OR
          b.firstName LIKE ? OR b.lastName LIKE ? OR b.companyName LIKE ? OR b.phoneNumber LIKE ?
        )
        `,
        [like, like, like, like, like, like]
      );
      return Number((rows as any)[0]?.cnt || 0);
    }
    const [rows] = await db.query(
      `SELECT COUNT(*) as cnt FROM users`
    );
    return Number((rows as any)[0]?.cnt || 0);
  }

  static async findById(id: number) {
    const [rows] = await db.query(
      `
      SELECT u.*, b.firstName, b.lastName, b.country, b.address, b.pincode, b.companyName, b.phoneNumber, b.userType, b.approval
      FROM users u
      LEFT JOIN basicinfo b ON u.id = b.userId
      WHERE u.id = ?
      `,
      [id]
    );
    return (rows as any)[0];
  }

  static async create(
    firstName: string,
    lastName: string,
    email: string,
    password: string,
    country: string | null,
    address: string | null,
    pincode: string | null,
    companyName: string | null,
    phoneNumber: string | null,
    userType: "superadmin" | "admin" | "customer" = "customer"
  ) {
    let username = `${firstName} ${lastName}`.trim();

    let [existingUser] = await db.query(
      "SELECT id FROM users WHERE username = ?",
      [username]
    );
    if ((existingUser as any).length > 0) {
      const [result] = await db.query("SELECT MAX(id) as maxId FROM users");
      const nextId = ((result as any)[0].maxId || 0) + 1;
      username = `${username} ${nextId}`;
    }

    const connection = await db.getConnection();
    try {
      await connection.beginTransaction();

      const [userResult] = await connection.query(
        `INSERT INTO users (username, email, password, createdAt) VALUES (?, ?, ?, ?)`,
        [username, email, password, new Date()]
      );

      const userId = (userResult as any).insertId;

      await connection.query(
        `INSERT INTO basicinfo (userId, firstName, lastName, country, address, pincode, companyName, phoneNumber, userType, approval) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [userId, firstName, lastName, country, address, pincode, companyName, phoneNumber, userType, false]
      );

      await connection.commit();

      return {
        id: userId,
        username,
        email,
        firstName,
        lastName,
        country,
        address,
        pincode,
        companyName,
        phoneNumber,
        userType,
        approval: false,
        createdAt: new Date().toISOString(),
      };
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  static async updateProfile(
    userId: number,
    firstName: string,
    lastName: string,
    email: string,
    country?: string,
    address?: string,
    pincode?: string,
    companyName?: string,
    phoneNumber?: string,
    userType?: string,
    approval?: boolean
  ) {
    let baseUsername = `${firstName} ${lastName}`.trim();
    let username = baseUsername;
    let suffix = 1;

    while (true) {
      const [existingUser] = await db.query(
        `SELECT id FROM users WHERE username = ? AND id != ?`,
        [username, userId]
      );
      if ((existingUser as any).length === 0) break;
      username = `${baseUsername} ${suffix}`;
      suffix++;
    }

    const connection = await db.getConnection();
    try {
      await connection.beginTransaction();

      await connection.query(
        `UPDATE users SET username = ?, email = ? WHERE id = ?`,
        [username, email, userId]
      );

      await connection.query(
        `UPDATE basicinfo 
         SET firstName=?, lastName=?, country=?, address=?, pincode=?, 
             companyName=?, phoneNumber=?, userType=?, approval=? 
         WHERE userId=?`,
        [
          firstName,
          lastName,
          country,
          address,
          pincode,
          companyName,
          phoneNumber,
          userType,
          approval ?? false,
          userId,
        ]
      );

      await connection.commit();

      return this.findById(userId);
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  static async updatePassword(userId: number, hashedPassword: string) {
    await db.query(`UPDATE users SET password = ? WHERE id = ?`, [
      hashedPassword,
      userId,
    ]);
  }

  static async approveUser(userId: number) {
    await db.query(`UPDATE basicinfo SET approval = true WHERE userId = ?`, [userId]);
  }

  static async updateUserType(userId: number, userType: 'superadmin'|'admin'|'customer') {
    await db.query(`UPDATE basicinfo SET userType = ? WHERE userId = ?`, [userType, userId]);
  }

  static async setApproval(userId: number, approval: boolean) {
    await db.query(`UPDATE basicinfo SET approval = ? WHERE userId = ?`, [approval, userId]);
  }

  static async updateUsername(userId: number, newUsername: string) {
    let baseUsername = newUsername.trim();
    if (!baseUsername) throw new Error("Username cannot be empty");
    let username = baseUsername;
    let suffix = 1;

    while (true) {
      const [existingUser] = await db.query(
        `SELECT id FROM users WHERE username = ? AND id != ?`,
        [username, userId]
      );
      if ((existingUser as any).length === 0) break;
      username = `${baseUsername} ${suffix}`;
      suffix++;
    }

    await db.query(`UPDATE users SET username = ? WHERE id = ?`, [username, userId]);
    return this.findById(userId);
  }

  static async createWithUsername(
    email: string,
    usernameInput: string,
    password: string,
    userType: "superadmin" | "admin" | "customer" = "customer"
  ) {
    let username = usernameInput.trim();
    if (!username) throw new Error("Username cannot be empty");

    let [existingUser] = await db.query(
      "SELECT id FROM users WHERE username = ?",
      [username]
    );
    if ((existingUser as any).length > 0) {
      const [result] = await db.query("SELECT MAX(id) as maxId FROM users");
      const nextId = ((result as any)[0].maxId || 0) + 1;
      username = `${username} ${nextId}`;
    }

    const connection = await db.getConnection();
    try {
      await connection.beginTransaction();

      const [userResult] = await connection.query(
        `INSERT INTO users (username, email, password, createdAt) VALUES (?, ?, ?, ?)`,
        [username, email, password, new Date()]
      );

      const userId = (userResult as any).insertId;

      await connection.query(
        `INSERT INTO basicinfo (userId, firstName, lastName, country, address, pincode, companyName, phoneNumber, userType, approval) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [userId, "Unknown", "Unknown", null, null, null, null, null, userType, false]
      );

      await connection.commit();

      return this.findById(userId);
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  static async deleteById(userId: number) {
    await db.query(`DELETE FROM users WHERE id = ?`, [userId]);
  }

  static async upsertKycSession(params: {
    userId: number;
    sessionId: string;
    status?: "Pending" | "Approved" | "Rejected" | "Cancelled";
    provider?: string;
    raw?: any;
  }) {
    const { userId, sessionId, status = "Pending", provider = "didit", raw } = params;
    const rawStr = raw ? (typeof raw === "string" ? raw : JSON.stringify(raw)) : null;

    await db.query(
      `
      INSERT INTO kyc (userId, sessionId, provider, status, raw, createdAt, updatedAt)
      VALUES (?, ?, ?, ?, ?, NOW(), NOW())
      ON DUPLICATE KEY UPDATE
        userId = VALUES(userId),
        provider = VALUES(provider),
        status = VALUES(status),
        raw = VALUES(raw),
        updatedAt = NOW()
      `,
      [userId, sessionId, provider, status, rawStr]
    );
  }

  static async updateKycStatus(sessionId: string, status: "Pending" | "Approved" | "Rejected" | "Cancelled", raw?: any) {
    const rawStr = raw ? (typeof raw === "string" ? raw : JSON.stringify(raw)) : null;
    await db.query(
      `UPDATE kyc SET status = ?, raw = COALESCE(?, raw), updatedAt = NOW() WHERE sessionId = ?`,
      [status, rawStr, sessionId]
    );
  }

  static async findKycBySessionId(sessionId: string) {
    const [rows] = await db.query(`SELECT * FROM kyc WHERE sessionId = ?`, [sessionId]);
    return (rows as any)[0] || null;
  }

  static async findLatestKycStatusByUserId(userId: number) {
    const [rows] = await db.query(
      `SELECT status, sessionId, updatedAt FROM kyc WHERE userId = ? ORDER BY updatedAt DESC LIMIT 1`,
      [userId]
    );
    return (rows as any)[0] || null;
  }
}

async function bootstrap() {
  await UserModel.initialize();
  await UserModel.migrate();
}

bootstrap();
