import { Sequelize } from "sequelize";
import { initWorkspaceModel, Workspace } from "./models/workspace";
import { initUserModel, User } from "./models/user";
import { initSurveyModel, Survey } from "./models/survey";
import { initQuestionModel, Question } from "./models/question";
import { initLogicRuleModel, LogicRule } from "./models/logic-rule";
import { initCollectorModel, Collector } from "./models/collector";
import { initRespondentModel, Respondent } from "./models/respondent";
import { initAnswerModel, Answer } from "./models/answer";
import { initInvoiceModel, Invoice } from "./models/invoice";
import { initSystemSettingModel, SystemSetting } from "./models/system-setting";
import { initAuditLogModel, AuditLog } from "./models/audit-log";
import { initContactModel, Contact } from "./models/contact";
import { initContactListModel, ContactList } from "./models/contact-list";
import {
  initContactListMemberModel,
  ContactListMember,
} from "./models/contact-list-member";

export type DbModels = {
  Workspace: typeof Workspace;
  User: typeof User;
  Survey: typeof Survey;
  Question: typeof Question;
  LogicRule: typeof LogicRule;
  Collector: typeof Collector;
  Respondent: typeof Respondent;
  Answer: typeof Answer;
  Invoice: typeof Invoice;
  SystemSetting: typeof SystemSetting;
  AuditLog: typeof AuditLog;
  Contact: typeof Contact;
  ContactList: typeof ContactList;
  ContactListMember: typeof ContactListMember;
};

const globalForDb = globalThis as typeof globalThis & {
  sequelize?: Sequelize;
  dbInitialized?: boolean;
  dbModels?: DbModels;
  dbSchemaVersion?: number;
};

/**
 * Bump this when model attributes change. Next.js keeps `initDb()` on
 * `globalThis`, so without a version check a running `next dev` keeps the
 * old Sequelize schema (e.g. Surveys without `displayConfig`) and silently
 * drops writes to new columns.
 */
const MODEL_SCHEMA_VERSION = 3;

function buildSequelize(): Sequelize {
  const databaseUrl = process.env.DATABASE_URL;

  const sharedOptions = {
    dialect: "mysql" as const,
    logging: process.env.NODE_ENV === "development" ? console.log : false,
    define: { timestamps: true },
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000,
      // Prefer SESSION (no SUPER needed). Never reject the connection if
      // the host forbids raising packet size — that used to white-screen
      // production when SET GLOBAL failed for non-root app users.
      afterCreate: (
        conn: { query: (sql: string, cb: (err: Error | null) => void) => void },
        done: (err: Error | null, conn: unknown) => void
      ) => {
        conn.query("SET SESSION max_allowed_packet=67108864", () => {
          done(null, conn);
        });
      },
    },
  };

  if (databaseUrl) {
    return new Sequelize(databaseUrl, sharedOptions);
  }

  return new Sequelize(
    process.env.DB_NAME || "surveystronghold",
    process.env.DB_USER || "root",
    process.env.DB_PASSWORD || "",
    {
      host: process.env.DB_HOST || "127.0.0.1",
      port: Number(process.env.DB_PORT || 3306),
      ...sharedOptions,
    }
  );
}

/** Lazy Sequelize instance — avoids DB connection during Next.js build. */
export function getSequelize(): Sequelize {
  if (!globalForDb.sequelize) {
    globalForDb.sequelize = buildSequelize();
  }
  return globalForDb.sequelize;
}

