<commit_before>-- NAMESPACE: plots
-- REQUIRES: clear, project

--
--  PLOT FUNCTIONS
--

CREATE OR REPLACE FUNCTION project_has_assigned(_project_id integer)
 RETURNS boolean AS $$

    SELECT count(*) > 0
    FROM plots, plot_assignments
    WHERE project_rid = _project_id
        AND plot_uid = plot_rid

$$ LANGUAGE SQL;

-- Select plots but only return a maximum number
-- TODO, CEO-32 update to only show users available plots
CREATE OR REPLACE FUNCTION select_limited_project_plots(_project_id integer, _maximum integer)
 RETURNS table (
    plot_id    integer,
    center     text,
    flagged    boolean,
    status     text
 ) AS $$

    WITH plot_sums AS (
        SELECT plot_uid,
            ST_AsGeoJSON(ST_Centroid(plot_geom)) AS center,
            sum(coalesce(flagged, false)::int) > 0 AS flagged,
            sum((pa.user_rid IS NOT NULL)::int) AS assigned,
            sum((up.user_rid IS NOT NULL)::int) AS collected
        FROM plots pl
        LEFT JOIN plot_assignments AS pa
                ON pa.plot_rid = pl.plot_uid
        LEFT JOIN user_plots up
            ON up.plot_rid = pl.plot_uid
            AND (pa.user_rid = up.user_rid OR NOT (project_has_assigned(_project_id)))
        GROUP BY plot_uid
        HAVING project_rid = _project_id
        LIMIT _maximum
    )

    SELECT plot_uid,
        center,
        flagged,
        CASE WHEN (assigned = 0 AND collected = 1) OR (assigned > 0 AND assigned = collected)
            THEN 'analyzed'
        WHEN assigned > collected AND collected > 1
            THEN 'partial'
        ELSE
            'unanalyzed'
        END
    FROM plot_sums

$$ LANGUAGE SQL;

-- Returns plot geom for the geodash
CREATE OR REPLACE FUNCTION select_plot_geom(_plot_id integer)
 RETURNS text AS $$

    SELECT ST_AsGeoJSON(plot_geom)
    FROM plots p
    WHERE p.plot_uid = _plot_id

$$ LANGUAGE SQL;

-- Get all users who have plotted on a project
CREATE OR REPLACE FUNCTION select_plotters(_project_id integer, _plot_id integer)
 RETURNS table (
    user_id    integer,
    email      text
 ) AS $$

    SELECT DISTINCT(u.user_uid) user_id, u.email
    FROM plots p
    LEFT JOIN user_plots up
        ON p.plot_uid = up.plot_rid
    LEFT JOIN plot_assignments pa
        ON p.plot_uid = pa.plot_rid
    INNER JOIN users u
        ON u.user_uid = up.user_rid
        OR u.user_uid = pa.user_rid
    WHERE p.project_rid = _project_id
        AND (plot_uid = _plot_id
             OR _plot_id = -1)
    ORDER BY u.email

$$ LANGUAGE SQL;

-- Get user plots for a plot
CREATE OR REPLACE FUNCTION select_user_plots_info( _plot_id integer)
 RETURNS table (
    user_id       integer,
    flagged       boolean,
    confidence    integer
 ) AS $$

    SELECT user_rid,
        flagged,
        confidence
    FROM user_plots
    WHERE plot_rid = _plot_id

$$ LANGUAGE SQL;

-- This return type is so the collection functions match return types.
DROP TYPE IF EXISTS collection_return CASCADE;
CREATE TYPE collection_return AS (
    plot_id            integer,
    flagged            boolean,
    flagged_reason     text,
    confidence         integer,
    visible_id         integer,
    plot_geom          text,
    extra_plot_info    jsonb,
    user_id            integer,
    email              text
);

CREATE OR REPLACE FUNCTION select_unanalyzed_plots(_project_id integer, _user_id integer, _review_mode boolean)
 RETURNS setOf collection_return AS $$

    SELECT plot_uid,
        flagged,
        flagged_reason,
        confidence,
        visible_id,
        ST_AsGeoJSON(plot_geom) as plot_geom,
        extra_plot_info,
        pa.user_rid,
        u.email
    FROM plots
    LEFT JOIN plot_assignments pa
        ON plot_uid = pa.plot_rid
    LEFT JOIN user_plots up
        ON plot_uid = up.plot_rid
        AND (pa.user_rid = up.user_rid OR NOT (project_has_assigned(_project_id)))
    LEFT JOIN plot_locks pl
        ON plot_uid = pl.plot_rid
    LEFT JOIN users u
        ON pa.user_rid = u.user_uid
    WHERE project_rid = _project_id
        AND user_plot_uid IS NULL
        AND ((pa.user_rid IS NULL
                AND (pl.lock_end IS NULL
                     OR localtimestamp > pl.lock_end)) -- unlocked
             OR pa.user_rid = _user_id                 -- assigned
             OR _review_mode)                           -- admin TODO, CEO-208 should admin be able to visit a locked plot? probably.
    ORDER BY visible_id ASC

$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION select_analyzed_plots(_project_id integer, _user_id integer, _review_mode boolean)
 RETURNS setOf collection_return AS $$

    SELECT plot_uid,
        flagged,
        flagged_reason,
        confidence,
        visible_id,
        ST_AsGeoJSON(plot_geom) as plot_geom,
        extra_plot_info,
        u.user_uid,<commit_msg>Fix typo for project_has_assigned<commit_after>-- NAMESPACE: plots
-- REQUIRES: clear, project

--
--  PLOT FUNCTIONS
--

