import { pgTable, text, serial, integer, boolean, timestamp, doublePrecision, varchar, bigint, unique, date, index } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { createInsertSchema } from "drizzle-zod";
import { z } from "zod";

export const categories = pgTable("categories", {
  id: serial("id").primaryKey(),
  name: text("name").notNull().unique(),
  description: text("description"),
  isArchived: boolean("is_archived").default(false),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const clients = pgTable("clients", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email"),
  category: text("category").notNull(),
  serviceDetails: text("service_details"),
  accountNumber: text("account_number"),
  status: text("status").default("Active"),
  contactInfo: text("contact_info"),
  address: text("address"),
  notes: text("notes"),
  isPrimarySim: boolean("is_primary_sim").default(false),
  primarySimId: integer("primary_sim_id"),
  simType: text("sim_type"),
  simSerialNumber: text("sim_serial_number"),
  status2: text("status2"),
  msisdn: text("msisdn"),
  comments: text("comments"),
  agent: text("agent"),
  telkomHandle: text("telkom_handle"),
  fup: text("fup"), // FUP field in GB for Telkom clients
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
  isReseller: boolean("is_reseller").default(false),
  financeBoxTotalIn: doublePrecision("finance_box_total_in"),
  financeBoxTotalOut: doublePrecision("finance_box_total_out"),
  financeBoxMonth: text("finance_box_month"),
  financeBoxNetMargin: doublePrecision("finance_box_net_margin"),
  financeBoxLastUpdated: timestamp("finance_box_last_updated"),
});

export const simswaps = pgTable("simswaps", {
  id: serial("id").primaryKey(),
  clientId: integer("client_id").references(() => clients.id).notNull(),
  currentSimNumber: varchar("current_sim_number", { length: 255 }).notNull(),
  newSimNumber: varchar("new_sim_number", { length: 255 }).notNull(),
  status: varchar("status", { length: 50 }).notNull().default("pending"),
  reason: text("reason"),
  requestedBy: integer("requested_by").notNull(),
  requestedAt: timestamp("requested_at").defaultNow(),
  completedBy: integer("completed_by"),
  completedAt: timestamp("completed_at"),
  cancelledBy: integer("cancelled_by"),
  cancelledAt: timestamp("cancelled_at"),
  cancelReason: text("cancel_reason"),
});

export const clientsRelations = relations(clients, ({ many }) => ({
  simswaps: many(simswaps),
}));

export const simswapsRelations = relations(simswaps, ({ one }) => ({
  client: one(clients, {
    fields: [simswaps.clientId],
    references: [clients.id],
  }),
}));

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  username: varchar("username", { length: 255 }).notNull().unique(),
  password: text("password").notNull(),
  name: text("name").notNull(),
  mobileNumber: varchar("mobile_number", { length: 20 }),
  role: text("role").default("staff"),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow()
});

