-- ajout extension plpython3u pour prose en charge de python3
CREATE EXTENSION IF NOT EXISTS plpython3u;

-- Fonction generant un nb aleatoire dans un interval specifique
CREATE OR REPLACE FUNCTION random_between (start integer, stop integer)
    RETURNS integer
AS $$
    import random
    return random.randrange(start,stop)
$$ LANGUAGE plpython3u;

-- Fonction generant un prenom aleatoire
CREATE OR REPLACE FUNCTION get_full_name()
    RETURNS varchar
AS $$
    import names
    return names.get_full_name()
$$ LANGUAGE plpython3u;

-- Fonction generant un texte aleatoire
CREATE OR REPLACE FUNCTION get_random_text()
    RETURNS varchar
AS $$
    import lorem
    return lorem.paragraph()
$$ LANGUAGE plpython3u;

-- Modifications pour scenario
delete from votetypes where id in (16,15);
delete from posthistory where posthistorytypeid in (22,24,25,31,33,34,35,36,37,38);
delete from posthistorytypes where id in (22,24,25,31,33,34,35,36,37,38);
alter table users alter column displayname type varchar(100);
select max(id) + 1 as id from badges \gset max_badges_
select max(id) + 1 as id from comments \gset max_comments_
select max(id) + 1 as id from flagtypes \gset max_flagtypes_
select max(id) + 1 as id from posthistory \gset max_posthistory_
select max(id) + 1 as id from posthistorytypes \gset max_posthistorytypes_
select max(id) + 1 as id from postlinks \gset max_postlinks_
select max(id) + 1 as id from postlinktypes \gset max_postlinktypes_
select max(id) + 1 as id from posts \gset max_posts_
select max(id) + 1 as id from posttypes \gset max_posttypes_
select max(id) + 1 as id from tags \gset max_tags_
select max(id) + 1 as id from users \gset max_users_
select max(id) + 1 as id from votes \gset max_votes_
select max(id) + 1 as id from votetypes \gset max_votetypes_
alter sequence badges_id_seq restart :max_badges_id;
alter sequence comments_id_seq restart :max_comments_id ;
alter sequence flagtypes_id_seq restart :max_flagtypes_id ;
alter sequence posthistory_id_seq restart :max_posthistory_id ;
alter sequence posthistorytypes_id_seq restart :max_posthistorytypes_id ;
alter sequence postlinks_id_seq restart :max_postlinks_id ;
alter sequence postlinktypes_id_seq restart :max_postlinktypes_id ;
alter sequence posts_id_seq restart :max_posts_id ;
alter sequence posttypes_id_seq restart :max_posttypes_id ;
alter sequence tags_id_seq restart :max_tags_id ;
alter sequence users_id_seq restart :max_users_id ;
alter sequence votes_id_seq restart :max_votes_id ;
alter sequence votetypes_id_seq restart :max_votetypes_id ;
drop index if exists ph_creation_date_idx;
drop index if exists cmnts_userid_idx;
drop index if exists cmnts_creation_date_idx;
create table interaction (id bigint primary key generated always as identity, creationdate timestamp without time zone, interac bigint);
create index on interaction (interac);
alter table interaction set (vacuum_index_cleanup = false);
insert into interaction (creationdate , interac) select now() , random_between(1,100) from generate_series (1,10000000);
delete from interaction where interac % 2 = 0;
insert into interaction (creationdate , interac) select now() , random_between(1,100) from generate_series (1,1000000);
delete from interaction where interac % 2 = 1;
insert into interaction (creationdate , interac) select now() , random_between(1,100) from generate_series (1,1000000);
delete from interaction where interac % 2 = 0;
insert into interaction (creationdate , interac) select now() , random_between(1,100) from generate_series (1,1000000);
delete from interaction where interac % 2 = 1;