-- Publish the supervisor registrations worth publishing.
--
-- 887 people registered on shodh.net's supervisor register. Only 122 carry the
-- legacy approval flag; the other 765 have an empty status column. The obvious
-- reading was a review backlog — the last approval is dated 28 May 2020 and
-- people kept registering for six years afterwards.
--
-- Reading the rows says otherwise. Among the 765 are keyboard-mash test
-- submissions ("5555555555555555555", "mmmmmmmmm"), 140 people whose
-- designation is Student or Research Scholar — registered on a register of
-- supervisors — 57 with no institution at all, and 109 groups of duplicates
-- accounting for 155 redundant rows. The approval step was doing quality
-- control, and it was working. What stopped in 2020 was the filtering, not
-- merely the paperwork.
--
-- So this publishes what passes the filter the old site's editor was applying
-- by hand, and leaves the rest hidden for a human to look at:
--
--   * a name, designation or subject that is a run of one repeated character,
--     or a name that is only digits, is a test submission;
--   * a designation of Student, Research Scholar or Ph.D. belongs on the
--     doctoral register, not this one;
--   * a name that is mostly digits ("67u677 c") is the same thing in a
--     different disguise — measured as a share rather than by pattern, because
--     one genuine entry does carry a stray digit;
--   * an entry with no institution cannot be useful to a researcher looking
--     for a supervisor;
--   * where the same name and institution appear more than once, the earliest
--     registration is published and the rest left alone.
--
-- Purely additive: nothing already visible is hidden, and where a duplicate
-- group already has a listed member, no second copy is added. An editor's past
-- decision to publish is never overridden here.
--
-- Contact details are not affected either way. The public projection nulls
-- address, email, phone and mobile for every caller, so what this exposes is
-- the professional listing alone: name, designation, institution, subject and
-- research interest — which is what these people registered in order to share.

UPDATE registry_entries SET visible = TRUE
WHERE id IN (
    WITH s AS (
        SELECT *, upper(trim(name)) || '|' || upper(coalesce(trim(university), '')) AS grp
        FROM registry_entries WHERE type = 'SUPERVISOR'
    ), ranked AS (
        SELECT id, visible, name, designation, subject, university,
               bool_or(visible) OVER (PARTITION BY grp) AS group_already_listed,
               row_number() OVER (PARTITION BY grp
                                  ORDER BY registered_at NULLS LAST, legacy_id) AS rn
        FROM s
    )
    SELECT id FROM ranked
    WHERE NOT visible
      AND NOT group_already_listed
      AND rn = 1
      AND name                       !~  '^[0-9]{6,}'
      AND name                       !~* '^(.)\1{4,}'
      AND coalesce(designation, '')  !~* '^(.)\1{4,}'
      AND coalesce(subject, '')      !~* '(.)\1{5,}'
      AND coalesce(designation, '')  !~* 'student|research scholar|^ph\.?d\.?|^scholar'
      AND coalesce(university, '')   <> ''
      -- Digits as a share of the name, not merely their presence: a real
      -- entry reads "Dr.Jyotika Patel Principal 1,P" at 11%, the junk at 71%.
      AND 100.0 * length(regexp_replace(name, '[^0-9]', '', 'g'))
          / nullif(length(replace(name, ' ', '')), 0) < 30
);

COMMENT ON COLUMN registry_entries.visible IS
    'Listed on the public register. Mirrors the legacy approval flag; V35 additionally
     published the pre-2020 backlog that passes basic quality checks.';