import { sql } from "drizzle-orm";
import { 
  pgTable, 
  text, 
  varchar, 
  integer, 
  decimal, 
  timestamp, 
  boolean,
  pgEnum,
  jsonb,
  index
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { createInsertSchema } from "drizzle-zod";
import { z } from "zod";

// Session storage table (required for express-session with postgres store)
export const sessions = pgTable("sessions", {
  sid: varchar("sid").primaryKey(),
  sess: jsonb("sess").notNull(),
  expire: timestamp("expire").notNull(),
}, (table) => ({
  expireIdx: index("IDX_session_expire").on(table.expire),
}));

// Enums
export const userRoleEnum = pgEnum('user_role', ['client', 'pharmacy', 'courier', 'admin']);
export const orderStatusEnum = pgEnum('order_status', [
  'CREATED', 
  'PHARMACY_CONFIRMED', 
  'COURIER_ASSIGNED', 
  'PICKED_UP', 
  'DELIVERED', 
  'CLOSED'
]);
export const entryTypeEnum = pgEnum('entry_type', ['DEBIT', 'CREDIT']);
export const accountTypeEnum = pgEnum('account_type', ['PHARMACY', 'COURIER', 'MEDIFLASH', 'CLIENT']);
export const ownerTypeEnum = pgEnum('owner_type', ['PHARMACY', 'COURIER', 'MEDIFLASH']);

// Users table
export const users = pgTable("users", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  role: userRoleEnum("role").notNull(),
  name: text("name").notNull(),
  phone: varchar("phone", { length: 20 }).notNull(),
  email: varchar("email").unique(),
  passwordHash: text("password_hash").notNull(),
  lat: decimal("lat", { precision: 10, scale: 8 }),
  lng: decimal("lng", { precision: 11, scale: 8 }),
  address: text("address"),
  active: boolean("active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});

// Pharmacies table
export const pharmacies = pgTable("pharmacies", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").references(() => users.id).notNull(),
  name: text("name").notNull(),
  lat: decimal("lat", { precision: 10, scale: 8 }).notNull(),
  lng: decimal("lng", { precision: 11, scale: 8 }).notNull(),
  address: text("address").notNull(),
  verified: boolean("verified").default(false),
  createdAt: timestamp("created_at").defaultNow(),
});

// Couriers table
export const couriers = pgTable("couriers", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").references(() => users.id).notNull(),
  vehicleType: varchar("vehicle_type", { length: 50 }).default('motorcycle'),
  lat: decimal("lat", { precision: 10, scale: 8 }),
  lng: decimal("lng", { precision: 11, scale: 8 }),
  active: boolean("active").default(true),
  available: boolean("available").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});

// Medicines table
export const medicines = pgTable("medicines", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  name: text("name").notNull(),
  genericName: text("generic_name"),
  atcCode: varchar("atc_code", { length: 20 }),
  category: varchar("category", { length: 100 }),
  description: text("description"),
  dosage: varchar("dosage", { length: 100 }),
  form: varchar("form", { length: 50 }), // tablet, syrup, injection, etc.
  manufacturer: varchar("manufacturer", { length: 100 }),
  requiresPrescription: boolean("requires_prescription").default(false),
  activeIngredient: text("active_ingredient"),
  sideEffects: text("side_effects"),
  contraindications: text("contraindications"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
}, (table) => ({
  nameIdx: index("medicines_name_idx").on(table.name),
  categoryIdx: index("medicines_category_idx").on(table.category),
}));

// Pharmacy stock table
export const pharmacyStock = pgTable("pharmacy_stock", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  pharmacyId: varchar("pharmacy_id").references(() => pharmacies.id).notNull(),
  medicineId: varchar("medicine_id").references(() => medicines.id).notNull(),
  stock: integer("stock").default(0),
  unitPrice: integer("unit_price").notNull(), // in FCFA centimes
  minimumStock: integer("minimum_stock").default(5),
  available: boolean("available").default(true),
  lastRestockedAt: timestamp("last_restocked_at"),
  updatedAt: timestamp("updated_at").defaultNow(),
}, (table) => ({
  pharmacyMedicineIdx: index("pharmacy_stock_pharmacy_medicine_idx").on(table.pharmacyId, table.medicineId),
  stockIdx: index("pharmacy_stock_stock_idx").on(table.stock),
}));

