<commit_before>begin;

create table eve_item_groups (
    item_group_id   integer not null primary key,
    parent_group_id integer,
    name            text not null,
    description     text
);
create index eve_item_groups_parent_group_id_idx on eve_item_groups (parent_group_id);
create index eve_item_groups_name_idx on eve_item_groups (name);
alter table eve_item_groups add foreign key (parent_group_id) references eve_item_groups (item_group_id) on update cascade on delete set null;

create table eve_items (
    item_id         integer not null primary key,
    item_group_id   integer not null,
    name            text not null,
    description     text,
    base_price      numeric(18,2) not null default 0.00
);
create index eve_items_item_group_id_idx on eve_items (item_group_id);
create index eve_items_name_text_pattern_ops_idx on eve_items (name text_pattern_ops);
create index eve_items_base_price_idx on eve_items (base_price);
alter table eve_items add foreign key (item_group_id) references eve_item_groups (item_group_id) on update cascade;

create table eve_regions (
    region_id      integer not null primary key,
    name           text not null,
    price_default  boolean not null default 'f'
);
create unique index eve_regions_name_idx on eve_regions (name);
create index eve_regions_price_default_idx on eve_regions (price_default);

create table eve_item_prices (
    item_id         integer not null,
    region_id       integer not null,
    buy_min         numeric(18,2) not null default 0.00,
    buy_max         numeric(18,2) not null default 0.00,
    buy_avg         numeric(18,2) not null default 0.00,
    buy_med         numeric(18,2) not null default 0.00,
    buy_stddev      numeric(18,2) not null default 0.00,
    buy_percentile  numeric(5,2) not null default 0.00,
    buy_volume      integer not null default 0,
    sell_min        numeric(18,2) not null default 0.00,
    sell_max        numeric(18,2) not null default 0.00,
    sell_avg        numeric(18,2) not null default 0.00,
    sell_med        numeric(18,2) not null default 0.00,
    sell_stddev     numeric(18,2) not null default 0.00,
    sell_percentile numeric(5,2) not null default 0.00,
    sell_volume     integer not null default 0,
    cached_until    timestamp with time zone not null default now() + interval '1 hour'
);
alter table eve_item_prices add primary key (item_id, region_id);
create index eve_item_prices_region_id_idx on eve_item_prices (region_id);
create index eve_item_prices_cached_until_idx on eve_item_prices (cached_until);
alter table eve_item_prices add foreign key (item_id) references eve_items (item_id) on update cascade on delete cascade;
alter table eve_item_prices add foreign key (region_id) references eve_regions (region_id) on update cascade on delete cascade;

create table eve_pilots (
    pilot_id      integer not null primary key,
    name          text not null,
    gender        text not null,
    race          text not null,
    bloodline     text not null,<commit_msg>median price column rename to match API element name (for easier parsing of return data -- no special-casing for xml->hashref transformation)<commit_after>begin;

create table eve_item_groups (
    item_group_id   integer not null primary key,
    parent_group_id integer,
    name            text not null,
    description     text
);
create index eve_item_groups_parent_group_id_idx on eve_item_groups (parent_group_id);
create index eve_item_groups_name_idx on eve_item_groups (name);
alter table eve_item_groups add foreign key (parent_group_id) references eve_item_groups (item_group_id) on update cascade on delete set null;

create table eve_items (
    item_id         integer not null primary key,
    item_group_id   integer not null,
    name            text not null,
    description     text,
    base_price      numeric(18,2) not null default 0.00
);
create index eve_items_item_group_id_idx on eve_items (item_group_id);
create index eve_items_name_text_pattern_ops_idx on eve_items (name text_pattern_ops);
create index eve_items_base_price_idx on eve_items (base_price);
alter table eve_items add foreign key (item_group_id) references eve_item_groups (item_group_id) on update cascade;

create table eve_regions (
    region_id      integer not null primary key,
    name           text not null,
    price_default  boolean not null default 'f'
);
create unique index eve_regions_name_idx on eve_regions (name);
create index eve_regions_price_default_idx on eve_regions (price_default);

create table eve_item_prices (
    item_id         integer not null,
    region_id       integer not null,
    buy_min         numeric(18,2) not null default 0.00,
    buy_max         numeric(18,2) not null default 0.00,
    buy_avg         numeric(18,2) not null default 0.00,
    buy_median      numeric(18,2) not null default 0.00,
    buy_stddev      numeric(18,2) not null default 0.00,
    buy_percentile  numeric(5,2) not null default 0.00,
    buy_volume      integer not null default 0,
    sell_min        numeric(18,2) not null default 0.00,
    sell_max        numeric(18,2) not null default 0.00,
    sell_avg        numeric(18,2) not null default 0.00,
    sell_median     numeric(18,2) not null default 0.00,
    sell_stddev     numeric(18,2) not null default 0.00,
    sell_percentile numeric(5,2) not null default 0.00,
    sell_volume     integer not null default 0,
    cached_until    timestamp with time zone not null default now() + interval '1 hour'
);
alter table eve_item_prices add primary key (item_id, region_id);
create index eve_item_prices_region_id_idx on eve_item_prices (region_id);
create index eve_item_prices_cached_until_idx on eve_item_prices (cached_until);
alter table eve_item_prices add foreign key (item_id) references eve_items (item_id) on update cascade on delete cascade;
alter table eve_item_prices add foreign key (region_id) references eve_regions (region_id) on update cascade on delete cascade;

create table eve_pilots (
    pilot_id      integer not null primary key,
    name          text not null,
    gender        text not null,
    race          text not null,
    bloodline     text not null,
{'fraction_non_alphanumeric': 0.05184108527131783, 'fraction_numerical': 0.025516795865633074, 'mean_word_length': 3.5171407731582787, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 1}, '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': '5474136', 'n_tokens_mistral': 2052, 'n_tokens_neox': 1843, 'n_words': 658}