import { integer, pgTable, serial, bigserial, text, timestamp, boolean, uniqueIndex, index, jsonb, doublePrecision } 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"),
  allergens: text("allergens"), caloriesKcal: doublePrecision("calories_kcal"), proteinG: doublePrecision("protein_g"), fatG: doublePrecision("fat_g"), carbohydrateG: doublePrecision("carbohydrate_g"), nutritionSource: text("nutrition_source"), nutritionUpdatedAt: timestamp("nutrition_updated_at", { withTimezone: true }),
  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 }), archivedAt: timestamp("archived_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_active_unique").on(t.phone).where(sql`archived_at IS NULL`), userUnique: uniqueIndex("user_phones_user_active_unique").on(t.userId).where(sql`archived_at IS NULL`) }));

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 }), archivedAt: timestamp("archived_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_active_unique").on(t.email).where(sql`archived_at IS NULL`), userUnique: uniqueIndex("user_emails_user_active_unique").on(t.userId).where(sql`archived_at IS NULL`) }));

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(), purpose: text("purpose").notNull().default("customer"), 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(), customerRef: text("customer_ref").notNull().default(sql`gen_random_uuid()::text`), email: text("email"), displayName: text("display_name"), suggestedDisplayName: text("suggested_display_name"), avatarUrl: text("avatar_url"), role: text("role").notNull().default("user"), profileCompletedAt: timestamp("profile_completed_at", { withTimezone: true }), state: text("state").notNull().default("active"), suspendedAt: timestamp("suspended_at", { withTimezone: true }), suspendReason: text("suspend_reason"), purgeAfter: timestamp("purge_after", { withTimezone: true }), mergedIntoUserId: integer("merged_into_user_id"), legalHold: boolean("legal_hold").notNull().default(false), legalHoldReason: text("legal_hold_reason"), legalHoldAt: timestamp("legal_hold_at", { withTimezone: true }), legalHoldBy: text("legal_hold_by"), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ customerRefUnique: uniqueIndex("auth_users_customer_ref_unique").on(t.customerRef) }));
export const customerAddresses = pgTable("customer_addresses", {
  id: serial("id").primaryKey(), userId: integer("user_id").notNull().references(() => authUsers.id, { onDelete: "cascade" }), label: text("label"), address: text("address").notNull(), apartment: text("apartment"), floor: text("floor"), intercom: text("intercom"), courierComment: text("courier_comment"), isDefault: boolean("is_default").notNull().default(false), archivedAt: timestamp("archived_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ userActiveIdx: index("customer_addresses_user_active_idx").on(t.userId, t.updatedAt).where(sql`archived_at IS NULL`), oneDefault: uniqueIndex("customer_addresses_one_default_idx").on(t.userId).where(sql`is_default = true AND archived_at IS NULL`) }));
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(), archivedAt: timestamp("archived_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ providerAccountUnique: uniqueIndex("auth_accounts_provider_account_active_unique").on(t.provider, t.providerAccountId).where(sql`archived_at IS NULL`) }));
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(), revokedAt: timestamp("revoked_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull()
});
export const adminIdentities = pgTable("admin_identities", {
  id: serial("id").primaryKey(), userId: integer("user_id").notNull().references(() => authUsers.id, { onDelete: "cascade" }), provider: text("provider").notNull(), providerAccountId: text("provider_account_id").notNull(), approvedAt: timestamp("approved_at", { withTimezone: true }).defaultNow().notNull(), approvedBy: text("approved_by").notNull().default("email"), revokedAt: timestamp("revoked_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ providerUnique: uniqueIndex("admin_identities_provider_active_unique").on(t.provider, t.providerAccountId).where(sql`revoked_at IS NULL`), userProviderUnique: uniqueIndex("admin_identities_user_provider_active_unique").on(t.userId, t.provider).where(sql`revoked_at IS NULL`) }));
export const adminSessions = pgTable("admin_sessions", {
  id: serial("id").primaryKey(), tokenHash: text("token_hash").notNull().unique(), userId: integer("user_id").notNull().references(() => authUsers.id, { onDelete: "cascade" }), authMethod: text("auth_method").notNull(), lastSeenAt: timestamp("last_seen_at", { withTimezone: true }).defaultNow().notNull(), expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(), revokedAt: timestamp("revoked_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ liveIdx: index("admin_sessions_live_idx").on(t.tokenHash, t.expiresAt) }));
export const adminAuthorizationTokens = pgTable("admin_authorization_tokens", {
  id: serial("id").primaryKey(), publicId: text("public_id").notNull().unique(), tokenHash: text("token_hash").notNull().unique(), kind: text("kind").notNull(), userId: integer("user_id").notNull().references(() => authUsers.id, { onDelete: "cascade" }), provider: text("provider").notNull(), providerAccountId: text("provider_account_id").notNull(), expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(), consumedAt: timestamp("consumed_at", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull()
}, (t) => ({ pendingIdx: index("admin_authorization_tokens_pending_idx").on(t.userId, t.provider, t.expiresAt) }));
export const privacySettings = pgTable("privacy_settings", { key: text("key").primaryKey(), valueInteger: integer("value_integer").notNull(), revision: integer("revision").notNull().default(1), updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(), updatedBy: text("updated_by") });
export const accountLifecycleEvents = pgTable("account_lifecycle_events", { id: bigserial("id", { mode: "number" }).primaryKey(), userId: integer("user_id").references(() => authUsers.id), customerRef: text("customer_ref"), eventType: text("event_type").notNull(), reason: text("reason"), actorType: text("actor_type").notNull().default("system"), actorId: text("actor_id"), details: jsonb("details"), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull() });
export const consentEvents = pgTable("consent_events", { id: bigserial("id", { mode: "number" }).primaryKey(), userId: integer("user_id").references(() => authUsers.id), customerRef: text("customer_ref"), purpose: text("purpose").notNull(), policyRevision: integer("policy_revision").notNull(), granted: boolean("granted").notNull(), source: text("source").notNull(), ip: text("ip"), userAgent: text("user_agent"), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull() });
export const pdnJobs = pgTable("pdn_jobs", { id: bigserial("id", { mode: "number" }).primaryKey(), userId: integer("user_id").references(() => authUsers.id), customerRef: text("customer_ref").notNull(), jobType: text("job_type").notNull(), state: text("state").notNull().default("pending"), payload: jsonb("payload"), attempts: integer("attempts").notNull().default(0), nextAttemptAt: timestamp("next_attempt_at", { withTimezone: true }).defaultNow().notNull(), lastError: text("last_error"), createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(), updatedAt: timestamp("updated_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()
});