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

CREATE TABLE public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  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 TABLE public.jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  slug text NOT NULL UNIQUE,
  department text NOT NULL DEFAULT 'General',
  location text NOT NULL DEFAULT 'Boulder, Colorado',
  employment_type text NOT NULL DEFAULT 'Full-time',
  salary_range text,
  summary text NOT NULL DEFAULT '',
  description text NOT NULL DEFAULT '',
  requirements text NOT NULL DEFAULT '',
  benefits text NOT NULL DEFAULT '',
  how_to_apply text NOT NULL DEFAULT '',
  is_published boolean NOT NULL DEFAULT true,
  posted_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.jobs TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.jobs TO authenticated;
GRANT ALL ON public.jobs TO service_role;
ALTER TABLE public.jobs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Published jobs are public" ON public.jobs FOR SELECT TO anon, authenticated USING (is_published = true);
CREATE POLICY "Admins can read all jobs" ON public.jobs FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins can insert jobs" ON public.jobs FOR INSERT TO authenticated WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins can update jobs" ON public.jobs FOR UPDATE TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins can delete jobs" ON public.jobs FOR DELETE TO authenticated USING (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.job_applications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  job_id uuid REFERENCES public.jobs(id) ON DELETE SET NULL,
  full_name text NOT NULL,
  email text NOT NULL,
  phone text,
  cover_letter text NOT NULL DEFAULT '',
  resume_url text,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT INSERT ON public.job_applications TO anon;
GRANT SELECT, INSERT, DELETE ON public.job_applications TO authenticated;
GRANT ALL ON public.job_applications TO service_role;
ALTER TABLE public.job_applications ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can apply" ON public.job_applications FOR INSERT TO anon, authenticated WITH CHECK (true);
CREATE POLICY "Admins can read applications" ON public.job_applications FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins can delete applications" ON public.job_applications FOR DELETE TO authenticated USING (public.has_role(auth.uid(),'admin'));

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 TRIGGER update_jobs_updated_at BEFORE UPDATE ON public.jobs FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

