-- Travel Planner v2.0 Database Schema
-- 멀티 사용자 및 여행 관리 시스템

-- 사용자 테이블
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('admin', 'user')),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login TIMESTAMP,
  created_by UUID REFERENCES users(id)
);

-- 여행 계획 테이블 (확장)
CREATE TABLE IF NOT EXISTS travel_plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  
  -- 목적지 정보
  destination_country VARCHAR(100) NOT NULL,
  destination_city VARCHAR(100) NOT NULL,
  destination_region VARCHAR(100),
  destination_lat DECIMAL(10, 7),
  destination_lng DECIMAL(10, 7),
  
  -- 날짜
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  
  -- 메타데이터
  is_public BOOLEAN DEFAULT false,
  is_template BOOLEAN DEFAULT false,
  template_category VARCHAR(20) CHECK (template_category IN ('japan', 'korea', 'asia', 'europe', 'america', 'other')),
  tags TEXT[] DEFAULT '{}',
  thumbnail VARCHAR(500),
  status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'completed', 'cancelled')),
  
  -- 타임스탬프
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 날짜별 일정 테이블
CREATE TABLE IF NOT EXISTS day_schedules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  travel_plan_id UUID NOT NULL 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 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  day_schedule_id UUID NOT NULL 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 CHECK (type IN ('attraction', 'food', 'accommodation', 'transport', 'other')),
  
  -- 좌표
  lat DECIMAL(10, 7),
  lng DECIMAL(10, 7),
  
  -- 미디어
  images TEXT[] DEFAULT '{}',
  links JSONB DEFAULT '[]',
  
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 관련 장소 테이블
CREATE TABLE IF NOT EXISTS related_places (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  activity_id UUID NOT NULL 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' CHECK (category IN ('restaurant', 'attraction', 'shopping', 'accommodation', 'other')),
  images TEXT[] DEFAULT '{}',
  links JSONB DEFAULT '[]',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 기본 포인트 테이블
CREATE TABLE IF NOT EXISTS base_points (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255),
  type VARCHAR(50) NOT NULL CHECK (type IN ('accommodation', 'airport', 'station', 'parking', 'other')),
  lat DECIMAL(10, 7) NOT NULL,
  lng DECIMAL(10, 7) NOT NULL,
  memo TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 예산 테이블
CREATE TABLE IF NOT EXISTS budgets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  travel_plan_id UUID NOT NULL REFERENCES travel_plans(id) ON DELETE CASCADE,
  total_amount DECIMAL(12, 2) DEFAULT 0,
  accommodation DECIMAL(12, 2) DEFAULT 0,
  food DECIMAL(12, 2) DEFAULT 0,
  transportation DECIMAL(12, 2) DEFAULT 0,
  shopping DECIMAL(12, 2) DEFAULT 0,
  activities DECIMAL(12, 2) DEFAULT 0,
  currency VARCHAR(3) DEFAULT 'KRW',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 체크리스트 테이블
CREATE TABLE IF NOT EXISTS checklist_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  travel_plan_id UUID NOT NULL REFERENCES travel_plans(id) ON DELETE CASCADE,
  text VARCHAR(500) NOT NULL,
  checked BOOLEAN DEFAULT false,
  category VARCHAR(50) DEFAULT 'other' CHECK (category IN ('preparation', 'shopping', 'visit', 'other')),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 공유 링크 테이블
CREATE TABLE IF NOT EXISTS share_links (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  trip_id UUID NOT NULL REFERENCES travel_plans(id) ON DELETE CASCADE,
  created_by UUID NOT NULL REFERENCES users(id),
  share_code VARCHAR(8) UNIQUE NOT NULL,
  expires_at TIMESTAMP,
  is_active BOOLEAN DEFAULT true,
  access_count INTEGER DEFAULT 0,
  max_access_count INTEGER,
  
  -- 권한
  can_view BOOLEAN DEFAULT true,
  can_edit BOOLEAN DEFAULT false,
  can_comment BOOLEAN DEFAULT false,
  
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_accessed TIMESTAMP
);

-- 댓글 테이블
CREATE TABLE IF NOT EXISTS trip_comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  trip_id UUID NOT NULL REFERENCES travel_plans(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id),
  content TEXT NOT NULL,
  is_edited BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 시스템 설정 테이블
CREATE TABLE IF NOT EXISTS system_settings (
  key VARCHAR(100) PRIMARY KEY,
  value TEXT,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 인덱스 생성
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_travel_plans_user ON travel_plans(user_id);
CREATE INDEX IF NOT EXISTS idx_travel_plans_status ON travel_plans(status);
CREATE INDEX IF NOT EXISTS idx_travel_plans_category ON travel_plans(template_category);
CREATE INDEX IF NOT EXISTS idx_travel_plans_public ON travel_plans(is_public);
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);
CREATE INDEX IF NOT EXISTS idx_base_points_user ON base_points(user_id);
CREATE INDEX IF NOT EXISTS idx_budgets_plan ON budgets(travel_plan_id);
CREATE INDEX IF NOT EXISTS idx_checklist_plan ON checklist_items(travel_plan_id);
CREATE INDEX IF NOT EXISTS idx_share_links_code ON share_links(share_code);
CREATE INDEX IF NOT EXISTS idx_share_links_trip ON share_links(trip_id);
CREATE INDEX IF NOT EXISTS idx_comments_trip ON trip_comments(trip_id);

-- 초기 시스템 설정 데이터
INSERT INTO system_settings (key, value, description) VALUES
  ('app_version', '2.0.0', '애플리케이션 버전'),
  ('setup_completed', 'false', '초기 설정 완료 여부'),
  ('jwt_secret_set', 'false', 'JWT 시크릿 설정 여부'),
  ('google_maps_configured', 'false', 'Google Maps API 설정 여부'),
  ('email_configured', 'false', '이메일 설정 여부')
ON CONFLICT (key) DO NOTHING;

-- 업데이트 트리거 함수
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ language 'plpgsql';

-- 업데이트 트리거 적용
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_travel_plans_updated_at ON travel_plans;
CREATE TRIGGER update_travel_plans_updated_at BEFORE UPDATE ON travel_plans FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_budgets_updated_at ON budgets;
CREATE TRIGGER update_budgets_updated_at BEFORE UPDATE ON budgets FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_checklist_updated_at ON checklist_items;
CREATE TRIGGER update_checklist_updated_at BEFORE UPDATE ON checklist_items FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_comments_updated_at ON trip_comments;
CREATE TRIGGER update_comments_updated_at BEFORE UPDATE ON trip_comments FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_settings_updated_at ON system_settings;
CREATE TRIGGER update_settings_updated_at BEFORE UPDATE ON system_settings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();