-- ============================================================================
-- Everything that Prisma cannot express, hand-written and kept in migration
-- history so `migrate reset` / `migrate deploy` reproduce it exactly.
--
--   1. ledger amount CHECK          docs/03 § B1
--   2. partial unique indexes       docs/03 § C1, D1
--   3. schedule conflict guards     docs/03 § D2
--   4. student_balances view        docs/03 § B1
--   5. Row-Level Security           docs/03 § F1
--   6. grants for the runtime role
-- ============================================================================

-- ── 1. Ledger amount sign ───────────────────────────────────────────────────
-- charge / payment / refund are always positive; the transaction type carries
-- the sign. Only `adjustment` may be negative (a manual correction that
-- increases a debt). See docs/03-biznes-qoidalar.md § B1.
ALTER TABLE "transactions"
  ADD CONSTRAINT "chk_transactions_amount_sign"
  CHECK ("amount" >= 0 OR "type" = 'adjustment');

-- ── 2. Partial unique indexes ───────────────────────────────────────────────
-- One active enrollment per (student, group). Historical rows stay, so a
-- student may re-join the same group later (docs/03 § C1).
CREATE UNIQUE INDEX "uq_enroll_active" ON "enrollments" ("student_id", "group_id")
  WHERE "status" = 'active' AND "deleted_at" IS NULL;

-- A group cannot have two lessons in the same slot.
CREATE UNIQUE INDEX "uq_lesson_group_slot" ON "lessons" ("group_id", "date", "start_time")
  WHERE "deleted_at" IS NULL;

-- ── 3. Schedule conflict guards (last line of defence) ──────────────────────
-- The service layer checks conflicts too and returns 422 SCHEDULE_CONFLICT;
-- these constraints make a conflicting row impossible even if it does not.
ALTER TABLE "lessons" ADD COLUMN "time_range" tstzrange
  GENERATED ALWAYS AS (
    tstzrange(
      ("date" + "start_time") AT TIME ZONE 'Asia/Tashkent',
      ("date" + "end_time")   AT TIME ZONE 'Asia/Tashkent',
      '[)'
    )
  ) STORED;

-- Guarded on `pg_extension`: without btree_gist, `EXCLUDE USING gist (uuid
-- WITH =, ...)` cannot be created at all — GiST has no equality operator
-- class for uuid on its own, which is exactly what btree_gist supplies.
-- Where it is missing, this is not a silent loss: the service layer already
-- rejects a conflicting lesson with 422 SCHEDULE_CONFLICT before any row is
-- written (docs/03 § D2), and this constraint only exists to catch that
-- check ever being wrong or bypassed. Losing it drops a safety net, not the
-- actual guarantee a user sees.
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'btree_gist') THEN
    EXECUTE '
      ALTER TABLE "lessons" ADD CONSTRAINT "excl_room_overlap"
        EXCLUDE USING gist (
          "room_id" WITH =,
          "time_range" WITH &&
        ) WHERE ("status" IN (''scheduled'', ''held'') AND "deleted_at" IS NULL AND "room_id" IS NOT NULL)
    ';
    EXECUTE '
      ALTER TABLE "lessons" ADD CONSTRAINT "excl_teacher_overlap"
        EXCLUDE USING gist (
          "teacher_id" WITH =,
          "time_range" WITH &&
        ) WHERE ("status" IN (''scheduled'', ''held'') AND "deleted_at" IS NULL AND "teacher_id" IS NOT NULL)
    ';
  END IF;
END $$;

-- ── 4. Balance view ─────────────────────────────────────────────────────────
-- The single source of truth for a student balance. `student_balance_cache`
-- is only a read cache refreshed by a background job (docs/03 § B1).
--
-- security_invoker = true is essential when the view's owner is a
-- BYPASSRLS role (educore_migrator, in the local two-role setup) and a
-- different, RLS-bound role (educore_app) queries it — without it the view
-- would run with the owner's bypass and leak every tenant's balances.
-- security_invoker itself only exists from PostgreSQL 15 — guarded here on
-- server_version_num so the same migration also runs on PG 13 (cPanel).
-- There it degrades safely rather than losing protection: cPanel has a
-- single role for both migrating and querying, that role has no BYPASSRLS,
-- and FORCE ROW LEVEL SECURITY (below) already applies RLS to a table's
-- owner too — so the view's owner has nothing to leak regardless.
DO $$
DECLARE
  pg_version int := current_setting('server_version_num')::int;
  view_options text := '';
BEGIN
  IF pg_version >= 150000 THEN
    view_options := ' WITH (security_invoker = true)';
  END IF;
  EXECUTE format(
    'CREATE VIEW "student_balances"%s AS
     SELECT
       t."tenant_id",
       t."student_id",
       COALESCE(SUM(
         CASE t."type"
           WHEN ''payment''    THEN  t."amount"
           WHEN ''refund''     THEN -t."amount"
           WHEN ''charge''     THEN -t."amount"
           WHEN ''adjustment'' THEN  t."amount"
           WHEN ''transfer''   THEN  t."amount"
         END
       ), 0) AS "balance",
       MAX(t."occurred_at") FILTER (WHERE t."type" = ''payment'') AS "last_payment_at"
     FROM "transactions" t
     WHERE t."deleted_at" IS NULL
     GROUP BY t."tenant_id", t."student_id"',
    view_options
  );
END $$;

-- ── 5. Row-Level Security ───────────────────────────────────────────────────
-- Every request sets the context first:  SELECT set_config('app.tenant_id', $1, true)
-- This is the last line of defence — the service layer still filters by
-- tenant_id explicitly (docs/03 § F1).
CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS uuid AS $$
  SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid;
$$ LANGUAGE sql STABLE;

-- Not covered on purpose:
--   tenants        — a row is created during registration, before any tenant
--                    context exists; protected in the service layer.
--   refresh_tokens — has no tenant_id, reachable only through user_id.
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'branches','holidays','users','roles','user_roles','teachers',
    'lead_sources','lead_stages','leads','lead_activities',
    'students','parents','student_parents','documents',
    'courses','course_levels','rooms','groups','group_schedule',
    'discounts','enrollments','lessons','attendance',
    'cash_sessions','transactions','student_balance_cache',
    'expense_categories','expenses',
    'salary_rules','payroll','message_templates','notifications',
    'integrations','audit_logs','retention_signals'
  ]
  LOOP
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);
    EXECUTE format(
      'CREATE POLICY tenant_isolation ON %I USING (tenant_id = current_tenant_id()) '
      'WITH CHECK (tenant_id = current_tenant_id())', t);
  END LOOP;
END $$;

-- ── 6. Runtime role grants ──────────────────────────────────────────────────
-- Re-applied here (not only in db/init) so that `prisma migrate reset`, which
-- drops and recreates the public schema, cannot leave educore_app without
-- privileges. Skipped when the role does not exist (e.g. a CI shadow database).
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'educore_app') THEN
    EXECUTE 'GRANT USAGE ON SCHEMA public TO educore_app';

    -- Future objects created by this role.
    EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA public '
            'GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO educore_app';
    EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA public '
            'GRANT USAGE, SELECT ON SEQUENCES TO educore_app';
    EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA public '
            'GRANT EXECUTE ON FUNCTIONS TO educore_app';

    -- Objects that already exist.
    EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO educore_app';
    EXECUTE 'GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO educore_app';
    EXECUTE 'GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO educore_app';
  END IF;
END $$;