-- Aidat yönetimi modülü: yeni enum'lar, StudentFinanceSettings, FeeItem,
-- Invoice ve Payment tablolarına yeni sütunlar.

-- ── 1. Yeni Enum Türleri ─────────────────────────────────────

DO $$ BEGIN
  CREATE TYPE "invoice_type" AS ENUM ('MONTHLY_TUITION', 'EXTRA_FEE');
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
  CREATE TYPE "fee_type" AS ENUM ('MONTHLY_TUITION', 'TRANSPORT', 'FOOD', 'STATIONERY', 'TRIP', 'OTHER');
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
  CREATE TYPE "PaymentMethod" AS ENUM ('CASH', 'BANK_TRANSFER', 'CREDIT_CARD', 'OTHER');
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

-- ── 2. Invoice Tablosu Güncellemeleri ────────────────────────

ALTER TABLE "invoices"
  ADD COLUMN IF NOT EXISTS "invoice_type" "invoice_type" NOT NULL DEFAULT 'MONTHLY_TUITION',
  ADD COLUMN IF NOT EXISTS "billing_period" TEXT,
  ADD COLUMN IF NOT EXISTS "paid_amount" DECIMAL(10,2) NOT NULL DEFAULT 0;

CREATE INDEX IF NOT EXISTS "invoices_tenant_id_billing_period_idx"
  ON "invoices"("tenant_id", "billing_period");

-- ── 3. Payment Tablosu Güncellemeleri ────────────────────────

ALTER TABLE "payments"
  ADD COLUMN IF NOT EXISTS "payment_method" "PaymentMethod" NOT NULL DEFAULT 'CASH',
  ADD COLUMN IF NOT EXISTS "notes" TEXT,
  ADD COLUMN IF NOT EXISTS "recorded_by" TEXT;

-- provider sütunu nullable yap
ALTER TABLE "payments"
  ALTER COLUMN "provider" DROP NOT NULL,
  ALTER COLUMN "provider" DROP DEFAULT;

-- status default değerini güncelle
ALTER TABLE "payments"
  ALTER COLUMN "status" SET DEFAULT 'SUCCESS';

-- paidAt alanını zorunlu hale getir (nullable'dan not null'a)
ALTER TABLE "payments"
  ALTER COLUMN "paid_at" SET DEFAULT NOW();

UPDATE "payments" SET "paid_at" = "created_at" WHERE "paid_at" IS NULL;

ALTER TABLE "payments"
  ALTER COLUMN "paid_at" SET NOT NULL;

-- ── 4. StudentFinanceSettings Tablosu ────────────────────────

CREATE TABLE IF NOT EXISTS "student_finance_settings" (
  "id"              TEXT NOT NULL DEFAULT gen_random_uuid()::TEXT,
  "tenant_id"       TEXT NOT NULL,
  "student_id"      TEXT NOT NULL,
  "monthly_fee"     DECIMAL(10,2) NOT NULL DEFAULT 0,
  "transport_fee"   DECIMAL(10,2) NOT NULL DEFAULT 0,
  "food_fee"        DECIMAL(10,2) NOT NULL DEFAULT 0,
  "discount_type"   TEXT,
  "discount_value"  DECIMAL(10,2) NOT NULL DEFAULT 0,
  "discount_reason" TEXT,
  "billing_day"     INTEGER NOT NULL DEFAULT 1,
  "notes"           TEXT,
  "created_at"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at"      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT "student_finance_settings_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "student_finance_settings_student_id_key" UNIQUE ("student_id"),
  CONSTRAINT "student_finance_settings_tenant_id_fkey"
    FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE CASCADE,
  CONSTRAINT "student_finance_settings_student_id_fkey"
    FOREIGN KEY ("student_id") REFERENCES "students"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS "student_finance_settings_tenant_id_idx"
  ON "student_finance_settings"("tenant_id");

-- ── 5. FeeItem Tablosu ────────────────────────────────────────

CREATE TABLE IF NOT EXISTS "fee_items" (
  "id"          TEXT NOT NULL DEFAULT gen_random_uuid()::TEXT,
  "invoice_id"  TEXT NOT NULL,
  "fee_type"    "fee_type" NOT NULL DEFAULT 'OTHER',
  "label"       TEXT NOT NULL,
  "amount"      DECIMAL(10,2) NOT NULL,
  "created_at"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT "fee_items_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "fee_items_invoice_id_fkey"
    FOREIGN KEY ("invoice_id") REFERENCES "invoices"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS "fee_items_invoice_id_idx"
  ON "fee_items"("invoice_id");