-- Phase 2 profile completion, gates, resident requests, announcements, visitor intelligence, and plan limits.

CREATE TYPE "VisitorRiskLevel" AS ENUM ('LOW', 'MEDIUM', 'HIGH');
CREATE TYPE "EstateGateType" AS ENUM ('MAIN', 'SERVICE', 'RESIDENT', 'EMERGENCY', 'DELIVERY', 'OTHER');
CREATE TYPE "AnnouncementCategory" AS ENUM ('SECURITY', 'MAINTENANCE', 'EVENTS', 'NOTICES', 'EMERGENCY', 'NEWS', 'SERVICE_CHARGE');
CREATE TYPE "AnnouncementPriority" AS ENUM ('NORMAL', 'IMPORTANT', 'EMERGENCY');
CREATE TYPE "AnnouncementStatus" AS ENUM ('DRAFT', 'PUBLISHED', 'ARCHIVED', 'BLOCKED');
CREATE TYPE "ResidentRequestCategory" AS ENUM ('SECURITY', 'INFRASTRUCTURE', 'ESTATE_SERVICES', 'SUPPORT');
CREATE TYPE "ResidentRequestStatus" AS ENUM ('OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED');
CREATE TYPE "ResidentRequestPriority" AS ENUM ('LOW', 'NORMAL', 'HIGH', 'URGENT');
CREATE TYPE "NotificationChannel" AS ENUM ('PUSH', 'IN_APP', 'EMAIL', 'SMS');
CREATE TYPE "NotificationStatus" AS ENUM ('PENDING', 'SENT', 'FAILED', 'READ');

ALTER TABLE "User"
  ADD COLUMN "firstName" TEXT,
  ADD COLUMN "lastName" TEXT,
  ADD COLUMN "email" TEXT,
  ADD COLUMN "profilePictureUrl" TEXT,
  ADD COLUMN "profilePictureThumbnailUrl" TEXT,
  ADD COLUMN "address" TEXT,
  ADD COLUMN "profileCompletion" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN "profileConfirmed" BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN "phoneVerifiedAt" TIMESTAMP(3),
  ADD COLUMN "emailVerifiedAt" TIMESTAMP(3),
  ADD COLUMN "metadata" JSONB NOT NULL DEFAULT '{}';

ALTER TABLE "Resident"
  ADD COLUMN "residentCode" TEXT,
  ADD COLUMN "firstName" TEXT,
  ADD COLUMN "lastName" TEXT,
  ADD COLUMN "profilePictureUrl" TEXT,
  ADD COLUMN "profilePictureThumbnailUrl" TEXT,
  ADD COLUMN "blockNumber" TEXT,
  ADD COLUMN "flatNumber" TEXT,
  ADD COLUMN "address" TEXT,
  ADD COLUMN "email" TEXT,
  ADD COLUMN "emergencyContact" JSONB NOT NULL DEFAULT '{}',
  ADD COLUMN "householdMembers" JSONB NOT NULL DEFAULT '[]',
  ADD COLUMN "metadata" JSONB NOT NULL DEFAULT '{}',
  ADD COLUMN "profileCompletion" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN "profileConfirmed" BOOLEAN NOT NULL DEFAULT false;

ALTER TABLE "Guard"
  ADD COLUMN "guardCode" TEXT,
  ADD COLUMN "firstName" TEXT,
  ADD COLUMN "lastName" TEXT,
  ADD COLUMN "profilePictureUrl" TEXT,
  ADD COLUMN "profilePictureThumbnailUrl" TEXT,
  ADD COLUMN "address" TEXT,
  ADD COLUMN "emergencyContact" JSONB NOT NULL DEFAULT '{}',
  ADD COLUMN "assignedGateId" TEXT,
  ADD COLUMN "assignedShifts" JSONB NOT NULL DEFAULT '[]',
  ADD COLUMN "metadata" JSONB NOT NULL DEFAULT '{}',
  ADD COLUMN "profileCompletion" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN "profileConfirmed" BOOLEAN NOT NULL DEFAULT false;

ALTER TABLE "Visitor"
  ADD COLUMN "riskScore" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN "riskLevel" "VisitorRiskLevel" NOT NULL DEFAULT 'LOW',
  ADD COLUMN "flagged" BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN "blacklisted" BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN "riskNotes" TEXT,
  ADD COLUMN "metadata" JSONB NOT NULL DEFAULT '{}';

