-- ============================================================
-- Kreş SaaS — Public Şema (platform geneli tablolar)
-- Bu dosya tek doğruluk kaynağıdır.
-- Supabase SQL Editor'a veya psql ile çalıştırın.
-- Prisma şeması (schema.prisma) bu yapıyla senkron tutulur.
-- ============================================================

-- Uzantılar
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================================
-- Tenants
-- ============================================================
CREATE TABLE IF NOT EXISTS public.tenants (
  id           TEXT        PRIMARY KEY DEFAULT gen_random_uuid()::text,
  name         TEXT        NOT NULL,
  slug         TEXT        NOT NULL UNIQUE,
  email        TEXT        NOT NULL UNIQUE,
  phone        TEXT,
  city         TEXT,
  address      TEXT,
  logo_url     TEXT,
  plan         TEXT        NOT NULL DEFAULT 'TRIAL',
  is_active    BOOLEAN     NOT NULL DEFAULT true,
  schema_name  TEXT        NOT NULL UNIQUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS tenants_slug_idx ON public.tenants (slug);

-- ============================================================
-- Subscriptions
-- ============================================================
CREATE TABLE IF NOT EXISTS public.subscriptions (
  id           TEXT        PRIMARY KEY DEFAULT gen_random_uuid()::text,
  tenant_id    TEXT        NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
  plan         TEXT        NOT NULL DEFAULT 'TRIAL',
  status       TEXT        NOT NULL DEFAULT 'TRIAL',
  max_students INT         NOT NULL DEFAULT 50,
  max_branches INT         NOT NULL DEFAULT 1,
  starts_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  ends_at      TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS subscriptions_tenant_id_idx ON public.subscriptions (tenant_id);

-- ============================================================
-- Super Admins
-- ============================================================
CREATE TABLE IF NOT EXISTS public.super_admins (
  id            TEXT        PRIMARY KEY DEFAULT gen_random_uuid()::text,
  email         TEXT        NOT NULL UNIQUE,
  password_hash TEXT        NOT NULL,
  full_name     TEXT        NOT NULL,
  is_active     BOOLEAN     NOT NULL DEFAULT true,
  last_login_at TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ============================================================
-- updated_at otomatik güncelleme trigger'ı
-- ============================================================
CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS tenants_updated_at ON public.tenants;
CREATE TRIGGER tenants_updated_at
  BEFORE UPDATE ON public.tenants
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();