guidly/db/migrations/001_initial.sql

-- Guidly: Initial database schema
-- Run with: npm run migrate

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================
-- Cities
-- ============================================
CREATE TABLE cities (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        VARCHAR(255) NOT NULL,
  country     VARCHAR(100) NOT NULL,
  description TEXT,
  status      VARCHAR(20) NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'inactive')),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ============================================
-- Users
-- ============================================
CREATE TABLE users (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  telegram_id     BIGINT NOT NULL UNIQUE,
  username        VARCHAR(255),
  display_name    VARCHAR(255),
  language_code   VARCHAR(10) DEFAULT 'ru',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_telegram_id ON users(telegram_id);

-- ============================================
-- Quests
-- ============================================
CREATE TABLE quests (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  city_id         UUID NOT NULL REFERENCES cities(id),
  user_id         UUID NOT NULL REFERENCES users(id),
  title           TEXT,
  description     TEXT,
  number_of_days  INT NOT NULL,
  pace            VARCHAR(20) NOT NULL CHECK (pace IN ('slow', 'normal', 'active')),
  companions      VARCHAR(20) NOT NULL CHECK (companions IN ('solo', 'couple', 'family', 'friends')),
  user_comment    TEXT,
  status          VARCHAR(30) NOT NULL DEFAULT 'not_started'
                    CHECK (status IN ('not_started', 'in_progress', 'completed')),
  started_at      TIMESTAMPTZ,
  completed_at    TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_quests_user_id ON quests(user_id);
CREATE INDEX idx_quests_user_status ON quests(user_id, status);

-- ============================================
-- Quest Days
-- ============================================
CREATE TABLE quest_days (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  quest_id        UUID NOT NULL REFERENCES quests(id) ON DELETE CASCADE,
  day_number      INT NOT NULL,
  theme           TEXT,
  status          VARCHAR(20) NOT NULL DEFAULT 'not_started'
                    CHECK (status IN ('not_started', 'in_progress', 'completed')),
  started_at      TIMESTAMPTZ,
  completed_at    TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  UNIQUE (quest_id, day_number)
);

CREATE INDEX idx_quest_days_quest ON quest_days(quest_id);

-- ============================================
-- Quest Points (individual stops/locations)
-- ============================================
CREATE TABLE quest_points (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  day_id          UUID NOT NULL REFERENCES quest_days(id) ON DELETE CASCADE,
  title           VARCHAR(500) NOT NULL,
  location_lat    DECIMAL(10, 7),
  location_lon    DECIMAL(10, 7),
  teaser_text     TEXT,
  content_text    TEXT,
  order_in_day    INT NOT NULL,
  status          VARCHAR(20) NOT NULL DEFAULT 'hidden'
                    CHECK (status IN ('hidden', 'preview', 'active', 'completed', 'skipped')),
  arrived_at      TIMESTAMPTZ,
  completed_at    TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  UNIQUE (day_id, order_in_day)
);

CREATE INDEX idx_quest_points_day ON quest_points(day_id);

-- ============================================
-- Quest Events (Mini App -> Bot communication)
-- ============================================
CREATE TABLE quest_events (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  quest_id        UUID NOT NULL REFERENCES quests(id),
  user_id         UUID NOT NULL REFERENCES users(id),
  point_id        UUID REFERENCES quest_points(id),
  event_type      VARCHAR(30) NOT NULL
                    CHECK (event_type IN (
                      'arrived', 'photo_uploaded', 'skipped',
                      'finished_today', 'point_completed',
                      'quest_started', 'quest_completed',
                      'day_started', 'day_completed',
                      'peek_next'
                    )),
  payload         JSONB DEFAULT '{}',
  processed       BOOLEAN NOT NULL DEFAULT false,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_quest_events_quest ON quest_events(quest_id);
CREATE INDEX idx_quest_events_unprocessed ON quest_events(processed) WHERE processed = false;

-- ============================================
-- Chat History
-- ============================================
CREATE TABLE chat_history (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES users(id),
  quest_id        UUID NOT NULL REFERENCES quests(id),
  point_id        UUID REFERENCES quest_points(id),
  message_type    VARCHAR(20) NOT NULL
                    CHECK (message_type IN ('user_text', 'user_voice', 'ai_text', 'ai_audio')),
  content         TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_chat_history_quest ON chat_history(quest_id);

-- ============================================
-- Achievements
-- ============================================
CREATE TABLE achievements (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES users(id),
  type            VARCHAR(50) NOT NULL
                    CHECK (type IN (
                      'first_quest', 'quest_completed',
                      'cities_2', 'cities_5', 'cities_10',
                      'steps_50', 'steps_100'
                    )),
  city_id         UUID REFERENCES cities(id),
  achieved_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  UNIQUE (user_id, type, city_id)
);

CREATE INDEX idx_achievements_user ON achievements(user_id);

-- ============================================
-- Seed: Test cities
-- ============================================
INSERT INTO cities (name, country, description) VALUES
  ('Novosibirsk', 'Russia', 'The cultural and scientific capital of Siberia'),
  ('Moscow', 'Russia', 'The heart of Russia with centuries of history'),
  ('Saint Petersburg', 'Russia', 'The cultural capital with imperial grandeur'),
  ('Istanbul', 'Turkey', 'Where East meets West across the Bosphorus'),
  ('Barcelona', 'Spain', 'A city of art, architecture and Mediterranean spirit');