ALTER TABLE "VisitSession"
  ADD COLUMN "gateId" TEXT,
  ADD COLUMN "visitorPhone" TEXT,
  ADD COLUMN "hostResidentId" TEXT,
  ADD COLUMN "scanLocation" JSONB NOT NULL DEFAULT '{}',
  ADD COLUMN "metadata" JSONB NOT NULL DEFAULT '{}';

ALTER TABLE "SubscriptionPlan"
  ADD COLUMN "oldMonthlyPriceKobo" INTEGER,
  ADD COLUMN "ctaLabel" TEXT,
  ADD COLUMN "sortOrder" INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN "trialDays" INTEGER NOT NULL DEFAULT 30,
  ADD COLUMN "gracePeriodDays" INTEGER NOT NULL DEFAULT 30,
  ADD COLUMN "publicVisible" BOOLEAN NOT NULL DEFAULT true,
  ADD COLUMN "residentMarketingEnabled" BOOLEAN NOT NULL DEFAULT true,
  ADD COLUMN "limits" JSONB NOT NULL DEFAULT '{}',
  ADD COLUMN "metadata" JSONB NOT NULL DEFAULT '{}';

CREATE TABLE "EstateGate" (
  "id" TEXT NOT NULL,
  "estateId" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  "type" "EstateGateType" NOT NULL DEFAULT 'MAIN',
  "description" TEXT,
  "isActive" BOOLEAN NOT NULL DEFAULT true,
  "metadata" JSONB NOT NULL DEFAULT '{}',
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "EstateGate_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "EstateAnnouncement" (
  "id" TEXT NOT NULL,
  "estateId" TEXT NOT NULL,
  "authorId" TEXT NOT NULL,
  "title" TEXT NOT NULL,
  "body" TEXT NOT NULL,
  "category" "AnnouncementCategory" NOT NULL,
  "priority" "AnnouncementPriority" NOT NULL DEFAULT 'NORMAL',
  "status" "AnnouncementStatus" NOT NULL DEFAULT 'PUBLISHED',
  "targetRoles" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  "targetUserIds" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  "deliveryChannels" "NotificationChannel"[] NOT NULL DEFAULT ARRAY['IN_APP','PUSH']::"NotificationChannel"[],
  "publishedAt" TIMESTAMP(3),
  "expiresAt" TIMESTAMP(3),
  "metadata" JSONB NOT NULL DEFAULT '{}',
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "EstateAnnouncement_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "ResidentRequest" (
  "id" TEXT NOT NULL,
  "requestCode" TEXT NOT NULL,
  "estateId" TEXT NOT NULL,
  "residentId" TEXT NOT NULL,
  "category" "ResidentRequestCategory" NOT NULL,
  "priority" "ResidentRequestPriority" NOT NULL DEFAULT 'NORMAL',
  "status" "ResidentRequestStatus" NOT NULL DEFAULT 'OPEN',
  "title" TEXT NOT NULL,
  "description" TEXT NOT NULL,
  "assignedToId" TEXT,
  "metadata" JSONB NOT NULL DEFAULT '{}',
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "resolvedAt" TIMESTAMP(3),
  "closedAt" TIMESTAMP(3),
  CONSTRAINT "ResidentRequest_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "ResidentRequestMessage" (
  "id" TEXT NOT NULL,
  "requestId" TEXT NOT NULL,
  "authorId" TEXT NOT NULL,
  "message" TEXT NOT NULL,
  "metadata" JSONB NOT NULL DEFAULT '{}',
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "ResidentRequestMessage_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "AppNotification" (
  "id" TEXT NOT NULL,
  "estateId" TEXT NOT NULL,
  "userId" TEXT,
  "announcementId" TEXT,
  "channel" "NotificationChannel" NOT NULL DEFAULT 'IN_APP',
  "status" "NotificationStatus" NOT NULL DEFAULT 'PENDING',
  "title" TEXT NOT NULL,
  "body" TEXT NOT NULL,
  "data" JSONB NOT NULL DEFAULT '{}',
  "readAt" TIMESTAMP(3),
  "sentAt" TIMESTAMP(3),
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "AppNotification_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "VisitorRiskEvent" (
  "id" TEXT NOT NULL,
  "visitorId" TEXT NOT NULL,
  "estateId" TEXT,
  "actorId" TEXT,
  "score" INTEGER NOT NULL,
  "level" "VisitorRiskLevel" NOT NULL,
  "reason" TEXT NOT NULL,
  "metadata" JSONB NOT NULL DEFAULT '{}',
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "VisitorRiskEvent_pkey" PRIMARY KEY ("id")
);

