supabase_schema.sql

-- WanderFund Database Schema
-- PostgreSQL / Supabase

-- Table 1: Annual Configuration
CREATE TABLE annual_budgets (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  year INT NOT NULL UNIQUE,          -- e.g., 2026
  total_amount DECIMAL NOT NULL,     -- Total Budget Cap
  currency TEXT DEFAULT 'CNY',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Table 2: Trips (The core entity)
CREATE TABLE trips (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  -- Status Logic: 
  -- 'wishlist': No dates, just an idea.
  -- 'planning': Dates set, budget frozen.
  -- 'booked': Expenses are being paid.
  -- 'completed': Trip finished.
  status TEXT NOT NULL CHECK (status IN ('wishlist', 'planning', 'booked', 'completed')), 
  start_date DATE,
  end_date DATE,
  priority TEXT DEFAULT 'medium',    -- low, medium, high
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Table 3: Expense Items
CREATE TABLE expenses (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  trip_id BIGINT REFERENCES trips(id) ON DELETE CASCADE,
  item_name TEXT NOT NULL,           
  category TEXT NOT NULL,            -- flight, hotel, food, activity, other
  is_paid BOOLEAN DEFAULT FALSE,     -- KEY LOGIC: false = Frozen Budget, true = Actual Spend
  estimated_cost DECIMAL NOT NULL,   -- The Plan
  actual_cost DECIMAL,               -- The Reality
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_trips_status ON trips(status);
CREATE INDEX idx_trips_start_date ON trips(start_date);
CREATE INDEX idx_expenses_trip_id ON expenses(trip_id);
CREATE INDEX idx_expenses_is_paid ON expenses(is_paid);

-- Sample data for testing (optional)
-- INSERT INTO annual_budgets (year, total_amount, currency) VALUES (2026, 50000, 'CNY');