-- 1. Country / currency support -------------------------------------------------
DO $$ BEGIN
  CREATE TYPE public.country_code AS ENUM ('NG','US','GB');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE public.invoice_status AS ENUM ('unpaid','partially_paid','paid','overdue','void');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE public.subscription_plan AS ENUM ('free_trial','monthly','yearly');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE public.subscription_status AS ENUM ('trialing','active','past_due','canceled','expired');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

ALTER TYPE public.quote_status ADD VALUE IF NOT EXISTS 'viewed';
ALTER TYPE public.quote_status ADD VALUE IF NOT EXISTS 'expired';

ALTER TABLE public.companies
  ADD COLUMN IF NOT EXISTS country public.country_code NOT NULL DEFAULT 'NG',
  ADD COLUMN IF NOT EXISTS currency text NOT NULL DEFAULT 'NGN',
  ADD COLUMN IF NOT EXISTS state_region text,
  ADD COLUMN IF NOT EXISTS lga text,
  ADD COLUMN IF NOT EXISTS city text,
  ADD COLUMN IF NOT EXISTS postcode text,
  ADD COLUMN IF NOT EXISTS street_address text,
  ADD COLUMN IF NOT EXISTS bank_name text,
  ADD COLUMN IF NOT EXISTS bank_account_name text,
  ADD COLUMN IF NOT EXISTS bank_account_number text;

ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS country public.country_code NOT NULL DEFAULT 'NG',
  ADD COLUMN IF NOT EXISTS state_region text,
  ADD COLUMN IF NOT EXISTS lga text,
  ADD COLUMN IF NOT EXISTS city text,
  ADD COLUMN IF NOT EXISTS postcode text,
  ADD COLUMN IF NOT EXISTS street_address text;

-- 2. Quotation workflow ----------------------------------------------------------
ALTER TABLE public.quotations
  ADD COLUMN IF NOT EXISTS company_id uuid REFERENCES public.companies(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS scan_id uuid REFERENCES public.mold_scans(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS country public.country_code NOT NULL DEFAULT 'NG',
  ADD COLUMN IF NOT EXISTS currency text NOT NULL DEFAULT 'NGN',
  ADD COLUMN IF NOT EXISTS customer_name text,
  ADD COLUMN IF NOT EXISTS customer_email text,
  ADD COLUMN IF NOT EXISTS customer_phone text,
  ADD COLUMN IF NOT EXISTS property_address text,
  ADD COLUMN IF NOT EXISTS subtotal numeric(14,2) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS discount numeric(14,2) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS vat_rate numeric(5,2) NOT NULL DEFAULT 7.5,
  ADD COLUMN IF NOT EXISTS vat_amount numeric(14,2) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS valid_until date,
  ADD COLUMN IF NOT EXISTS sent_at timestamptz,
  ADD COLUMN IF NOT EXISTS viewed_at timestamptz,
  ADD COLUMN IF NOT EXISTS accepted_at timestamptz,
  ADD COLUMN IF NOT EXISTS rejected_at timestamptz,
  ADD COLUMN IF NOT EXISTS expired_at timestamptz;

CREATE TABLE IF NOT EXISTS public.quotation_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  quotation_id uuid NOT NULL REFERENCES public.quotations(id) ON DELETE CASCADE,
  from_status text,
  to_status text NOT NULL,
  actor_id uuid,
  note text,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.quotation_events TO authenticated;
GRANT ALL ON public.quotation_events TO service_role;
ALTER TABLE public.quotation_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Owners and admins read quotation history" ON public.quotation_events
  FOR SELECT TO authenticated USING (
    public.has_role(auth.uid(), 'admin')
    OR EXISTS (SELECT 1 FROM public.quotations q WHERE q.id = quotation_id AND q.user_id = auth.uid())
    OR EXISTS (
      SELECT 1 FROM public.quotations q
      JOIN public.company_members cm ON cm.company_id = q.company_id
      WHERE q.id = quotation_id AND cm.user_id = auth.uid()
    )
  );

-- 3. Invoices --------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.invoices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  invoice_no text NOT NULL UNIQUE,
  quotation_id uuid REFERENCES public.quotations(id) ON DELETE SET NULL,
  company_id uuid REFERENCES public.companies(id) ON DELETE SET NULL,
  user_id uuid NOT NULL,
  country public.country_code NOT NULL DEFAULT 'NG',
  currency text NOT NULL DEFAULT 'NGN',
  customer_name text,
  customer_email text,
  customer_phone text,
  property_address text,
  subtotal numeric(14,2) NOT NULL DEFAULT 0,
  discount numeric(14,2) NOT NULL DEFAULT 0,
  vat_rate numeric(5,2) NOT NULL DEFAULT 7.5,
  vat_amount numeric(14,2) NOT NULL DEFAULT 0,
  total numeric(14,2) NOT NULL DEFAULT 0,
  amount_paid numeric(14,2) NOT NULL DEFAULT 0,
  status public.invoice_status NOT NULL DEFAULT 'unpaid',
  due_date date NOT NULL DEFAULT (current_date + 14),
  bank_name text,
  bank_account_name text,
  bank_account_number text,
  notes text,
  pdf_path text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE ON public.invoices TO authenticated;
GRANT ALL ON public.invoices TO service_role;
ALTER TABLE public.invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Customers read own invoices" ON public.invoices
  FOR SELECT TO authenticated USING (
    user_id = auth.uid()
    OR public.has_role(auth.uid(), 'admin')
    OR EXISTS (SELECT 1 FROM public.company_members cm WHERE cm.company_id = invoices.company_id AND cm.user_id = auth.uid())
  );
CREATE POLICY "Providers and admins create invoices" ON public.invoices
  FOR INSERT TO authenticated WITH CHECK (
    public.has_role(auth.uid(), 'admin')
    OR EXISTS (SELECT 1 FROM public.company_members cm WHERE cm.company_id = invoices.company_id AND cm.user_id = auth.uid())
  );
CREATE POLICY "Providers and admins update invoices" ON public.invoices
  FOR UPDATE TO authenticated USING (
    public.has_role(auth.uid(), 'admin')
    OR EXISTS (SELECT 1 FROM public.company_members cm WHERE cm.company_id = invoices.company_id AND cm.user_id = auth.uid())
  );

CREATE TABLE IF NOT EXISTS public.invoice_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  invoice_id uuid NOT NULL REFERENCES public.invoices(id) ON DELETE CASCADE,
  description text NOT NULL,
  quantity numeric(10,2) NOT NULL DEFAULT 1,
  unit_price numeric(14,2) NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.invoice_items TO authenticated;
GRANT ALL ON public.invoice_items TO service_role;
ALTER TABLE public.invoice_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Invoice items follow the invoice" ON public.invoice_items
  FOR ALL TO authenticated USING (
    EXISTS (
      SELECT 1 FROM public.invoices i WHERE i.id = invoice_id AND (
        i.user_id = auth.uid()
        OR public.has_role(auth.uid(), 'admin')
        OR EXISTS (SELECT 1 FROM public.company_members cm WHERE cm.company_id = i.company_id AND cm.user_id = auth.uid())
      )
    )
  ) WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.invoices i WHERE i.id = invoice_id AND (
        public.has_role(auth.uid(), 'admin')
        OR EXISTS (SELECT 1 FROM public.company_members cm WHERE cm.company_id = i.company_id AND cm.user_id = auth.uid())
      )
    )
  );