UPDATE "User"
SET
  "firstName" = NULLIF(split_part("name", ' ', 1), ''),
  "lastName" = NULLIF(trim(substr("name", length(split_part("name", ' ', 1)) + 1)), ''),
  "profileCompletion" = 100,
  "profileConfirmed" = true,
  "phoneVerifiedAt" = COALESCE("phoneVerifiedAt", "createdAt");

UPDATE "Resident"
SET
  "residentCode" = 'RES-' || upper(left(replace("id", '-', ''), 12)),
  "firstName" = NULLIF(split_part("name", ' ', 1), ''),
  "lastName" = NULLIF(trim(substr("name", length(split_part("name", ' ', 1)) + 1)), ''),
  "flatNumber" = "unit",
  "address" = COALESCE("unit", ''),
  "profileCompletion" = 100,
  "profileConfirmed" = true;

UPDATE "Guard"
SET
  "guardCode" = 'GRD-' || upper(left(replace("id", '-', ''), 12)),
  "firstName" = NULLIF(split_part("name", ' ', 1), ''),
  "lastName" = NULLIF(trim(substr("name", length(split_part("name", ' ', 1)) + 1)), ''),
  "address" = COALESCE("name", ''),
  "profileCompletion" = 100,
  "profileConfirmed" = true;

ALTER TABLE "Resident" ALTER COLUMN "residentCode" SET NOT NULL;
ALTER TABLE "Guard" ALTER COLUMN "guardCode" SET NOT NULL;

CREATE UNIQUE INDEX "Resident_residentCode_key" ON "Resident"("residentCode");
CREATE UNIQUE INDEX "Guard_guardCode_key" ON "Guard"("guardCode");
CREATE INDEX "User_email_idx" ON "User"("email");
CREATE INDEX "Resident_estateId_profileCompletion_idx" ON "Resident"("estateId", "profileCompletion");
CREATE INDEX "Guard_estateId_assignedGateId_idx" ON "Guard"("estateId", "assignedGateId");
CREATE INDEX "Guard_estateId_profileCompletion_idx" ON "Guard"("estateId", "profileCompletion");
CREATE INDEX "Visitor_estateId_riskLevel_idx" ON "Visitor"("estateId", "riskLevel");
CREATE INDEX "Visitor_estateId_blacklisted_idx" ON "Visitor"("estateId", "blacklisted");
CREATE INDEX "VisitSession_estateId_gateId_checkinTime_idx" ON "VisitSession"("estateId", "gateId", "checkinTime");
CREATE INDEX "VisitSession_estateId_hostResidentId_idx" ON "VisitSession"("estateId", "hostResidentId");
CREATE INDEX "VisitSession_estateId_visitorPhone_idx" ON "VisitSession"("estateId", "visitorPhone");

CREATE UNIQUE INDEX "EstateGate_estateId_name_key" ON "EstateGate"("estateId", "name");
CREATE INDEX "EstateGate_estateId_isActive_idx" ON "EstateGate"("estateId", "isActive");
CREATE INDEX "EstateAnnouncement_estateId_category_status_idx" ON "EstateAnnouncement"("estateId", "category", "status");
CREATE INDEX "EstateAnnouncement_estateId_publishedAt_idx" ON "EstateAnnouncement"("estateId", "publishedAt");
CREATE UNIQUE INDEX "ResidentRequest_requestCode_key" ON "ResidentRequest"("requestCode");
CREATE INDEX "ResidentRequest_estateId_status_category_idx" ON "ResidentRequest"("estateId", "status", "category");
CREATE INDEX "ResidentRequest_residentId_createdAt_idx" ON "ResidentRequest"("residentId", "createdAt");
CREATE INDEX "ResidentRequestMessage_requestId_createdAt_idx" ON "ResidentRequestMessage"("requestId", "createdAt");
CREATE INDEX "AppNotification_estateId_userId_createdAt_idx" ON "AppNotification"("estateId", "userId", "createdAt");
CREATE INDEX "AppNotification_estateId_status_idx" ON "AppNotification"("estateId", "status");
CREATE INDEX "VisitorRiskEvent_visitorId_createdAt_idx" ON "VisitorRiskEvent"("visitorId", "createdAt");
CREATE INDEX "VisitorRiskEvent_estateId_level_idx" ON "VisitorRiskEvent"("estateId", "level");
CREATE INDEX "SubscriptionPlan_publicVisible_sortOrder_idx" ON "SubscriptionPlan"("publicVisible", "sortOrder");