export const mtnStatistics = pgTable("mtn_statistics", {
  id: serial("id").primaryKey(),
  username: varchar("username", { length: 255 }).notNull(),
  msisdn: varchar("msisdn", { length: 20 }),
  year: integer("year").notNull(),
  month: integer("month").notNull(),
  totalBytes: bigint("total_bytes", { mode: "number" }).notNull().default(0),
  connectedTimeMinutes: integer("connected_time_minutes").notNull().default(0),
  refreshedAt: timestamp("refreshed_at").defaultNow(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
}, (table) => ({
  uniqueUserMonth: unique().on(table.username, table.year, table.month),
}));

export const recentlyViewed = pgTable("recently_viewed", {
  id: serial("id").primaryKey(),
  clientId: integer("client_id").notNull().references(() => clients.id, { onDelete: "cascade" }),
  userId: integer("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  viewedAt: timestamp("viewed_at").defaultNow().notNull(),
});

export const mtnMobileTop15 = pgTable("mtnmobiletop15", {
  id: serial("id").primaryKey(),
  accountNumber: varchar("account_number", { length: 255 }).notNull(),
  clientName: varchar("client_name", { length: 255 }).notNull(),
  msisdn: varchar("msisdn", { length: 50 }),
  totalBytes: bigint("total_bytes", { mode: "number" }).notNull().default(0),
  connectedTimeMinutes: integer("connected_time_minutes").notNull().default(0),
  year: integer("year").notNull(),
  month: integer("month").notNull(),
  rankPosition: integer("rank_position"),
  syncTimestamp: timestamp("sync_timestamp").defaultNow().notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  uniqueAccountYearMonth: unique().on(table.accountNumber, table.year, table.month),
}));

export const staffNotifications = pgTable("staff_notifications", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  message: text("message").notNull(),
  type: varchar("type", { length: 50 }).notNull().default("info"), // info, warning, error, success
  priority: varchar("priority", { length: 50 }).notNull().default("medium"), // low, medium, high
  isActive: boolean("is_active").default(true),
  createdBy: integer("created_by").notNull().references(() => users.id),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const mtnMobileDailyUsage = pgTable("mtn_mobile_daily_usage", {
  id: serial("id").primaryKey(),
  username: varchar("username", { length: 255 }).notNull(),
  year: integer("year").notNull(),
  month: integer("month").notNull(),
  day: integer("day").notNull(),
  date: date("date").notNull(),
  usageBytes: bigint("usage_bytes", { mode: "number" }).notNull().default(0),
  usageGB: varchar("usage_gb", { length: 20 }).notNull().default("0.000"),
  entries: integer("entries").notNull().default(0),
  success: boolean("success").notNull().default(true),
  error: text("error"),
  lastSyncedAt: timestamp("last_synced_at").defaultNow().notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  // Unique constraint on username, year, month, day
  uniqueUsagePerDay: unique().on(table.username, table.year, table.month, table.day),
  // Index for faster queries
  usernameIdx: index("mtn_mobile_daily_usage_username_idx").on(table.username),
  dateIdx: index("mtn_mobile_daily_usage_date_idx").on(table.year, table.month, table.day),
}));

export const mtnMobileSyncProgress = pgTable("mtn_mobile_sync_progress", {
  id: serial("id").primaryKey(),
  sessionId: varchar("session_id", { length: 100 }).notNull(),
  username: varchar("username", { length: 255 }).notNull(),
  totalAccounts: integer("total_accounts").notNull(),
  processedAccounts: integer("processed_accounts").notNull().default(0),
  currentAccount: varchar("current_account", { length: 255 }),
  currentStatus: varchar("current_status", { length: 255 }),
  totalDays: integer("total_days").notNull().default(0),
  processedDays: integer("processed_days").notNull().default(0),
  errors: text("errors").array().default([]),
  isCompleted: boolean("is_completed").notNull().default(false),
  isCancelled: boolean("is_cancelled").notNull().default(false),
  startedAt: timestamp("started_at").defaultNow().notNull(),
  completedAt: timestamp("completed_at"),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const apiKeys = pgTable("api_keys", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  keyHash: text("key_hash").notNull().unique(),
  keyPreview: text("key_preview").notNull(), // First 8 chars + "..." for display
  isActive: boolean("is_active").default(true),
  createdBy: integer("created_by").notNull().references(() => users.id),
  lastUsed: timestamp("last_used"),
  expiresAt: timestamp("expires_at"),
  permissions: text("permissions").array().default(["read:categories", "read:accounts"]), // Array of permissions
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const insertClientSchema = createInsertSchema(clients).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertSimswapSchema = createInsertSchema(simswaps).omit({
  id: true,
  requestedAt: true,
});

export const insertUserSchema = createInsertSchema(users).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertCategorySchema = createInsertSchema(categories).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertMtnStatisticsSchema = createInsertSchema(mtnStatistics).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
  refreshedAt: true,
});

export const insertRecentlyViewedSchema = createInsertSchema(recentlyViewed).omit({
  id: true,
  viewedAt: true,
});

export const insertMtnMobileTop15Schema = createInsertSchema(mtnMobileTop15).omit({
  id: true,
  syncTimestamp: true,
  createdAt: true,
  updatedAt: true,
});

export const insertStaffNotificationSchema = createInsertSchema(staffNotifications).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertMtnMobileDailyUsageSchema = createInsertSchema(mtnMobileDailyUsage).omit({
  id: true,
  lastSyncedAt: true,
  createdAt: true,
  updatedAt: true,
});

export const insertMtnMobileSyncProgressSchema = createInsertSchema(mtnMobileSyncProgress).omit({
  id: true,
  startedAt: true,
  completedAt: true,
  updatedAt: true,
});

export const insertApiKeySchema = createInsertSchema(apiKeys).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
  lastUsed: true,
});

