<commit_before># tables

#utility tables
# will be a single row
DROP TABLE IF EXISTS badwords;
CREATE TABLE badwords (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    kill_words TEXT,
    warn_words TEXT,
    updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS images;
CREATE TABLE `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app` varchar(255) NOT NULL,
  `mirrored` tinyint(1) DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `app` (`app`),
  KEY `mirrored` (`mirrored`)
);

#location
drop table if exists location;
create table location (
    location_id int not NULL
    ,name varchar(50) not NULL
    ,lat decimal(9,6) not NULL
    ,lon decimal(9,6) not NULL
    ,borough varchar(50) null
    ,address varchar(100) null
    ,city varchar(50) null
    ,state char(2) null
    ,primary key (location_id)
);

#users
drop table if exists user;
create table user (
    user_id int not null auto_increment
    ,user_key varchar(10) not null
    ,email varchar(100) not NULL
    ,password varchar(255) not NULL
    ,salt varchar(255) not NULL
    ,phone varchar(10) NULL
    ,first_name varchar(50) NULL
    ,last_name varchar(50) NULL
    ,full_display_name varchar(255) null
    ,image_id int NULL
    ,location_id int null
    ,description varchar(255) NULL
    ,affiliation varchar(100) null
    ,is_oncall bool not null default 0
    ,email_notification enum('none', 'digest') not null default 'digest'
    ,last_account_page_access_datetime timestamp null
    ,is_active bool not null default 1
    ,created_datetime timestamp not null default '0000-00-00 00:00:00'
    ,updated_datetime timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    ,primary key (user_id)
    ,unique (email)
    ,unique (phone)
);

drop table if exists unauthenticated_user;
create table unauthenticated_user (
    auth_guid char(36) not null
    ,email varchar(100) not NULL
    ,password varchar(255) not NULL
    ,salt varchar(255) not NULL
    ,phone varchar(10) NULL
    ,first_name varchar(50) NULL
    ,last_name varchar(50) NULL
    ,created_datetime timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    ,primary key (auth_guid)
);

create table twitter_user (
    twitter_id int not null
    ,twitter_username varchar(255) not null
    ,user_id int not null references user(user_id)
    ,primary key(user_id)
    ,unique(twitter_id)
);

create table facebook_user (
    facebook_id bigint not null
    ,user_id int not null references user(user_id)
    ,primary key(user_id)
    ,unique(facebook_id)
);

drop table if exists user_group;
create table user_group (
    user_group_id int not null
    ,group_name varchar(50) not NULL
    ,description varchar(255) NULL
    ,is_active bool not null default 1
    ,created_datetime timestamp default CURRENT_TIMESTAMP
    ,primary key (user_group_id)
);

drop table if exists user__user_group;
create table user__user_group (
    user_id int not NULL
    ,user_group_id int not NULL
    ,created_datetime timestamp default CURRENT_TIMESTAMP
    ,primary key (user_id, user_group_id)
);

#ideas
drop table if exists idea;
create table idea (
    idea_id int not null auto_increment
    ,description varchar(255) not NULL
    ,location_id int not null default -1
    ,submission_type enum('web','sms','email') not NULL
    ,user_id int null
    ,email varchar(100) null
    ,phone varchar(10) null
    ,first_name varchar(50) NULL
    ,last_name varchar(50) NULL
    ,num_flags smallint not null default 0
    ,is_active bool not null default 1
    ,created_datetime timestamp default CURRENT_TIMESTAMP
    ,primary key (idea_id)
);

#projects
drop table if exists project;
create table project (
    project_id int not null auto_increment
    ,title varchar(100) not null
    ,description varchar(255) null
    ,image_id int NULL
    ,location_id int null
    ,keywords text null
    ,num_flags smallint not null default 0
    ,is_official bool not null default 0
    ,is_active bool not null default 1
    ,created_datetime timestamp not null default '0000-00-00 00:00:00'
    ,updated_datetime timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    ,primary key (project_id)
);

drop table if exists project__user;
create table project__user (
    project_id int not NULL
    ,user_id int not NULL
    ,is_project_admin bool not null default 0
    ,created_datetime timestamp not null default CURRENT_TIMESTAMP
    ,primary key (project_id, user_id)
);

drop table if exists project__idea;
create table project__idea (
    project_id int not NULL
    ,idea_id int not NULL
    ,created_datetime timestamp not null default CURRENT_TIMESTAMP
    ,primary key (project_id, idea_id)
);

drop table if exists project__project_resource;<commit_msg>Added organization column to project as it is expected by the code and interface.<commit_after># tables

