-- CreateTable
CREATE TABLE "marketing_spend" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "tenant_id" UUID NOT NULL,
    "branch_id" UUID,
    "source_id" UUID NOT NULL,
    "period" DATE NOT NULL,
    "amount" DECIMAL(14,2) NOT NULL,
    "note" TEXT,
    "created_by" UUID,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted_at" TIMESTAMPTZ(6),

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

-- CreateIndex
CREATE INDEX "idx_marketing_spend_tenant_period" ON "marketing_spend"("tenant_id", "period");

-- AddForeignKey
ALTER TABLE "marketing_spend" ADD CONSTRAINT "marketing_spend_tenant_id_fkey" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "marketing_spend" ADD CONSTRAINT "marketing_spend_branch_id_fkey" FOREIGN KEY ("branch_id") REFERENCES "branches"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "marketing_spend" ADD CONSTRAINT "marketing_spend_source_id_fkey" FOREIGN KEY ("source_id") REFERENCES "lead_sources"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "marketing_spend" ADD CONSTRAINT "marketing_spend_created_by_fkey" FOREIGN KEY ("created_by") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- ── Row-Level Security ──────────────────────────────────────────────────────
-- Every other domain table carries this (docs/03 § F1). Prisma does not
-- generate it, so a new table is outside the tenant boundary until it is
-- added by hand — which is exactly the kind of gap `pnpm db:drift` exists to
-- catch. The service layer still filters by tenant_id as well.
ALTER TABLE "marketing_spend" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "marketing_spend" FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON "marketing_spend"
  USING (tenant_id = current_tenant_id())
  WITH CHECK (tenant_id = current_tenant_id());

-- ── Runtime role grant ──────────────────────────────────────────────────────
-- ALTER DEFAULT PRIVILEGES covers tables created *after* it was set, so this is
-- usually redundant. Stated anyway: a database restored from a dump taken
-- before those defaults existed would otherwise have a table educore_app
-- cannot read, and the failure would look like an empty report.
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'educore_app') THEN
    EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON "marketing_spend" TO educore_app';
  END IF;
END $$;