import 'dotenv/config';
import { drizzle } from 'drizzle-orm/mysql2';
import { createConnection } from 'mysql2/promise';
import { mysqlTable, datetime, boolean, text, varchar, int, primaryKey, index, timestamp, unique } from 'drizzle-orm/mysql-core';
import { sql, relations } from 'drizzle-orm';

const chat = mysqlTable(
  "chats",
  {
    id: int().autoincrement().notNull(),
    email: varchar({ length: 255 }).notNull(),
    name: varchar({ length: 255 }).notNull(),
    subject: varchar({ length: 255 }).notNull(),
    message: text().notNull(),
    isRead: boolean("is_read").default(false).notNull(),
    isReplied: boolean("is_replied").default(false).notNull(),
    time: datetime("created_at").default(sql`(CURRENT_TIMESTAMP)`).notNull()
  },
  (table) => {
    return {
      chats_id: primaryKey({ columns: [table.id], name: "chats_id" })
    };
  }
);
const reply = mysqlTable(
  "reply",
  {
    id: int().autoincrement().notNull(),
    chatId: int("chat_id").notNull().references(() => chat.id, { onDelete: "cascade", onUpdate: "cascade" }),
    reply: text().notNull(),
    repliedBy: int("replied_by").references(() => chat.id, {
      onDelete: "cascade",
      onUpdate: "cascade"
    }),
    time: datetime("replied_at").default(sql`(CURRENT_TIMESTAMP)`).notNull()
  },
  (table) => {
    return {
      chat_id: index("chat_id").on(table.chatId),
      reply_id: primaryKey({ columns: [table.id], name: "reply_id" }),
      reply_replied_by: index("replied_by").on(table.repliedBy)
    };
  }
);
const token = mysqlTable(
  "tokens",
  {
    tokenId: int("token_id").autoincrement().notNull(),
    email: varchar({ length: 255 }).notNull().references(() => user.email, {
      onDelete: "cascade",
      onUpdate: "cascade"
    }),
    token: varchar({ length: 255 }).notNull(),
    expiredAt: timestamp("expired_at"),
    generatedAt: timestamp("generated_at").defaultNow().notNull()
  },
  (table) => {
    return {
      email: index("email").on(table.email),
      tokens_token_id: primaryKey({
        columns: [table.tokenId],
        name: "tokens_token_id"
      })
    };
  }
);
const user = mysqlTable(
  "users",
  {
    id: int().autoincrement().notNull(),
    username: varchar({ length: 255 }).notNull(),
    email: varchar({ length: 255 }).notNull(),
    phone: varchar({ length: 15 }),
    address: varchar({ length: 255 }),
    position: varchar({ length: 50 }).default("staff").notNull(),
    password: varchar({ length: 500 }).notNull(),
    firstName: varchar("first_name", { length: 255 }),
    lastName: varchar("last_name", { length: 255 }),
    isAdmin: boolean("is_admin"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().onUpdateNow().notNull()
  },
  (table) => {
    return {
      users_id: primaryKey({ columns: [table.id], name: "users_id" }),
      email: unique("email").on(table.email),
      phone: unique("phone").on(table.phone),
      username: unique("username").on(table.username)
    };
  }
);
const replyRelations = relations(reply, ({ one }) => ({
  message: one(chat, {
    fields: [reply.chatId],
    references: [chat.id]
  }),
  staff: one(user, {
    fields: [reply.repliedBy],
    references: [user.id]
  })
}));
const chatsRelations = relations(chat, ({ many }) => ({
  replies: many(reply)
}));
const tokensRelations = relations(token, ({ one }) => ({
  user: one(user, {
    fields: [token.email],
    references: [user.email]
  })
}));
const usersRelations = relations(user, ({ many }) => ({
  tokens: many(token)
}));

const schema = /*#__PURE__*/Object.freeze(/*#__PURE__*/Object.defineProperty({
  __proto__: null,
  chat,
  chatsRelations,
  reply,
  replyRelations,
  token,
  tokensRelations,
  user,
  usersRelations
}, Symbol.toStringTag, { value: 'Module' }));

const options = {
  host: process.env.DB_SERVER,
  user: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME
};
const connection = await createConnection(options);
const database = drizzle(connection, {
  schema,
  mode: "default"
});

const PAGINATION = 20;

export { PAGINATION as P, chat as c, database as d, reply as r, token as t, user as u };
