-- Run this SQL in your Truehost PostgreSQL Database via phpPgAdmin

-- 1. USERS TABLE
create type user_role as enum ('owner', 'visitor', 'admin');
create table public.users (
  id serial primary key,
  email text unique not null,
  role user_role default 'visitor',
  full_name text,
  password_hash text, -- Added for real auth
  created_at timestamp with time zone default now() not null
);

-- 2. SUBSCRIPTIONS TABLE
create type sub_tier as enum ('free', 'premium', 'elite');
create table public.subscriptions (
  id serial primary key,
  user_id integer references public.users(id) on delete cascade not null,
  tier sub_tier default 'free',
  start_date timestamp with time zone default now() not null,
  end_date timestamp with time zone,
  is_active boolean default true,
  payment_provider text,
  payment_reference text,
  updated_at timestamp with time zone default now(),
  constraint unique_active_sub unique (user_id)
);

-- 3. BUSINESSES TABLE
create table public.businesses (
  id serial primary key,
  owner_id integer references public.users(id) on delete set null,
  name text not null,
  description text,
  address_nanyuki text not null,
  whatsapp text,
  website text,
  image_url text,
  category text,
  click_count integer default 0,
  featured boolean default false,
  gallery jsonb default '[]'::jsonb,
  created_at timestamp with time zone default now() not null
);

-- 4. REVIEWS TABLE
create table public.reviews (
  id serial primary key,
  business_id integer references public.businesses(id) on delete cascade not null,
  user_id integer references public.users(id) on delete set null,
  rating integer check (rating >= 1 and rating <= 5) not null,
  comment text,
  created_at timestamp with time zone default now() not null
);

-- 5. Insert Mock Data (Optional)
INSERT INTO businesses (name, description, address_nanyuki, category, click_count, featured) VALUES 
('Cedar Mall Coffee House', 'The best roasted coffee in the heart of Nanyuki.', 'Cedar Mall, Nanyuki CBD', 'Hospitality', 142, true),
('Nanyuki Hardware', 'All your building needs under one roof.', 'Industrial Area', 'Retail', 45, false);