-- ============================================================
-- Thinulaka Uttamachara 2026 - nomination system (stage 1-3 schema)
-- Additive only: no column is dropped or retyped, so the live site keeps working.
-- ============================================================

-- 1. Relax the checks that no longer match the approved application model.
--    Age is replaced by birth year; email becomes optional; the narrative may be long.
ALTER TABLE public.nominations DROP CONSTRAINT IF EXISTS nominations_age_check;
ALTER TABLE public.nominations ADD CONSTRAINT nominations_age_check CHECK (age >= 0 AND age <= 120);
ALTER TABLE public.nominations DROP CONSTRAINT IF EXISTS nominations_nominator_email_check;
ALTER TABLE public.nominations ADD CONSTRAINT nominations_nominator_email_check CHECK (char_length(nominator_email) <= 255);
ALTER TABLE public.nominations DROP CONSTRAINT IF EXISTS nominations_description_check;
ALTER TABLE public.nominations ADD CONSTRAINT nominations_description_check CHECK (char_length(description) <= 20000);

-- 2. New columns (nullable or defaulted - existing inserts keep working).
ALTER TABLE public.nominations
  ADD COLUMN IF NOT EXISTS status text NOT NULL DEFAULT 'submitted',
  ADD COLUMN IF NOT EXISTS created_by uuid REFERENCES public.profiles(id) ON DELETE CASCADE,
  ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now(),
  ADD COLUMN IF NOT EXISTS birth_year integer,
  ADD COLUMN IF NOT EXISTS apply_as text,
  ADD COLUMN IF NOT EXISTS nominee_common_name text,
  ADD COLUMN IF NOT EXISTS gender text,
  ADD COLUMN IF NOT EXISTS address text,
  ADD COLUMN IF NOT EXISTS secretariat text,
  ADD COLUMN IF NOT EXISTS nominee_email text,
  ADD COLUMN IF NOT EXISTS profession text,
  ADD COLUMN IF NOT EXISTS organisation text,
  ADD COLUMN IF NOT EXISTS service_period text,
  ADD COLUMN IF NOT EXISTS service_years_note text,
  ADD COLUMN IF NOT EXISTS secondary_fields text,
  ADD COLUMN IF NOT EXISTS story_life text,
  ADD COLUMN IF NOT EXISTS story_service text,
  ADD COLUMN IF NOT EXISTS story_impact text,
  ADD COLUMN IF NOT EXISTS story_challenges text,
  ADD COLUMN IF NOT EXISTS story_example text,
  ADD COLUMN IF NOT EXISTS story_why text,
  ADD COLUMN IF NOT EXISTS story_moment text,
  ADD COLUMN IF NOT EXISTS referrer1_name text,
  ADD COLUMN IF NOT EXISTS referrer1_role text,
  ADD COLUMN IF NOT EXISTS referrer1_phone text,
  ADD COLUMN IF NOT EXISTS referrer2_name text,
  ADD COLUMN IF NOT EXISTS referrer2_role text,
  ADD COLUMN IF NOT EXISTS referrer2_phone text,
  ADD COLUMN IF NOT EXISTS nominator_address text,
  ADD COLUMN IF NOT EXISTS nominator_relation text,
  ADD COLUMN IF NOT EXISTS nominator_knowledge text,
  ADD COLUMN IF NOT EXISTS consent_truth boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS consent_permission boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS consent_privacy boolean NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS permission_pending boolean NOT NULL DEFAULT false;

ALTER TABLE public.nominations DROP CONSTRAINT IF EXISTS nominations_status_check;
ALTER TABLE public.nominations ADD CONSTRAINT nominations_status_check CHECK (status IN ('draft','submitted','initial_review','more_info','verifying','committee','selected','not_selected'));
ALTER TABLE public.nominations DROP CONSTRAINT IF EXISTS nominations_birth_year_check;
ALTER TABLE public.nominations ADD CONSTRAINT nominations_birth_year_check CHECK (birth_year IS NULL OR (birth_year >= 1900 AND birth_year <= 2026));
ALTER TABLE public.nominations DROP CONSTRAINT IF EXISTS nominations_apply_as_check;
ALTER TABLE public.nominations ADD CONSTRAINT nominations_apply_as_check CHECK (apply_as IS NULL OR apply_as IN ('self','other','organisation'));

