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"),
]);