ALTER TABLE "EstateGate" ADD CONSTRAINT "EstateGate_estateId_fkey"
FOREIGN KEY ("estateId") REFERENCES "Estate"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Guard" ADD CONSTRAINT "Guard_assignedGateId_fkey"
FOREIGN KEY ("assignedGateId") REFERENCES "EstateGate"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "VisitSession" ADD CONSTRAINT "VisitSession_gateId_fkey"
FOREIGN KEY ("gateId") REFERENCES "EstateGate"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "EstateAnnouncement" ADD CONSTRAINT "EstateAnnouncement_estateId_fkey"
FOREIGN KEY ("estateId") REFERENCES "Estate"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "EstateAnnouncement" ADD CONSTRAINT "EstateAnnouncement_authorId_fkey"
FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE "ResidentRequest" ADD CONSTRAINT "ResidentRequest_estateId_fkey"
FOREIGN KEY ("estateId") REFERENCES "Estate"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "ResidentRequest" ADD CONSTRAINT "ResidentRequest_residentId_fkey"
FOREIGN KEY ("residentId") REFERENCES "Resident"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "ResidentRequestMessage" ADD CONSTRAINT "ResidentRequestMessage_requestId_fkey"
FOREIGN KEY ("requestId") REFERENCES "ResidentRequest"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "ResidentRequestMessage" ADD CONSTRAINT "ResidentRequestMessage_authorId_fkey"
FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE "AppNotification" ADD CONSTRAINT "AppNotification_estateId_fkey"
FOREIGN KEY ("estateId") REFERENCES "Estate"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "AppNotification" ADD CONSTRAINT "AppNotification_userId_fkey"
FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "AppNotification" ADD CONSTRAINT "AppNotification_announcementId_fkey"
FOREIGN KEY ("announcementId") REFERENCES "EstateAnnouncement"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "VisitorRiskEvent" ADD CONSTRAINT "VisitorRiskEvent_visitorId_fkey"
FOREIGN KEY ("visitorId") REFERENCES "Visitor"("id") ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO "EstateGate" ("id", "estateId", "name", "type", "description", "metadata")
SELECT 'gate-' || left(replace("id", '-', ''), 24), "id", 'Main Gate', 'MAIN', 'Default main gate', '{"seeded":true}'::jsonb
FROM "Estate"
ON CONFLICT ("estateId", "name") DO NOTHING;

UPDATE "Guard"
SET "assignedGateId" = gate_source."id"
FROM "EstateGate" gate_source
WHERE gate_source."estateId" = "Guard"."estateId"
  AND gate_source."name" = 'Main Gate'
  AND "Guard"."assignedGateId" IS NULL;

UPDATE "VisitSession" session
SET
  "gateId" = gate_source."id",
  "visitorPhone" = visitor_source."phone",
  "hostResidentId" = pass_source."hostResidentId"
FROM "VisitorPass" pass_source
JOIN "Visitor" visitor_source ON visitor_source."id" = pass_source."visitorId"
LEFT JOIN "EstateGate" gate_source ON gate_source."estateId" = session."estateId"
  AND gate_source."name" = COALESCE(session."entryGate", 'Main Gate')
WHERE session."visitorPassId" = pass_source."id";

