import {
  mysqlTable,
  mysqlSchema,
  type AnyMySqlColumn,
  primaryKey,
  int,
  varchar,
  text,
  datetime,
  index,
  foreignKey,
  timestamp,
  unique,
  boolean,
} from "drizzle-orm/mysql-core";
import { relations, sql } from "drizzle-orm";

export const chats = mysqlTable(
  "chats",
  {
    id: int().autoincrement().notNull(),
    email: varchar({ length: 255 }).notNull(),
    name: varchar({ length: 255 }).notNull(),
    subject: varchar({ length: 255 }).notNull(),
    message: text().notNull(),
    is_read: boolean().default(false).notNull(),
    is_replied: boolean().default(false).notNull(),
    created_at: datetime({ mode: "string" })
      .default(sql`(CURRENT_TIMESTAMP)`)
      .notNull(),
  },
  (table) => {
    return {
      chats_id: primaryKey({ columns: [table.id], name: "chats_id" }),
    };
  }
);

export const reply = mysqlTable(
  "reply",
  {
    id: int().autoincrement().notNull(),
    chat_id: int()
      .notNull()
      .references(() => chats.id, { onDelete: "cascade", onUpdate: "cascade" }),
    reply: text().notNull(),
    replied_by: varchar({ length: 255 }).notNull(),
    replied_at: datetime({ mode: "string" })
      .default(sql`(CURRENT_TIMESTAMP)`)
      .notNull(),
  },
  (table) => {
    return {
      chat_id: index("chat_id").on(table.chat_id),
      reply_id: primaryKey({ columns: [table.id], name: "reply_id" }),
    };
  }
);

export const tokens = mysqlTable(
  "tokens",
  {
    token_id: int().autoincrement().notNull(),
    email: varchar({ length: 255 })
      .notNull()
      .references(() => users.email, {
        onDelete: "cascade",
        onUpdate: "cascade",
      }),
    token: varchar({ length: 255 }).notNull(),
    expired_at: timestamp({ mode: "string" }),
    generated_at: timestamp({ mode: "string" }).defaultNow().notNull(),
  },
  (table) => {
    return {
      email: index("email").on(table.email),
      tokens_token_id: primaryKey({
        columns: [table.token_id],
        name: "tokens_token_id",
      }),
    };
  }
);

export const users = 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(),
    first_name: varchar({ length: 255 }),
    last_name: varchar({ length: 255 }),
    is_admin: boolean(),
    created_at: timestamp({ mode: "string" }).defaultNow().notNull(),
    updated_at: timestamp({ mode: "string" })
      .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),
    };
  }
);

export const replyRelations = relations(reply, ({ one }) => ({
  chat: one(chats, {
    fields: [reply.chat_id],
    references: [chats.id],
  }),
}));

export const chatsRelations = relations(chats, ({ many }) => ({
  replies: many(reply),
}));

export const tokensRelations = relations(tokens, ({ one }) => ({
  user: one(users, {
    fields: [tokens.email],
    references: [users.email],
  }),
}));

export const usersRelations = relations(users, ({ many }) => ({
  tokens: many(tokens),
}));
