<commit_before>-- Archive old projects
SELECT archive_project(project_uid)
FROM (
    SELECT project_uid, availability,
    EXTRACT(days FROM now() - created_date) as p_age,
    CASE WHEN COUNT(DISTINCT(user_plot_uid)) = 0 THEN 0
        ELSE COUNT(DISTINCT(user_plot_uid)) / COUNT(DISTINCT(plot_uid))
        END as complete
    FROM projects
    LEFT JOIN plots
        ON project_uid = project_rid
    LEFT JOIN user_plots up
        ON plot_uid = up.plot_rid
    GROUP BY project_uid
) A
WHERE
    (p_age > 180
        AND ((availability = 'unpublished' AND complete < 0.05)
            OR (availability = 'closed' AND complete < 0.05)
            OR (availability = 'published' AND complete = 0)))
    OR (p_age > 365 AND complete < 0.05);

-- Delete archived projects
SELECT delete_project(project_uid)
FROM projects
WHERE project_uid IN
(
    SELECT project_uid
        FROM (
            SELECT project_uid, availability, archived_date,
            EXTRACT(days from now() - archived_date) as p_age<commit_msg>add query for orphan tables<commit_after>-- Clean up any orphaned tables
DO $$
 DECLARE
    _sql text;
 BEGIN
    SELECT INTO _sql
        'DROP TABLE ext_tables.' || table_name
    FROM (
        SELECT table_name,
            (select project_uid FROM projects WHERE plots_ext_table = table_name) as p,
            (select project_uid FROM projects WHERE samples_ext_table = table_name) as s
        FROM information_schema.tables
        WHERE table_schema='ext_tables'
            AND table_type='BASE TABLE'
    ) a
    WHERE p is null and s is null;

    IF _sql IS NOT NULL THEN
        EXECUTE _sql;
    ELSE
        RAISE NOTICE 'No functions found.';
    END IF;
 END
$$  LANGUAGE plpgsql;

-- Archive old projects
SELECT archive_project(project_uid)
FROM (
    SELECT project_uid, availability,
    EXTRACT(days FROM now() - created_date) as p_age,
    CASE WHEN COUNT(DISTINCT(user_plot_uid)) = 0 THEN 0
        ELSE COUNT(DISTINCT(user_plot_uid)) / COUNT(DISTINCT(plot_uid))
        END as complete
    FROM projects
    LEFT JOIN plots
        ON project_uid = project_rid
    LEFT JOIN user_plots up
        ON plot_uid = up.plot_rid
    GROUP BY project_uid
) A
WHERE
    (p_age > 180
        AND ((availability = 'unpublished' AND complete < 0.05)
            OR (availability = 'closed' AND complete < 0.05)
            OR (availability = 'published' AND complete = 0)))
    OR (p_age > 270 AND complete < 0.05);

-- Delete archived projects
SELECT delete_project(project_uid)
FROM projects
WHERE project_uid IN
(
    SELECT project_uid
        FROM (
            SELECT project_uid, availability, archived_date,
            EXTRACT(days from now() - archived_date) as p_age
{'fraction_non_alphanumeric': 0.07198228128460686, 'fraction_numerical': 0.013289036544850499, 'mean_word_length': 2.657219973009447, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 7, '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': '3005099', 'n_tokens_mistral': 892, 'n_tokens_neox': 798, 'n_words': 270}