UPDATE "SubscriptionPlan"
SET
  "code" = CASE WHEN "code" = 'GROWTH' THEN 'SECURE' ELSE "code" END,
  "name" = CASE
    WHEN "code" = 'STARTER' THEN 'GateWise Starter'
    WHEN "code" = 'GROWTH' THEN 'GateWise Secure'
    WHEN "code" = 'ENTERPRISE' THEN 'Enterprise'
    ELSE "name"
  END,
  "minResidents" = CASE WHEN "code" = 'ENTERPRISE' THEN 5001 ELSE "minResidents" END,
  "maxResidents" = CASE
    WHEN "code" = 'STARTER' THEN 100
    WHEN "code" = 'GROWTH' THEN 500
    WHEN "code" = 'ENTERPRISE' THEN NULL
    ELSE "maxResidents"
  END,
  "monthlyPriceKobo" = CASE
    WHEN "code" = 'STARTER' THEN 20000000
    WHEN "code" = 'GROWTH' THEN 50000000
    WHEN "code" = 'ENTERPRISE' THEN NULL
    ELSE "monthlyPriceKobo"
  END,
  "customPricing" = CASE WHEN "code" = 'ENTERPRISE' THEN true ELSE false END,
  "publicVisible" = CASE WHEN "code" = 'ENTERPRISE' THEN false ELSE true END,
  "sortOrder" = CASE
    WHEN "code" = 'STARTER' THEN 10
    WHEN "code" = 'GROWTH' THEN 20
    WHEN "code" = 'ENTERPRISE' THEN 50
    ELSE "sortOrder"
  END,
  "ctaLabel" = CASE
    WHEN "code" = 'STARTER' THEN 'Start with GateWise'
    WHEN "code" = 'GROWTH' THEN 'Upgrade to Secure'
    WHEN "code" = 'ENTERPRISE' THEN 'Contact us'
    ELSE "ctaLabel"
  END,
  "description" = CASE
    WHEN "code" = 'STARTER' THEN 'Small estates, streets, and communities trying GateWise NG.'
    WHEN "code" = 'GROWTH' THEN 'Mid-sized estates and gated communities.'
    WHEN "code" = 'ENTERPRISE' THEN 'Future enterprise plan for super-luxury estates, security companies, and large operators.'
    ELSE "description"
  END,
  "limits" = CASE
    WHEN "code" = 'STARTER' THEN '{"gates":1,"estateAdmins":1,"supervisors":2,"guards":4,"residents":100,"visitorPassesPerMonth":400,"announcementsPerMonth":1,"entryExitNotificationsPerResidentPerWeek":2,"sosAlertsPerMonth":0,"storageMb":512}'::jsonb
    WHEN "code" = 'GROWTH' THEN '{"gates":3,"estateAdmins":3,"supervisors":5,"guards":10,"residents":500,"visitorPassesPerMonth":800,"announcementsPerWeek":2,"entryExitNotificationsPerResidentPerWeek":5,"sosAlertsPerMonth":1,"storageMb":2048}'::jsonb
    WHEN "code" = 'ENTERPRISE' THEN '{"gates":null,"estateAdmins":null,"supervisors":null,"guards":null,"residents":null,"visitorPassesPerMonth":null,"announcementsPerWeek":null,"entryExitNotificationsPerResidentPerWeek":null,"sosAlertsPerMonth":null,"storageMb":null}'::jsonb
    ELSE "limits"
  END,
  "metadata" = CASE
    WHEN "code" = 'STARTER' THEN '{"recommendedFor":"Small estates, streets, and communities trying GateWise NG","residentMarketing":true}'::jsonb
    WHEN "code" = 'GROWTH' THEN '{"recommendedFor":"Mid-sized estates and gated communities","residentMarketing":true}'::jsonb
    WHEN "code" = 'ENTERPRISE' THEN '{"recommendedFor":"Super-luxury estates, security companies, and large real estate operators","futurePlan":true,"residentMarketing":false}'::jsonb
    ELSE "metadata"
  END;

INSERT INTO "SubscriptionPlan" (
  "id", "code", "name", "minResidents", "maxResidents", "monthlyPriceKobo", "currency", "customPricing",
  "description", "ctaLabel", "sortOrder", "trialDays", "gracePeriodDays", "publicVisible", "residentMarketingEnabled",
  "limits", "metadata", "isActive", "updatedAt"
) VALUES
  (
    '00000000-0000-4000-8000-000000000104',
    'LUXURY',
    'GateWise Luxury',
    501,
    2000,
    200000000,
    'NGN',
    false,
    'Large estates and mini smart cities.',
    'Upgrade to Luxury',
    30,
    30,
    30,
    true,
    true,
    '{"gates":5,"estateAdmins":5,"supervisors":5,"guards":25,"residents":2000,"visitorPassesPerMonth":null,"announcementsPerWeek":5,"entryExitNotificationsPerResidentPerWeek":10,"sosAlertsPerMonth":5,"storageMb":10240}'::jsonb,
    '{"recommendedFor":"Large estates and mini smart cities","recommended":true,"residentMarketing":true}'::jsonb,
    true,
    CURRENT_TIMESTAMP
  ),
  (
    '00000000-0000-4000-8000-000000000105',
    'BUSINESS',
    'GateWise Business',
    2001,
    5000,
    500000000,
    'NGN',
    false,
    'Large estates, estate managers, owners, and mini smart cities.',
    'Talk to GateWise',
    40,
    30,
    30,
    true,
    true,
    '{"gates":10,"managedEstates":5,"estateAdmins":10,"supervisors":20,"guards":100,"residents":5000,"visitorPassesPerMonth":null,"announcementsPerWeek":50,"entryExitNotificationsPerResidentPerWeek":15,"sosAlertsPerMonth":10,"storageMb":51200}'::jsonb,
    '{"recommendedFor":"Large estates, estate managers, owners, and mini smart cities","residentMarketing":false}'::jsonb,
    true,
    CURRENT_TIMESTAMP
  )
