-- Аудиты: статусы, индекс сложности, данные осмотра по пунктам/подпунктам

-- Статус аудита: новый, в работе, завершён
ALTER TABLE development_audits ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'new';
UPDATE development_audits SET status = 'completed' WHERE status IS NULL OR status = '';
ALTER TABLE development_audits ALTER COLUMN status SET DEFAULT 'new';
ALTER TABLE development_audits ALTER COLUMN status SET NOT NULL;
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'development_audits_status_check') THEN
    ALTER TABLE development_audits ADD CONSTRAINT development_audits_status_check
      CHECK (status IN ('new', 'in_progress', 'completed'));
  END IF;
END $$;

-- Индекс сложности дома (0–100)
ALTER TABLE development_audits ADD COLUMN IF NOT EXISTS complexity_index NUMERIC(5, 2);
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'development_audits_complexity_index_check') THEN
    ALTER TABLE development_audits ADD CONSTRAINT development_audits_complexity_index_check
      CHECK (complexity_index IS NULL OR (complexity_index >= 0 AND complexity_index <= 100));
  END IF;
END $$;

-- Данные осмотра: пункты и подпункты (JSONB)
ALTER TABLE development_audits ADD COLUMN IF NOT EXISTS inspection_data JSONB DEFAULT '{}';

CREATE INDEX IF NOT EXISTS idx_dev_audits_status ON development_audits(status);