// Orders table
export const orders = pgTable("orders", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  clientId: varchar("client_id").references(() => users.id).notNull(),
  pharmacyId: varchar("pharmacy_id").references(() => pharmacies.id),
  courierId: varchar("courier_id").references(() => couriers.id),
  medicineId: varchar("medicine_id").references(() => medicines.id),
  medicineName: text("medicine_name").notNull(),
  qty: integer("qty").default(1),
  medPriceTotal: integer("med_price_total").default(0),
  serviceFee: integer("service_fee").default(500),
  deliveryFee: integer("delivery_fee").default(600),
  distanceKm: decimal("distance_km", { precision: 6, scale: 2 }),
  status: orderStatusEnum("status").default('CREATED'),
  clientLat: decimal("client_lat", { precision: 10, scale: 8 }).notNull(),
  clientLng: decimal("client_lng", { precision: 11, scale: 8 }).notNull(),
  clientAddress: text("client_address").notNull(),
  clientPhone: varchar("client_phone", { length: 20 }).notNull(),
  prescriptionUrl: text("prescription_url"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
}, (table) => ({
  clientIdIdx: index("orders_client_id_idx").on(table.clientId),
  statusIdx: index("orders_status_idx").on(table.status),
}));

// Order events table
export const orderEvents = pgTable("order_events", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  orderId: varchar("order_id").references(() => orders.id).notNull(),
  event: text("event").notNull(),
  meta: jsonb("meta"),
  createdAt: timestamp("created_at").defaultNow(),
});

// Wallets table
export const wallets = pgTable("wallets", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  ownerType: ownerTypeEnum("owner_type").notNull(),
  ownerId: varchar("owner_id"),
  balance: integer("balance").default(0),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Ledger table
export const ledger = pgTable("ledger", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  orderId: varchar("order_id").references(() => orders.id).notNull(),
  entryType: entryTypeEnum("entry_type").notNull(),
  account: accountTypeEnum("account").notNull(),
  amount: integer("amount").notNull(),
  note: text("note"),
  createdAt: timestamp("created_at").defaultNow(),
});

// Settings table
export const settings = pgTable("settings", {
  key: text("key").primaryKey(),
  value: text("value").notNull(),
});

