from mm_stats.config import POSTGRES_USER, PGPASSWORD


def load_sql() -> str:
    """Load sql template."""
    sql = f"""
        DROP TABLE IF EXISTS data_preparation.tasks;
        CREATE TABLE data_preparation.tasks as
        select
          id
          ,project_id
          ,x
          ,y
          ,zoom
          ,public.ST_MakeValid(geometry) as geometry
          ,task_status
          ,locked_by
          ,mapped_by
          ,validated_by
          ,is_square
          ,extra_properties
          ,public.ST_Centroid(geometry) as centroid
          ,public.ST_Transform(public.ST_MakeValid(geometry), 954009) as geometry_mollweide
          ,public.ST_Transform(public.ST_Centroid(geometry), 954009) as centroid_mollweide
          ,public.ST_Area(public.ST_MakeValid(geometry)::public.geography) as area
        from public.dblink('
            dbname=hot_tm
            user={POSTGRES_USER}
            password={PGPASSWORD}
            options=-csearch_path=',
            'select * from public.tasks')
        AS t(
            id integer,
            project_id integer,
            x integer,
            y integer,
            zoom integer,
            geometry public.geometry(MultiPolygon,4326),
            task_status integer,
            locked_by bigint,
            mapped_by bigint,
            validated_by bigint,
            is_square boolean,
            extra_properties character varying COLLATE pg_catalog."default"
        );

        alter table data_preparation.tasks
            rename column id to task_id;

        CREATE INDEX tasks_task_id_idx ON data_preparation.tasks USING btree (task_id);
        CREATE INDEX tasks_project_id_idx ON data_preparation.tasks USING btree (project_id);
        CREATE INDEX tasks_geom_idx ON data_preparation.tasks USING gist (geometry);
        CREATE INDEX tasks_centroid_idx ON data_preparation.tasks USING gist (centroid);
        CREATE INDEX tasks_geom_mollweide_idx ON data_preparation.tasks USING gist (geometry_mollweide);
        CREATE INDEX tasks_centroid_mollweide_idx ON data_preparation.tasks USING gist (centroid_mollweide);
    """

    return sql