-- IMPORTANT: Run this script as the database user (e.g., peullate_admin) to ensure 
-- that all tables are owned by that user and not the cPanel superuser.

-- 1. PROFILES TABLE (Standalone PostgreSQL)
create table public.profiles (
  id serial primary key,
  email text not null unique,
  password_hash text, -- Added for local authentication
  pesapal_customer_id text,
  pesapal_tracking_id text, -- Changed from uuid to text
  pesapal_merchant_reference text, -- Merchant reference for reconciliation
  subscription_expiry timestamp with time zone, -- Expiration check for middleware
  -- Subscription Status: 'free' is default. 'past_due' allows for grace periods.
  subscription_status text check (subscription_status in ('active', 'past_due', 'canceled', 'free')) default 'free',
  -- Plan Tier: determines feature access
  plan_tier text check (plan_tier in ('free', 'pro', 'elite')) default 'free',
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- 2. PESAPAL SYNC LOG TABLE (Webhook Audit Trail)
create table public.pesapal_sync (
  id serial primary key,
  event_id text, -- Pesapal Event ID for deduplication
  event_type text not null, -- e.g., 'subscription.created', 'payment.succeeded'
  payload jsonb not null, -- Full webhook body for debugging
  processed boolean default false, -- Flag to track if your backend handled it
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- 3. PAYMENTS TABLE (Transaction History)
create table public.payments (
  id serial primary key,
  user_id integer references public.profiles(id) on delete set null,
  amount numeric(10, 2) not null,
  currency text default 'USD',
  status text check (status in ('PENDING', 'COMPLETED', 'FAILED')) default 'PENDING',
  reference text unique not null, -- Unique Payment Reference
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);