CREATE TRIGGER invoices_touch BEFORE UPDATE ON public.invoices
  FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- 4. Provider subscriptions ------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.subscriptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL UNIQUE REFERENCES public.companies(id) ON DELETE CASCADE,
  plan public.subscription_plan NOT NULL DEFAULT 'free_trial',
  status public.subscription_status NOT NULL DEFAULT 'trialing',
  trial_ends_at timestamptz NOT NULL DEFAULT (now() + interval '1 day'),
  current_period_end timestamptz,
  testing_mode boolean NOT NULL DEFAULT true,
  amount numeric(14,2) NOT NULL DEFAULT 0,
  currency text NOT NULL DEFAULT 'NGN',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE ON public.subscriptions TO authenticated;
GRANT ALL ON public.subscriptions TO service_role;
ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Members and admins read subscriptions" ON public.subscriptions
  FOR SELECT TO authenticated USING (
    public.has_role(auth.uid(), 'admin')
    OR EXISTS (SELECT 1 FROM public.company_members cm WHERE cm.company_id = subscriptions.company_id AND cm.user_id = auth.uid())
  );
CREATE POLICY "Members and admins manage subscriptions" ON public.subscriptions
  FOR UPDATE TO authenticated USING (
    public.has_role(auth.uid(), 'admin')
    OR EXISTS (SELECT 1 FROM public.company_members cm WHERE cm.company_id = subscriptions.company_id AND cm.user_id = auth.uid())
  );
CREATE POLICY "Members and admins start subscriptions" ON public.subscriptions
  FOR INSERT TO authenticated WITH CHECK (
    public.has_role(auth.uid(), 'admin')
    OR EXISTS (SELECT 1 FROM public.company_members cm WHERE cm.company_id = subscriptions.company_id AND cm.user_id = auth.uid())
  );

CREATE TRIGGER subscriptions_touch BEFORE UPDATE ON public.subscriptions
  FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- Start every new provider on a 1-day free trial automatically.
CREATE OR REPLACE FUNCTION public.start_company_trial()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  INSERT INTO public.subscriptions (company_id, plan, status, trial_ends_at, currency)
  VALUES (NEW.id, 'free_trial', 'trialing', now() + interval '1 day', COALESCE(NEW.currency, 'NGN'))
  ON CONFLICT (company_id) DO NOTHING;
  RETURN NEW;
END $$;

DROP TRIGGER IF EXISTS companies_start_trial ON public.companies;
CREATE TRIGGER companies_start_trial AFTER INSERT ON public.companies
  FOR EACH ROW EXECUTE FUNCTION public.start_company_trial();

INSERT INTO public.subscriptions (company_id, plan, status, trial_ends_at)
SELECT c.id, 'free_trial', 'trialing', now() + interval '1 day' FROM public.companies c
ON CONFLICT (company_id) DO NOTHING;

INSERT INTO public.app_settings (key, value) VALUES ('subscriptions_live', 'false'::jsonb)
ON CONFLICT (key) DO NOTHING;