-- Partner sales-admin controls and subscription plan feature ownership.

ALTER TABLE "Estate" ADD COLUMN "salesPartnerId" TEXT;

CREATE TABLE "SubscriptionPlanFeature" (
    "id" TEXT NOT NULL,
    "planId" TEXT NOT NULL,
    "feature" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "SubscriptionPlanFeature_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "SalesPartnerProfile" (
    "id" TEXT NOT NULL,
    "salesAdminId" TEXT NOT NULL,
    "enabled" BOOLEAN NOT NULL DEFAULT false,
    "commissionPercent" DECIMAL(5,2),
    "notes" TEXT,
    "updatedById" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "SalesPartnerProfile_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "SubscriptionPlanFeature_planId_feature_key" ON "SubscriptionPlanFeature"("planId", "feature");
CREATE INDEX "SubscriptionPlanFeature_feature_idx" ON "SubscriptionPlanFeature"("feature");
CREATE UNIQUE INDEX "SalesPartnerProfile_salesAdminId_key" ON "SalesPartnerProfile"("salesAdminId");
CREATE INDEX "SalesPartnerProfile_enabled_idx" ON "SalesPartnerProfile"("enabled");
CREATE INDEX "Estate_salesPartnerId_idx" ON "Estate"("salesPartnerId");
CREATE INDEX "User_role_status_idx" ON "User"("role", "status");

ALTER TABLE "SubscriptionPlanFeature" ADD CONSTRAINT "SubscriptionPlanFeature_planId_fkey"
FOREIGN KEY ("planId") REFERENCES "SubscriptionPlan"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "SalesPartnerProfile" ADD CONSTRAINT "SalesPartnerProfile_salesAdminId_fkey"
FOREIGN KEY ("salesAdminId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Estate" ADD CONSTRAINT "Estate_salesPartnerId_fkey"
FOREIGN KEY ("salesPartnerId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;

INSERT INTO "SalesPartnerProfile" ("id", "salesAdminId", "enabled", "commissionPercent", "notes", "updatedAt")
SELECT 'sales-partner-profile-' || "id", "id", false, NULL, 'Partner earnings disabled by default.', CURRENT_TIMESTAMP
FROM "User"
WHERE "role" = 'SALES_ADMIN'
ON CONFLICT ("salesAdminId") DO NOTHING;

UPDATE "Estate"
SET "subscriptionPlanId" = CASE
    WHEN "unitCount" <= 100 THEN '00000000-0000-4000-8000-000000000101'
    WHEN "unitCount" <= 300 THEN '00000000-0000-4000-8000-000000000102'
    ELSE '00000000-0000-4000-8000-000000000103'
END
WHERE "subscriptionPlanId" IS NULL;

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', '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'),
      ('GROWTH', 'RESIDENTS_VIEW'),
      ('GROWTH', 'RESIDENTS_MANAGE'),
      ('GROWTH', 'GUARDS_VIEW'),
      ('GROWTH', 'GUARDS_MANAGE'),
      ('GROWTH', 'VISITORS_VIEW'),
      ('GROWTH', 'VISITORS_CREATE'),
      ('GROWTH', 'VISITORS_MANAGE'),
      ('GROWTH', 'VISITOR_PASSES_VIEW'),
      ('GROWTH', 'VISITOR_PASSES_CREATE'),
      ('GROWTH', 'VISITOR_PASSES_REVOKE'),
      ('GROWTH', 'CHECKIN_SCAN'),
      ('GROWTH', 'CHECKIN_MANUAL'),
      ('GROWTH', 'CHECKOUT'),
      ('GROWTH', 'VISIT_SESSIONS_VIEW'),
      ('GROWTH', 'INCIDENTS_VIEW'),
      ('GROWTH', 'INCIDENTS_MANAGE'),
      ('GROWTH', 'NOTIFICATIONS_SEND'),
      ('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')
) AS feature_source("code", "feature") ON feature_source."code" = plan_source."code"
ON CONFLICT ("planId", "feature") DO NOTHING;
