-- mysql database schema for dasr started 3/19/11

create table researcher (
  researcher_id int(11) auto_increment primary key,
  email varchar(128),
  firstname varchar(64),
  lastname varchar(64),
  username varchar(32) not null unique,
  pword_hash char(64) not null
  approved tinyint not null default 0,
  admin tinyint not null default 0
) engine=InnoDB default charset=utf8;

create table experiment (
  experiment_id int(11) auto_increment primary key,
  title varchar(128) not null,
  researcher_id int(11),
  exp_date datetime not null,
  create_date datetime not null,
  -- other_method varchar(64),
  keywords text,
  description text,
  bedcount int -- number of bedfiles for this experiment
) engine=InnoDB default charset=utf8;

create table genome (
  genome_id int(11) auto_increment primary key,
  name varchar(64) not null unique,
  -- chromosomes varchar(255), -- optional comma-delimited list of chromosomes allowed in this genome
  researcher_id int(11), -- optional, researchers will want to add genomes going forward
  clade varchar(32),  -- these fields are the corresponding values from UCSC genome browser
  org varchar(32),
  db varchar(32)
) engine=InnoDB default charset=utf8;

create table method (
  method_id int(11) auto_increment primary key,
  name varchar(64) not null unique
) engine=InnoDB default charset=utf8;

create table rawbed (
  rawbed_id int(11) auto_increment primary key,
  bedfile_id int(11) not null,
  filepath varchar(128) not null
) engine=InnoDB default charset=utf8;

create table summarybed (
  summarybed_id int(11) auto_increment primary key,
  bedfile_id int(11) not null,
  -- rawbed_id int(11),
  -- experiment_id int(11) not null,
  chrom varchar(16) not null,
  chromstart bigint not null,
  chromend bigint not null,
  name varchar(32),
  score int,
  strand enum('+','-'),
  thickstart bigint,
  thickend bigint,
  itemrgb varchar(16),
  remainder text,
  blockcount bigint,
  blocksizes varchar(32),
  blockstarts varchar(32)
) engine=InnoDB default charset=utf8;

create table agbed (
  agbed_id int(11) auto_increment primary key,
  bedfile_id int(11) not null,
  chrom varchar(16) not null,
  chromstart bigint not null,
  chromend bigint not null,
  name varchar(32),
  score int,
  strand enum('+','-'),
  thickstart bigint,
  thickend bigint,
  itemrgb varchar(16),
  remainder text,
  blockcount bigint,
  blocksizes varchar(32),
  blockstarts varchar(32)
) engine=InnoDB default charset=utf8;

create table bedfile (
  bedfile_id int(11) auto_increment primary key,
  experiment_id int(11) not null,
  method_id int(11) not null,
  genome_id int(11) not null,
  filetype varchar(32), -- should be rawbed, summarybed, agbed
  name varchar(128) not null,
  upload_date datetime not null,
  expiration datetime,
  header text,
  descr text,
  token varchar(32),
  token_expires datetime,
  overlap_counts_chart_url text,
  overlaps_per_read_chart_url text,
  is_reference tinyint not null default 0, 
  table_of_reads text,
  read_count int,
  read_dist_chart text
) engine=InnoDB default charset=utf8;

create table chrom_length (
  chrom_length_id int(11) auto_increment primary key,
  genome_id int(11) not null,
  chrom varchar(16),
  length bigint not null
) engine=InnoDB default charset=utf8;