CREATE OR REPLACE FUNCTION project_has_assigned(_project_id integer)
 RETURNS boolean AS $$

    SELECT count(*) > 0
    FROM plots, plot_assignments
    WHERE project_rid = _project_id
        AND plot_uid = plot_rid

$$ LANGUAGE SQL;

-- Select plots but only return a maximum number
-- TODO, CEO-32 update to only show users available plots
CREATE OR REPLACE FUNCTION select_limited_project_plots(_project_id integer, _maximum integer)
 RETURNS table (
    plot_id    integer,
    center     text,
    flagged    boolean,
    status     text
 ) AS $$

    WITH plot_sums AS (
        SELECT plot_uid,
            ST_AsGeoJSON(ST_Centroid(plot_geom)) AS center,
            sum(coalesce(flagged, false)::int) > 0 AS flagged,
            sum((pa.user_rid IS NOT NULL)::int) AS assigned,
            sum((up.user_rid IS NOT NULL)::int) AS collected
        FROM plots pl
        LEFT JOIN plot_assignments AS pa
                ON pa.plot_rid = pl.plot_uid
        LEFT JOIN user_plots up
            ON up.plot_rid = pl.plot_uid
            AND (pa.user_rid = up.user_rid OR NOT (SELECT project_has_assigned(_project_id)))
        GROUP BY plot_uid
        HAVING project_rid = _project_id
        LIMIT _maximum
    )

    SELECT plot_uid,
        center,
        flagged,
        CASE WHEN (assigned = 0 AND collected = 1) OR (assigned > 0 AND assigned = collected)
            THEN 'analyzed'
        WHEN assigned > collected AND collected > 1
            THEN 'partial'
        ELSE
            'unanalyzed'
        END
    FROM plot_sums

$$ LANGUAGE SQL;

-- Returns plot geom for the geodash
CREATE OR REPLACE FUNCTION select_plot_geom(_plot_id integer)
 RETURNS text AS $$

    SELECT ST_AsGeoJSON(plot_geom)
    FROM plots p
    WHERE p.plot_uid = _plot_id

$$ LANGUAGE SQL;

-- Get all users who have plotted on a project
CREATE OR REPLACE FUNCTION select_plotters(_project_id integer, _plot_id integer)
 RETURNS table (
    user_id    integer,
    email      text
 ) AS $$

    SELECT DISTINCT(u.user_uid) user_id, u.email
    FROM plots p
    LEFT JOIN user_plots up
        ON p.plot_uid = up.plot_rid
    LEFT JOIN plot_assignments pa
        ON p.plot_uid = pa.plot_rid
    INNER JOIN users u
        ON u.user_uid = up.user_rid
        OR u.user_uid = pa.user_rid
    WHERE p.project_rid = _project_id
        AND (plot_uid = _plot_id
             OR _plot_id = -1)
    ORDER BY u.email

$$ LANGUAGE SQL;

-- Get user plots for a plot
CREATE OR REPLACE FUNCTION select_user_plots_info( _plot_id integer)
 RETURNS table (
    user_id       integer,
    flagged       boolean,
    confidence    integer
 ) AS $$

    SELECT user_rid,
        flagged,
        confidence
    FROM user_plots
    WHERE plot_rid = _plot_id

$$ LANGUAGE SQL;

-- This return type is so the collection functions match return types.
DROP TYPE IF EXISTS collection_return CASCADE;
CREATE TYPE collection_return AS (
    plot_id            integer,
    flagged            boolean,
    flagged_reason     text,
    confidence         integer,
    visible_id         integer,
    plot_geom          text,
    extra_plot_info    jsonb,
    user_id            integer,
    email              text
);

CREATE OR REPLACE FUNCTION select_unanalyzed_plots(_project_id integer, _user_id integer, _review_mode boolean)
 RETURNS setOf collection_return AS $$

    SELECT plot_uid,
        flagged,
        flagged_reason,
        confidence,
        visible_id,
        ST_AsGeoJSON(plot_geom) as plot_geom,
        extra_plot_info,
        pa.user_rid,
        u.email
    FROM plots
    LEFT JOIN plot_assignments pa
        ON plot_uid = pa.plot_rid
    LEFT JOIN user_plots up
        ON plot_uid = up.plot_rid
        AND (pa.user_rid = up.user_rid OR NOT (SELECT project_has_assigned(_project_id)))
    LEFT JOIN plot_locks pl
        ON plot_uid = pl.plot_rid
    LEFT JOIN users u
        ON pa.user_rid = u.user_uid
    WHERE project_rid = _project_id
        AND user_plot_uid IS NULL
        AND ((pa.user_rid IS NULL
                AND (pl.lock_end IS NULL
                     OR localtimestamp > pl.lock_end)) -- unlocked
             OR pa.user_rid = _user_id                 -- assigned
             OR _review_mode)                           -- admin TODO, CEO-208 should admin be able to visit a locked plot? probably.
    ORDER BY visible_id ASC

$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION select_analyzed_plots(_project_id integer, _user_id integer, _review_mode boolean)
 RETURNS setOf collection_return AS $$

    SELECT plot_uid,
        flagged,
        flagged_reason,
        confidence,
        visible_id,
        ST_AsGeoJSON(plot_geom) as plot_geom,
        extra_plot_info,
        u.user_uid,
{'fraction_non_alphanumeric': 0.057564422277639235, 'fraction_numerical': 0.0024937655860349127, 'mean_word_length': 2.1765676567656764, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2332726', 'n_tokens_mistral': 3098, 'n_tokens_neox': 2943, 'n_words': 914}