// Enhanced wallet system tables
export const transactions = pgTable("transactions", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  walletId: varchar("wallet_id").notNull().references(() => wallets.id),
  orderId: varchar("order_id").references(() => orders.id), // Optional: link to order
  type: varchar("type").notNull(), // "deposit", "payment", "refund", "earnings", "withdrawal", "fee"
  amount: integer("amount").notNull(), // Amount in FCFA (cents)
  balanceAfter: integer("balance_after").notNull(),
  description: text("description").notNull(),
  metadata: jsonb("metadata"), // Additional transaction data
  status: varchar("status").default("completed").notNull(), // "pending", "completed", "failed", "cancelled"
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const paymentMethods = pgTable("payment_methods", {
  id: varchar("id").primaryKey().default(sql`gen_random_uuid()`),
  userId: varchar("user_id").notNull().references(() => users.id),
  type: varchar("type").notNull(), // "mobile_money", "bank_card", "bank_transfer"
  provider: varchar("provider"), // "orange_money", "mtn_money", "moov_money", "visa", "mastercard"
  accountNumber: varchar("account_number").notNull(), // Phone number for mobile money, card number for cards
  accountName: varchar("account_name"), // Account holder name
  isDefault: boolean("is_default").default(false).notNull(),
  isActive: boolean("is_active").default(true).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// Relations
export const usersRelations = relations(users, ({ one, many }) => ({
  pharmacy: one(pharmacies, { fields: [users.id], references: [pharmacies.userId] }),
  courier: one(couriers, { fields: [users.id], references: [couriers.userId] }),
  orders: many(orders, { relationName: "client_orders" }),
}));

export const pharmaciesRelations = relations(pharmacies, ({ one, many }) => ({
  user: one(users, { fields: [pharmacies.userId], references: [users.id] }),
  stock: many(pharmacyStock),
  orders: many(orders),
}));

export const couriersRelations = relations(couriers, ({ one, many }) => ({
  user: one(users, { fields: [couriers.userId], references: [users.id] }),
  orders: many(orders),
}));

export const ordersRelations = relations(orders, ({ one, many }) => ({
  client: one(users, { fields: [orders.clientId], references: [users.id], relationName: "client_orders" }),
  pharmacy: one(pharmacies, { fields: [orders.pharmacyId], references: [pharmacies.id] }),
  courier: one(couriers, { fields: [orders.courierId], references: [couriers.id] }),
  medicine: one(medicines, { fields: [orders.medicineId], references: [medicines.id] }),
  events: many(orderEvents),
}));

export const medicinesRelations = relations(medicines, ({ many }) => ({
  stock: many(pharmacyStock),
  orders: many(orders),
}));

export const pharmacyStockRelations = relations(pharmacyStock, ({ one }) => ({
  pharmacy: one(pharmacies, { fields: [pharmacyStock.pharmacyId], references: [pharmacies.id] }),
  medicine: one(medicines, { fields: [pharmacyStock.medicineId], references: [medicines.id] }),
}));

export const walletsRelations = relations(wallets, ({ many }) => ({
  transactions: many(transactions),
}));

export const transactionsRelations = relations(transactions, ({ one }) => ({
  wallet: one(wallets, { fields: [transactions.walletId], references: [wallets.id] }),
  order: one(orders, { fields: [transactions.orderId], references: [orders.id] }),
}));

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

// Insert schemas
export const insertUserSchema = createInsertSchema(users).omit({ id: true, createdAt: true });
export const insertPharmacySchema = createInsertSchema(pharmacies).omit({ id: true, createdAt: true });
export const insertCourierSchema = createInsertSchema(couriers).omit({ id: true, createdAt: true });
export const insertMedicineSchema = createInsertSchema(medicines).omit({ 
  id: true, 
  createdAt: true, 
  updatedAt: true 
});
export const insertPharmacyStockSchema = createInsertSchema(pharmacyStock).omit({ 
  id: true, 
  updatedAt: true 
});
export const insertOrderSchema = createInsertSchema(orders).omit({ 
  id: true, 
  createdAt: true, 
  updatedAt: true 
});

// SOS request schema
export const sosRequestSchema = z.object({
  medicineName: z.string().min(1, "Le nom du médicament est requis"),
  quantity: z.number().min(1).max(10),
  clientPhone: z.string().min(8, "Numéro de téléphone requis"),
  clientAddress: z.string().min(1, "Adresse requise"),
  clientLat: z.number(),
  clientLng: z.number(),
  prescriptionUrl: z.string().optional(),
});

// Types
export type User = typeof users.$inferSelect;
export type InsertUser = z.infer<typeof insertUserSchema>;
export type Pharmacy = typeof pharmacies.$inferSelect;
export type InsertPharmacy = z.infer<typeof insertPharmacySchema>;
export type Courier = typeof couriers.$inferSelect;
export type InsertCourier = z.infer<typeof insertCourierSchema>;
export type Medicine = typeof medicines.$inferSelect;
export type InsertMedicine = z.infer<typeof insertMedicineSchema>;
export type InsertPharmacyStock = z.infer<typeof insertPharmacyStockSchema>;
export type Order = typeof orders.$inferSelect;
export type InsertOrder = z.infer<typeof insertOrderSchema>;
export type OrderEvent = typeof orderEvents.$inferSelect;
export type PharmacyStock = typeof pharmacyStock.$inferSelect;
export type Wallet = typeof wallets.$inferSelect;
export type LedgerEntry = typeof ledger.$inferSelect;
export type SosRequest = z.infer<typeof sosRequestSchema>;

// Extended types with relations
export type OrderWithDetails = Order & {
  client: User;
  pharmacy: Pharmacy & { user: User } | null;
  courier: Courier & { user: User } | null;
  medicine: Medicine | null;
  events: OrderEvent[];
};

export type PharmacyWithUser = Pharmacy & {
  user: User;
};

// Wallet system types
export type Transaction = typeof transactions.$inferSelect;
export type PaymentMethod = typeof paymentMethods.$inferSelect;
export type InsertTransaction = typeof transactions.$inferInsert;
export type InsertPaymentMethod = typeof paymentMethods.$inferInsert;

export type WalletWithUser = Wallet & {
  user: User;
};

export type TransactionWithWallet = Transaction & {
  wallet: Wallet;
};

export type CourierWithUser = Courier & {
  user: User;
};
