CREATE TYPE public.app_role AS ENUM ('owner', 'admin', 'finance_manager', 'analyst', 'viewer');
CREATE TYPE public.invoice_status AS ENUM ('draft', 'sent', 'paid', 'partially_paid', 'overdue');
CREATE TYPE public.payment_status AS ENUM ('completed', 'pending', 'failed');
CREATE TYPE public.reconciliation_status AS ENUM ('matched', 'unmatched', 'needs_review');

CREATE TABLE public.profiles (
  id uuid PRIMARY KEY,
  full_name text NOT NULL CHECK (char_length(full_name) BETWEEN 2 AND 100),
  work_email text NOT NULL CHECK (char_length(work_email) <= 255),
  job_title text CHECK (char_length(job_title) <= 100),
  country text CHECK (char_length(country) <= 100),
  avatar_url text,
  account_status text NOT NULL DEFAULT 'active' CHECK (account_status IN ('active','invited','suspended')),
  notification_preferences jsonb NOT NULL DEFAULT '{"payment_updates":true,"risk_alerts":true,"weekly_summary":true}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE ON public.profiles TO authenticated;
GRANT ALL ON public.profiles TO service_role;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "profiles_read_own" ON public.profiles FOR SELECT TO authenticated USING (id = auth.uid());
CREATE POLICY "profiles_update_own" ON public.profiles FOR UPDATE TO authenticated USING (id = auth.uid()) WITH CHECK (id = auth.uid());

CREATE TABLE public.workspaces (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL CHECK (char_length(name) BETWEEN 2 AND 120),
  country text NOT NULL CHECK (char_length(country) <= 100),
  created_by uuid NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE ON public.workspaces TO authenticated;
GRANT ALL ON public.workspaces TO service_role;
ALTER TABLE public.workspaces ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  workspace_id uuid NOT NULL REFERENCES public.workspaces(id) ON DELETE CASCADE,
  role public.app_role NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, workspace_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 "roles_read_own" ON public.user_roles FOR SELECT TO authenticated USING (user_id = auth.uid());

CREATE OR REPLACE FUNCTION public.is_workspace_member(_workspace_id uuid, _user_id uuid DEFAULT auth.uid())
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE workspace_id = _workspace_id AND user_id = _user_id)
$$;
GRANT EXECUTE ON FUNCTION public.is_workspace_member(uuid, uuid) TO authenticated;

CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _workspace_id uuid, _roles 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 workspace_id = _workspace_id AND role = ANY(_roles))
$$;
GRANT EXECUTE ON FUNCTION public.has_role(uuid, uuid, public.app_role[]) TO authenticated;

CREATE POLICY "workspaces_member_read" ON public.workspaces FOR SELECT TO authenticated USING (public.is_workspace_member(id));
CREATE POLICY "workspaces_creator_insert" ON public.workspaces FOR INSERT TO authenticated WITH CHECK (created_by = auth.uid());
CREATE POLICY "workspaces_admin_update" ON public.workspaces FOR UPDATE TO authenticated USING (public.has_role(auth.uid(), id, ARRAY['owner','admin']::public.app_role[]));

CREATE TABLE public.customers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL REFERENCES public.workspaces(id) ON DELETE CASCADE,
  company_name text NOT NULL CHECK (char_length(company_name) <= 120), contact_name text, email text, phone text,
  total_invoiced numeric(14,2) NOT NULL DEFAULT 0, total_paid numeric(14,2) NOT NULL DEFAULT 0,
  outstanding_balance numeric(14,2) NOT NULL DEFAULT 0, average_payment_days integer NOT NULL DEFAULT 0,
  risk_score integer NOT NULL DEFAULT 80 CHECK (risk_score BETWEEN 0 AND 100), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.customers TO authenticated; GRANT ALL ON public.customers TO service_role;
ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;
CREATE POLICY "customers_workspace_access" ON public.customers FOR ALL TO authenticated USING (public.is_workspace_member(workspace_id)) WITH CHECK (public.is_workspace_member(workspace_id));

