mkd/backend/migrate_pr_nps.sql

-- ========= PR и NPS: МИГРАЦИЯ =========
-- Создание таблиц для функционала PR и NPS

-- ========= ОТЗЫВЫ =========
CREATE TABLE IF NOT EXISTS reviews (
  id                BIGSERIAL PRIMARY KEY,
  building_id       VARCHAR(50) REFERENCES buildings(id) ON DELETE CASCADE,
  source            VARCHAR(20) NOT NULL CHECK (source IN ('yandex_maps', '2gis', 'internal', 'other')),
  source_url        TEXT,
  author_name       TEXT,
  text              TEXT NOT NULL,
  rating            INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 10),
  date              DATE NOT NULL,
  status            VARCHAR(20) NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'processed', 'archived')),
  processed_at      TIMESTAMPTZ,
  processed_by      TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_reviews_building ON reviews(building_id);
CREATE INDEX IF NOT EXISTS idx_reviews_source ON reviews(source);
CREATE INDEX IF NOT EXISTS idx_reviews_status ON reviews(status);
CREATE INDEX IF NOT EXISTS idx_reviews_date ON reviews(date DESC);

-- ========= ИНЦИДЕНТЫ =========
CREATE TABLE IF NOT EXISTS incidents (
  id                BIGSERIAL PRIMARY KEY,
  review_id         BIGINT REFERENCES reviews(id) ON DELETE SET NULL,
  building_id       VARCHAR(50) REFERENCES buildings(id) ON DELETE CASCADE,
  type              VARCHAR(50) NOT NULL CHECK (type IN ('property_damage', 'debtor_complaint', 'service_quality', 'other')),
  title             TEXT NOT NULL,
  description       TEXT NOT NULL,
  status            VARCHAR(20) NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'in_progress', 'resolved', 'closed')),
  priority          VARCHAR(10) NOT NULL DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
  assigned_to       TEXT,
  created_by        TEXT NOT NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  resolved_at       TIMESTAMPTZ,
  resolution_notes  TEXT,
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_incidents_building ON incidents(building_id);
CREATE INDEX IF NOT EXISTS idx_incidents_status ON incidents(status);
CREATE INDEX IF NOT EXISTS idx_incidents_type ON incidents(type);
CREATE INDEX IF NOT EXISTS idx_incidents_assigned ON incidents(assigned_to);
CREATE INDEX IF NOT EXISTS idx_incidents_review ON incidents(review_id);

-- ========= ОТЧЕТЫ ЖИТЕЛЯМ =========
CREATE TABLE IF NOT EXISTS resident_reports (
  id                BIGSERIAL PRIMARY KEY,
  building_id       VARCHAR(50) NOT NULL REFERENCES buildings(id) ON DELETE CASCADE,
  month             VARCHAR(20) NOT NULL,
  period_start      DATE NOT NULL,
  period_end        DATE NOT NULL,
  status            VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published')),
  published_at      TIMESTAMPTZ,
  content           JSONB, -- Структурированный контент отчета
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_resident_reports_building ON resident_reports(building_id);
CREATE INDEX IF NOT EXISTS idx_resident_reports_status ON resident_reports(status);
CREATE INDEX IF NOT EXISTS idx_resident_reports_period ON resident_reports(period_start);

-- ========= ФОТО ОТЧЕТЫ РАБОТ =========
CREATE TABLE IF NOT EXISTS work_photos (
  id                BIGSERIAL PRIMARY KEY,
  building_id       VARCHAR(50) NOT NULL REFERENCES buildings(id) ON DELETE CASCADE,
  resident_report_id BIGINT REFERENCES resident_reports(id) ON DELETE SET NULL,
  work_name         TEXT NOT NULL,
  work_date         DATE NOT NULL,
  description       TEXT,
  photo_before_url  TEXT,
  photo_after_url   TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_work_photos_building ON work_photos(building_id);
CREATE INDEX IF NOT EXISTS idx_work_photos_report ON work_photos(resident_report_id);
CREATE INDEX IF NOT EXISTS idx_work_photos_date ON work_photos(work_date DESC);

-- ========= НАСТРОЙКИ ПАРСИНГА =========
CREATE TABLE IF NOT EXISTS parsing_settings (
  id                    BIGSERIAL PRIMARY KEY,
  source                VARCHAR(20) NOT NULL UNIQUE CHECK (source IN ('yandex_maps', '2gis')),
  enabled               BOOLEAN NOT NULL DEFAULT TRUE,
  url_template          TEXT,
  api_key               TEXT,
  parsing_interval_hours INTEGER DEFAULT 24,
  last_parsed_at        TIMESTAMPTZ,
  settings              JSONB, -- Дополнительные настройки
  created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_parsing_settings_source ON parsing_settings(source);

-- Инициализация настроек парсинга по умолчанию
INSERT INTO parsing_settings (source, enabled, url_template, parsing_interval_hours)
VALUES 
  ('yandex_maps', FALSE, '', 24),
  ('2gis', FALSE, '', 24)
ON CONFLICT (source) DO NOTHING;