CREATE INDEX IF NOT EXISTS nominations_created_by_idx ON public.nominations (created_by);
CREATE INDEX IF NOT EXISTS nominations_status_idx ON public.nominations (status);

-- 3. Draft applications - one per signed-in organizer/applicant account.
CREATE TABLE public.nomination_drafts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_by uuid NOT NULL UNIQUE REFERENCES public.profiles(id) ON DELETE CASCADE,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  step integer NOT NULL DEFAULT 1,
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.nomination_drafts TO authenticated;
GRANT ALL ON public.nomination_drafts TO service_role;
ALTER TABLE public.nomination_drafts ENABLE ROW LEVEL SECURITY;
CREATE POLICY nomination_drafts_own ON public.nomination_drafts FOR ALL TO authenticated
  USING (created_by = auth.uid()) WITH CHECK (created_by = auth.uid());

-- 4. Evidence records. nomination_id stays empty until the application is submitted.
CREATE TABLE public.nomination_evidence (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_by uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  nomination_id uuid REFERENCES public.nominations(id) ON DELETE CASCADE,
  storage_path text NOT NULL UNIQUE,
  file_name text NOT NULL,
  mime_type text NOT NULL,
  byte_size integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, DELETE ON public.nomination_evidence TO authenticated;
GRANT ALL ON public.nomination_evidence TO service_role;
ALTER TABLE public.nomination_evidence ENABLE ROW LEVEL SECURITY;
CREATE POLICY evidence_owner_insert ON public.nomination_evidence FOR INSERT TO authenticated WITH CHECK (created_by = auth.uid());
CREATE POLICY evidence_owner_select ON public.nomination_evidence FOR SELECT TO authenticated USING (created_by = auth.uid());
CREATE POLICY evidence_owner_delete ON public.nomination_evidence FOR DELETE TO authenticated USING (created_by = auth.uid());
CREATE POLICY evidence_organizer_select ON public.nomination_evidence FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'organizer') OR public.has_role(auth.uid(), 'admin'));
CREATE INDEX IF NOT EXISTS evidence_nomination_idx ON public.nomination_evidence (nomination_id);

-- 5. Row level security on the applications themselves.
DROP POLICY IF EXISTS "Anyone can submit a nomination" ON public.nominations;
CREATE POLICY nominations_anon_submit ON public.nominations FOR INSERT TO anon
  WITH CHECK (created_by IS NULL AND status = 'submitted');
CREATE POLICY nominations_owner_insert ON public.nominations FOR INSERT TO authenticated
  WITH CHECK (created_by = auth.uid());
CREATE POLICY nominations_owner_select ON public.nominations FOR SELECT TO authenticated
  USING (created_by = auth.uid());
-- A signed-in owner may keep their own draft in progress; submitted rows are read-only to them.
CREATE POLICY nominations_owner_update ON public.nominations FOR UPDATE TO authenticated
  USING (created_by = auth.uid() AND status = 'draft') WITH CHECK (created_by = auth.uid() AND status = 'draft');

-- 6. Private evidence store: only the uploader and organizers/admins can read it.
CREATE POLICY evidence_upload ON storage.objects FOR INSERT TO authenticated
  WITH CHECK (bucket_id = 'nomination-evidence'
    AND (storage.foldername(name))[1] = auth.uid()::text
    AND lower(storage.extension(name)) IN ('jpg','jpeg','png','webp','pdf'));
CREATE POLICY evidence_read ON storage.objects FOR SELECT TO authenticated
  USING (bucket_id = 'nomination-evidence'
    AND ((storage.foldername(name))[1] = auth.uid()::text
      OR public.has_role(auth.uid(), 'organizer')
      OR public.has_role(auth.uid(), 'admin')));
CREATE POLICY evidence_delete ON storage.objects FOR DELETE TO authenticated
  USING (bucket_id = 'nomination-evidence' AND (storage.foldername(name))[1] = auth.uid()::text);

-- 7. Submission: writes the application, links the evidence, clears the draft,
--    and refuses an accidental second copy of the same application within 24 hours.
CREATE OR REPLACE FUNCTION public.submit_nomination_full(p jsonb)
RETURNS text
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $fn$
DECLARE
  uid uuid := auth.uid();
  r text;
  dup text;
