drop sequence d_water_body_type_id_seq;
drop sequence angler_id_seq;
drop sequence water_body_id_seq;
drop sequence river_id_seq;
drop sequence lake_id_seq;
drop sequence sea_shore_id_seq;
drop sequence ocean_id_seq;
drop sequence fishing_spot_id_seq;
drop sequence fishing_spot_point_id_seq;
drop sequence fishing_spot_area_id_seq;
drop sequence d_fish_category_id_seq;
drop sequence d_fish_species_id_seq;
drop sequence d_fishing_method_id_seq;
drop sequence d_bait_id_seq;
drop sequence fishing_season_id_seq;
drop sequence catch_record_id_seq;

create sequence d_water_body_type_id_seq;
create sequence angler_id_seq;
create sequence water_body_id_seq;
create sequence river_id_seq;
create sequence lake_id_seq;
create sequence sea_shore_id_seq;
create sequence ocean_id_seq;
create sequence fishing_spot_id_seq;
create sequence fishing_spot_point_id_seq;
create sequence fishing_spot_area_id_seq;
create sequence d_fish_category_id_seq;
create sequence d_fish_species_id_seq;
create sequence d_fishing_method_id_seq;
create sequence d_bait_id_seq;
create sequence fishing_season_id_seq;
create sequence catch_record_id_seq;

select dropgeometrycolumn('', 'fishing_spot_point', 'the_geom');
select dropgeometrycolumn('', 'fishing_spot_area', 'the_geom');

drop table catch_record;
drop table fishing_spot_point;
drop table fishing_spot_area;
drop table fishing_spot;
drop table fishing_season;

drop table river;
drop table lake;
drop table sea_shore;
drop table ocean;
drop table water_body;
drop table angler;

drop table d_fish_species;
drop table d_fish_category;
drop table d_fishing_method;
drop table d_bait;
drop table d_water_body_type;

create table angler (
  angler_id      int primary key default nextval('angler_id_seq'),
  angler_name    varchar(60),
  login_name     character varying(20),
  rating         float,
  "password"     character varying(20),
  address        character varying(60),
  city           character varying(20),
  d_state_id     integer,
  zip_code       character varying(10),
  phone          character varying(20),
  email          character varying(40),
  d_login_user_role_id integer,
  comments       character varying(200),
  security_question character varying(60),
  security_answer   character varying(60),
  registration   date,
  modified_date  date,
  modified_by    integer,
  row_owner_id   integer
);

create table d_water_body_type (
  d_water_body_type_id  int primary key
                            default nextval('d_water_body_type_id_seq'),
  water_body_type_name  varchar(60),
  display_order         double precision,
  description character varying(200),
  modified_date         date,
  modified_by           integer,
  row_owner_id          integer
);

create table water_body (
  water_body_id      int primary key default nextval('water_body_id_seq'),
  d_water_body_type  int,
  water_body_name    varchar(60),
  modified_date      date,
  modified_by        integer,
  row_owner_id       integer
);

create table river (
) inherits (water_body);

create table lake (
) inherits (water_body);

create table sea_shore (
) inherits (water_body);

create table ocean (
) inherits (water_body);

create table fishing_spot (
  fishing_spot_id      int primary key default nextval('fishing_spot_id_seq'), 
  water_body_id        int,
  fishing_spot_name    varchar(60),
  d_state_id           int,
  d_county_id          int,
  d_city_id            int,
  rating               float,
  desctiption          varchar(400),
  modified_date        date,
  modified_by          integer,
  row_owner_id         integer
);

create table fishing_spot_point (
) inherits (fishing_spot);


select addgeometrycolumn('', 'fishing_spot_point', 
                         'the_geom', 2992, 'POINT', 2);

create table fishing_spot_area (
) inherits (fishing_spot);

select addgeometrycolumn('', 'fishing_spot_area', 
                         'the_geom', 2992, 'POLYGON', 2);

create table d_fish_category (
  d_fish_category_id   int primary key default nextval('d_fish_category_id_seq'), 
  display_value    char(60), 
  description      varchar(160),
  modified_date    date,
  modified_by      integer,
  row_owner_id     integer
);

create table d_fish_species (
  d_fish_species_id  int primary key default nextval('d_fish_species_id_seq'), 
  d_fish_category_id int,
  display_value      char(60), 
  photo_file         varchar(60),
  description        varchar(200),
  modified_date      date,
  modified_by        integer,
  row_owner_id       integer
);

create table d_fishing_method (
  d_fishing_method_id   int primary key default nextval('d_fishing_method_id_seq'), 
  display_value    char(60), 
  description      varchar(160),
  modified_date    date,
  modified_by      integer,
  row_owner_id     integer
);

create table d_bait (
  d_bait_id   int primary key default nextval('d_bait_id_seq'), 
  display_value    char(60), 
  description      varchar(160),
  modified_date    date,
  modified_by      integer,
  row_owner_id     integer
);

create table fishing_season (
  fishing_season_id  int primary key default nextval('fishing_season_id_seq'),
  fishing_spot_id    int,
  d_fish_species_id  int,
  start_date         date,
  end_date           date,
  num_caught         int,
  description        varchar(320),
  modified_date      date,
  modified_by        integer,
  row_owner_id       integer
);

create table catch_record (
  catch_record_id     int primary key default nextval('catch_record_id_seq'),
  angler_id           int,
  fishing_spot_id     int,
  d_fish_category_id  int,
  d_fish_species_id   int,
  d_fishing_method_id int,
  d_bait_id           int,
  photo_file_1        varchar(60),
  photo_file_2        varchar(60),
  date                date,
  num_caught          int,
  length              float,
  weight              float,
  description         varchar(320),
  modified_date       date,
  modified_by         integer,
  row_owner_id        integer
);