HRIH Investments — Backend Setup & Deployment

┌─────────────────────────────────────────────────────────┐
│                   HRIH PLATFORM STACK                    │
├─────────────────────────────────────────────────────────┤
│                                                          │
│  FRONTEND (hrih-membership-platform.html)               │
│  ├── Public Site        → Netlify / Vercel CDN          │
│  ├── Member Dashboard   → Auth-gated                    │
│  └── Back-Office CRM    → Admin-only                    │
│                                                          │
│  BACKEND (Supabase)                                      │
│  ├── PostgreSQL DB      → All data                      │
│  ├── Auth               → JWT · Google OAuth            │
│  ├── Storage            → Products, avatars, invoices   │
│  ├── Realtime           → Live CRM updates              │
│  └── Edge Functions     → API calls (PayFast, WA, etc) │
│                                                          │
│  INTEGRATIONS                                            │
│  ├── PayFast            → ZAR payments (SA)             │
│  ├── Stripe             → Intl card payments            │
│  ├── SendGrid           → Email from your domain        │
│  ├── Twilio/360dialog   → WhatsApp Business API         │
│  ├── Google Drive API   → Product file sync             │
│  ├── Google Business    → Profile & reviews             │
│  ├── ScraperAPI         → Product web scraping          │
│  └── Meta / LinkedIn    → Social posting                │
└─────────────────────────────────────────────────────────┘

-- ══════════════════════════════════════════════════════
-- HRIH INVESTMENTS — SUPABASE DATABASE SCHEMA v1.0
-- Run in: Supabase Dashboard → SQL Editor
-- ══════════════════════════════════════════════════════

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ── PROFILES (extends Supabase auth.users) ──────────
CREATE TABLE profiles (
  id           UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name    TEXT,
  phone        TEXT,
  company_name TEXT,
  avatar_url   TEXT,
  role         TEXT DEFAULT 'member',  -- 'member' | 'admin'
  created_at   TIMESTAMPTZ DEFAULT NOW(),
  updated_at   TIMESTAMPTZ DEFAULT NOW()
);

-- ── MEMBERSHIP TIERS ─────────────────────────────────
CREATE TABLE tiers (
  id           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name         TEXT NOT NULL,              -- 'foundation' | 'strategic' | 'corporate'
  display_name TEXT NOT NULL,
  price_monthly NUMERIC(10,2) NOT NULL,
  price_annual  NUMERIC(10,2),
  features     JSONB,                     -- Array of feature strings
  max_sessions  INT DEFAULT 0,
  is_active    BOOLEAN DEFAULT TRUE,
  sort_order   INT DEFAULT 0
);

-- Seed tier data
INSERT INTO tiers (name, display_name, price_monthly, price_annual, max_sessions, sort_order)
VALUES
  ('foundation', 'Foundation', 499.00, 4990.00, 0, 1),
  ('strategic', 'Strategic', 1499.00, 14990.00, 2, 2),
  ('corporate', 'Corporate', 3999.00, 39990.00, -1, 3);  -- -1 = unlimited

