import { pgTable, index, foreignKey, uuid, text, jsonb, integer, numeric, timestamp, unique, boolean, uniqueIndex, date, vector, doublePrecision, primaryKey, pgEnum, check } from "drizzle-orm/pg-core" import { sql } from "drizzle-orm" export const adminRole = pgEnum("admin_role", ['superadmin', 'support']) export const agentRole = pgEnum("agent_role", ['orders', 'reservations', 'campaigns', 'voice', 'chat', 'general']) export const blogStatus = pgEnum("blog_status", ['draft', 'published', 'scheduled', 'archived']) export const bookingStatus = pgEnum("booking_status", ['pending', 'confirmed', 'seated', 'completed', 'cancelled', 'noshow', 'escalated', 'reminder_sent']) export const channelType = pgEnum("channel_type", ['chat', 'voice', 'whatsapp', 'email', 'sms', 'storefront', 'pos', 'zomato', 'swiggy', 'online']) export const conversationStatus = pgEnum("conversation_status", ['ai', 'human', 'resolved']) export const kbType = pgEnum("kb_type", ['doc', 'url', 'qa', 'article']) export const notificationType = pgEnum("notification_type", ['order', 'booking', 'ai', 'system', 'billing', 'staff']) export const orderStatus = pgEnum("order_status", ['pending', 'confirmed', 'kitchen', 'ready', 'delivered', 'completed', 'cancelled', 'failed', 'escalated', 'processing', 'rider']) export const phoneType = pgEnum("phone_type", ['purchased', 'sip']) export const planStatus = pgEnum("plan_status", ['active', 'inactive', 'trial', 'cancelled', 'past_due']) export const ticketPriority = pgEnum("ticket_priority", ['low', 'medium', 'high', 'urgent']) export const ticketStatus = pgEnum("ticket_status", ['open', 'in_progress', 'resolved', 'closed']) export const userRole = pgEnum("user_role", ['owner', 'manager', 'staff', 'superadmin', 'support']) export const customers = pgTable("customers", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), name: text().notNull(), email: text(), phone: text(), preferences: jsonb().default([]), tags: jsonb().default([]), totalVisits: integer("total_visits").default(0), totalSpend: numeric("total_spend", { precision: 10, scale: 2 }).default('0'), tier: text().default('bronze'), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), notes: text(), }, (table) => [ index("idx_customers_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "customers_restaurant_id_fkey" }).onDelete("cascade"), ]); export const adminUsers = pgTable("admin_users", { id: uuid().defaultRandom().primaryKey().notNull(), email: text().notNull(), passwordHash: text("password_hash").notNull(), role: adminRole().default('support'), isActive: boolean("is_active").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ unique("admin_users_email_key").on(table.email), ]); export const plans = pgTable("plans", { id: uuid().defaultRandom().primaryKey().notNull(), name: text().notNull(), priceMonthly: numeric("price_monthly", { precision: 10, scale: 2 }).default('0').notNull(), priceAnnual: numeric("price_annual", { precision: 10, scale: 2 }).default('0').notNull(), features: jsonb().default([]), limits: jsonb().default({}), isActive: boolean("is_active").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), stripePriceIdMonthly: text("stripe_price_id_monthly"), stripePriceIdAnnual: text("stripe_price_id_annual"), stripeProductId: text("stripe_product_id"), description: text(), trialDays: integer("trial_days").default(14), slug: text(), }, (table) => [ uniqueIndex("idx_plans_slug").using("btree", table.slug.asc().nullsLast().op("text_ops")), ]); export const subscriptions = pgTable("subscriptions", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), planId: uuid("plan_id"), status: planStatus().default('trial'), currentPeriodEnd: timestamp("current_period_end", { withTimezone: true, mode: 'string' }), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), stripeCustomerId: text("stripe_customer_id"), stripeSubscriptionId: text("stripe_subscription_id"), stripePriceId: text("stripe_price_id"), trialStart: timestamp("trial_start", { withTimezone: true, mode: 'string' }), trialEnd: timestamp("trial_end", { withTimezone: true, mode: 'string' }), }, (table) => [ foreignKey({ columns: [table.planId], foreignColumns: [plans.id], name: "subscriptions_plan_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "subscriptions_restaurant_id_fkey" }).onDelete("cascade"), unique("subscriptions_restaurant_id_key").on(table.restaurantId), // Task #309: a row in 'trial' status must always carry a trial_end so // the gate cannot be bypassed by a NULL trial_end. Backfill + ADD // CONSTRAINT lives in MIGRATION_SQL (init.ts). check( "subscriptions_trial_end_required", sql`${table.status} <> 'trial' OR ${table.trialEnd} IS NOT NULL`, ), ]); export const branches = pgTable("branches", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), name: text().notNull(), address: text(), phone: text(), hours: jsonb().default({}), timezone: text().default('UTC'), isActive: boolean("is_active").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), smtpHost: text("smtp_host"), smtpPort: integer("smtp_port"), smtpUser: text("smtp_user"), smtpPass: text("smtp_pass"), smtpFrom: text("smtp_from"), slug: text(), storefrontEnabled: boolean("storefront_enabled").default(false).notNull(), acceptsDineIn: boolean("accepts_dine_in").default(true).notNull(), acceptsTakeaway: boolean("accepts_takeaway").default(true).notNull(), acceptsDelivery: boolean("accepts_delivery").default(false).notNull(), minOrderValue: numeric("min_order_value", { precision: 10, scale: 2 }).default('0').notNull(), qrStyleJson: jsonb("qr_style_json").default({}).notNull(), storefrontMessage: text("storefront_message"), nameCustomized: boolean("name_customized").default(false).notNull(), }, (table) => [ index("idx_branches_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), uniqueIndex("idx_branches_restaurant_slug").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops"), table.slug.asc().nullsLast().op("text_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "branches_restaurant_id_fkey" }).onDelete("cascade"), ]); export const restaurantTables = pgTable("restaurant_tables", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id").notNull(), tableNumber: text("table_number").notNull(), label: text(), capacity: integer().default(2).notNull(), zone: text(), qrStyleJson: jsonb("qr_style_json").default({}).notNull(), isActive: boolean("is_active").default(true).notNull(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(), }, (table) => [ uniqueIndex("idx_tables_branch_number").using("btree", table.branchId.asc().nullsLast().op("uuid_ops"), table.tableNumber.asc().nullsLast().op("text_ops")), index("idx_tables_restaurant").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "restaurant_tables_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "restaurant_tables_branch_id_fkey" }).onDelete("cascade"), ]); export const restaurants = pgTable("restaurants", { id: uuid().defaultRandom().primaryKey().notNull(), name: text().notNull(), ownerId: uuid("owner_id").notNull(), plan: text().default('starter'), status: text().default('active'), logoUrl: text("logo_url"), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), currency: text(), currencyLocked: boolean("currency_locked").default(false), currencySetAt: timestamp("currency_set_at", { withTimezone: true, mode: 'string' }), cuisineType: text("cuisine_type"), phone: text(), address: text(), description: text(), seatingCapacity: integer("seating_capacity"), displayId: text("display_id"), smtpHost: text("smtp_host"), smtpPort: integer("smtp_port"), smtpUser: text("smtp_user"), smtpPassEnc: text("smtp_pass_enc"), smtpFrom: text("smtp_from"), smtpSecure: boolean("smtp_secure").default(false), smtpEnabled: boolean("smtp_enabled").default(false), slug: text(), }, (table) => [ uniqueIndex("idx_restaurants_display_id").using("btree", table.displayId.asc().nullsLast().op("text_ops")), uniqueIndex("idx_restaurants_slug").using("btree", table.slug.asc().nullsLast().op("text_ops")), index("idx_restaurants_owner_id").using("btree", table.ownerId.asc().nullsLast().op("uuid_ops")), ]); export const users = pgTable("users", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id"), branchId: uuid("branch_id"), role: userRole().default('staff'), name: text().default('').notNull(), email: text().notNull(), passwordHash: text("password_hash").notNull(), isActive: boolean("is_active").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), preferredLanguage: text("preferred_language").default('en'), pendingEmail: text("pending_email"), }, (table) => [ index("idx_users_email").using("btree", table.email.asc().nullsLast().op("text_ops")), index("idx_users_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "users_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "users_restaurant_id_fkey" }).onDelete("cascade"), unique("users_email_key").on(table.email), ]); export const orders = pgTable("orders", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), customerId: uuid("customer_id"), customerName: text("customer_name"), customerPhone: text("customer_phone"), items: jsonb().default([]), status: orderStatus().default('pending'), channel: channelType().default('chat'), deliveryType: text("delivery_type").default('dine-in'), tableNumber: text("table_number"), subtotal: numeric({ precision: 10, scale: 2 }).default('0'), tax: numeric({ precision: 10, scale: 2 }).default('0'), total: numeric({ precision: 10, scale: 2 }).default('0'), aiConfidence: integer("ai_confidence"), agentId: text("agent_id"), conversationId: text("conversation_id"), specialInstructions: text("special_instructions"), transcriptSummary: text("transcript_summary"), isModified: boolean("is_modified").default(false), modificationNote: text("modification_note"), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), deliveryAddress: text("delivery_address"), orderNumber: integer("order_number").notNull(), customerEmail: text("customer_email"), source: text(), deliveryAddressJson: jsonb("delivery_address_json"), tableId: uuid("table_id"), couponId: uuid("coupon_id"), couponCode: text("coupon_code"), discountAmount: numeric("discount_amount", { precision: 10, scale: 2 }).default('0'), }, (table) => [ index("idx_orders_branch_id").using("btree", table.branchId.asc().nullsLast().op("uuid_ops")), index("idx_orders_source").using("btree", table.source.asc().nullsLast().op("text_ops")), index("idx_orders_table").using("btree", table.tableId.asc().nullsLast().op("uuid_ops")), index("idx_orders_created_at").using("btree", table.createdAt.desc().nullsFirst().op("timestamptz_ops")), index("idx_orders_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), uniqueIndex("idx_orders_restaurant_order_number").using("btree", table.restaurantId.asc().nullsLast().op("int4_ops"), table.orderNumber.asc().nullsLast().op("uuid_ops")), index("idx_orders_status").using("btree", table.status.asc().nullsLast().op("enum_ops")), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "orders_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.customerId], foreignColumns: [customers.id], name: "orders_customer_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "orders_restaurant_id_fkey" }).onDelete("cascade"), ]); export const bookings = pgTable("bookings", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), customerId: uuid("customer_id"), guestName: text("guest_name").notNull(), guestPhone: text("guest_phone"), guestEmail: text("guest_email"), tableNo: text("table_no"), partySize: integer("party_size").default(1), bookingDate: date("booking_date"), bookingTime: text("booking_time"), zone: text(), status: bookingStatus().default('pending'), channel: channelType().default('chat'), occasion: text(), dietaryNeeds: jsonb("dietary_needs").default([]), specialRequests: text("special_requests"), aiNotes: text("ai_notes"), aiConfidence: integer("ai_confidence"), reminderSent: boolean("reminder_sent").default(false), isVip: boolean("is_vip").default(false), confirmationChannel: text("confirmation_channel").default('none'), agentId: text("agent_id"), conversationId: text("conversation_id"), notes: text(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_bookings_branch_id").using("btree", table.branchId.asc().nullsLast().op("uuid_ops")), index("idx_bookings_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), index("idx_bookings_status").using("btree", table.status.asc().nullsLast().op("enum_ops")), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "bookings_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.customerId], foreignColumns: [customers.id], name: "bookings_customer_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "bookings_restaurant_id_fkey" }).onDelete("cascade"), ]); export const menuCategories = pgTable("menu_categories", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), name: text().notNull(), displayOrder: integer("display_order").default(0), isActive: boolean("is_active").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_menu_categories_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "menu_categories_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "menu_categories_restaurant_id_fkey" }).onDelete("cascade"), ]); export const phoneNumbers = pgTable("phone_numbers", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), number: text().notNull(), type: phoneType().default('purchased'), sipConfig: jsonb("sip_config").default({}), isActive: boolean("is_active").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), assignedAgentId: uuid("assigned_agent_id"), displayName: text("display_name"), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), callRecording: boolean("call_recording").default(false), callTranscription: boolean("call_transcription").default(true), greetingOverride: text("greeting_override"), twilioNumberSid: text("twilio_number_sid"), fallbackNumber: text("fallback_number"), }, (table) => [ index("idx_phone_numbers_assigned_agent").using("btree", table.assignedAgentId.asc().nullsLast().op("uuid_ops")), index("idx_phone_numbers_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.assignedAgentId], foreignColumns: [aiAgents.id], name: "phone_numbers_assigned_agent_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "phone_numbers_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "phone_numbers_restaurant_id_fkey" }).onDelete("cascade"), ]); export const conversations = pgTable("conversations", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), customerId: uuid("customer_id"), customerName: text("customer_name"), customerAvatar: text("customer_avatar"), channel: channelType().default('chat'), status: conversationStatus().default('ai'), messages: jsonb().default([]), topic: text(), unreadCount: integer("unread_count").default(0), aiConfidence: integer("ai_confidence"), assignedAgent: text("assigned_agent"), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_conversations_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), index("idx_conversations_status").using("btree", table.status.asc().nullsLast().op("enum_ops")), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "conversations_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.customerId], foreignColumns: [customers.id], name: "conversations_customer_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "conversations_restaurant_id_fkey" }).onDelete("cascade"), ]); export const aiAgentConfigs = pgTable("ai_agent_configs", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), voiceEnabled: boolean("voice_enabled").default(true), chatEnabled: boolean("chat_enabled").default(true), model: text().default('gpt-4o'), voiceModel: text("voice_model").default('nova'), responseStyle: text("response_style").default('friendly'), systemPrompt: text("system_prompt"), greetingScript: text("greeting_script"), closingScript: text("closing_script"), fallbackRules: jsonb("fallback_rules").default([]), capabilities: jsonb().default({}), twilioPhoneNumber: text("twilio_phone_number"), inboundRouting: text("inbound_routing").default('ai_agent'), outboundEnabled: boolean("outbound_enabled").default(true), callGreeting: text("call_greeting").default('Hello! Thank you for calling. I am your AI assistant. How can I help you today?'), callRecordingEnabled: boolean("call_recording_enabled").default(false), callTranscriptionEnabled: boolean("call_transcription_enabled").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "ai_agent_configs_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "ai_agent_configs_restaurant_id_fkey" }).onDelete("cascade"), ]); export const knowledgeBase = pgTable("knowledge_base", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), type: kbType().default('doc'), title: text().notNull(), content: text(), url: text(), question: text(), answer: text(), fileSize: text("file_size"), fileType: text("file_type"), pages: integer(), status: text().default('active'), isActive: boolean("is_active").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), embedding: vector({ dimensions: 1536 }), embeddingStatus: text("embedding_status").default('pending').notNull(), embeddingError: text("embedding_error"), }, (table) => [ index("kb_embedding_idx").using("ivfflat", table.embedding.asc().nullsLast().op("vector_cosine_ops")).with({lists: "100"}), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "knowledge_base_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "knowledge_base_restaurant_id_fkey" }).onDelete("cascade"), ]); export const staff = pgTable("staff", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), userId: uuid("user_id"), name: text().notNull(), email: text(), role: text().default('staff'), permissions: jsonb().default([]), isActive: boolean("is_active").default(true), lastActiveAt: timestamp("last_active_at", { withTimezone: true, mode: 'string' }), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), roleId: uuid("role_id"), }, (table) => [ foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "staff_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "staff_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.roleId], foreignColumns: [roles.id], name: "staff_role_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.userId], foreignColumns: [users.id], name: "staff_user_id_fkey" }).onDelete("set null"), ]); export const webhooks = pgTable("webhooks", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), endpointUrl: text("endpoint_url").notNull(), events: jsonb().default([]), secret: text(), status: text().default('active'), successRate: numeric("success_rate", { precision: 5, scale: 2 }).default('100'), totalDeliveries: integer("total_deliveries").default(0), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "webhooks_restaurant_id_fkey" }).onDelete("cascade"), ]); export const apiKeys = pgTable("api_keys", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), name: text().notNull(), keyHash: text("key_hash").notNull(), keyPreview: text("key_preview"), permissions: jsonb().default([]), callsThisMonth: integer("calls_this_month").default(0), status: text().default('active'), lastUsedAt: timestamp("last_used_at", { withTimezone: true, mode: 'string' }), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "api_keys_restaurant_id_fkey" }).onDelete("cascade"), ]); export const notifications = pgTable("notifications", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), userId: uuid("user_id"), type: notificationType().default('system'), title: text().notNull(), message: text(), isRead: boolean("is_read").default(false), actionLabel: text("action_label"), actionHref: text("action_href"), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_notifications_is_read").using("btree", table.isRead.asc().nullsLast().op("bool_ops")), index("idx_notifications_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), index("idx_notifications_user_id").using("btree", table.userId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "notifications_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.userId], foreignColumns: [users.id], name: "notifications_user_id_fkey" }).onDelete("cascade"), ]); export const supportTickets = pgTable("support_tickets", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), userId: uuid("user_id"), subject: text().notNull(), category: text().default('Other'), priority: ticketPriority().default('medium'), status: ticketStatus().default('open'), description: text(), messages: jsonb().default([]), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_support_tickets_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "support_tickets_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.userId], foreignColumns: [users.id], name: "support_tickets_user_id_fkey" }).onDelete("set null"), ]); export const blogPosts = pgTable("blog_posts", { id: uuid().defaultRandom().primaryKey().notNull(), authorId: uuid("author_id"), title: text().notNull(), slug: text().notNull(), content: jsonb().default([]), heroImage: text("hero_image"), excerpt: text(), category: text().default('General'), tags: jsonb().default([]), status: blogStatus().default('draft'), views: integer().default(0), publishedAt: timestamp("published_at", { withTimezone: true, mode: 'string' }), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_blog_posts_slug").using("btree", table.slug.asc().nullsLast().op("text_ops")), index("idx_blog_posts_status").using("btree", table.status.asc().nullsLast().op("enum_ops")), unique("blog_posts_slug_key").on(table.slug), ]); export const callLogs = pgTable("call_logs", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), conversationId: uuid("conversation_id"), callSid: text("call_sid"), direction: text().default('inbound').notNull(), fromNumber: text("from_number"), toNumber: text("to_number"), status: text().default('initiated'), durationSeconds: integer("duration_seconds").default(0), transcript: text(), recordingUrl: text("recording_url"), aiHandled: boolean("ai_handled").default(true), escalatedToHuman: boolean("escalated_to_human").default(false), escalationReason: text("escalation_reason"), startedAt: timestamp("started_at", { withTimezone: true, mode: 'string' }).defaultNow(), endedAt: timestamp("ended_at", { withTimezone: true, mode: 'string' }), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_call_logs_call_sid").using("btree", table.callSid.asc().nullsLast().op("text_ops")), index("idx_call_logs_conversation_id").using("btree", table.conversationId.asc().nullsLast().op("uuid_ops")), index("idx_call_logs_direction").using("btree", table.direction.asc().nullsLast().op("text_ops")), index("idx_call_logs_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "call_logs_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.conversationId], foreignColumns: [conversations.id], name: "call_logs_conversation_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "call_logs_restaurant_id_fkey" }).onDelete("cascade"), ]); export const passwordResetTokens = pgTable("password_reset_tokens", { id: uuid().defaultRandom().primaryKey().notNull(), userId: uuid("user_id").notNull(), token: text().notNull(), expiresAt: timestamp("expires_at", { withTimezone: true, mode: 'string' }).notNull(), used: boolean().default(false), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_password_reset_tokens_token").using("btree", table.token.asc().nullsLast().op("text_ops")), foreignKey({ columns: [table.userId], foreignColumns: [users.id], name: "password_reset_tokens_user_id_fkey" }).onDelete("cascade"), unique("password_reset_tokens_user_id_key").on(table.userId), ]); export const platformSettings = pgTable("platform_settings", { id: uuid().defaultRandom().primaryKey().notNull(), key: text().notNull(), value: jsonb().default({}).notNull(), updatedBy: uuid("updated_by"), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_platform_settings_key").using("btree", table.key.asc().nullsLast().op("text_ops")), unique("platform_settings_key_key").on(table.key), ]); export const notificationPreferences = pgTable("notification_preferences", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), userId: uuid("user_id"), preferences: jsonb().default([]), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_notification_preferences_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), index("idx_notification_preferences_user_id").using("btree", table.userId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "notification_preferences_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.userId], foreignColumns: [users.id], name: "notification_preferences_user_id_fkey" }).onDelete("cascade"), ]); export const messageTemplates = pgTable("message_templates", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), templateKey: text("template_key").notNull(), channel: text().notNull(), content: text().notNull(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_message_templates_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "message_templates_restaurant_id_fkey" }).onDelete("cascade"), unique("message_templates_restaurant_id_template_key_channel_key").on(table.restaurantId, table.templateKey, table.channel), ]); export const auditLogs = pgTable("audit_logs", { id: uuid().defaultRandom().primaryKey().notNull(), actorId: uuid("actor_id"), actorEmail: text("actor_email"), actorType: text("actor_type").default('system').notNull(), action: text().notNull(), resource: text(), resourceId: uuid("resource_id"), ipAddress: text("ip_address"), severity: text().default('info').notNull(), metadata: jsonb().default({}), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_audit_logs_actor_type").using("btree", table.actorType.asc().nullsLast().op("text_ops")), index("idx_audit_logs_created_at").using("btree", table.createdAt.desc().nullsFirst().op("timestamptz_ops")), index("idx_audit_logs_severity").using("btree", table.severity.asc().nullsLast().op("text_ops")), ]); export const webhookDeliveryLogs = pgTable("webhook_delivery_logs", { id: uuid().defaultRandom().primaryKey().notNull(), webhookId: uuid("webhook_id").notNull(), restaurantId: uuid("restaurant_id").notNull(), event: text().notNull(), status: text().default('success').notNull(), responseCode: integer("response_code"), durationMs: integer("duration_ms"), errorMessage: text("error_message"), triggeredAt: timestamp("triggered_at", { withTimezone: true, mode: 'string' }).defaultNow(), responseBody: text("response_body"), }, (table) => [ foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "webhook_delivery_logs_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.webhookId], foreignColumns: [webhooks.id], name: "webhook_delivery_logs_webhook_id_fkey" }).onDelete("cascade"), ]); export const menuItems = pgTable("menu_items", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), categoryId: uuid("category_id"), name: text().notNull(), description: text(), price: numeric({ precision: 10, scale: 2 }).default('0'), modifiers: jsonb().default([]), dietaryTags: jsonb("dietary_tags").default([]), imageUrl: text("image_url"), isAvailable: boolean("is_available").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), sortOrder: integer("sort_order").default(0), spiceLevel: integer("spice_level").default(0), isVeg: boolean("is_veg").default(false), prepTimeMinutes: integer("prep_time_minutes").default(0), calories: integer().default(0), }, (table) => [ index("idx_menu_items_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "menu_items_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.categoryId], foreignColumns: [menuCategories.id], name: "menu_items_category_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "menu_items_restaurant_id_fkey" }).onDelete("cascade"), ]); export const menuModifierGroups = pgTable("menu_modifier_groups", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), menuItemId: uuid("menu_item_id").notNull(), name: text().notNull(), type: text().default('checkbox').notNull(), isRequired: boolean("is_required").default(false), minSelections: integer("min_selections").default(0), maxSelections: integer("max_selections"), displayOrder: integer("display_order").default(0), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_menu_modifier_groups_item_id").using("btree", table.menuItemId.asc().nullsLast().op("uuid_ops")), index("idx_menu_modifier_groups_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "menu_modifier_groups_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.menuItemId], foreignColumns: [menuItems.id], name: "menu_modifier_groups_menu_item_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "menu_modifier_groups_restaurant_id_fkey" }).onDelete("cascade"), ]); export const menuModifierOptions = pgTable("menu_modifier_options", { id: uuid().defaultRandom().primaryKey().notNull(), modifierGroupId: uuid("modifier_group_id").notNull(), name: text().notNull(), priceDelta: numeric("price_delta", { precision: 10, scale: 2 }).default('0'), isDefault: boolean("is_default").default(false), isAvailable: boolean("is_available").default(true), displayOrder: integer("display_order").default(0), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_menu_modifier_options_group_id").using("btree", table.modifierGroupId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.modifierGroupId], foreignColumns: [menuModifierGroups.id], name: "menu_modifier_options_modifier_group_id_fkey" }).onDelete("cascade"), ]); export const llmProviders = pgTable("llm_providers", { id: uuid().defaultRandom().primaryKey().notNull(), name: text().notNull(), displayName: text("display_name").notNull(), apiBaseUrl: text("api_base_url").notNull(), authHeaderFormat: text("auth_header_format").default('Bearer').notNull(), apiKeyEnvVar: text("api_key_env_var"), isActive: boolean("is_active").default(true), displayOrder: integer("display_order").default(0), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ unique("llm_providers_name_key").on(table.name), ]); export const llmModels = pgTable("llm_models", { id: uuid().defaultRandom().primaryKey().notNull(), providerId: uuid("provider_id").notNull(), modelId: text("model_id").notNull(), displayName: text("display_name").notNull(), contextLength: integer("context_length").default(4096), supportsFunctions: boolean("supports_functions").default(false), isActive: boolean("is_active").default(true), displayOrder: integer("display_order").default(0), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_llm_models_provider_id").using("btree", table.providerId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.providerId], foreignColumns: [llmProviders.id], name: "llm_models_provider_id_fkey" }).onDelete("cascade"), unique("llm_models_provider_id_model_id_key").on(table.providerId, table.modelId), ]); export const voiceProviders = pgTable("voice_providers", { id: uuid().defaultRandom().primaryKey().notNull(), name: text().notNull(), displayName: text("display_name").notNull(), apiBaseUrl: text("api_base_url").notNull(), apiKeyEnvVar: text("api_key_env_var"), isActive: boolean("is_active").default(true), displayOrder: integer("display_order").default(0), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ unique("voice_providers_name_key").on(table.name), ]); export const voiceModels = pgTable("voice_models", { id: uuid().defaultRandom().primaryKey().notNull(), voiceProviderId: uuid("voice_provider_id").notNull(), voiceId: text("voice_id").notNull(), displayName: text("display_name").notNull(), languageCode: text("language_code").default('en-US'), gender: text().default('neutral'), isActive: boolean("is_active").default(true), displayOrder: integer("display_order").default(0), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_voice_models_provider_id").using("btree", table.voiceProviderId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.voiceProviderId], foreignColumns: [voiceProviders.id], name: "voice_models_voice_provider_id_fkey" }).onDelete("cascade"), unique("voice_models_voice_provider_id_voice_id_key").on(table.voiceProviderId, table.voiceId), ]); export const restaurantApiKeys = pgTable("restaurant_api_keys", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), providerName: text("provider_name").notNull(), encryptedApiKey: text("encrypted_api_key").notNull(), keyHint: text("key_hint").notNull(), isActive: boolean("is_active").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_restaurant_api_keys_restaurant").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "restaurant_api_keys_restaurant_id_fkey" }).onDelete("cascade"), unique("restaurant_api_keys_restaurant_id_provider_name_key").on(table.restaurantId, table.providerName), ]); export const telephoneSettings = pgTable("telephone_settings", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), twilioConnected: boolean("twilio_connected").default(false), twilioAccountSidHint: text("twilio_account_sid_hint"), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), twilioAccountSid: text("twilio_account_sid"), twilioAuthToken: text("twilio_auth_token"), }, (table) => [ foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "telephone_settings_restaurant_id_fkey" }).onDelete("cascade"), unique("telephone_settings_restaurant_id_key").on(table.restaurantId), ]); export const sipRegistrations = pgTable("sip_registrations", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id"), contactUri: text("contact_uri").notNull(), aor: text().notNull(), expiresAt: timestamp("expires_at", { withTimezone: true, mode: 'string' }).notNull(), userAgent: text("user_agent"), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_sip_registrations_aor").using("btree", table.aor.asc().nullsLast().op("text_ops")), uniqueIndex("idx_sip_registrations_aor_contact").using("btree", table.aor.asc().nullsLast().op("text_ops"), table.contactUri.asc().nullsLast().op("text_ops")), index("idx_sip_registrations_expires").using("btree", table.expiresAt.asc().nullsLast().op("timestamptz_ops")), index("idx_sip_registrations_restaurant").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "sip_registrations_restaurant_id_fkey" }).onDelete("cascade"), ]); export const sipCallSessions = pgTable("sip_call_sessions", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), phoneNumberId: uuid("phone_number_id"), agentId: uuid("agent_id"), sipCallId: text("sip_call_id"), callerNumber: text("caller_number"), calleeNumber: text("callee_number"), direction: text().default('inbound').notNull(), status: text().default('ringing').notNull(), startedAt: timestamp("started_at", { withTimezone: true, mode: 'string' }).defaultNow(), endedAt: timestamp("ended_at", { withTimezone: true, mode: 'string' }), durationSeconds: integer("duration_seconds").default(0), transcript: jsonb().default([]), escalatedToHuman: boolean("escalated_to_human").default(false), metadata: jsonb().default({}), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), recordingUrl: text("recording_url"), mentionedOfferIds: jsonb("mentioned_offer_ids").default([]), }, (table) => [ index("idx_sip_call_sessions_restaurant").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), index("idx_sip_call_sessions_started_at").using("btree", table.startedAt.desc().nullsFirst().op("timestamptz_ops")), index("idx_sip_call_sessions_status").using("btree", table.status.asc().nullsLast().op("text_ops")), foreignKey({ columns: [table.agentId], foreignColumns: [aiAgents.id], name: "sip_call_sessions_agent_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.phoneNumberId], foreignColumns: [phoneNumbers.id], name: "sip_call_sessions_phone_number_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "sip_call_sessions_restaurant_id_fkey" }).onDelete("cascade"), ]); export const widgetSettings = pgTable("widget_settings", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), primaryColor: text("primary_color").default('#f97316'), welcomeMessage: text("welcome_message").default('Hi! How can we help you today?'), placeholderText: text("placeholder_text").default('Type your message...'), position: text().default('bottom-right'), autoOpenDelay: integer("auto_open_delay").default(0), botName: text("bot_name").default('AI Assistant'), showBranding: boolean("show_branding").default(true), isEnabled: boolean("is_enabled").default(true), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), agentId: uuid("agent_id"), iconUrl: text("icon_url"), }, (table) => [ foreignKey({ columns: [table.agentId], foreignColumns: [aiAgents.id], name: "widget_settings_agent_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "widget_settings_restaurant_id_fkey" }).onDelete("cascade"), unique("widget_settings_restaurant_id_key").on(table.restaurantId), ]); export const aiAgents = pgTable("ai_agents", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), name: text().notNull(), description: text(), role: agentRole().default('general'), llmModelId: uuid("llm_model_id"), voiceModelId: uuid("voice_model_id"), systemPrompt: text("system_prompt"), greetingScript: text("greeting_script"), closingScript: text("closing_script"), fallbackRules: jsonb("fallback_rules").default([]), capabilities: jsonb().default({}), isActive: boolean("is_active").default(true), isDefault: boolean("is_default").default(false), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), voiceLanguageCode: text("voice_language_code").default('hi-IN'), channels: text().array().default(["chat", "voice"]).notNull(), realtimeModel: text("realtime_model").default('gpt-realtime-1.5'), vadThreshold: doublePrecision("vad_threshold").default(0.5), vadPrefixPaddingMs: integer("vad_prefix_padding_ms").default(300), vadSilenceDurationMs: integer("vad_silence_duration_ms").default(600), }, (table) => [ uniqueIndex("idx_ai_agents_default_per_restaurant").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")).where(sql`(is_default = true)`), index("idx_ai_agents_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), index("idx_ai_agents_role").using("btree", table.role.asc().nullsLast().op("enum_ops")), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "ai_agents_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.llmModelId], foreignColumns: [llmModels.id], name: "ai_agents_llm_model_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "ai_agents_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.voiceModelId], foreignColumns: [voiceModels.id], name: "ai_agents_voice_model_id_fkey" }).onDelete("set null"), ]); export const roles = pgTable("roles", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id"), name: text().notNull(), description: text(), permissions: jsonb().default({}).notNull(), isSystem: boolean("is_system").default(false).notNull(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_roles_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), uniqueIndex("idx_roles_restaurant_name").using("btree", table.restaurantId.asc().nullsLast().op("text_ops"), table.name.asc().nullsLast().op("text_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "roles_restaurant_id_fkey" }).onDelete("cascade"), ]); export const alertSettings = pgTable("alert_settings", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), soundEnabled: boolean("sound_enabled").default(true).notNull(), volume: integer().default(70).notNull(), autoDismiss: integer("auto_dismiss").default(30).notNull(), showOrders: boolean("show_orders").default(true).notNull(), showBookings: boolean("show_bookings").default(true).notNull(), showAiEscalations: boolean("show_ai_escalations").default(true).notNull(), browserNotifications: boolean("browser_notifications").default(false).notNull(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_alert_settings_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "alert_settings_restaurant_id_fkey" }).onDelete("cascade"), unique("alert_settings_restaurant_unique").on(table.restaurantId), ]); export const languages = pgTable("languages", { id: uuid().defaultRandom().primaryKey().notNull(), code: text().notNull(), name: text().notNull(), nativeName: text("native_name").notNull(), flagEmoji: text("flag_emoji").default('').notNull(), isRtl: boolean("is_rtl").default(false).notNull(), isActive: boolean("is_active").default(true).notNull(), isDefault: boolean("is_default").default(false).notNull(), displayOrder: integer("display_order").default(0).notNull(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_languages_code").using("btree", table.code.asc().nullsLast().op("text_ops")), index("idx_languages_is_active").using("btree", table.isActive.asc().nullsLast().op("bool_ops")), unique("languages_code_key").on(table.code), ]); export const translations = pgTable("translations", { id: uuid().defaultRandom().primaryKey().notNull(), langCode: text("lang_code").notNull(), keyId: uuid("key_id").notNull(), value: text().default('').notNull(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_translations_key_id").using("btree", table.keyId.asc().nullsLast().op("uuid_ops")), index("idx_translations_lang_code").using("btree", table.langCode.asc().nullsLast().op("text_ops")), foreignKey({ columns: [table.keyId], foreignColumns: [translationKeys.id], name: "translations_key_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.langCode], foreignColumns: [languages.code], name: "translations_lang_code_fkey" }).onDelete("cascade"), unique("translations_lang_code_key_id_key").on(table.langCode, table.keyId), ]); export const translationKeys = pgTable("translation_keys", { id: uuid().defaultRandom().primaryKey().notNull(), key: text().notNull(), category: text().default('common').notNull(), description: text(), defaultValue: text("default_value").default('').notNull(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_translation_keys_category").using("btree", table.category.asc().nullsLast().op("text_ops")), index("idx_translation_keys_key").using("btree", table.key.asc().nullsLast().op("text_ops")), unique("translation_keys_key_key").on(table.key), ]); export const agentKnowledgeBases = pgTable("agent_knowledge_bases", { agentId: uuid("agent_id").notNull(), kbId: uuid("kb_id").notNull(), }, (table) => [ foreignKey({ columns: [table.agentId], foreignColumns: [aiAgents.id], name: "agent_knowledge_bases_agent_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.kbId], foreignColumns: [knowledgeBase.id], name: "agent_knowledge_bases_kb_id_fkey" }).onDelete("cascade"), primaryKey({ columns: [table.agentId, table.kbId], name: "agent_knowledge_bases_pkey"}), ]); export const branchWhatsappCredentials = pgTable("branch_whatsapp_credentials", { branchId: uuid("branch_id").primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), phoneNumberId: text("phone_number_id").notNull(), wabaId: text("waba_id"), whatsappPhoneNumber: text("whatsapp_phone_number"), accessTokenEncrypted: text("access_token_encrypted").notNull(), appSecretEncrypted: text("app_secret_encrypted"), verifyToken: text("verify_token").notNull(), displayName: text("display_name"), isActive: boolean("is_active").default(true), unsignedGraceUntil: timestamp("unsigned_grace_until", { withTimezone: true, mode: 'string' }), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_bwc_restaurant_id").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "branch_whatsapp_credentials_branch_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "branch_whatsapp_credentials_restaurant_id_fkey" }).onDelete("cascade"), ]); export const whatsappMessageLog = pgTable("whatsapp_message_log", { id: uuid().defaultRandom().primaryKey().notNull(), branchId: uuid("branch_id").notNull(), restaurantId: uuid("restaurant_id").notNull(), conversationId: uuid("conversation_id"), waMessageId: text("wa_message_id"), direction: text().notNull(), fromE164: text("from_e164"), toE164: text("to_e164"), messageType: text("message_type").default('text'), body: text(), status: text().default('received'), error: text(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow(), }, (table) => [ index("idx_wml_branch_created").using("btree", table.branchId.asc().nullsLast().op("uuid_ops"), table.createdAt.desc().nullsLast().op("timestamptz_ops")), index("idx_wml_conversation").using("btree", table.conversationId.asc().nullsLast().op("uuid_ops")), uniqueIndex("uq_wml_branch_inbound_msgid").using("btree", table.branchId.asc().nullsLast().op("uuid_ops"), table.waMessageId.asc().nullsLast().op("text_ops")).where(sql`direction = 'inbound' AND wa_message_id IS NOT NULL`), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "whatsapp_message_log_branch_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "whatsapp_message_log_restaurant_id_fkey" }).onDelete("cascade"), ]); export const giftCardSettings = pgTable("gift_card_settings", { restaurantId: uuid("restaurant_id").primaryKey().notNull(), enabled: boolean().default(true).notNull(), denominations: jsonb().default([10, 25, 50, 100]).notNull(), customAmountEnabled: boolean("custom_amount_enabled").default(true).notNull(), customMin: numeric("custom_min", { precision: 10, scale: 2 }).default('5').notNull(), customMax: numeric("custom_max", { precision: 10, scale: 2 }).default('500').notNull(), currency: text(), defaultExpiryMonths: integer("default_expiry_months"), codeLength: integer("code_length").default(12).notNull(), codePrefix: text("code_prefix").default('GC').notNull(), brandLogoUrl: text("brand_logo_url"), brandPrimaryColor: text("brand_primary_color").default('#f97316').notNull(), messageTemplate: text("message_template").default('You have received a gift card!').notNull(), emailFromLine: text("email_from_line"), redemptionRule: text("redemption_rule").default('subtotal').notNull(), stackWithCoupons: boolean("stack_with_coupons").default(true).notNull(), stackWithLoyalty: boolean("stack_with_loyalty").default(true).notNull(), showFullCodeOnVoucher: boolean("show_full_code_on_voucher").default(true).notNull(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(), }, (table) => [ foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "gift_card_settings_restaurant_id_fkey" }).onDelete("cascade"), ]); export const giftCards = pgTable("gift_cards", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), codeHash: text("code_hash").notNull(), codeLast4: text("code_last4").notNull(), codePrefix: text("code_prefix").default('GC').notNull(), originalAmount: numeric("original_amount", { precision: 10, scale: 2 }).notNull(), balance: numeric({ precision: 10, scale: 2 }).notNull(), currency: text().notNull(), recipientName: text("recipient_name").notNull(), recipientEmail: text("recipient_email"), senderName: text("sender_name"), senderEmail: text("sender_email"), personalMessage: text("personal_message"), status: text().default('active').notNull(), issuedByUserId: uuid("issued_by_user_id"), issuedBranchId: uuid("issued_branch_id"), expiresAt: timestamp("expires_at", { withTimezone: true, mode: 'string' }), voidedAt: timestamp("voided_at", { withTimezone: true, mode: 'string' }), voidReason: text("void_reason"), lastUsedAt: timestamp("last_used_at", { withTimezone: true, mode: 'string' }), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(), }, (table) => [ uniqueIndex("idx_gift_cards_code_hash").using("btree", table.codeHash.asc().nullsLast().op("text_ops")), index("idx_gift_cards_restaurant").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops"), table.createdAt.desc().nullsLast().op("timestamptz_ops")), index("idx_gift_cards_status").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops"), table.status.asc().nullsLast().op("text_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "gift_cards_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.issuedBranchId], foreignColumns: [branches.id], name: "gift_cards_issued_branch_id_fkey" }).onDelete("set null"), ]); export const giftCardLedger = pgTable("gift_card_ledger", { id: uuid().defaultRandom().primaryKey().notNull(), giftCardId: uuid("gift_card_id").notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id"), orderId: uuid("order_id"), entryType: text("entry_type").notNull(), amount: numeric({ precision: 10, scale: 2 }).notNull(), balanceAfter: numeric("balance_after", { precision: 10, scale: 2 }).notNull(), note: text(), byUserId: uuid("by_user_id"), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(), }, (table) => [ index("idx_gift_card_ledger_card").using("btree", table.giftCardId.asc().nullsLast().op("uuid_ops"), table.createdAt.desc().nullsLast().op("timestamptz_ops")), index("idx_gift_card_ledger_restaurant").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops"), table.createdAt.desc().nullsLast().op("timestamptz_ops")), index("idx_gift_card_ledger_order").using("btree", table.orderId.asc().nullsLast().op("uuid_ops")), foreignKey({ columns: [table.giftCardId], foreignColumns: [giftCards.id], name: "gift_card_ledger_gift_card_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "gift_card_ledger_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "gift_card_ledger_branch_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.orderId], foreignColumns: [orders.id], name: "gift_card_ledger_order_id_fkey" }).onDelete("set null"), ]); export const coupons = pgTable("coupons", { id: uuid().defaultRandom().primaryKey().notNull(), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id").notNull(), code: text().notNull(), displayName: text("display_name"), description: text(), type: text().notNull(), value: numeric({ precision: 10, scale: 2 }).default('0').notNull(), bogoConfig: jsonb("bogo_config").default({}).notNull(), validFrom: timestamp("valid_from", { withTimezone: true, mode: 'string' }), validUntil: timestamp("valid_until", { withTimezone: true, mode: 'string' }), totalRedemptionCap: integer("total_redemption_cap"), perCustomerCap: integer("per_customer_cap"), minOrderValue: numeric("min_order_value", { precision: 10, scale: 2 }).default('0').notNull(), applicableOrderTypes: text("applicable_order_types").array().notNull(), applicableChannels: text("applicable_channels").array().notNull(), applicableBranches: uuid("applicable_branches").array().notNull(), stackWithLoyalty: boolean("stack_with_loyalty").default(true).notNull(), stackWithGiftCard: boolean("stack_with_gift_card").default(true).notNull(), status: text().default('active').notNull(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(), }, (table) => [ uniqueIndex("uniq_coupons_branch_code").using("btree", table.branchId.asc().nullsLast().op("uuid_ops"), sql`lower(code)`), index("idx_coupons_restaurant").using("btree", table.restaurantId.asc().nullsLast().op("uuid_ops")), index("idx_coupons_status").using("btree", table.status.asc().nullsLast().op("text_ops")), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "coupons_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "coupons_branch_id_fkey" }).onDelete("cascade"), ]); export const couponRedemptions = pgTable("coupon_redemptions", { id: uuid().defaultRandom().primaryKey().notNull(), couponId: uuid("coupon_id").notNull(), orderId: uuid("order_id"), restaurantId: uuid("restaurant_id").notNull(), branchId: uuid("branch_id").notNull(), customerId: uuid("customer_id"), channel: text().notNull(), discountAmount: numeric("discount_amount", { precision: 10, scale: 2 }).default('0').notNull(), createdAt: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(), }, (table) => [ index("idx_coupon_redemptions_coupon").using("btree", table.couponId.asc().nullsLast().op("uuid_ops"), table.createdAt.desc().nullsLast().op("timestamptz_ops")), index("idx_coupon_redemptions_branch").using("btree", table.branchId.asc().nullsLast().op("uuid_ops"), table.createdAt.desc().nullsLast().op("timestamptz_ops")), foreignKey({ columns: [table.couponId], foreignColumns: [coupons.id], name: "coupon_redemptions_coupon_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.orderId], foreignColumns: [orders.id], name: "coupon_redemptions_order_id_fkey" }).onDelete("set null"), foreignKey({ columns: [table.restaurantId], foreignColumns: [restaurants.id], name: "coupon_redemptions_restaurant_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.branchId], foreignColumns: [branches.id], name: "coupon_redemptions_branch_id_fkey" }).onDelete("cascade"), foreignKey({ columns: [table.customerId], foreignColumns: [customers.id], name: "coupon_redemptions_customer_id_fkey" }).onDelete("set null"), ]);