-- ============================================================
-- CampGear Supabase Schema
-- Supabase SQL Editor에서 실행
-- 멱등성 보장: 재실행해도 안전 (IF NOT EXISTS / DO 블록 사용)
-- ============================================================

-- UUID 확장
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";


-- ============================================================
-- ENUM 타입 (이미 존재하면 무시)
-- ============================================================

DO $$ BEGIN
  CREATE TYPE equipment_category AS ENUM (
    'tent', 'sleeping', 'cooking', 'lighting', 'clothing',
    'backpack', 'furniture', 'safety', 'electronics', 'other'
  );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
  CREATE TYPE sale_status AS ENUM ('listing', 'reserved', 'sold', 'cancelled');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
  CREATE TYPE sale_platform AS ENUM ('danggeun', 'bunjang', 'joonggo', 'naver', 'other');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;


-- ============================================================
-- 테이블
-- ============================================================

CREATE TABLE IF NOT EXISTS purchases (
  id            UUID         DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id       UUID         REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  name          TEXT         NOT NULL,
  category      equipment_category NOT NULL,
  brand         TEXT,
  price         INTEGER      NOT NULL DEFAULT 0,
  quantity      INTEGER      NOT NULL DEFAULT 1,
  purchase_date DATE         NOT NULL,
  store         TEXT,
  warranty_until DATE,
  notes         TEXT,
  created_at    TIMESTAMPTZ  DEFAULT NOW() NOT NULL,
  updated_at    TIMESTAMPTZ  DEFAULT NOW() NOT NULL
);

CREATE TABLE IF NOT EXISTS used_sales (
  id          UUID        DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id     UUID        REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  purchase_id UUID        REFERENCES purchases(id) ON DELETE SET NULL,
  item_name   TEXT        NOT NULL,
  sale_price  INTEGER     NOT NULL DEFAULT 0,
  final_price INTEGER,
  platform    sale_platform NOT NULL,
  status      sale_status   NOT NULL DEFAULT 'listing',
  notes       TEXT,
  listed_at   DATE        NOT NULL,
  sold_at     DATE,
  created_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE TABLE IF NOT EXISTS ai_conversations (
  id         UUID        DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id    UUID        REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  title      TEXT        NOT NULL DEFAULT '새 대화',
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE TABLE IF NOT EXISTS ai_messages (
  id              UUID        DEFAULT uuid_generate_v4() PRIMARY KEY,
  conversation_id UUID        REFERENCES ai_conversations(id) ON DELETE CASCADE NOT NULL,
  role            TEXT        NOT NULL CHECK (role IN ('user', 'assistant')),
  content         TEXT        NOT NULL,
  created_at      TIMESTAMPTZ DEFAULT NOW() NOT NULL
);


-- ============================================================
-- 컬럼 마이그레이션 (ADD COLUMN IF NOT EXISTS)
-- ============================================================

-- purchases.quantity 추가 (기존 테이블에 없을 경우)
ALTER TABLE purchases ADD COLUMN IF NOT EXISTS quantity INTEGER NOT NULL DEFAULT 1;


-- ============================================================
-- updated_at 자동 갱신 트리거
-- ============================================================

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS purchases_updated_at ON purchases;
CREATE TRIGGER purchases_updated_at
  BEFORE UPDATE ON purchases
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS used_sales_updated_at ON used_sales;
CREATE TRIGGER used_sales_updated_at
  BEFORE UPDATE ON used_sales
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS ai_conversations_updated_at ON ai_conversations;
CREATE TRIGGER ai_conversations_updated_at
  BEFORE UPDATE ON ai_conversations
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();


-- ============================================================
-- Row Level Security
-- ============================================================

ALTER TABLE purchases       ENABLE ROW LEVEL SECURITY;
ALTER TABLE used_sales      ENABLE ROW LEVEL SECURITY;
ALTER TABLE ai_conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE ai_messages     ENABLE ROW LEVEL SECURITY;

-- purchases RLS
DROP POLICY IF EXISTS "Users can manage own purchases" ON purchases;
CREATE POLICY "Users can manage own purchases" ON purchases
  FOR ALL USING (auth.uid() = user_id);

-- used_sales RLS
DROP POLICY IF EXISTS "Users can manage own used_sales" ON used_sales;
CREATE POLICY "Users can manage own used_sales" ON used_sales
  FOR ALL USING (auth.uid() = user_id);

-- ai_conversations RLS
DROP POLICY IF EXISTS "Users can manage own conversations" ON ai_conversations;
CREATE POLICY "Users can manage own conversations" ON ai_conversations
  FOR ALL USING (auth.uid() = user_id);

-- ai_messages RLS (대화 소유자를 통해 접근)
DROP POLICY IF EXISTS "Users can manage messages in own conversations" ON ai_messages;
CREATE POLICY "Users can manage messages in own conversations" ON ai_messages
  FOR ALL USING (
    conversation_id IN (
      SELECT id FROM ai_conversations WHERE user_id = auth.uid()
    )
  );