import { int, mysqlEnum, mysqlTable, text, timestamp, varchar, boolean, decimal, index } from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm";

/**
 * Core user table backing auth flow.
 * Extend this file with additional tables as your product grows.
 * Columns use camelCase to match both database fields and generated types.
 */
export const users = mysqlTable("users", {
  /**
   * Surrogate primary key. Auto-incremented numeric value managed by the database.
   * Use this for relations between tables.
   */
  id: int("id").autoincrement().primaryKey(),
  /** Manus OAuth identifier (openId) returned from the OAuth callback. Unique per user. */
  openId: varchar("openId", { length: 64 }).notNull().unique(),
  name: text("name"),
  email: varchar("email", { length: 320 }),
  loginMethod: varchar("loginMethod", { length: 64 }),
  role: mysqlEnum("role", ["user", "admin"]).default("user").notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
  lastSignedIn: timestamp("lastSignedIn").defaultNow().notNull(),
});

export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;

/**
 * Xtream Codes compatible client users table.
 * Stores credentials and metadata for music streaming clients.
 */
export const xtreamUsers = mysqlTable("xtream_users", {
  id: int("id").autoincrement().primaryKey(),
  username: varchar("username", { length: 255 }).notNull().unique(),
  password: varchar("password", { length: 255 }).notNull(),
  expirationDate: timestamp("expirationDate"),
  maxConnections: int("maxConnections").default(1).notNull(),
  isActive: boolean("isActive").default(true).notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
}, (table) => ({
  usernameIdx: index("username_idx").on(table.username),
}));

export type XtreamUser = typeof xtreamUsers.$inferSelect;
export type InsertXtreamUser = typeof xtreamUsers.$inferInsert;

/**
 * Music streaming categories (e.g., Rock, Pop, Jazz, etc.)
 */
export const streamCategories = mysqlTable("stream_categories", {
  id: int("id").autoincrement().primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  categoryType: mysqlEnum("categoryType", ["live", "vod", "series", "music"]).default("music").notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
});

export type StreamCategory = typeof streamCategories.$inferSelect;
export type InsertStreamCategory = typeof streamCategories.$inferInsert;

/**
 * Music tracks/streams available in the platform.
 */
export const musicTracks = mysqlTable("music_tracks", {
  id: int("id").autoincrement().primaryKey(),
  categoryId: int("categoryId").notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  artist: varchar("artist", { length: 255 }),
  album: varchar("album", { length: 255 }),
  duration: int("duration"), // in seconds
  streamUrl: text("streamUrl").notNull(),
  thumbnailUrl: text("thumbnailUrl"),
  isActive: boolean("isActive").default(true).notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
}, (table) => ({
  categoryIdIdx: index("categoryId_idx").on(table.categoryId),
}));

export type MusicTrack = typeof musicTracks.$inferSelect;
export type InsertMusicTrack = typeof musicTracks.$inferInsert;

/**
 * Active connections tracking for simultaneous connection limits.
 */
export const activeConnections = mysqlTable("active_connections", {
  id: int("id").autoincrement().primaryKey(),
  userId: int("userId").notNull(),
  sessionToken: varchar("sessionToken", { length: 255 }).notNull().unique(),
  ipAddress: varchar("ipAddress", { length: 45 }),
  userAgent: text("userAgent"),
  connectedAt: timestamp("connectedAt").defaultNow().notNull(),
  lastActivityAt: timestamp("lastActivityAt").defaultNow().notNull(),
}, (table) => ({
  userIdIdx: index("userId_idx").on(table.userId),
  sessionTokenIdx: index("sessionToken_idx").on(table.sessionToken),
}));

export type ActiveConnection = typeof activeConnections.$inferSelect;
export type InsertActiveConnection = typeof activeConnections.$inferInsert;