const sequelize = require("../config/dbconnection");
const Sequelize = require("sequelize");

const ProjectFilterPreference = sequelize.define(
  "project_filter_preferences",
  {
    id: { type: Sequelize.INTEGER, allowNull: false, primaryKey: true, autoIncrement: true },
    user_id: { type: Sequelize.INTEGER, allowNull: false, unique: false },
    page_key: { type: Sequelize.STRING(50), allowNull: false, defaultValue: "production" },
    team_filter_ids: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    client_filter_ids: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    community_filter_ids: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    user_filter_ids: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    job_class_filter_ids: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    job_type_filter_ids: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    project_type_filter: { type: Sequelize.STRING(32), allowNull: true, defaultValue: null },
    due_filter: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 1 },
    sort_filter: { type: Sequelize.STRING(100), allowNull: false, defaultValue: "new" },
    from_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    to_date: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    sales_rep_filter_ids: { type: Sequelize.TEXT, allowNull: true, defaultValue: null },
    appointment_filter_id: { type: Sequelize.INTEGER, allowNull: true, defaultValue: null },
    appointment_filter_name: { type: Sequelize.STRING(500), allowNull: true, defaultValue: null },
    updated_at: { type: Sequelize.INTEGER, allowNull: false, defaultValue: 0 },
  },
  { tableName: "project_filter_preferences", timestamps: false }
);

const parseIds = (rawValue) => {
  if (!rawValue) return [];
  return String(rawValue)
    .split(",")
    .map((id) => {
      const trimmedId = id.trim();
      return trimmedId === "other" ? "other" : Number(trimmedId);
    })
    .filter((id) => id === "other" || !Number.isNaN(id));
};

const formatFilterIds = (ids) => {
  if (!Array.isArray(ids) || ids.length === 0) return null;
  return ids.map(String).join(",");
};

const getProjectFiltersByUserId = async (userId, pageKey = "production") => {
  try {
    const row = await ProjectFilterPreference.findOne({
      where: { user_id: userId, page_key: pageKey },
      attributes: [
        "team_filter_ids",
        "client_filter_ids",
        "community_filter_ids",
        "user_filter_ids",
        "job_class_filter_ids",
        "job_type_filter_ids",
        "project_type_filter",
        "due_filter",
        "sort_filter",
        "from_date",
        "to_date",
        "sales_rep_filter_ids",
        "appointment_filter_id",
        "appointment_filter_name",
      ],
    });

    if (!row) {
      return {
        teamFilter: [],
        clientFilter: [],
        communityFilter: [],
        userFilter: [],
        jobClassFilter: [],
        jobTypeFilter: [],
        projectTypeFilter: "ALL",
        dueFilter: 1,
        sortFilter: "new",
        fromDate: null,
        toDate: null,
        salesRepFilter: [],
        appointmentFilterId: null,
        appointmentFilterName: null,
        filtersApplied: false,
      };
    }

    const hasAnyFilter =
      !!row.team_filter_ids ||
      !!row.client_filter_ids ||
      !!row.community_filter_ids ||
      !!row.user_filter_ids ||
      !!row.job_class_filter_ids ||
      !!row.job_type_filter_ids ||
      row.due_filter !== 1 ||
      row.sort_filter !== "new" ||
      row.project_type_filter !== "ALL" ||
      row.from_date !== null ||
      row.to_date !== null ||
      !!row.sales_rep_filter_ids ||
      row.appointment_filter_id !== null ||
      !!row.appointment_filter_name;

    return {
      teamFilter: parseIds(row.team_filter_ids),
      clientFilter: parseIds(row.client_filter_ids),
      communityFilter: parseIds(row.community_filter_ids),
      userFilter: parseIds(row.user_filter_ids),
      jobClassFilter: parseIds(row.job_class_filter_ids),
      jobTypeFilter: parseIds(row.job_type_filter_ids),
      projectTypeFilter: row.project_type_filter || "ALL",
      dueFilter: row.due_filter === null || row.due_filter === undefined ? 1 : Number(row.due_filter),
      sortFilter: row.sort_filter || "new",
      fromDate: row.from_date ? row.from_date : null,
      toDate: row.to_date ? row.to_date : null,
      salesRepFilter: parseIds(row.sales_rep_filter_ids),
      appointmentFilterId: row.appointment_filter_id || null,
      appointmentFilterName: row.appointment_filter_name || null,
      filtersApplied: hasAnyFilter,
    };
  } catch (error) {
    console.error("Error fetching project filter preferences:", error);
    return {
      teamFilter: [],
      clientFilter: [],
      communityFilter: [],
      userFilter: [],
      jobClassFilter: [],
      jobTypeFilter: [],
      projectTypeFilter: "ALL",
      dueFilter: 1,
      sortFilter: "new",
      fromDate: null,
      toDate: null,
      salesRepFilter: [],
      appointmentFilterId: null,
      appointmentFilterName: null,
      filtersApplied: false,
    };
  }
};

const updateProjectFiltersByUserId = async (userId, filters, pageKey = "production") => {
  try {
    const payload = {
      team_filter_ids: formatFilterIds(filters.teamFilter),
      client_filter_ids: formatFilterIds(filters.clientFilter),
      community_filter_ids: formatFilterIds(filters.communityFilter),
      user_filter_ids: formatFilterIds(filters.userFilter),
      job_class_filter_ids: formatFilterIds(filters.jobClassFilter),
      job_type_filter_ids: formatFilterIds(filters.jobTypeFilter),
      project_type_filter: filters.projectTypeFilter ? String(filters.projectTypeFilter) : null,
      due_filter: Number(filters?.dueFilter ?? 1) === 1 ? 1 : 0,
      sort_filter: filters.sortFilter ? String(filters.sortFilter) : "new",
      from_date: filters.fromDate || null,
      to_date: filters.toDate || null,
      sales_rep_filter_ids: formatFilterIds(filters.salesRepFilter),
      appointment_filter_id: filters.appointmentFilterId ? Number(filters.appointmentFilterId) : null,
      appointment_filter_name: filters.appointmentFilterName ? String(filters.appointmentFilterName) : null,
      updated_at: Math.floor(Date.now() / 1000),
      page_key: pageKey,
    };

    const existing = await ProjectFilterPreference.findOne({
      where: { user_id: userId, page_key: pageKey },
      attributes: ["id"],
    });

    if (existing) {
      const result = await ProjectFilterPreference.update(payload, {
        where: { user_id: userId, page_key: pageKey },
      });
      return result[0] > 0;
    }

    const created = await ProjectFilterPreference.create({
      user_id: userId,
      ...payload,
    });
    return !!created;
  } catch (error) {
    console.error("Error updating project filter preferences:", error);
    return false;
  }
};

const deleteProjectFiltersByUserId = async (userId, pageKey = "production") => {
  try {
    const deletedCount = await ProjectFilterPreference.destroy({
      where: { user_id: userId, page_key: pageKey },
    });
    return deletedCount > 0;
  } catch (error) {
    console.error("Error deleting project filter preferences:", error);
    return false;
  }
};

module.exports = {
  updateProjectFiltersByUserId,
  getProjectFiltersByUserId,
  deleteProjectFiltersByUserId,
};