#utility tables
# will be a single row
DROP TABLE IF EXISTS badwords;
CREATE TABLE badwords (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    kill_words TEXT,
    warn_words TEXT,
    updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS images;
CREATE TABLE `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app` varchar(255) NOT NULL,
  `mirrored` tinyint(1) DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `app` (`app`),
  KEY `mirrored` (`mirrored`)
);

#location
drop table if exists location;
create table location (
    location_id int not NULL
    ,name varchar(50) not NULL
    ,lat decimal(9,6) not NULL
    ,lon decimal(9,6) not NULL
    ,borough varchar(50) null
    ,address varchar(100) null
    ,city varchar(50) null
    ,state char(2) null
    ,primary key (location_id)
);

#users
drop table if exists user;
create table user (
    user_id int not null auto_increment
    ,user_key varchar(10) not null
    ,email varchar(100) not NULL
    ,password varchar(255) not NULL
    ,salt varchar(255) not NULL
    ,phone varchar(10) NULL
    ,first_name varchar(50) NULL
    ,last_name varchar(50) NULL
    ,full_display_name varchar(255) null
    ,image_id int NULL
    ,location_id int null
    ,description varchar(255) NULL
    ,affiliation varchar(100) null
    ,is_oncall bool not null default 0
    ,email_notification enum('none', 'digest') not null default 'digest'
    ,last_account_page_access_datetime timestamp null
    ,is_active bool not null default 1
    ,created_datetime timestamp not null default '0000-00-00 00:00:00'
    ,updated_datetime timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    ,primary key (user_id)
    ,unique (email)
    ,unique (phone)
);

drop table if exists unauthenticated_user;
create table unauthenticated_user (
    auth_guid char(36) not null
    ,email varchar(100) not NULL
    ,password varchar(255) not NULL
    ,salt varchar(255) not NULL
    ,phone varchar(10) NULL
    ,first_name varchar(50) NULL
    ,last_name varchar(50) NULL
    ,created_datetime timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    ,primary key (auth_guid)
);

create table twitter_user (
    twitter_id int not null
    ,twitter_username varchar(255) not null
    ,user_id int not null references user(user_id)
    ,primary key(user_id)
    ,unique(twitter_id)
);

create table facebook_user (
    facebook_id bigint not null
    ,user_id int not null references user(user_id)
    ,primary key(user_id)
    ,unique(facebook_id)
);

drop table if exists user_group;
create table user_group (
    user_group_id int not null
    ,group_name varchar(50) not NULL
    ,description varchar(255) NULL
    ,is_active bool not null default 1
    ,created_datetime timestamp default CURRENT_TIMESTAMP
    ,primary key (user_group_id)
);

drop table if exists user__user_group;
create table user__user_group (
    user_id int not NULL
    ,user_group_id int not NULL
    ,created_datetime timestamp default CURRENT_TIMESTAMP
    ,primary key (user_id, user_group_id)
);

#ideas
drop table if exists idea;
create table idea (
    idea_id int not null auto_increment
    ,description varchar(255) not NULL
    ,location_id int not null default -1
    ,submission_type enum('web','sms','email') not NULL
    ,user_id int null
    ,email varchar(100) null
    ,phone varchar(10) null
    ,first_name varchar(50) NULL
    ,last_name varchar(50) NULL
    ,num_flags smallint not null default 0
    ,is_active bool not null default 1
    ,created_datetime timestamp default CURRENT_TIMESTAMP
    ,primary key (idea_id)
);

#projects
drop table if exists project;
create table project (
    project_id int not null auto_increment
    ,title varchar(100) not null
    ,description varchar(255) null
    ,image_id int NULL
    ,location_id int null
    ,keywords text null
    ,num_flags smallint not null default 0
    ,is_official bool not null default 0
    ,is_active bool not null default 1
    ,created_datetime timestamp not null default '0000-00-00 00:00:00'
    ,updated_datetime timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    ,organization varchar(255)
    ,primary key (project_id)
);

drop table if exists project__user;
create table project__user (
    project_id int not NULL
    ,user_id int not NULL
    ,is_project_admin bool not null default 0
    ,created_datetime timestamp not null default CURRENT_TIMESTAMP
    ,primary key (project_id, user_id)
);

drop table if exists project__idea;
create table project__idea (
    project_id int not NULL
    ,idea_id int not NULL
    ,created_datetime timestamp not null default CURRENT_TIMESTAMP
    ,primary key (project_id, idea_id)
);

drop table if exists project__project_resource;
{'fraction_non_alphanumeric': 0.066987606268565, 'fraction_numerical': 0.026528730922872067, 'mean_word_length': 3.9387961557916036, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, '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': '3253419', 'n_tokens_mistral': 3258, 'n_tokens_neox': 2960, 'n_words': 1076}