CREATE TYPE public.app_role AS ENUM ('admin', 'editor', 'user');

CREATE TABLE public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role public.app_role NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, role)
);
GRANT SELECT ON public.user_roles TO authenticated;
GRANT ALL ON public.user_roles TO service_role;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can read own roles" ON public.user_roles FOR SELECT TO authenticated USING (auth.uid() = user_id);

CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role public.app_role)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND role = _role);
$$;

CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = public
AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;

CREATE TYPE public.lead_status AS ENUM ('new', 'contacted', 'qualified', 'archived');

CREATE TABLE public.contact_submissions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  email text NOT NULL,
  company text,
  message text NOT NULL,
  source text NOT NULL DEFAULT 'website',
  status public.lead_status NOT NULL DEFAULT 'new',
  internal_notes text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.contact_submissions TO authenticated;
GRANT ALL ON public.contact_submissions TO service_role;
ALTER TABLE public.contact_submissions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Admins can read submissions" ON public.contact_submissions FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Admins can update submissions" ON public.contact_submissions FOR UPDATE TO authenticated USING (public.has_role(auth.uid(), 'admin')) WITH CHECK (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Admins can delete submissions" ON public.contact_submissions FOR DELETE TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE TRIGGER contact_submissions_updated_at BEFORE UPDATE ON public.contact_submissions FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE TABLE public.blog_posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug text NOT NULL UNIQUE,
  title text NOT NULL,
  category text NOT NULL,
  excerpt text NOT NULL,
  content text NOT NULL,
  cover_image text,
  author text NOT NULL DEFAULT 'Gatavase Corporation',
  published boolean NOT NULL DEFAULT false,
  published_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.blog_posts TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.blog_posts TO authenticated;
GRANT ALL ON public.blog_posts TO service_role;
ALTER TABLE public.blog_posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Published posts are public" ON public.blog_posts FOR SELECT TO anon, authenticated USING (published = true);
CREATE POLICY "Admins read all posts" ON public.blog_posts FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'editor'));
CREATE POLICY "Admins insert posts" ON public.blog_posts FOR INSERT TO authenticated WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'editor'));
CREATE POLICY "Admins update posts" ON public.blog_posts FOR UPDATE TO authenticated USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'editor')) WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'editor'));
CREATE POLICY "Admins delete posts" ON public.blog_posts FOR DELETE TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE TRIGGER blog_posts_updated_at BEFORE UPDATE ON public.blog_posts FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

INSERT INTO public.blog_posts (slug, title, category, excerpt, content, published, published_at) VALUES
('ai-automation-for-african-enterprises', 'AI Automation for African Enterprises: Where to Start', 'AI Automation', 'A practical sequence for introducing AI automation into an existing business without disrupting operations.', E'Most organisations do not need a moonshot AI project. They need three or four repetitive workflows removed from human hands.\n\n## Start with the paperwork\nInvoice capture, KYC document review, and customer support triage are the highest-yield first automations. They are high volume, rule-adjacent, and easy to measure.\n\n## Measure before you build\nBaseline the current cost per transaction. If a workflow costs less than a few dollars per run and happens rarely, automate it later.\n\n## Keep a human in the loop\nGatavase deploys automation with review queues by default. Confidence thresholds route uncertain cases to a person, and the model improves from those corrections.\n\n## Deploy where the data lives\nFor regulated clients in Uganda and Qatar, we deploy inside the client environment so no customer record leaves their infrastructure.', true, now() - interval '21 days'),
('building-mobile-apps-for-low-bandwidth-markets', 'Building Mobile Apps That Work on Low Bandwidth', 'Mobile Apps', 'Offline-first architecture, aggressive caching, and USSD fallbacks — how we ship apps that work outside the city.', E'A mobile app that assumes constant connectivity fails the moment it leaves the capital.\n\n## Offline-first is the default\nWe treat the local database as the source of truth and synchronise opportunistically. Users create records offline and the queue drains when signal returns.\n\n## Budget your payloads\nEvery screen has a byte budget. Images are served responsively, lists paginate small, and analytics batch instead of firing per event.\n\n## Fall back gracefully\nFor critical flows such as payments and confirmations, an SMS or USSD path keeps the service usable on feature phones.\n\n## Test on real devices\nWe test on entry-level Android hardware over throttled networks before release, not on flagship phones over office WiFi.', true, now() - interval '14 days'),
('smart-contracts-beyond-the-hype', 'Smart Contracts Beyond the Hype: Real Uses We Ship', 'Blockchain', 'Where blockchain genuinely earns its place — provenance, settlement, and tokenised assets — and where it does not.', E'Blockchain is a coordination tool, not a database upgrade. It earns its place when several parties who do not fully trust each other need one shared record.\n\n## Provenance and supply chain\nAgricultural exports benefit from a tamper-evident chain of custody from farm to port. Buyers verify origin without trusting an intermediary.\n\n## Settlement between institutions\nCross-border settlement between partners in different jurisdictions is faster and cheaper on-chain than through correspondent banking.\n\n## Tokenised assets\nFractional ownership of real assets requires a registry that both issuer and holder can audit.\n\n## When not to use it\nIf one organisation controls the data and the writes, use Postgres. We tell clients this before they spend money.', true, now() - interval '7 days'),
('technology-with-a-social-return', 'Technology With a Social Return', 'Social Impact', 'How Destiny & Divine Foundation turns Gatavase engineering capacity into community outcomes.', E'Gatavase commits engineering capacity to community programmes through Destiny & Divine Foundation.\n\n## Digital skills training\nComputer training programmes take young people from basic literacy to employable technical skills, with graduates recruited into our own delivery teams.\n\n## Tools for community organisations\nSchools, clinics, and NGOs receive software built and maintained at no cost — attendance systems, records management, and reporting tools.\n\n## Measure the outcome, not the donation\nWe report graduates placed, organisations supported, and systems still running a year later — not hours donated.', true, now() - interval '3 days');