BEGIN
  IF coalesce(p->>'nominee_name','') = '' OR coalesce(p->>'nominator_phone','') = '' THEN
    RAISE EXCEPTION 'incomplete';
  END IF;

  SELECT reference INTO dup
  FROM public.nominations
  WHERE status <> 'draft'
    AND lower(btrim(nominee_name)) = lower(btrim(coalesce(p->>'nominee_name','')))
    AND regexp_replace(nominator_phone, '[^0-9]', '', 'g') = regexp_replace(coalesce(p->>'nominator_phone',''), '[^0-9]', '', 'g')
    AND category = coalesce(p->>'category','')
    AND created_at > now() - interval '24 hours'
  ORDER BY created_at DESC
  LIMIT 1;
  IF dup IS NOT NULL THEN
    RAISE EXCEPTION 'duplicate:%', dup;
  END IF;

  INSERT INTO public.nominations (
    status, created_by, lang, reference,
    nominee_name, age, birth_year, apply_as, nominee_common_name, gender, address, district, secretariat,
    nominee_contact, nominee_email, profession, organisation, service_period, years, service_years_note,
    category, secondary_fields, description,
    story_life, story_service, story_impact, story_challenges, story_example, story_why, story_moment,
    referrer1_name, referrer1_role, referrer1_phone,
    referrer2_name, referrer2_role, referrer2_phone,
    nominator_name, nominator_phone, nominator_email, nominator_address, nominator_relation, nominator_knowledge,
    consent_truth, consent_permission, consent_privacy, permission_pending
  ) VALUES (
    'submitted', uid, coalesce(nullif(p->>'lang',''),'si'),
    default,
    left(coalesce(p->>'nominee_name',''),200),
    coalesce((p->>'age')::int, 0),
    nullif(p->>'birth_year','')::int,
    nullif(p->>'apply_as',''),
    nullif(p->>'nominee_common_name',''),
    nullif(p->>'gender',''),
    nullif(p->>'address',''),
    left(coalesce(nullif(p->>'district',''),''),100),
    nullif(p->>'secretariat',''),
    left(coalesce(nullif(p->>'nominee_contact',''),''),300),
    nullif(p->>'nominee_email',''),
    nullif(p->>'profession',''),
    nullif(p->>'organisation',''),
    nullif(p->>'service_period',''),
    coalesce((p->>'years')::int, 0),
    nullif(p->>'service_years_note',''),
    left(coalesce(nullif(p->>'category',''),''),200),
    nullif(p->>'secondary_fields',''),
    left(coalesce(nullif(p->>'description',''),''),20000),
    nullif(p->>'story_life',''), nullif(p->>'story_service',''), nullif(p->>'story_impact',''),
    nullif(p->>'story_challenges',''), nullif(p->>'story_example',''), nullif(p->>'story_why',''),
    nullif(p->>'story_moment',''),
    nullif(p->>'referrer1_name',''), nullif(p->>'referrer1_role',''), nullif(p->>'referrer1_phone',''),
    nullif(p->>'referrer2_name',''), nullif(p->>'referrer2_role',''), nullif(p->>'referrer2_phone',''),
    left(coalesce(nullif(p->>'nominator_name',''),''),200),
    left(coalesce(nullif(p->>'nominator_phone',''),''),30),
    left(coalesce(nullif(p->>'nominator_email',''),''),255),
    nullif(p->>'nominator_address',''),
    nullif(p->>'nominator_relation',''),
    nullif(p->>'nominator_knowledge',''),
    coalesce((p->>'consent_truth')::boolean, false),
    coalesce((p->>'consent_permission')::boolean, false),
    coalesce((p->>'consent_privacy')::boolean, false),
    coalesce((p->>'permission_pending')::boolean, false)
  )
  RETURNING reference INTO r;

  UPDATE public.nomination_evidence SET nomination_id = (SELECT id FROM public.nominations WHERE reference = r)
  WHERE created_by = uid AND nomination_id IS NULL;

  DELETE FROM public.nomination_drafts WHERE created_by = uid;

  RETURN r;
END
$fn$;

GRANT EXECUTE ON FUNCTION public.submit_nomination_full(jsonb) TO anon, authenticated;
GRANT USAGE, SELECT ON SEQUENCE public.nomination_ref_seq TO anon, authenticated;