# WellNuo Database Migration

## Step 1: Open Supabase SQL Editor

Go to: https://supabase.com/dashboard/project/bfzizknbxbsfrffqityf/sql/new

## Step 2: Copy and Run This SQL

Click the button to copy, then paste in SQL Editor and click "Run":

    -- WellNuo Orders & Subscriptions Schema
    -- Compatible with existing database structure

    -- ============ 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 INTEGER REFERENCES person_details(user_id),

      -- Beneficiary info (stored directly, not FK)
      beneficiary_name VARCHAR(255),
      beneficiary_address TEXT,
      beneficiary_phone VARCHAR(50),

      -- 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 IF NOT EXISTS idx_orders_user_id ON orders(user_id);
    CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
    CREATE INDEX IF NOT EXISTS 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 INTEGER REFERENCES person_details(user_id),
      deployment_id INTEGER REFERENCES deployments(deployment_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 IF NOT EXISTS idx_subscriptions_user_id ON subscriptions(user_id);
    CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON subscriptions(status);

    -- ============ PUSH TOKENS TABLE ============
    CREATE TABLE IF NOT EXISTS push_tokens (
      id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
      user_id INTEGER REFERENCES person_details(user_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 IF NOT EXISTS idx_push_tokens_user_id ON push_tokens(user_id);

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

    -- Drop triggers if exist and recreate
    DROP TRIGGER IF EXISTS update_orders_updated_at ON orders;
    CREATE TRIGGER update_orders_updated_at BEFORE UPDATE ON orders
      FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

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

    -- ============ TEST INSERT ============
    -- Insert a test order to verify everything works
    INSERT INTO orders (order_number, user_id, beneficiary_name, beneficiary_address, status, amount_total, items)
    VALUES ('WN-TEST-0001', NULL, 'Test Beneficiary', '123 Test St', 'paid', 25899, '[{"type": "starter_kit", "name": "WellNuo Starter Kit", "price": 24900}, {"type": "subscription", "name": "Premium Monthly", "price": 999}]')
    ON CONFLICT (order_number) DO NOTHING;

## Step 3: Verify Tables Created

After running, you should see:

- orders - Table for storing orders
- subscriptions - Table for managing subscriptions
- push_tokens - Table for push notification tokens

After migration: The admin panel at https://wellnuo.smartlaunchhub.com/admin should work fully!