CREATE TABLE public.invoices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL REFERENCES public.workspaces(id) ON DELETE CASCADE,
  customer_id uuid REFERENCES public.customers(id) ON DELETE SET NULL, invoice_number text NOT NULL,
  customer_name text NOT NULL, amount numeric(14,2) NOT NULL CHECK (amount >= 0), issue_date date NOT NULL, due_date date NOT NULL,
  status public.invoice_status NOT NULL DEFAULT 'draft', notes text CHECK (char_length(notes) <= 1000),
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), UNIQUE(workspace_id, invoice_number)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.invoices TO authenticated; GRANT ALL ON public.invoices TO service_role;
ALTER TABLE public.invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY "invoices_workspace_access" ON public.invoices FOR ALL TO authenticated USING (public.is_workspace_member(workspace_id)) WITH CHECK (public.is_workspace_member(workspace_id));

CREATE TABLE public.payments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL REFERENCES public.workspaces(id) ON DELETE CASCADE,
  invoice_id uuid REFERENCES public.invoices(id) ON DELETE SET NULL, payment_reference text NOT NULL,
  customer_name text NOT NULL, invoice_number text, amount numeric(14,2) NOT NULL CHECK (amount >= 0), paid_at timestamptz NOT NULL,
  method text NOT NULL, status public.payment_status NOT NULL DEFAULT 'completed', reconciliation public.reconciliation_status NOT NULL DEFAULT 'unmatched',
  created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.payments TO authenticated; GRANT ALL ON public.payments TO service_role;
ALTER TABLE public.payments ENABLE ROW LEVEL SECURITY;
CREATE POLICY "payments_workspace_access" ON public.payments FOR ALL TO authenticated USING (public.is_workspace_member(workspace_id)) WITH CHECK (public.is_workspace_member(workspace_id));

CREATE TABLE public.demo_requests (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), full_name text NOT NULL, work_email text NOT NULL, company_name text NOT NULL,
 job_title text NOT NULL, company_size text NOT NULL, country text NOT NULL, phone text, area_of_interest text NOT NULL,
 message text CHECK (char_length(message) <= 2000), created_at timestamptz NOT NULL DEFAULT now()
);
GRANT INSERT ON public.demo_requests TO anon, authenticated; GRANT ALL ON public.demo_requests TO service_role;
ALTER TABLE public.demo_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY "public_submit_demo" ON public.demo_requests FOR INSERT TO anon, authenticated WITH CHECK (true);

CREATE TABLE public.contact_requests (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text NOT NULL, email text NOT NULL, company text, subject text NOT NULL,
 message text NOT NULL CHECK (char_length(message) <= 2000), created_at timestamptz NOT NULL DEFAULT now()
);
GRANT INSERT ON public.contact_requests TO anon, authenticated; GRANT ALL ON public.contact_requests TO service_role;
ALTER TABLE public.contact_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY "public_submit_contact" ON public.contact_requests FOR INSERT TO anon, authenticated WITH CHECK (true);

CREATE OR REPLACE FUNCTION public.complete_onboarding(_full_name text, _work_email text, _company_name text, _job_title text, _country text)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _workspace_id uuid;
BEGIN
  IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Authentication required'; END IF;
  IF char_length(trim(_full_name)) < 2 OR char_length(trim(_company_name)) < 2 THEN RAISE EXCEPTION 'Invalid onboarding details'; END IF;
  INSERT INTO public.workspaces(name, country, created_by) VALUES(trim(_company_name), trim(_country), auth.uid()) RETURNING id INTO _workspace_id;
  INSERT INTO public.profiles(id, full_name, work_email, job_title, country) VALUES(auth.uid(), trim(_full_name), lower(trim(_work_email)), trim(_job_title), trim(_country)) ON CONFLICT(id) DO UPDATE SET full_name=excluded.full_name, job_title=excluded.job_title, country=excluded.country, updated_at=now();
  INSERT INTO public.user_roles(user_id, workspace_id, role) VALUES(auth.uid(), _workspace_id, 'owner');
  RETURN _workspace_id;
END $$;
GRANT EXECUTE ON FUNCTION public.complete_onboarding(text,text,text,text,text) TO authenticated;

CREATE OR REPLACE FUNCTION public.set_updated_at() RETURNS trigger LANGUAGE plpgsql SET search_path = public AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END $$;
CREATE TRIGGER profiles_updated BEFORE UPDATE ON public.profiles FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER workspaces_updated BEFORE UPDATE ON public.workspaces FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER customers_updated BEFORE UPDATE ON public.customers FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER invoices_updated BEFORE UPDATE ON public.invoices FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER payments_updated BEFORE UPDATE ON public.payments FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();