function defineAssociations(models: DbModels) {
  const {
    Workspace,
    User,
    Survey,
    Question,
    LogicRule,
    Collector,
    Respondent,
    Answer,
    Invoice,
    AuditLog,
    Contact,
    ContactList,
    ContactListMember,
  } = models;

  Workspace.hasMany(User, { foreignKey: "workspaceId", as: "users" });
  User.belongsTo(Workspace, { foreignKey: "workspaceId", as: "workspace" });

  Workspace.hasMany(Survey, { foreignKey: "workspaceId", as: "surveys" });
  Survey.belongsTo(Workspace, { foreignKey: "workspaceId", as: "workspace" });

  User.hasMany(Survey, { foreignKey: "createdBy", as: "createdSurveys" });
  Survey.belongsTo(User, { foreignKey: "createdBy", as: "creator" });

  Survey.hasMany(Question, { foreignKey: "surveyId", as: "questions" });
  Question.belongsTo(Survey, { foreignKey: "surveyId", as: "survey" });

  Question.hasMany(LogicRule, { foreignKey: "questionId", as: "logicRules" });
  LogicRule.belongsTo(Question, { foreignKey: "questionId", as: "question" });
  LogicRule.belongsTo(Question, {
    foreignKey: "targetQuestionId",
    as: "targetQuestion",
  });

  Survey.hasMany(Collector, { foreignKey: "surveyId", as: "collectors" });
  Collector.belongsTo(Survey, { foreignKey: "surveyId", as: "survey" });

  Survey.hasMany(Respondent, { foreignKey: "surveyId", as: "respondents" });
  Respondent.belongsTo(Survey, { foreignKey: "surveyId", as: "survey" });
  Collector.hasMany(Respondent, {
    foreignKey: "collectorId",
    as: "respondents",
  });
  Respondent.belongsTo(Collector, {
    foreignKey: "collectorId",
    as: "collector",
  });

  Respondent.hasMany(Answer, { foreignKey: "respondentId", as: "answers" });
  Answer.belongsTo(Respondent, { foreignKey: "respondentId", as: "respondent" });
  Question.hasMany(Answer, { foreignKey: "questionId", as: "answers" });
  Answer.belongsTo(Question, { foreignKey: "questionId", as: "question" });

  Workspace.hasMany(Invoice, { foreignKey: "workspaceId", as: "invoices" });
  Invoice.belongsTo(Workspace, { foreignKey: "workspaceId", as: "workspace" });

  User.hasMany(AuditLog, { foreignKey: "actorUserId", as: "auditLogs" });
  AuditLog.belongsTo(User, { foreignKey: "actorUserId", as: "actor" });
  Workspace.hasMany(AuditLog, { foreignKey: "workspaceId", as: "auditLogs" });
  AuditLog.belongsTo(Workspace, { foreignKey: "workspaceId", as: "workspace" });

  Workspace.hasMany(Contact, { foreignKey: "workspaceId", as: "contacts" });
  Contact.belongsTo(Workspace, { foreignKey: "workspaceId", as: "workspace" });

  Workspace.hasMany(ContactList, {
    foreignKey: "workspaceId",
    as: "contactLists",
  });
  ContactList.belongsTo(Workspace, {
    foreignKey: "workspaceId",
    as: "workspace",
  });

  ContactList.hasMany(ContactListMember, {
    foreignKey: "contactListId",
    as: "members",
  });
  ContactListMember.belongsTo(ContactList, {
    foreignKey: "contactListId",
    as: "list",
  });

  Contact.hasMany(ContactListMember, {
    foreignKey: "contactId",
    as: "listMemberships",
  });
  ContactListMember.belongsTo(Contact, {
    foreignKey: "contactId",
    as: "contact",
  });

  Contact.belongsToMany(ContactList, {
    through: ContactListMember,
    foreignKey: "contactId",
    otherKey: "contactListId",
    as: "lists",
  });
  ContactList.belongsToMany(Contact, {
    through: ContactListMember,
    foreignKey: "contactListId",
    otherKey: "contactId",
    as: "contacts",
  });
}

function resetSequelizeModels(sequelize: Sequelize) {
  for (const model of [...sequelize.modelManager.all]) {
    sequelize.modelManager.removeModel(model as unknown as never);
  }
}

/** Initialize all models and associations (call once per process). */
export function initDb(): DbModels & { sequelize: Sequelize } {
  const sequelize = getSequelize();

  const schemaIsCurrent = globalForDb.dbSchemaVersion === MODEL_SCHEMA_VERSION;
  const hasDisplayConfig = Boolean(
    globalForDb.dbModels?.Survey?.rawAttributes?.displayConfig
  );

  if (
    globalForDb.dbInitialized &&
    globalForDb.dbModels &&
    schemaIsCurrent &&
    hasDisplayConfig
  ) {
    return { sequelize, ...globalForDb.dbModels };
  }

  if (globalForDb.dbInitialized) {
    resetSequelizeModels(sequelize);
    globalForDb.dbInitialized = false;
    globalForDb.dbModels = undefined;
  }

  initWorkspaceModel(sequelize);
  initUserModel(sequelize);
  initSurveyModel(sequelize);
  initQuestionModel(sequelize);
  initLogicRuleModel(sequelize);
  initCollectorModel(sequelize);
  initRespondentModel(sequelize);
  initAnswerModel(sequelize);
  initInvoiceModel(sequelize);
  initSystemSettingModel(sequelize);
  initAuditLogModel(sequelize);
  initContactModel(sequelize);
  initContactListModel(sequelize);
  initContactListMemberModel(sequelize);

  const models: DbModels = {
    Workspace,
    User,
    Survey,
    Question,
    LogicRule,
    Collector,
    Respondent,
    Answer,
    Invoice,
    SystemSetting,
    AuditLog,
    Contact,
    ContactList,
    ContactListMember,
  };

  defineAssociations(models);
  globalForDb.dbModels = models;
  globalForDb.dbInitialized = true;
  globalForDb.dbSchemaVersion = MODEL_SCHEMA_VERSION;

  return { sequelize, ...models };
}

export {
  Workspace,
  User,
  Survey,
  Question,
  LogicRule,
  Collector,
  Respondent,
  Answer,
  Invoice,
  SystemSetting,
  AuditLog,
  Contact,
  ContactList,
  ContactListMember,
};

export default getSequelize;