-- Performance optimization indexes for Polartek API
-- Run this SQL to add additional performance-critical indexes

-- Composite indexes for common query patterns

-- Customer analytics queries (date + status combinations)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reservation_date_status ON "Reservation" ("date", "status");
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reservation_customer_date ON "Reservation" ("customerId", "date") WHERE "customerId" IS NOT NULL;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reservation_service_date ON "Reservation" ("serviceId", "date") WHERE "serviceId" IS NOT NULL;

-- Revenue analytics (date + amount combinations)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reservation_date_amount ON "Reservation" ("date", "totalAmount") WHERE "totalAmount" IS NOT NULL;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reservation_utc_amount ON "Reservation" ("utcDateTime", "totalAmount") WHERE "utcDateTime" IS NOT NULL AND "totalAmount" IS NOT NULL;

-- Customer search and filtering
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_customer_marketing_language ON "Customer" ("marketingConsent", "preferredLanguage");
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_customer_name_gin ON "Customer" USING gin(to_tsvector('english', "name"));
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_customer_email_domain ON "Customer" (substring("email" from '@(.*)$'));

-- Contact message search
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contact_message_search ON "ContactMessage" USING gin(to_tsvector('english', "name" || ' ' || "message"));
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contact_read_created ON "ContactMessage" ("isRead", "createdAt");

-- Service performance queries
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_service_active_type ON "Service" ("isActive", "serviceType") WHERE "isActive" = true;

-- Package analytics
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_package_purchase_active_date ON "PackagePurchase" ("isActive", "purchaseDate");
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_package_purchase_expires ON "PackagePurchase" ("expiresAt") WHERE "expiresAt" IS NOT NULL;

-- Loyalty system queries
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_loyalty_tier_activity ON "CustomerLoyalty" ("tier", "lastActivityDate");
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_loyalty_points_range ON "CustomerLoyalty" ("points") WHERE "points" > 0;

-- Audit log performance
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_entity_date ON "AuditLog" ("entityType", "createdAt");
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_audit_email_action ON "AuditLog" ("email", "action");

-- Rate limiting performance
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_rate_limit_cleanup ON "RateLimit" ("window") WHERE "window" < NOW() - INTERVAL '1 hour';

-- Newsletter and marketing
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_newsletter_active_email ON "NewsletterSubscription" ("isActive", "email") WHERE "isActive" = true;

-- Campaign performance
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_campaign_scheduled_active ON "MarketingCampaign" ("scheduledAt", "isActive") WHERE "isActive" = true;

-- Recurring booking patterns
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_recurring_next_active ON "RecurringBooking" ("nextBookingDate", "isActive") WHERE "isActive" = true;

-- Waiting list optimization
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_waiting_list_date_priority ON "WaitingList" ("preferredDate", "priority", "isActive") WHERE "isActive" = true;

-- Service package usage
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_package_usage_remaining ON "PackagePurchase" ("remainingSessions") WHERE "remainingSessions" > 0;

-- GDPR compliance queries
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_consent_retention_date ON "ConsentLog" ("retentionDate") WHERE "retentionDate" IS NOT NULL;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_deletion_pending ON "DeletionRequest" ("requestedAt") WHERE "status" = 'PENDING';

-- Search optimization for names and emails (case-insensitive)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_customer_name_lower ON "Customer" (LOWER("name"));
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_customer_email_lower ON "Customer" (LOWER("email"));
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_contact_name_lower ON "ContactMessage" (LOWER("name"));

-- Partial indexes for active records (better performance for filtered queries)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_service_active_only ON "Service" ("id") WHERE "isActive" = true;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_packages_active_only ON "ServicePackage" ("id") WHERE "isActive" = true;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reservations_pending ON "Reservation" ("date", "time") WHERE "status" = 'PENDING';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reservations_confirmed ON "Reservation" ("date", "time") WHERE "status" = 'CONFIRMED';

-- Time-series analytics optimization
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_reservation_month_year ON "Reservation" (EXTRACT(YEAR FROM "createdAt"), EXTRACT(MONTH FROM "createdAt"));
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_package_purchase_month ON "PackagePurchase" (EXTRACT(YEAR FROM "purchaseDate"), EXTRACT(MONTH FROM "purchaseDate"));

-- Foreign key performance (if not automatically indexed by Prisma)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_loyalty_transaction_loyalty_id ON "LoyaltyTransaction" ("loyaltyId");
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_loyalty_offer_loyalty_id ON "LoyaltyOffer" ("loyaltyId");

-- Comments for maintenance
COMMENT ON INDEX idx_reservation_date_status IS 'Optimizes analytics queries filtering by date and status';
COMMENT ON INDEX idx_customer_marketing_language IS 'Optimizes customer segmentation queries';
COMMENT ON INDEX idx_contact_message_search IS 'Enables full-text search on contact messages';
COMMENT ON INDEX idx_loyalty_tier_activity IS 'Optimizes loyalty analytics and tier calculations';

-- Update table statistics for better query planning
ANALYZE "Customer";
ANALYZE "Reservation";
ANALYZE "Service";
ANALYZE "ServicePackage";
ANALYZE "PackagePurchase";
ANALYZE "CustomerLoyalty";
ANALYZE "ContactMessage";
ANALYZE "AuditLog";