INSERT INTO public.jobs (title, slug, department, location, employment_type, salary_range, summary, description, requirements, benefits, how_to_apply) VALUES
('Gallery Sales Associate','gallery-sales-associate','Retail','Boulder, Colorado — Pearl Street Mall','Full-time','$22 - $28 / hour + commission','Help collectors find their next antique map or print in our historic Pearl Street gallery.','As a Gallery Sales Associate you are the face of Art Source International. You will greet visitors, share the stories behind our 35,000+ original antique maps and 7,000 antique prints, guide customers through the collection, and complete sales both in the gallery and over the phone.','2+ years retail or gallery sales experience
A genuine interest in history, cartography or fine art
Excellent written and verbal communication
Comfortable with point-of-sale and inventory systems
Weekend availability','Health and dental contribution
Generous staff discount on maps, prints and globes
Paid time off and holidays
Ongoing training in antique cartography','Send your resume and a short note about your favourite historical period using the form on this page.'),
('Antique Print Cataloger','antique-print-cataloger','Collections','Boulder, Colorado','Part-time','$20 - $24 / hour','Research, measure, photograph and catalog antique prints for our online collection.','You will work alongside our collections team to bring thousands of uncatalogued prints online: verifying printmaker attributions, dating impressions, writing accurate descriptions, and preparing high-resolution photography for the website.','Background in art history, library science or a related field
Meticulous attention to detail and records
Experience handling fragile works on paper preferred
Confident writing clear, accurate descriptions','Flexible schedule
Hands-on work with rare original material
Mentorship from 45 years of institutional expertise','Apply through the form on this page and include a writing sample if you have one.'),
('Custom Framing Specialist','custom-framing-specialist','Framing Studio','Boulder, Colorado','Full-time','$24 - $32 / hour','Design and build conservation-quality frames for antique maps, prints and posters.','Our framing studio produces museum-grade, fully reversible framing for irreplaceable works. You will consult with clients on mouldings and mats, cut and join frames, and mount works using archival conservation techniques.','3+ years custom framing experience
Knowledge of conservation and archival mounting
Precision with mat cutting and joinery
Strong customer consultation skills','Full benefits package
Well-equipped studio
Staff discount on framing and inventory','Use the application form on this page — photos of past framing work are welcome.');REVOKE EXECUTE ON FUNCTION public.has_role(uuid, public.app_role) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.has_role(uuid, public.app_role) TO authenticated, service_role;-- Grant the admin role automatically to the first user who signs up so the
-- careers control panel is reachable after deployment.
CREATE OR REPLACE FUNCTION public.bootstrap_first_admin()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM public.user_roles WHERE role = 'admin') THEN
    INSERT INTO public.user_roles (user_id, role)
    VALUES (NEW.id, 'admin')
    ON CONFLICT (user_id, role) DO NOTHING;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS on_auth_user_created_bootstrap_admin ON auth.users;
CREATE TRIGGER on_auth_user_created_bootstrap_admin
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.bootstrap_first_admin();-- The bootstrap trigger function must only be executed by the auth trigger,
-- never through the Data API.
REVOKE ALL ON FUNCTION public.bootstrap_first_admin() FROM PUBLIC, anon, authenticated;CREATE TYPE public.app_role AS ENUM ('admin','user');

CREATE TABLE public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  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 TABLE public.jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  slug text NOT NULL UNIQUE,
  department text NOT NULL DEFAULT 'General',
  location text NOT NULL DEFAULT 'Boulder, Colorado',
  employment_type text NOT NULL DEFAULT 'Full-time',
  salary_range text,
  summary text NOT NULL DEFAULT '',
  description text NOT NULL DEFAULT '',
  requirements text NOT NULL DEFAULT '',
  benefits text NOT NULL DEFAULT '',
  how_to_apply text NOT NULL DEFAULT '',
  is_published boolean NOT NULL DEFAULT true,
  posted_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.jobs TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.jobs TO authenticated;
GRANT ALL ON public.jobs TO service_role;
ALTER TABLE public.jobs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Published jobs are public" ON public.jobs FOR SELECT TO anon, authenticated USING (is_published = true);
CREATE POLICY "Admins can read all jobs" ON public.jobs FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins can insert jobs" ON public.jobs FOR INSERT TO authenticated WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins can update jobs" ON public.jobs FOR UPDATE TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins can delete jobs" ON public.jobs FOR DELETE TO authenticated USING (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.job_applications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  job_id uuid REFERENCES public.jobs(id) ON DELETE SET NULL,
  full_name text NOT NULL,
  email text NOT NULL,
  phone text,
  cover_letter text NOT NULL DEFAULT '',
  resume_url text,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT INSERT ON public.job_applications TO anon;
GRANT SELECT, INSERT, DELETE ON public.job_applications TO authenticated;
GRANT ALL ON public.job_applications TO service_role;
ALTER TABLE public.job_applications ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can apply" ON public.job_applications FOR INSERT TO anon, authenticated WITH CHECK (true);
CREATE POLICY "Admins can read applications" ON public.job_applications FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins can delete applications" ON public.job_applications FOR DELETE TO authenticated USING (public.has_role(auth.uid(),'admin'));

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 TRIGGER update_jobs_updated_at BEFORE UPDATE ON public.jobs FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

INSERT INTO public.jobs (title, slug, department, location, employment_type, salary_range, summary, description, requirements, benefits, how_to_apply) VALUES
('Gallery Sales Associate','gallery-sales-associate','Retail','Boulder, Colorado — Pearl Street Mall','Full-time','$22 - $28 / hour + commission','Help collectors find their next antique map or print in our historic Pearl Street gallery.','As a Gallery Sales Associate you are the face of Art Source International. You will greet visitors, share the stories behind our 35,000+ original antique maps and 7,000 antique prints, guide customers through the collection, and complete sales both in the gallery and over the phone.','2+ years retail or gallery sales experience
A genuine interest in history, cartography or fine art
Excellent written and verbal communication
Comfortable with point-of-sale and inventory systems
Weekend availability','Health and dental contribution
Generous staff discount on maps, prints and globes
Paid time off and holidays
Ongoing training in antique cartography','Send your resume and a short note about your favourite historical period using the form on this page.'),
('Antique Print Cataloger','antique-print-cataloger','Collections','Boulder, Colorado','Part-time','$20 - $24 / hour','Research, measure, photograph and catalog antique prints for our online collection.','You will work alongside our collections team to bring thousands of uncatalogued prints online: verifying printmaker attributions, dating impressions, writing accurate descriptions, and preparing high-resolution photography for the website.','Background in art history, library science or a related field
Meticulous attention to detail and records
Experience handling fragile works on paper preferred
Confident writing clear, accurate descriptions','Flexible schedule
Hands-on work with rare original material
Mentorship from 45 years of institutional expertise','Apply through the form on this page and include a writing sample if you have one.'),
('Custom Framing Specialist','custom-framing-specialist','Framing Studio','Boulder, Colorado','Full-time','$24 - $32 / hour','Design and build conservation-quality frames for antique maps, prints and posters.','Our framing studio produces museum-grade, fully reversible framing for irreplaceable works. You will consult with clients on mouldings and mats, cut and join frames, and mount works using archival conservation techniques.','3+ years custom framing experience
Knowledge of conservation and archival mounting
Precision with mat cutting and joinery
Strong customer consultation skills','Full benefits package
Well-equipped studio
Staff discount on framing and inventory','Use the application form on this page — photos of past framing work are welcome.');

REVOKE EXECUTE ON FUNCTION public.has_role(uuid, public.app_role) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.has_role(uuid, public.app_role) TO authenticated, service_role;

CREATE OR REPLACE FUNCTION public.bootstrap_first_admin()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM public.user_roles WHERE role = 'admin') THEN
    INSERT INTO public.user_roles (user_id, role) VALUES (NEW.id, 'admin')
    ON CONFLICT (user_id, role) DO NOTHING;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS on_auth_user_created_bootstrap_admin ON auth.users;
CREATE TRIGGER on_auth_user_created_bootstrap_admin
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.bootstrap_first_admin();

REVOKE ALL ON FUNCTION public.bootstrap_first_admin() FROM PUBLIC, anon, authenticated;