import { integer, pgTable, serial, text, timestamp, boolean, uniqueIndex, index, jsonb } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const menuCategories = pgTable("menu_categories", {
  id: serial("id").primaryKey(), slug: text("slug").notNull(), name: text("name").notNull(),
  yandexCategoryId: integer("yandex_category_id").notNull(), sbisCategoryId: text("sbis_category_id"), sbisCategoryNameSnapshot: text("sbis_category_name_snapshot"), categorySyncState: text("category_sync_state").notNull().default("unlinked"), sortOrder: integer("sort_order").notNull().default(0),
  isActive: boolean("is_active").notNull().default(true), archivedAt: timestamp("archived_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ slugUnique: uniqueIndex("menu_categories_slug_unique").on(t.slug), yandexUnique: uniqueIndex("menu_categories_yandex_id_unique").on(t.yandexCategoryId) }));

export const menuItems = pgTable("menu_items", {
  id: serial("id").primaryKey(), slug: text("slug").notNull(), categoryId: integer("category_id").notNull().references(() => menuCategories.id),
  name: text("name").notNull(), description: text("description"), shortDescription: text("short_description"), ingredients: text("ingredients"), weightText: text("weight_text"), imageUrl: text("image_url"),
  isAvailable: boolean("is_available").notNull().default(true), isAlcohol: boolean("is_alcohol").notNull().default(false), alcoholPercent: text("alcohol_percent"), country: text("country"), producer: text("producer"),
  showOnWebsite: boolean("show_on_website").notNull().default(true), publishToYandex: boolean("publish_to_yandex").notNull().default(false), allowOnlineOrder: boolean("allow_online_order").notNull().default(true), allowDelivery: boolean("allow_delivery").notNull().default(true),
  showDetails: boolean("show_details").notNull().default(true),
  sortOrder: integer("sort_order").notNull().default(0), source: text("source").notNull().default("manual"), sourceExternalId: text("source_external_id"), archivedAt: timestamp("archived_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ slugUnique: uniqueIndex("menu_items_slug_unique").on(t.slug), externalUnique: uniqueIndex("menu_items_source_external_unique").on(t.sourceExternalId), categoryIdx: index("menu_items_category_idx").on(t.categoryId) }));

export const menuItemVariants = pgTable("menu_item_variants", {
  id: serial("id").primaryKey(), itemId: integer("item_id").notNull().references(() => menuItems.id, { onDelete: "cascade" }), label: text("label").notNull(), volumeMl: integer("volume_ml"), weightGrams: integer("weight_grams"), priceKopecks: integer("price_kopecks").notNull(), yandexOfferId: text("yandex_offer_id").notNull(), isAvailable: boolean("is_available").notNull().default(true), sortOrder: integer("sort_order").notNull().default(0), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ offerUnique: uniqueIndex("menu_item_variants_offer_unique").on(t.yandexOfferId), itemIdx: index("menu_item_variants_item_idx").on(t.itemId) }));

export const menuFeedRequests = pgTable("menu_feed_requests", {
  id: serial("id").primaryKey(), requestedAt: timestamp("requested_at", { withTimezone: true }).defaultNow().notNull(), userAgent: text("user_agent"), responseStatus: integer("response_status").notNull(), itemsCount: integer("items_count").notNull().default(0), feedVersion: text("feed_version").notNull()
});

export const sbisCatalogSnapshots = pgTable("sbis_catalog_snapshots", {
  id: serial("id").primaryKey(), snapshotId: text("snapshot_id").notNull(), receivedAt: timestamp("received_at", { withTimezone: true }).defaultNow().notNull(), sourceVersion: text("source_version"), isComplete: boolean("is_complete").notNull().default(false), categoriesCount: integer("categories_count").notNull().default(0), itemsCount: integer("items_count").notNull().default(0), durationMs: integer("duration_ms"), error: text("error")
}, (t) => ({ snapshotUnique: uniqueIndex("sbis_catalog_snapshots_snapshot_id_unique").on(t.snapshotId) }));

export const sbisCatalogItems = pgTable("sbis_catalog_items", {
  id: serial("id").primaryKey(), sbisId: text("sbis_id").notNull(), categoryId: text("category_id"), categoryName: text("category_name").notNull(),
  name: text("name").notNull(), description: text("description"), imageUrl: text("image_url"), priceKopecks: integer("price_kopecks").notNull(), stockLeft: integer("stock_left"), available: boolean("available").notNull().default(true), inCurrentPrice: boolean("in_current_price").notNull().default(true), missingSnapshotCount: integer("missing_snapshot_count").notNull().default(0), unavailableText: text("unavailable_text"), variantOf: text("variant_of"), snapshotId: text("snapshot_id"), lastSeenAt: timestamp("last_seen_at", { withTimezone: true }),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ sbisIdUnique: uniqueIndex("sbis_catalog_items_sbis_id_unique").on(t.sbisId), categoryIdx: index("sbis_catalog_items_category_idx").on(t.categoryId) }));

export const menuItemSbisLinks = pgTable("menu_item_sbis_links", {
  id: serial("id").primaryKey(), menuItemId: integer("menu_item_id").notNull().references(() => menuItems.id, { onDelete: "cascade" }), sbisId: text("sbis_id").notNull().references(() => sbisCatalogItems.sbisId), linkedAt: timestamp("linked_at", { withTimezone: true }).defaultNow().notNull(), linkedBy: text("linked_by").notNull(), localFieldsHash: text("local_fields_hash"), sbisFieldsHash: text("sbis_fields_hash"), lastPullAt: timestamp("last_pull_at", { withTimezone: true }), lastPushAt: timestamp("last_push_at", { withTimezone: true }), lastResult: text("last_result"), lastError: text("last_error"), unlinkedAt: timestamp("unlinked_at", { withTimezone: true }), unlinkReason: text("unlink_reason")
}, (t) => ({ itemIdx: index("menu_item_sbis_links_item_idx").on(t.menuItemId), sbisIdx: index("menu_item_sbis_links_sbis_idx").on(t.sbisId), activeIdx: index("menu_item_sbis_links_active_idx").on(t.menuItemId, t.sbisId) }));

export const orderAttempts = pgTable("order_attempts", {
  id: serial("id").primaryKey(), clientRef: text("client_ref").notNull(), payloadHash: text("payload_hash").notNull(), sessionId: text("session_id"), state: text("state").notNull().default("creating"), botOrderId: text("bot_order_id"), moderationId: text("moderation_id"), botState: text("bot_state"), botResponse: jsonb("bot_response"), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ clientRefUnique: uniqueIndex("order_attempts_client_ref_unique").on(t.clientRef), stateIdx: index("order_attempts_state_idx").on(t.state) }));

export const adminAuditEvents = pgTable("admin_audit_events", {
  id: serial("id").primaryKey(), actor: text("actor").notNull(), action: text("action").notNull(), entityType: text("entity_type"), entityId: text("entity_id"), details: jsonb("details"), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ createdIdx: index("admin_audit_events_created_idx").on(t.createdAt) }));

export const sbisSyncState = pgTable("sbis_sync_state", {
  id: integer("id").primaryKey(), lastAttemptAt: timestamp("last_attempt_at", { withTimezone: true }), lastSuccessAt: timestamp("last_success_at", { withTimezone: true }), lastDurationMs: integer("last_duration_ms"), lastError: text("last_error"), lastSnapshotId: text("last_snapshot_id"), sourceVersion: text("source_version"), itemsCount: integer("items_count").notNull().default(0), categoriesCount: integer("categories_count").notNull().default(0), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
});

export const userPhones = pgTable("user_phones", {
  id: serial("id").primaryKey(), userId: integer("user_id").notNull().references(() => authUsers.id, { onDelete: "cascade" }), phone: text("phone").notNull(), verifiedAt: timestamp("verified_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ phoneUnique: uniqueIndex("user_phones_phone_unique").on(t.phone), userUnique: uniqueIndex("user_phones_user_unique").on(t.userId) }));

export const phoneVerificationChallenges = pgTable("phone_verification_challenges", {
  id: serial("id").primaryKey(), phone: text("phone").notNull(), pinHash: text("pin_hash").notNull(), userId: integer("user_id").references(() => authUsers.id, { onDelete: "cascade" }), ip: text("ip"), attempts: integer("attempts").notNull().default(0), expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(), consumedAt: timestamp("consumed_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ phoneIdx: index("phone_challenges_phone_idx").on(t.phone, t.createdAt), openUnique: uniqueIndex("phone_challenges_open_unique").on(t.phone).where(sql`consumed_at IS NULL`) }));

export const userEmails = pgTable("user_emails", {
  id: serial("id").primaryKey(), userId: integer("user_id").notNull().references(() => authUsers.id, { onDelete: "cascade" }), email: text("email").notNull(), verifiedAt: timestamp("verified_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ emailUnique: uniqueIndex("user_emails_email_unique").on(t.email), userUnique: uniqueIndex("user_emails_user_unique").on(t.userId) }));

export const emailVerificationChallenges = pgTable("email_verification_challenges", {
  id: serial("id").primaryKey(), email: text("email").notNull(), pinHash: text("pin_hash").notNull(), userId: integer("user_id").references(() => authUsers.id, { onDelete: "cascade" }), ip: text("ip"), attempts: integer("attempts").notNull().default(0), expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(), consumedAt: timestamp("consumed_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ emailIdx: index("email_challenges_email_idx").on(t.email, t.createdAt), openUnique: uniqueIndex("email_challenges_open_unique").on(t.email).where(sql`consumed_at IS NULL`) }));

export const messengerLoginRequests = pgTable("messenger_login_requests", {
  id: serial("id").primaryKey(), nonce: text("nonce").notNull(), messenger: text("messenger").notNull(), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(), claimedAt: timestamp("claimed_at", { withTimezone: true }), claimedMessengerUserId: text("claimed_messenger_user_id"), claimedDisplayName: text("claimed_display_name"), claimedPhone: text("claimed_phone"), phoneConfirmed: boolean("phone_confirmed").notNull().default(false)
}, (t) => ({ nonceUnique: uniqueIndex("messenger_login_requests_nonce_unique").on(t.nonce) }));

export type MenuCategoryRow = typeof menuCategories.$inferSelect;
export type MenuItemRow = typeof menuItems.$inferSelect;
export type MenuItemVariantRow = typeof menuItemVariants.$inferSelect;
export type SbisCatalogItemRow = typeof sbisCatalogItems.$inferSelect;
export const authUsers = pgTable("auth_users", {
  id: serial("id").primaryKey(), email: text("email"), displayName: text("display_name").notNull(), avatarUrl: text("avatar_url"), role: text("role").notNull().default("user"), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
});
export const authAccounts = pgTable("auth_accounts", {
  id: serial("id").primaryKey(), userId: integer("user_id").notNull().references(() => authUsers.id, { onDelete: "cascade" }), provider: text("provider").notNull(), providerAccountId: text("provider_account_id").notNull(), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ providerAccountUnique: uniqueIndex("auth_accounts_provider_account_unique").on(t.provider, t.providerAccountId) }));
export const authSessions = pgTable("auth_sessions", {
  id: serial("id").primaryKey(), tokenHash: text("token_hash").notNull().unique(), userId: integer("user_id").notNull().references(() => authUsers.id, { onDelete: "cascade" }), expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull()
});
export const siteSettings = pgTable("site_settings", {
  key: text("key").primaryKey(), value: boolean("value").notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
});
export const siteContactSettings = pgTable("site_contact_settings", {
  key: text("key").primaryKey(), value: text("value").notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
});