ON CONFLICT ("code") DO NOTHING;

DELETE FROM "SubscriptionPlanFeature";

INSERT INTO "SubscriptionPlanFeature" ("id", "planId", "feature", "createdAt", "updatedAt")
SELECT
  'plan-feature-' || lower(plan_source."code") || '-' || lower(replace(feature_source."feature", '_', '-')),
  plan_source."id",
  feature_source."feature",
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
FROM "SubscriptionPlan" plan_source
JOIN (
  VALUES
    ('STARTER', 'PROFILE_VIEW'), ('STARTER', 'PROFILE_MANAGE'), ('STARTER', 'PLAN_COMPARISON_VIEW'),
    ('STARTER', 'RESIDENTS_VIEW'), ('STARTER', 'GUARDS_VIEW'), ('STARTER', 'VISITORS_VIEW'), ('STARTER', 'VISITORS_CREATE'),
    ('STARTER', 'VISITOR_PASSES_VIEW'), ('STARTER', 'VISITOR_PASSES_CREATE'), ('STARTER', 'CHECKIN_SCAN'), ('STARTER', 'CHECKOUT'),
    ('STARTER', 'VISIT_SESSIONS_VIEW'), ('STARTER', 'INCIDENTS_VIEW'), ('STARTER', 'REQUESTS_CREATE'), ('STARTER', 'REQUESTS_VIEW'),
    ('STARTER', 'REQUESTS_RESPOND'), ('STARTER', 'ANNOUNCEMENTS_VIEW'), ('STARTER', 'ANNOUNCEMENTS_SEND'), ('STARTER', 'NOTIFICATIONS_SEND'),
    ('STARTER', 'GATES_VIEW'), ('STARTER', 'GATES_MANAGE'),
    ('SECURE', 'PROFILE_VIEW'), ('SECURE', 'PROFILE_MANAGE'), ('SECURE', 'PLAN_COMPARISON_VIEW'), ('SECURE', 'RESIDENTS_VIEW'),
    ('SECURE', 'RESIDENTS_MANAGE'), ('SECURE', 'GUARDS_VIEW'), ('SECURE', 'GUARDS_MANAGE'), ('SECURE', 'VISITORS_VIEW'),
    ('SECURE', 'VISITORS_CREATE'), ('SECURE', 'VISITORS_MANAGE'), ('SECURE', 'VISITOR_PASSES_VIEW'), ('SECURE', 'VISITOR_PASSES_CREATE'),
    ('SECURE', 'VISITOR_PASSES_REVOKE'), ('SECURE', 'CHECKIN_SCAN'), ('SECURE', 'CHECKIN_MANUAL'), ('SECURE', 'CHECKOUT'),
    ('SECURE', 'VISIT_SESSIONS_VIEW'), ('SECURE', 'INCIDENTS_VIEW'), ('SECURE', 'INCIDENTS_MANAGE'), ('SECURE', 'NOTIFICATIONS_SEND'),
    ('SECURE', 'REQUESTS_CREATE'), ('SECURE', 'REQUESTS_VIEW'), ('SECURE', 'REQUESTS_RESPOND'), ('SECURE', 'REQUESTS_MANAGE'),
    ('SECURE', 'ANNOUNCEMENTS_VIEW'), ('SECURE', 'ANNOUNCEMENTS_SEND'), ('SECURE', 'ANNOUNCEMENTS_EMERGENCY_SEND'),
    ('SECURE', 'GATES_VIEW'), ('SECURE', 'GATES_MANAGE'), ('SECURE', 'GUARD_SHIFT_MANAGE'), ('SECURE', 'SERVICE_CHARGE_PAYMENTS'),
    ('SECURE', 'ESTATE_NEWS_VIEW'), ('SECURE', 'EXPORT_REPORTS'), ('SECURE', 'VISITOR_INTELLIGENCE_VIEW'),
    ('LUXURY', 'PROFILE_VIEW'), ('LUXURY', 'PROFILE_MANAGE'), ('LUXURY', 'PLAN_COMPARISON_VIEW'), ('LUXURY', 'RESIDENTS_VIEW'),
    ('LUXURY', 'RESIDENTS_MANAGE'), ('LUXURY', 'GUARDS_VIEW'), ('LUXURY', 'GUARDS_MANAGE'), ('LUXURY', 'VISITORS_VIEW'),
    ('LUXURY', 'VISITORS_CREATE'), ('LUXURY', 'VISITORS_MANAGE'), ('LUXURY', 'VISITOR_PASSES_VIEW'), ('LUXURY', 'VISITOR_PASSES_CREATE'),
    ('LUXURY', 'VISITOR_PASSES_REVOKE'), ('LUXURY', 'CHECKIN_SCAN'), ('LUXURY', 'CHECKIN_MANUAL'), ('LUXURY', 'CHECKOUT'),
    ('LUXURY', 'VISIT_SESSIONS_VIEW'), ('LUXURY', 'INCIDENTS_VIEW'), ('LUXURY', 'INCIDENTS_MANAGE'), ('LUXURY', 'OFFLINE_BOOTSTRAP'),
    ('LUXURY', 'OFFLINE_SYNC'), ('LUXURY', 'NOTIFICATIONS_SEND'), ('LUXURY', 'REQUESTS_CREATE'), ('LUXURY', 'REQUESTS_VIEW'),
    ('LUXURY', 'REQUESTS_RESPOND'), ('LUXURY', 'REQUESTS_MANAGE'), ('LUXURY', 'ANNOUNCEMENTS_VIEW'), ('LUXURY', 'ANNOUNCEMENTS_SEND'),
    ('LUXURY', 'ANNOUNCEMENTS_EMERGENCY_SEND'), ('LUXURY', 'GATES_VIEW'), ('LUXURY', 'GATES_MANAGE'), ('LUXURY', 'GUARD_SHIFT_MANAGE'),
    ('LUXURY', 'SERVICE_CHARGE_PAYMENTS'), ('LUXURY', 'ESTATE_NEWS_VIEW'), ('LUXURY', 'EXPORT_REPORTS'), ('LUXURY', 'IMPORT_RESIDENTS'),
    ('LUXURY', 'VISITOR_INTELLIGENCE_VIEW'), ('LUXURY', 'VISITOR_INTELLIGENCE_MANAGE'), ('LUXURY', 'VISITOR_RISK_SCORING'), ('LUXURY', 'ANALYTICS_VIEW'),
    ('BUSINESS', 'PROFILE_VIEW'), ('BUSINESS', 'PROFILE_MANAGE'), ('BUSINESS', 'RESIDENTS_VIEW'), ('BUSINESS', 'RESIDENTS_MANAGE'),
    ('BUSINESS', 'GUARDS_VIEW'), ('BUSINESS', 'GUARDS_MANAGE'), ('BUSINESS', 'VISITORS_VIEW'), ('BUSINESS', 'VISITORS_CREATE'),
    ('BUSINESS', 'VISITORS_MANAGE'), ('BUSINESS', 'VISITOR_PASSES_VIEW'), ('BUSINESS', 'VISITOR_PASSES_CREATE'), ('BUSINESS', 'VISITOR_PASSES_REVOKE'),
    ('BUSINESS', 'CHECKIN_SCAN'), ('BUSINESS', 'CHECKIN_MANUAL'), ('BUSINESS', 'CHECKOUT'), ('BUSINESS', 'VISIT_SESSIONS_VIEW'),
    ('BUSINESS', 'INCIDENTS_VIEW'), ('BUSINESS', 'INCIDENTS_MANAGE'), ('BUSINESS', 'OFFLINE_BOOTSTRAP'), ('BUSINESS', 'OFFLINE_SYNC'),
    ('BUSINESS', 'NOTIFICATIONS_SEND'), ('BUSINESS', 'REQUESTS_CREATE'), ('BUSINESS', 'REQUESTS_VIEW'), ('BUSINESS', 'REQUESTS_RESPOND'),
    ('BUSINESS', 'REQUESTS_MANAGE'), ('BUSINESS', 'ANNOUNCEMENTS_VIEW'), ('BUSINESS', 'ANNOUNCEMENTS_SEND'), ('BUSINESS', 'ANNOUNCEMENTS_EMERGENCY_SEND'),
    ('BUSINESS', 'GATES_VIEW'), ('BUSINESS', 'GATES_MANAGE'), ('BUSINESS', 'GUARD_SHIFT_MANAGE'), ('BUSINESS', 'SERVICE_CHARGE_PAYMENTS'),
    ('BUSINESS', 'ESTATE_NEWS_VIEW'), ('BUSINESS', 'ESTATE_NEWS_MANAGE'), ('BUSINESS', 'EXPORT_REPORTS'), ('BUSINESS', 'IMPORT_RESIDENTS'),
    ('BUSINESS', 'VISITOR_INTELLIGENCE_VIEW'), ('BUSINESS', 'VISITOR_INTELLIGENCE_MANAGE'), ('BUSINESS', 'VISITOR_RISK_SCORING'), ('BUSINESS', 'ANALYTICS_VIEW'),
    ('ENTERPRISE', 'PROFILE_VIEW'), ('ENTERPRISE', 'PROFILE_MANAGE'), ('ENTERPRISE', 'PLAN_COMPARISON_VIEW'), ('ENTERPRISE', 'RESIDENTS_VIEW'),
    ('ENTERPRISE', 'RESIDENTS_MANAGE'), ('ENTERPRISE', 'GUARDS_VIEW'), ('ENTERPRISE', 'GUARDS_MANAGE'), ('ENTERPRISE', 'VISITORS_VIEW'),
    ('ENTERPRISE', 'VISITORS_CREATE'), ('ENTERPRISE', 'VISITORS_MANAGE'), ('ENTERPRISE', 'VISITOR_PASSES_VIEW'), ('ENTERPRISE', 'VISITOR_PASSES_CREATE'),
    ('ENTERPRISE', 'VISITOR_PASSES_REVOKE'), ('ENTERPRISE', 'CHECKIN_SCAN'), ('ENTERPRISE', 'CHECKIN_MANUAL'), ('ENTERPRISE', 'CHECKOUT'),
    ('ENTERPRISE', 'VISIT_SESSIONS_VIEW'), ('ENTERPRISE', 'INCIDENTS_VIEW'), ('ENTERPRISE', 'INCIDENTS_MANAGE'), ('ENTERPRISE', 'OFFLINE_BOOTSTRAP'),
    ('ENTERPRISE', 'OFFLINE_SYNC'), ('ENTERPRISE', 'NOTIFICATIONS_SEND'), ('ENTERPRISE', 'REQUESTS_CREATE'), ('ENTERPRISE', 'REQUESTS_VIEW'),
    ('ENTERPRISE', 'REQUESTS_RESPOND'), ('ENTERPRISE', 'REQUESTS_MANAGE'), ('ENTERPRISE', 'ANNOUNCEMENTS_VIEW'), ('ENTERPRISE', 'ANNOUNCEMENTS_SEND'),
    ('ENTERPRISE', 'ANNOUNCEMENTS_EMERGENCY_SEND'), ('ENTERPRISE', 'GATES_VIEW'), ('ENTERPRISE', 'GATES_MANAGE'), ('ENTERPRISE', 'GUARD_SHIFT_MANAGE'),
    ('ENTERPRISE', 'SERVICE_CHARGE_PAYMENTS'), ('ENTERPRISE', 'ESTATE_NEWS_VIEW'), ('ENTERPRISE', 'ESTATE_NEWS_MANAGE'), ('ENTERPRISE', 'EXPORT_REPORTS'),
    ('ENTERPRISE', 'IMPORT_RESIDENTS'), ('ENTERPRISE', 'VISITOR_INTELLIGENCE_VIEW'), ('ENTERPRISE', 'VISITOR_INTELLIGENCE_MANAGE'),
    ('ENTERPRISE', 'VISITOR_RISK_SCORING'), ('ENTERPRISE', 'ANALYTICS_VIEW'), ('ENTERPRISE', 'WHITE_LABEL_BRANDING'), ('ENTERPRISE', 'API_ACCESS'),
    ('ENTERPRISE', 'SMART_GATE_INTEGRATIONS')
) AS feature_source("code", "feature") ON feature_source."code" = plan_source."code"
ON CONFLICT ("planId", "feature") DO NOTHING;
