WellNuo/backend/scripts/create-tables.sql

-- WellNuo Database Schema
-- Run this in Supabase SQL Editor

-- ============ ORDERS TABLE ============
CREATE TABLE IF NOT EXISTS orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  order_number VARCHAR(50) UNIQUE NOT NULL,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  beneficiary_id UUID REFERENCES beneficiaries(id),

  -- Stripe
  stripe_session_id VARCHAR(255),
  stripe_customer_id VARCHAR(255),
  stripe_subscription_id VARCHAR(255),

  -- Order details
  status VARCHAR(50) DEFAULT 'paid' CHECK (status IN ('paid', 'preparing', 'shipped', 'delivered', 'installed', 'canceled')),
  amount_total INTEGER NOT NULL, -- in cents
  currency VARCHAR(3) DEFAULT 'usd',

  -- Shipping
  shipping_address JSONB,
  shipping_name VARCHAR(255),
  tracking_number VARCHAR(255),
  carrier VARCHAR(50),
  estimated_delivery DATE,

  -- Items
  items JSONB NOT NULL DEFAULT '[]',

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  shipped_at TIMESTAMPTZ,
  delivered_at TIMESTAMPTZ
);

-- Index for faster queries
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- ============ SUBSCRIPTIONS TABLE ============
CREATE TABLE IF NOT EXISTS subscriptions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  beneficiary_id UUID REFERENCES beneficiaries(id),

  -- Stripe
  stripe_subscription_id VARCHAR(255) UNIQUE,
  stripe_customer_id VARCHAR(255),

  -- Plan
  plan VARCHAR(50) DEFAULT 'free' CHECK (plan IN ('free', 'premium')),
  status VARCHAR(50) DEFAULT 'active' CHECK (status IN ('active', 'past_due', 'canceled', 'unpaid')),

  -- Billing period
  current_period_start TIMESTAMPTZ,
  current_period_end TIMESTAMPTZ,
  canceled_at TIMESTAMPTZ,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);

-- ============ BENEFICIARIES TABLE (if not exists) ============
CREATE TABLE IF NOT EXISTS beneficiaries (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id),

  -- Info
  name VARCHAR(255) NOT NULL,
  photo_url TEXT,
  address TEXT,
  phone VARCHAR(50),
  notes TEXT,

  -- Status
  status VARCHAR(50) DEFAULT 'awaiting_sensors' CHECK (status IN ('awaiting_sensors', 'setup_pending', 'active', 'inactive')),

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_beneficiaries_user_id ON beneficiaries(user_id);

-- ============ DEVICES TABLE ============
CREATE TABLE IF NOT EXISTS devices (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  beneficiary_id UUID REFERENCES beneficiaries(id),

  -- Device info
  device_type VARCHAR(50) NOT NULL CHECK (device_type IN ('hub', 'motion', 'door', 'window', 'environment')),
  serial_number VARCHAR(255) UNIQUE,
  name VARCHAR(255),
  room VARCHAR(255),

  -- Status
  status VARCHAR(50) DEFAULT 'offline' CHECK (status IN ('online', 'offline', 'error')),
  last_seen TIMESTAMPTZ,
  battery_level INTEGER,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_devices_beneficiary_id ON devices(beneficiary_id);
CREATE INDEX idx_devices_status ON devices(status);

-- ============ PUSH TOKENS TABLE ============
CREATE TABLE IF NOT EXISTS push_tokens (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  token TEXT NOT NULL,
  platform VARCHAR(20) CHECK (platform IN ('ios', 'android', 'web')),
  device_id VARCHAR(255),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(user_id, token)
);

CREATE INDEX idx_push_tokens_user_id ON push_tokens(user_id);

-- ============ ROW LEVEL SECURITY ============

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE beneficiaries ENABLE ROW LEVEL SECURITY;
ALTER TABLE devices ENABLE ROW LEVEL SECURITY;
ALTER TABLE push_tokens ENABLE ROW LEVEL SECURITY;

-- Orders: users can only see their own orders
CREATE POLICY "Users can view own orders" ON orders
  FOR SELECT USING (auth.uid() = user_id);

-- Subscriptions: users can only see their own subscriptions
CREATE POLICY "Users can view own subscriptions" ON subscriptions
  FOR SELECT USING (auth.uid() = user_id);

-- Beneficiaries: users can view and manage their own beneficiaries
CREATE POLICY "Users can view own beneficiaries" ON beneficiaries
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own beneficiaries" ON beneficiaries
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own beneficiaries" ON beneficiaries
  FOR UPDATE USING (auth.uid() = user_id);

-- Devices: users can view devices for their beneficiaries
CREATE POLICY "Users can view devices for own beneficiaries" ON devices
  FOR SELECT USING (
    beneficiary_id IN (
      SELECT id FROM beneficiaries WHERE user_id = auth.uid()
    )
  );

-- Push tokens: users can manage their own tokens
CREATE POLICY "Users can manage own push tokens" ON push_tokens
  FOR ALL USING (auth.uid() = user_id);

-- ============ SERVICE ROLE POLICIES ============
-- These allow the backend (service role) to manage all data

CREATE POLICY "Service role can manage orders" ON orders
  FOR ALL USING (auth.role() = 'service_role');

CREATE POLICY "Service role can manage subscriptions" ON subscriptions
  FOR ALL USING (auth.role() = 'service_role');

CREATE POLICY "Service role can manage beneficiaries" ON beneficiaries
  FOR ALL USING (auth.role() = 'service_role');

CREATE POLICY "Service role can manage devices" ON devices
  FOR ALL USING (auth.role() = 'service_role');

-- ============ UPDATED_AT TRIGGERS ============

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

CREATE TRIGGER update_orders_updated_at BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_subscriptions_updated_at BEFORE UPDATE ON subscriptions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_beneficiaries_updated_at BEFORE UPDATE ON beneficiaries
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_devices_updated_at BEFORE UPDATE ON devices
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();