-- 여행 계획 테이블
CREATE TABLE IF NOT EXISTS travel_plans (
  id SERIAL PRIMARY KEY,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 날짜별 일정 테이블
CREATE TABLE IF NOT EXISTS day_schedules (
  id SERIAL PRIMARY KEY,
  travel_plan_id INTEGER REFERENCES travel_plans(id) ON DELETE CASCADE,
  schedule_date DATE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 활동 테이블
CREATE TABLE IF NOT EXISTS activities (
  id SERIAL PRIMARY KEY,
  day_schedule_id INTEGER REFERENCES day_schedules(id) ON DELETE CASCADE,
  time VARCHAR(10) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  location VARCHAR(255),
  type VARCHAR(50) NOT NULL,
  lat DECIMAL(10, 7),
  lng DECIMAL(10, 7),
  images TEXT[],
  links TEXT[],
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 관련 장소 테이블
CREATE TABLE IF NOT EXISTS related_places (
  id SERIAL PRIMARY KEY,
  activity_id INTEGER REFERENCES activities(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  address VARCHAR(255),
  lat DECIMAL(10, 7),
  lng DECIMAL(10, 7),
  memo TEXT,
  will_visit BOOLEAN DEFAULT false,
  category VARCHAR(50) DEFAULT 'other',
  images TEXT[],
  links TEXT[],
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 기본 포인트 테이블
CREATE TABLE IF NOT EXISTS base_points (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255),
  type VARCHAR(50) NOT NULL,
  lat DECIMAL(10, 7) NOT NULL,
  lng DECIMAL(10, 7) NOT NULL,
  memo TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 인덱스 생성
CREATE INDEX IF NOT EXISTS idx_day_schedules_plan ON day_schedules(travel_plan_id);
CREATE INDEX IF NOT EXISTS idx_activities_schedule ON activities(day_schedule_id);
CREATE INDEX IF NOT EXISTS idx_related_places_activity ON related_places(activity_id);