-- Sınıf etkinlikleri (kreş — aylık / takvim programı)
-- PostgreSQL — tenants, classrooms, users ile uyumlu.
-- Daha önce tone sütunu ile oluşturduysanız: 07_drop_classroom_activity_tone.sql çalıştırın.

CREATE TABLE classroom_activities (
  id                  TEXT                        NOT NULL,
  tenant_id           TEXT                        NOT NULL,
  classroom_id        TEXT                        NOT NULL,
  title               TEXT                        NOT NULL,
  description         TEXT,
  starts_on           DATE                        NOT NULL,
  ends_on             DATE                        NOT NULL,
  created_at          TIMESTAMP(3)                NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          TIMESTAMP(3)                NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by_user_id  TEXT,

  CONSTRAINT classroom_activities_pkey PRIMARY KEY (id),
  CONSTRAINT classroom_activities_tenant_id_fkey
    FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT classroom_activities_classroom_id_fkey
    FOREIGN KEY (classroom_id) REFERENCES classrooms (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT classroom_activities_created_by_user_id_fkey
    FOREIGN KEY (created_by_user_id) REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT classroom_activities_date_range_chk
    CHECK (ends_on >= starts_on)
);

CREATE INDEX classroom_activities_tenant_id_classroom_id_starts_on_idx
  ON classroom_activities (tenant_id, classroom_id, starts_on);

CREATE INDEX classroom_activities_tenant_id_starts_on_idx
  ON classroom_activities (tenant_id, starts_on);

CREATE INDEX classroom_activities_classroom_id_starts_on_idx
  ON classroom_activities (classroom_id, starts_on);

COMMENT ON TABLE classroom_activities IS 'Okul (tenant) bazında sınıf etkinlikleri; aylık program / takvim için.';

CREATE OR REPLACE FUNCTION set_classroom_activities_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER classroom_activities_set_updated_at
  BEFORE UPDATE ON classroom_activities
  FOR EACH ROW
  EXECUTE PROCEDURE set_classroom_activities_updated_at();