-- ── MEMBERSHIPS ──────────────────────────────────────
CREATE TABLE memberships (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  tier_id         UUID NOT NULL REFERENCES tiers(id),
  status          TEXT DEFAULT 'active',  -- 'trial'|'active'|'paused'|'cancelled'
  billing_cycle   TEXT DEFAULT 'monthly', -- 'monthly' | 'annual'
  start_date      DATE NOT NULL DEFAULT CURRENT_DATE,
  next_billing    DATE,
  trial_ends      DATE,
  payfast_token   TEXT,                   -- PayFast recurring token
  stripe_sub_id   TEXT,                   -- Stripe subscription ID
  sessions_used   INT DEFAULT 0,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ── PRODUCTS ─────────────────────────────────────────
CREATE TABLE products (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name          TEXT NOT NULL,
  slug          TEXT UNIQUE NOT NULL,
  description   TEXT,
  category      TEXT NOT NULL,  -- 'webinar'|'ebook'|'video'|'software'|'consulting'|'template'
  price         NUMERIC(10,2) NOT NULL DEFAULT 0,
  price_type    TEXT DEFAULT 'one_time',  -- 'one_time' | 'subscription'
  file_url      TEXT,              -- Supabase Storage URL
  drive_url     TEXT,              -- Google Drive URL
  thumbnail     TEXT,
  access_tier   TEXT DEFAULT 'public',  -- 'public'|'foundation'|'strategic'|'corporate'
  is_active     BOOLEAN DEFAULT TRUE,
  is_featured   BOOLEAN DEFAULT FALSE,
  source        TEXT DEFAULT 'manual',   -- 'manual'|'scraped'|'drive'|'upload'
  metadata      JSONB,
  sales_count   INT DEFAULT 0,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ── ORDERS ───────────────────────────────────────────
CREATE TABLE orders (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id       UUID REFERENCES profiles(id),
  product_id    UUID REFERENCES products(id),
  amount        NUMERIC(10,2) NOT NULL,
  currency      TEXT DEFAULT 'ZAR',
  status        TEXT DEFAULT 'pending',  -- 'pending'|'paid'|'failed'|'refunded'
  payment_method TEXT,                  -- 'payfast'|'stripe'|'eft'
  payment_ref   TEXT,
  download_count INT DEFAULT 0,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ── INVOICES ─────────────────────────────────────────
CREATE TABLE invoices (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  invoice_num   TEXT UNIQUE NOT NULL,    -- 'INV-0001'
  user_id       UUID REFERENCES profiles(id),
  client_name   TEXT,
  client_email  TEXT,
  items         JSONB NOT NULL,          -- [{desc, qty, unit_price, total}]
  subtotal      NUMERIC(10,2),
  vat_amount    NUMERIC(10,2),
  total         NUMERIC(10,2) NOT NULL,
  status        TEXT DEFAULT 'draft',   -- 'draft'|'sent'|'paid'|'overdue'
  due_date      DATE,
  paid_at       TIMESTAMPTZ,
  notes         TEXT,
  pdf_url       TEXT,                   -- Generated PDF in Storage
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  updated_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ── QUOTES ───────────────────────────────────────────
CREATE TABLE quotes (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  quote_num     TEXT UNIQUE NOT NULL,    -- 'QT-0001'
  client_name   TEXT NOT NULL,
  client_email  TEXT,
  items         JSONB NOT NULL,
  total         NUMERIC(10,2) NOT NULL,
  valid_until   DATE,
  status        TEXT DEFAULT 'draft',   -- 'draft'|'sent'|'accepted'|'declined'|'expired'
  notes         TEXT,
  converted_invoice UUID REFERENCES invoices(id),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ── SESSIONS (consulting bookings) ───────────────────
CREATE TABLE sessions (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id       UUID REFERENCES profiles(id),
  session_type  TEXT,               -- 'strategy'|'finance'|'compliance'|'general'
  scheduled_at  TIMESTAMPTZ,
  duration_min  INT DEFAULT 60,
  channel       TEXT DEFAULT 'zoom',  -- 'zoom'|'whatsapp'|'phone'|'meet'
  agenda        TEXT,
  notes         TEXT,
  status        TEXT DEFAULT 'requested',
  meeting_link  TEXT,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ── LEADS ────────────────────────────────────────────
CREATE TABLE leads (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name          TEXT NOT NULL,
  email         TEXT,
  phone         TEXT,
  company       TEXT,
  interest_tier TEXT,
  stage         TEXT DEFAULT 'new',  -- 'new'|'contacted'|'quoted'|'converted'|'lost'
  source        TEXT,               -- 'whatsapp'|'website'|'referral'|'social'
  notes         TEXT,
  referred_by   UUID REFERENCES profiles(id),
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ── ENROLLMENTS (courses/products) ───────────────────
CREATE TABLE enrollments (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id       UUID REFERENCES profiles(id),
  product_id    UUID REFERENCES products(id),
  progress_pct  INT DEFAULT 0,         -- 0-100
  last_module   INT DEFAULT 0,
  completed_at  TIMESTAMPTZ,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, product_id)
);

-- ── NOTIFICATIONS ─────────────────────────────────────
CREATE TABLE notifications (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id       UUID REFERENCES profiles(id),
  title         TEXT NOT NULL,
  body          TEXT,
  type          TEXT,               -- 'payment'|'session'|'product'|'system'
  is_read       BOOLEAN DEFAULT FALSE,
  action_url    TEXT,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- ── INDEXES ──────────────────────────────────────────
CREATE INDEX idx_memberships_user ON memberships(user_id);
CREATE INDEX idx_memberships_status ON memberships(status);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_notifications_user ON notifications(user_id, is_read);

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

CREATE TRIGGER trg_profiles_updated
  BEFORE UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_products_updated
  BEFORE UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_invoices_updated
  BEFORE UPDATE ON invoices
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── AUTO-CREATE PROFILE ON SIGNUP ────────────────────
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO profiles (id, full_name)
  VALUES (NEW.id, NEW.raw_user_meta_data->>>'full_name');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- ── INVOICE SEQUENCE FUNCTION ─────────────────────────
CREATE SEQUENCE invoice_seq START WITH 43;  -- Starts at INV-0043

CREATE OR REPLACE FUNCTION next_invoice_num()
RETURNS TEXT AS $$
  SELECT 'INV-' || LPAD(nextval('invoice_seq')::TEXT, 4, '0');
$$ LANGUAGE sql;

CREATE SEQUENCE quote_seq START WITH 19;

CREATE OR REPLACE FUNCTION next_quote_num()
RETURNS TEXT AS $$
  SELECT 'QT-' || LPAD(nextval('quote_seq')::TEXT, 4, '0');
$$ LANGUAGE sql;

-- ── ENABLE RLS ON ALL USER TABLES ─────────────────────
ALTER TABLE profiles      ENABLE ROW LEVEL SECURITY;
ALTER TABLE memberships   ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders        ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices      ENABLE ROW LEVEL SECURITY;
ALTER TABLE sessions      ENABLE ROW LEVEL SECURITY;
ALTER TABLE enrollments   ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;

-- ── HELPER: is_admin() ────────────────────────────────
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN AS $$
  SELECT role = 'admin' FROM profiles WHERE id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER;

-- ── PROFILES ──────────────────────────────────────────
CREATE POLICY "Own profile" ON profiles
  FOR ALL USING (id = auth.uid() OR is_admin());

-- ── MEMBERSHIPS ───────────────────────────────────────
CREATE POLICY "Own membership" ON memberships
  FOR SELECT USING (user_id = auth.uid() OR is_admin());
CREATE POLICY "Admin manage memberships" ON memberships
  FOR ALL USING (is_admin());

-- ── ORDERS ────────────────────────────────────────────
CREATE POLICY "Own orders" ON orders
  FOR SELECT USING (user_id = auth.uid() OR is_admin());

-- ── INVOICES ──────────────────────────────────────────
CREATE POLICY "Own invoices" ON invoices
  FOR SELECT USING (user_id = auth.uid() OR is_admin());
CREATE POLICY "Admin manage invoices" ON invoices
  FOR ALL USING (is_admin());

-- ── PRODUCTS (public read, admin write) ───────────────
CREATE POLICY "Public products" ON products
  FOR SELECT USING (is_active = TRUE);
CREATE POLICY "Admin manage products" ON products
  FOR ALL USING (is_admin());

-- ── NOTIFICATIONS ─────────────────────────────────────
CREATE POLICY "Own notifications" ON notifications
  FOR ALL USING (user_id = auth.uid() OR is_admin());

// supabase/functions/payfast-webhook/index.ts
// Handles PayFast ITN (Instant Transaction Notification)
// Deploy: supabase functions deploy payfast-webhook

import { serve } from "https://deno.land/std@0.168.0/http/server.ts"
import { createClient } from "https://esm.sh/@supabase/supabase-js@2"

serve(async (req) => {
  const body = await req.formData()
  const data = Object.fromEntries(body)

  // Verify PayFast signature
  const pfData = { ...data }
  delete pfData.signature
  const pfString = Object.entries(pfData)
    .map(([k, v]) => `${k}=${encodeURIComponent(String(v))}`)
    .join('&')

  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  )

  if (data.payment_status === 'COMPLETE') {
    // Update order status
    await supabase
      .from('orders')
      .update({ status: 'paid', payment_ref: data.pf_payment_id })
      .eq('id', data.m_payment_id)

    // Activate membership if this was a subscription signup
    if (data.token) {
      await supabase
        .from('memberships')
        .update({ status: 'active', payfast_token: data.token })
        .eq('id', data.m_payment_id)
    }

    // Create notification
    await supabase.from('notifications').insert({
      user_id: data.custom_str1,
      title: 'Payment received',
      body: `R${data.amount_gross} confirmed via PayFast`,
      type: 'payment'
    })
  }

  return new Response('OK', { status: 200 })
})

// supabase/functions/send-whatsapp/index.ts
// Sends WhatsApp messages via 360dialog API

import { serve } from "https://deno.land/std@0.168.0/http/server.ts"

serve(async (req) => {
  const { to, message, template } = await req.json()
  const apiKey = Deno.env.get('DIALOG360_API_KEY')!

  const payload = template ? {
    // Template message (for first-contact / automated)
    messaging_product: "whatsapp",
    to,
    type: "template",
    template: { name: template.name, language: { code: "en" }, components: template.components }
  } : {
    // Free-form message (within 24hr window)
    messaging_product: "whatsapp",
    to,
    type: "text",
    text: { body: message }
  }

  const res = await fetch(
    'https://waba.360dialog.io/v1/messages',
    {
      method: 'POST',
      headers: { 'D360-API-KEY': apiKey, 'Content-Type': 'application/json' },
      body: JSON.stringify(payload)
    }
  )

  const result = await res.json()
  return new Response(JSON.stringify(result), {
    headers: { 'Content-Type': 'application/json' }
  })
})

// supabase/functions/send-email/index.ts

import { serve } from "https://deno.land/std@0.168.0/http/server.ts"

serve(async (req) => {
  const { to, subject, html, text } = await req.json()

  const res = await fetch('https://api.sendgrid.com/v3/mail/send', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${Deno.env.get('SENDGRID_API_KEY')}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      personalizations: [{ to: [{ email: to }] }],
      from: {
        email: 'hilton@hrihinvestments.co.za',
        name: 'Hilton Hartnick — HRIH Investments'
      },
      subject,
      content: [
        { type: 'text/plain', value: text || subject },
        { type: 'text/html', value: html }
      ]
    })
  })

  return new Response(
    JSON.stringify({ success: res.ok, status: res.status }),
    { headers: { 'Content-Type': 'application/json' } }
  )
})

// supabase/functions/sync-drive/index.ts
// Lists files from a shared Google Drive folder and upserts to products table

import { serve } from "https://deno.land/std@0.168.0/http/server.ts"
import { createClient } from "https://esm.sh/@supabase/supabase-js@2"

serve(async (req) => {
  const { folder_id } = await req.json()
  const apiKey = Deno.env.get('GOOGLE_API_KEY')!

  // List files in folder
  const driveRes = await fetch(
    `https://www.googleapis.com/drive/v3/files?q='${folder_id}'+in+parents&key=${apiKey}&fields=files(id,name,mimeType,size,webViewLink,thumbnailLink)`
  )
  const { files } = await driveRes.json()

  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  )

  // Map Drive files to products
  const products = files.map((f: any) => ({
    name: f.name.replace(/\.[^.]+$/, ''),  // Strip extension
    slug: f.name.toLowerCase().replace(/\s+/g, '-').replace(/[^a-z0-9-]/g, ''),
    category: guessCategory(f.mimeType, f.name),
    drive_url: f.webViewLink,
    thumbnail: f.thumbnailLink,
    source: 'drive',
    is_active: true
  }))

  const { data, error } = await supabase
    .from('products')
    .upsert(products, { onConflict: 'slug' })

  return new Response(
    JSON.stringify({ synced: products.length, error }),
    { headers: { 'Content-Type': 'application/json' } }
  )
})

function guessCategory(mime: string, name: string): string {
  if (mime.includes('video')) return 'video'
  if (mime.includes('pdf') || name.includes('ebook')) return 'ebook'
  if (mime.includes('spreadsheet')) return 'template'
  if (name.includes('software') || name.includes('.exe')) return 'software'
  return 'ebook'
}

# ── SUPABASE ─────────────────────────────────────────
NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGci...your-anon-key
SUPABASE_SERVICE_ROLE_KEY=eyJhbGci...your-service-role-key

# ── PAYFAST (ZAR Payments) ────────────────────────────
PAYFAST_MERCHANT_ID=your-merchant-id
PAYFAST_MERCHANT_KEY=your-merchant-key
PAYFAST_PASSPHRASE=your-passphrase
PAYFAST_SANDBOX=false  # true for testing

# ── STRIPE (International) ────────────────────────────
STRIPE_SECRET_KEY=sk_live_...
STRIPE_PUBLISHABLE_KEY=pk_live_...
STRIPE_WEBHOOK_SECRET=whsec_...

# ── SENDGRID (Email) ──────────────────────────────────
SENDGRID_API_KEY=SG.your-api-key
FROM_EMAIL=hilton@hrihinvestments.co.za
FROM_NAME=Hilton Hartnick — HRIH Investments

# ── WHATSAPP (360dialog) ──────────────────────────────
DIALOG360_API_KEY=your-360dialog-api-key
WA_BUSINESS_NUMBER=27610776410

# ── GOOGLE ────────────────────────────────────────────
GOOGLE_API_KEY=AIza...your-google-api-key
GOOGLE_CLIENT_ID=your-client-id.apps.googleusercontent.com
GOOGLE_CLIENT_SECRET=your-client-secret

# ── SCRAPERAPI ────────────────────────────────────────
SCRAPERAPI_KEY=your-scraperapi-key

# ── APP CONFIG ───────────────────────────────────────
SITE_URL=https://members.hrihinvestments.co.za
COMPANY_NAME=HRIH Investments (Pty) Ltd
COMPANY_REG=2015/036586/07
COMPANY_PHONE=+27 61 077 6410
COMPANY_ADDRESS=24 Siegelaar Street, Railton, Swellendam, 6740
VAT_RATE=0.15

# Install Supabase CLI
npm install -g supabase

# Login and link your project
supabase login
supabase link --project-ref your-project-ref

# Set secrets for edge functions
supabase secrets set SENDGRID_API_KEY=SG.xxx
supabase secrets set DIALOG360_API_KEY=xxx
supabase secrets set PAYFAST_MERCHANT_ID=xxx
supabase secrets set GOOGLE_API_KEY=AIza...
supabase secrets set SCRAPERAPI_KEY=xxx

# Deploy all edge functions
supabase functions deploy payfast-webhook
supabase functions deploy send-whatsapp
supabase functions deploy send-email
supabase functions deploy sync-drive