-- PR Мероприятия: pr_events, pr_event_assignees, pr_event_photos
-- Таблица мероприятий
CREATE TABLE IF NOT EXISTS pr_events (
  id                    BIGSERIAL PRIMARY KEY,
  title                 TEXT NOT NULL,
  date                  DATE NOT NULL,
  type                  VARCHAR(20) NOT NULL CHECK (type IN ('resident', 'internal')),
  category              VARCHAR(20) NOT NULL CHECK (category IN ('holiday', 'eco', 'sport', 'training', 'meeting')),
  status                VARCHAR(20) NOT NULL DEFAULT 'planned' CHECK (status IN ('planned', 'in_progress', 'completed', 'canceled')),
  location              TEXT,
  location_type         VARCHAR(20) CHECK (location_type IN ('building', 'office')),
  location_building_id   VARCHAR(50) REFERENCES buildings(id) ON DELETE SET NULL,
  attendees_count       INTEGER NOT NULL DEFAULT 0,
  budget                NUMERIC(15, 2),
  short_plan             TEXT,
  announcement          TEXT,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by            TEXT
);

CREATE INDEX IF NOT EXISTS idx_pr_events_date ON pr_events(date DESC);
CREATE INDEX IF NOT EXISTS idx_pr_events_status ON pr_events(status);
CREATE INDEX IF NOT EXISTS idx_pr_events_type ON pr_events(type);
CREATE INDEX IF NOT EXISTS idx_pr_events_location_building ON pr_events(location_building_id) WHERE location_building_id IS NOT NULL;

-- Сотрудники, назначенные на помощь по мероприятию
CREATE TABLE IF NOT EXISTS pr_event_assignees (
  event_id    BIGINT NOT NULL REFERENCES pr_events(id) ON DELETE CASCADE,
  employee_id VARCHAR(50) NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
  PRIMARY KEY (event_id, employee_id)
);

CREATE INDEX IF NOT EXISTS idx_pr_event_assignees_event ON pr_event_assignees(event_id);
CREATE INDEX IF NOT EXISTS idx_pr_event_assignees_employee ON pr_event_assignees(employee_id);

-- Фотоотчёт по мероприятию (привязка к дому/офису для внутренних)
CREATE TABLE IF NOT EXISTS pr_event_photos (
  id                    BIGSERIAL PRIMARY KEY,
  event_id              BIGINT NOT NULL REFERENCES pr_events(id) ON DELETE CASCADE,
  photo_url              TEXT NOT NULL,
  caption                TEXT,
  location_type          VARCHAR(20) CHECK (location_type IN ('building', 'office')),
  location_building_id   VARCHAR(50) REFERENCES buildings(id) ON DELETE SET NULL,
  created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pr_event_photos_event ON pr_event_photos(event_id);

COMMENT ON TABLE pr_events IS 'Мероприятия PR: для жителей и внутренние';
COMMENT ON TABLE pr_event_assignees IS 'Сотрудники для помощи по мероприятию';
COMMENT ON TABLE pr_event_photos IS 'Фотоотчёт по мероприятию';