export type InsertClient = z.infer<typeof insertClientSchema>;
export type Client = typeof clients.$inferSelect;
export type InsertSimswap = z.infer<typeof insertSimswapSchema>;
export type Simswap = typeof simswaps.$inferSelect;
export type InsertUser = z.infer<typeof insertUserSchema>;
export type User = typeof users.$inferSelect;
export type InsertCategory = z.infer<typeof insertCategorySchema>;
export type Category = typeof categories.$inferSelect;
export type InsertMtnStatistics = z.infer<typeof insertMtnStatisticsSchema>;
export type MtnStatistics = typeof mtnStatistics.$inferSelect;
export type InsertRecentlyViewed = z.infer<typeof insertRecentlyViewedSchema>;
export type RecentlyViewed = typeof recentlyViewed.$inferSelect;
export type InsertMtnMobileTop15 = z.infer<typeof insertMtnMobileTop15Schema>;
export type MtnMobileTop15 = typeof mtnMobileTop15.$inferSelect;
export type InsertStaffNotification = z.infer<typeof insertStaffNotificationSchema>;
export type StaffNotification = typeof staffNotifications.$inferSelect;
export type InsertMtnMobileDailyUsage = z.infer<typeof insertMtnMobileDailyUsageSchema>;
export type MtnMobileDailyUsage = typeof mtnMobileDailyUsage.$inferSelect;
export type InsertMtnMobileSyncProgress = z.infer<typeof insertMtnMobileSyncProgressSchema>;
export type MtnMobileSyncProgress = typeof mtnMobileSyncProgress.$inferSelect;
export type InsertApiKey = z.infer<typeof insertApiKeySchema>;
export type ApiKey = typeof apiKeys.$inferSelect;

// Activity Logs table for comprehensive user action tracking
export const activityLogs = pgTable("activity_logs", {
  id: serial("id").primaryKey(),
  userId: integer("user_id").references(() => users.id).notNull(),
  action: text("action").notNull(), // e.g., "login", "client_create", "client_edit", "client_delete", "client_view", etc.
  resourceType: text("resource_type"), // e.g., "client", "user", "category", "apikey", etc.
  resourceId: integer("resource_id"), // ID of the resource being acted upon
  resourceName: text("resource_name"), // Name/identifier of the resource
  details: text("details"), // JSON string with additional details
  ipAddress: text("ip_address"),
  userAgent: text("user_agent"),
  timestamp: timestamp("timestamp").defaultNow(),
});

export const activityLogsRelations = relations(activityLogs, ({ one }) => ({
  user: one(users, {
    fields: [activityLogs.userId],
    references: [users.id],
  }),
}));

export const insertActivityLogSchema = createInsertSchema(activityLogs).omit({
  id: true,
  timestamp: true,
});

export type InsertActivityLog = z.infer<typeof insertActivityLogSchema>;
export type ActivityLog = typeof activityLogs.$inferSelect;

// Extended types for UI - adapting to your actual data structure
export type ClientWithSimswaps = Client & {
  simswaps: Simswap[];
};

// For Telkom clients, we'll work with the primary/secondary structure using the existing fields
export type TelkomClient = Client & {
  primarySim: {
    simNumber: string;
    phoneNumber: string;
    status: string;
    dataPlan: string;
  };
  secondarySims: {
    id: number;
    name: string;
    simNumber: string;
    phoneNumber: string;
    msisdn: string;
    status: string;
    dataPlan: string;
    email: string;
    serviceDetails: string;
    accountNumber: string;
    status2: string;
    category: string;
    isReseller: boolean;
  }[];
};
