import {
  mysqlTable,
  int,
  varchar,
  text,
  timestamp,
  boolean,
  json,
  uniqueIndex,
  index,
} from 'drizzle-orm/mysql-core';

export const users = mysqlTable(
  'users',
  {
    id: int('id').autoincrement().primaryKey(),
    nama: varchar('nama', { length: 150 }).notNull(),
    email: varchar('email', { length: 191 }).notNull(),
    passwordHash: varchar('password_hash', { length: 255 }),
    googleId: varchar('google_id', { length: 191 }),
    role: varchar('role', { length: 20 }).notNull().default('wali_kelas'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().onUpdateNow().notNull(),
  },
  (t) => ({
    emailIdx: uniqueIndex('users_email_idx').on(t.email),
    googleIdx: index('users_google_idx').on(t.googleId),
  }),
);

export const angkatan = mysqlTable(
  'angkatan',
  {
    id: int('id').autoincrement().primaryKey(),
    nomor: int('nomor').notNull(),
    tahun: int('tahun').notNull(),
    status: varchar('status', { length: 20 }).notNull().default('aktif'),
    backgroundUrl: varchar('background_url', { length: 500 }),
    backsoundUrl: varchar('backsound_url', { length: 500 }),
    namaLabelPosition: json('nama_label_position'),
    waTemplate: text('wa_template'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().onUpdateNow().notNull(),
  },
  (t) => ({
    nomorIdx: uniqueIndex('angkatan_nomor_idx').on(t.nomor),
  }),
);

export const kelas = mysqlTable(
  'kelas',
  {
    id: int('id').autoincrement().primaryKey(),
    angkatanId: int('angkatan_id').notNull(),
    nama: varchar('nama', { length: 100 }).notNull(),
    jenjang: varchar('jenjang', { length: 10 }).notNull(),
    waliKelasUserId: int('wali_kelas_user_id'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().onUpdateNow().notNull(),
  },
  (t) => ({
    angkatanIdx: index('kelas_angkatan_idx').on(t.angkatanId),
    waliIdx: index('kelas_wali_idx').on(t.waliKelasUserId),
  }),
);

export const santri = mysqlTable(
  'santri',
  {
    id: int('id').autoincrement().primaryKey(),
    kelasId: int('kelas_id').notNull(),
    angkatanId: int('angkatan_id').notNull(),
    nis: varchar('nis', { length: 50 }).notNull(),
    nama: varchar('nama', { length: 200 }).notNull(),
    slug: varchar('slug', { length: 220 }).notNull(),
    nomorWaEnc: text('nomor_wa_enc').notNull(),
    statusTerkirim: boolean('status_terkirim').notNull().default(false),
    terkirimAt: timestamp('terkirim_at'),
    terkirimByUserId: int('terkirim_by_user_id'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().onUpdateNow().notNull(),
  },
  (t) => ({
    angkatanSlugIdx: uniqueIndex('santri_angkatan_slug_idx').on(t.angkatanId, t.slug),
    angkatanNisIdx: uniqueIndex('santri_angkatan_nis_idx').on(t.angkatanId, t.nis),
    kelasIdx: index('santri_kelas_idx').on(t.kelasId),
  }),
);

export const auditLog = mysqlTable(
  'audit_log',
  {
    id: int('id').autoincrement().primaryKey(),
    userId: int('user_id'),
    action: varchar('action', { length: 100 }).notNull(),
    entityType: varchar('entity_type', { length: 50 }),
    entityId: int('entity_id'),
    metadata: json('metadata'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
  },
  (t) => ({
    userIdx: index('audit_user_idx').on(t.userId),
    entityIdx: index('audit_entity_idx').on(t.entityType, t.entityId),
  }),
);

export const sessions = mysqlTable('sessions', {
  sessionId: varchar('session_id', { length: 128 }).primaryKey(),
  expires: int('expires').notNull(),
  data: text('data'),
});
