from mm_stats.config import POSTGRES_USER, PGPASSWORD


def load_sql() -> str:
    """Load sql template."""
    sql = f"""
        DROP TABLE IF EXISTS data_preparation.projects;
        CREATE TABLE data_preparation.projects as
        select
          *
          ,public.ST_Transform(geometry, 954009) as geometry_mollweide
          ,public.ST_Transform(centroid, 954009) as centroid_mollweide
        from public.dblink('
            dbname=hot_tm
            user={POSTGRES_USER}
            password={PGPASSWORD}
            options=-csearch_path=',
            'select * from public.projects')
        AS p(
            id integer,
            status integer,
            created timestamp without time zone,
            priority integer,
            default_locale character varying(10),
            author_id bigint,
            mapper_level integer,
            private boolean,
            changeset_comment character varying,
            due_date timestamp without time zone,
            imagery character varying,
            josm_preset character varying,
            last_updated timestamp without time zone,
            mapping_types integer[],
            total_tasks integer,
            tasks_mapped integer,
            tasks_validated integer,
            tasks_bad_imagery integer,
            license_id integer,
            centroid public.geometry(Point,4326),
            geometry public.geometry(MultiPolygon,4326),
            task_creation_mode integer,
            mapping_editors integer[],
            validation_editors integer[],
            osmcha_filter_id character varying,
            enforce_random_task_selection boolean,
            id_presets character varying[],
            featured boolean,
            country character varying[],
            organisation_id integer,
            mapping_permission integer,
            validation_permission integer,
            rapid_power_user bool,
            extra_id_params character varying,
            progress_email_sent bool
        );

        alter table data_preparation.projects
        rename column id to project_id;

        -- we add columsn here for each project
        -- to be able to link to natural earth boundaries
        alter table data_preparation.projects
        add column ne_name varchar,
        add column ne_id int,
        add column ne_iso_a3 varchar,
        add column region_name varchar,
        add column region_id int;

        update data_preparation.projects as p
        set
            ne_name = c.name
            ,ne_id = c.id
            ,ne_iso_a3 = c.iso_a3
            ,region_name = c.region_name
            ,region_id = c.region_id
        from data_preparation.ne_10m_admin_0_countries as c
        where
            -- check if country name matches with "name"
            replace(
                substr(
                    p.country::text , 2, length(p.country::text) - 2), '"', ''
                ) ilike c."name"::text
            or
            -- check if country name matches with "name_long"
            replace(
                substr(
                    p.country::text , 2, length(p.country::text) - 2), '"', ''
                ) ilike c."name_long"::text
            or
            -- check if country name matches with "formal_en"
            replace(
                substr(
                    p.country::text , 2, length(p.country::text) - 2), '"', ''
                ) ilike c."formal_en" ::text
            or
            -- when there is no country name defined in the tasking manager
            -- check if the project centroid is contained by a country geometry
            (
                p.country is null and
                    public.ST_Contains(
                        public.ST_SetSRID(c.geom, 4326),
                        p.centroid
                    )
            );

        CREATE INDEX projects_project_id_idx
        ON data_preparation.projects
        USING btree (project_id);

        CREATE INDEX projects_created_idx
        ON data_preparation.projects
        USING btree (created);

        CREATE INDEX projects_geom_idx
        ON data_preparation.projects
        USING gist (geometry);

        CREATE INDEX projects_centroid_idx
        ON data_preparation.projects
        USING gist (centroid);

        CREATE INDEX projects_geom_mollweide_idx
        ON data_preparation.projects
        USING gist (geometry_mollweide);

        CREATE INDEX projects_centroid_mollweide_idx
        ON data_preparation.projects
        USING gist (centroid_mollweide);
    """

    return sql