/*-------------------------------

  Scholastic High-tech Internet Teachers Store
  Model  r7v13

Revision Log
-----------------------------------------------------------------------
Who     Date            Comments
======  ==============  ===============================================
Alan    15 June 2006    Initial pass
Alan    05 July 2006    Revisions: more tables and fields 
Alan    05 July 2006    Use Scholastic and Rails naming convention
Alan    10 July 2006    More massaging - now matches ERD r5v2
Alan    12 July 2006    Changed up (in a good way) the products data.
Peter   17-19 July 2006 Added and changed lots around products
Alan    19 July 2006    More changes around products. 
                        Added associative entity IDs for Rails.
                        Finally added phones, emails, addresses tables.
Peter   20 July 2006    Added a series id (legacy_srs_id)to the series table
                        and upn to products
Peter   21 July 2006    Changed to make more legacy frieldly
						publisher.id from int to varchar(3), 
						people.legacy_clubs_id to clubs_custid,
						schools.dept_of_ed_id to jde_an8 
Damien	24 July 2006	Added ids to some join tables for Rails, tags and countries.
						Renamed some join tables now they represent models.
						E.g. addresses_schools to school_addresses
Peter   25 July 2006    Moved pub & release date to products,
						removed not null constraint because of external products
Damien	25 July 2006	Added sequences to ids missing them.
Peter   26 July 2006    changed products.description to long_description,
                        added offers.is_active, added tables system_constants, 
                        promos, promo_results, offer_promos, 
                        removed table items_publishers and added items.publisher_id
Damien	26 July 2006	Removed unneeded sequences from tables with non integer ids
						Changes person's salt and password to varchar(40)
Steve   27 July 2006    Removed unnecessary "start 1" etc. from CREATE SEQUENCE commands
Damien	27 July 2006	Changed seq names to table_col_seq so Rails can easily find them
						Added asset_root to system_constant
Peter   27 July 2006	Added asset_derivative table and fixed up asset table						
						Renamed password to crypted_password so we can have nice forms
Alan    27 July 2006    Added drop sequence for system_constants_id_seq, which was missing.
                        Reordered the drop table statements to reverse-dependency order, 
                        elimating the need for 'cascade'.
                        Put 'start 1 increment 1 no cycle' back on sequences for clarity, 
                        intentionality, and to hedge against future version shock.
Peter   27 July 2006    Minor change to promos table, and now to size of asset_kind field...
Damien	27 July 2006	Changed all the Primary Keys to be the ID field. 
						Added unique constraints to all the former (proper) Pks.
Damien	30 July 2006	Removed all unneeded PKs and restored original composite keys.
						Removed sequences no longer needed.
Damien	7  Aug  2006	Renamed offer_promos to promo_offer_conditions.
						promo_type is now promo_kind and is more consistent in use (examples in comments)
						Renamed some condition and result columns to give consistency and clear separation between conditions and results of a promotion
        (Un-noted changes in mid-August... mostly from Alan's other files (views) and new stuff from Steve.)						
Alan    18 Aug  2006    Added order_line_items_promos.
Peter   20 Aug  2006	Added table.related_products to accomplish similar objective of series.member_of
						but, closer to real business logic of the read & responde
						added age_group_years.long_description, age_group_years.short_description 
						for resource page text
Max		21 Aug 2006  	renamed region_shipping to region_shipping_charges for easier railsness
Damien	20 Aug 2006		Added exception_logger table.
Damien	30 Aug 2006		Added rails sessions table.
Damien	4 Sep 2006		updated_at for sessions table.

Template
---------------------------------------------------- 
-- table_name --
Create Table table_name (
  . . .
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key()
);

-------------------------------*/

-- Connect to the server and use the ScholasticWeb database.

-- Set the Schema search path.
-- set search_path to 'ECM';


--------------------------
-- create the sequences --
--------------------------
create sequence people_id_seq start 1 increment 1 no cycle;
create sequence schools_id_seq start 1 increment 1 no cycle;
create sequence products_id_seq start 1 increment 1 no cycle;
create sequence series_id_seq start 1 increment 1 no cycle;
create sequence assets_id_seq start 1 increment 1 no cycle;
create sequence asset_derivatives_id_seq start 1 increment 1 no cycle;
create sequence shops_id_seq start 1 increment 1 no cycle;
create sequence catalogs_id_seq start 1 increment 1 no cycle;
create sequence offers_id_seq start 1 increment 1 no cycle;
create sequence orders_id_seq start 1 increment 1 no cycle;
create sequence payments_id_seq start 1 increment 1 no cycle;
create sequence voucher_redemptions_id_seq start 1 increment 1 no cycle;
create sequence clubsnfairs_id_seq start 1 increment 1 no cycle;
create sequence tags_id_seq start 1 increment 1 no cycle;
create sequence addresses_id_seq start 1 increment 1 no cycle;
create sequence promos_id_seq start 1 increment 1 no cycle;
create sequence age_groups_id_seq start 1 increment 1 no cycle;
create sequence age_group_years_id_seq start 1 increment 1 no cycle;
create sequence regions_id_seq start 1 increment 1 no cycle;
create sequence logged_exceptions_id_seq start 1 increment 1 no cycle;
create sequence sessions_id_seq start 1 increment 1 no cycle;
create sequence countries_id_seq start 1 increment 1 no cycle;
create sequence listener_queue_seq start 1 increment 1 no cycle;
create sequence listener_state_seq start 1 increment 1 no cycle;


-----------------------
-- create the tables -- 
-----------------------

-- people --
create table people (
  id              integer not null default nextval('people_id_seq'),
  prefix          varchar(16),
  given_name      varchar(100) not null,
  middle_name     varchar(40),
  surname         varchar(100) not null,
  suffix          varchar(16),
  gender          char(1) check( gender in ('m', 'f') ),    -- Are we allowed to ask this?
  dob             date,
  job_title       varchar(50), 
  userid          varchar(50),
  salt            varchar(40),
  crypted_password varchar(40),
  remember_token  varchar(40),
  remember_token_expires_at timestamp with time zone,
  clubs_custid    integer, -- prm, made this an integer
  is_active       boolean not null default 't',
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- children --
create table children (
  id              integer not null references people(id),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- parents --
create table parents (
  id              integer not null references people(id),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- family_members -- Associates parents with their children.
create table family_members (
  parent_id      integer not null references parents(id),
  child_id       integer not null references children(id),
  relation       varchar(6) check (relation in ('mother', 'father', 'other') ),
  updated_on     timestamp with time zone not null,
  updated_by     varchar(30) not null,
  created_on     timestamp with time zone not null,
  created_by     varchar(30) not null,
check (parent_id != child_id),
primary key (parent_id, child_id)
);

-- staff -- People who work at the Schools
create table staff (
  id              integer not null references people(id),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- employees -- People who work at Scholastic.
create table employees (
  id              integer not null references people(id),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- contributors -- These people write/illustrate/edit/score books, cds, etc.; whatever we sell.
create table contributors (
  id                integer not null references people(id),
  legacy_contrib_id integer, -- backend system's "Author ID"
  websiteurl        varchar(100),
  updated_on        timestamp with time zone not null,
  updated_by        varchar(30) not null,
  created_on        timestamp with time zone not null,
  created_by        varchar(30) not null,
primary key(id)
);

-- schools --
create table schools (
  id              integer not null default nextval('schools_id_seq'),
  name            varchar(100) not null,
  open_time_24hr  time,
  close_time_24hr time,
  age_start       integer,
  age_end         integer,
  enrollment      integer,
  is_active       boolean not null default 't',
  kind            varchar(20), -- nursery, primary, secondary, etc.  perhaps we need a check or fk constraint?
  jde_an8         integer, -- prm, was dept_of_ed_id
  clubs_asssch    integer, -- custid of this school on custmr
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id),
unique(jde_an8),
unique(clubs_asssch)
);

-- enrollments -- Associates children and schools
create table enrollments (
  child_id      integer not null references children(id),
  school_id     integer not null references schools(id),
  start_date    date not null,
  end_date      date check (end_date >= start_date),
  updated_on    timestamp with time zone not null,
  updated_by    varchar(30) not null,
  created_on    timestamp with time zone not null,
  created_by    varchar(30) not null,  
primary key (child_id, school_id)
);

-- ptas -- Associates parents and schools.
create table ptas (
  parent_id     integer not null references parents(id),
  school_id     integer not null references schools(id),
  role          varchar(20),
  is_active     boolean not null default 't',
  updated_on    timestamp with time zone not null,
  updated_by    varchar(30) not null,
  created_on    timestamp with time zone not null,
  created_by    varchar(30) not null,  
primary key (parent_id, school_id)
);

-- school_staff -- Associates (school) staff with schools.
create table school_staff (
  staff_id      integer not null references staff(id),
  school_id     integer not null references schools(id),
  role          varchar(20), -- examples: teaches, headmaster, assistent, etc. perhaps we need a check or fk constraint?
  start_date    date,
  end_date      date check (end_date > start_date),
  contract_type varchar(30),
  updated_on    timestamp with time zone not null,
  updated_by    varchar(30) not null,
  created_on    timestamp with time zone not null,
  created_by    varchar(30) not null,  
unique (staff_id),
primary key (staff_id, school_id)
);

-- school_employees -- Associates (Scholastic) employees with schools.
create table school_employees (
  employee_id  integer not null references employees(id),
  school_id    integer not null references schools(id),
  team         varchar(30),
  is_active    boolean not null default 't',
  updated_on   timestamp with time zone not null,
  updated_by   varchar(30) not null,
  created_on   timestamp with time zone not null,
  created_by   varchar(30) not null,  
primary key (employee_id, school_id)
);

-- head category lookup table --
create table head_categories (
  id                varchar(3) not null,
  title             varchar(100) not null,
  short_description varchar(200), 
  long_description  text,
  updated_on        timestamp with time zone not null,
  updated_by        varchar(30) not null,
  created_on        timestamp with time zone not null,
  created_by        varchar(30) not null,  
primary key (id)  
); 

-- subject category lookup table --
create table subject_categories (
  id                varchar(3) not null,
  title             varchar(100) not null,
  short_description varchar(200), 
  long_description  text,
  updated_on        timestamp with time zone not null,
  updated_by        varchar(30) not null,
  created_on        timestamp with time zone not null,
  created_by        varchar(30) not null,  
primary key (id)  
); 

-- key brand lookup table --
create table key_brands (
  id                varchar(3) not null,
  title             varchar(100) not null,
  short_description varchar(200), 
  long_description  text,
  updated_on        timestamp with time zone not null,
  updated_by        varchar(30) not null,
  created_on        timestamp with time zone not null,
  created_by        varchar(30) not null,  
primary key (id)  
); 

-- products -- Items or Packs of items that we sell.
create table products (
  id                   integer not null default nextval('products_id_seq'),
  title                varchar(200) not null,
  isbn                 varchar(20),
  upn                  integer, -- prm
  short_description    text,
  long_description     text,
  head_category_id     varchar(3) references head_categories(id),
  subject_category_id  varchar(3) references subject_categories(id),
  key_brand_id         varchar(3) references key_brands(id),
  vat_type             varchar(10),                     
  website_url          varchar(100),
  pub_date             date, -- prm, moved from item_publishers
  release_date         date, -- prm, moved from item_publishers and removed not null constraint
  is_active            boolean not null default 't',
  updated_on           timestamp with time zone not null,
  updated_by           varchar(30) not null,
  created_on           timestamp with time zone not null,
  created_by           varchar(30) not null,  
primary key (id)  
);

-- related_products -- table to relate products together that have no formal series/pack links
create table related_products (
  parent_id           integer not null references products(id),
  child_id	          integer not null references products(id),
primary key (parent_id, child_id)
);

-- item_formats lookup table --
create table item_formats (
  id                varchar(3) not null,
  title             varchar(100) not null,
  short_description varchar(200), 
  long_description  text,
  updated_on        timestamp with time zone not null,
  updated_by        varchar(30) not null,
  created_on        timestamp with time zone not null,
  created_by        varchar(30) not null,  
primary key (id)  
); 

-- publishers --
Create Table publishers (
  id              varchar(3) not null,
  name            varchar(50) not null,
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- items -- Individual things that we sell, like a book: "Chitty Chitty Bang Bang".
Create Table items (
  id              integer not null references products(id),
  publisher_id    varchar(3) references publishers(id),
  format_id       varchar(3) references item_formats(id),
  extent          varchar(100),
  age_start       integer,
  age_end         integer,
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- item_contributors --
Create Table item_contributors (
  contributor_id  integer not null references contributors(id),
  item_id         integer not null references items(id),
  kind            varchar(30), -- e.g., writer / illustrator / editer / composer . . .
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key (contributor_id, item_id)
);

-- packs -- Collection of individual items that we sell AS A PACKAGE: "The Ian Fleming Pack".
Create Table packs (
  id              integer not null references products(id),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- items_packs -- 
create table items_packs (
  item_id        integer not null references items(id),
  pack_id        integer not null references packs(id),    
  updated_on     timestamp with time zone not null,
  updated_by     varchar(30) not null,
  created_on     timestamp with time zone not null,
  created_by     varchar(30) not null,
primary key(item_id, pack_id)
);

-- feature groups --
create table feature_groups (
  id                varchar(4) not null,
  title             varchar(100) not null,
  short_description varchar(200), 
  long_description  text,
  updated_on        timestamp with time zone not null,
  updated_by        varchar(30) not null,
  created_on        timestamp with time zone not null,
  created_by        varchar(30) not null,  
primary key (id)  
); 

-- features -- 
Create Table features (
  id                varchar(3) not null,
  group_id          varchar(4) not null references feature_groups(id),
  title             varchar(100) not null, 
  short_description varchar(200) not null, 
  long_description  text, 
  updated_on        timestamp with time zone not null,
  updated_by        varchar(30) not null,
  created_on        timestamp with time zone not null,
  created_by        varchar(30) not null,
primary key(id)
);

-- feature products -- 
Create Table features_products (
  feature_id  varchar(3) not null references features(id),
  product_id  integer not null references products(id),
  updated_on  timestamp with time zone not null,
  updated_by  varchar(30) not null,
  created_on  timestamp with time zone not null,
  created_by  varchar(30) not null,
primary key(feature_id, product_id)
);

-- series --
Create Table series (
  id                  integer not null default nextval('series_id_seq'),
  pd_srs_id           integer, -- prm legacy hook, can be null for series of series
  title               varchar(100) not null,
  member_of           integer references series(id),    -- Don't panic, this is nullable. With it we can make a hierarchy of series.  (Cool!)
  short_description   text,
  description	      text,
  updated_on          timestamp with time zone not null,
  updated_by          varchar(30) not null,
  created_on          timestamp with time zone not null,
  created_by          varchar(30) not null,
primary key(id)
);

-- products_series -- 
Create Table products_series (
  product_id     integer not null references products(id),
  series_id      integer not null references series(id),  
  updated_on     timestamp with time zone not null,
  updated_by     varchar(30) not null,
  created_on     timestamp with time zone not null,
  created_by     varchar(30) not null,
primary key(product_id, series_id)
);

-- assets kinds lookup -- 
Create Table asset_kinds (
  kind        varchar(100) not null,  
  description text, 
  updated_on  timestamp with time zone not null,
  updated_by  varchar(30) not null,
  created_on  timestamp with time zone not null,
  created_by  varchar(30) not null,  
primary key (kind)  
);

-- assets -- 
Create Table assets (
  id                     integer not null default nextval('assets_id_seq'),
  title                  varchar(200) not null,
  kind                   varchar(100) not null references asset_kinds(kind),  -- cover, sameple page, etc...
  description            text,
  is_visible             boolean not null default 't',
  updated_on             timestamp with time zone not null,
  updated_by             varchar(30) not null,
  created_on             timestamp with time zone not null,
  created_by             varchar(30) not null,  
primary key (id)  
);


-- assets_derivatives -- 
Create Table asset_derivatives (
  id                     integer not null default nextval('asset_derivatives_id_seq'),
  asset_id               integer not null references assets(id),
  size                   varchar(20), -- small, medium, large
  mime_type              varchar(20) not null, -- image/jpeg, binary/pdf, etc...
  width                  integer,
  height                 integer,
  bytes                  integer,
  pages                  integer,
  duration               decimal(11,4),
  file_pointer           varchar(256),
  background             varchar(20), -- background colour, null=transparent, else white, yellow etc.
  updated_on             timestamp with time zone not null,
  updated_by             varchar(30) not null,
  created_on             timestamp with time zone not null,
  created_by             varchar(30) not null,  
primary key (id)  
);

create index asset_derivatives_asset_id_idx on asset_derivatives (asset_id);

-- assets_products --
Create Table assets_products (
  asset_id        integer not null references assets(id),
  product_id      integer not null references products(id),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(asset_id, product_id)
);

-- assets_contributors --
Create Table assets_contributors (
  asset_id        integer not null references assets(id),
  contributor_id  integer not null references contributors(id),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(asset_id, contributor_id)
);

-- assets_series --
Create Table assets_series (
  asset_id        integer not null references assets(id),
  series_id       integer not null references series(id),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(asset_id, series_id)
);

-- shops --
Create Table shops (
  id                      integer not null default nextval('shops_id_seq'),
  name                    varchar(30) not null,
  description             varchar(100),
  start_date              date not null,
  end_date                date check (end_date >= start_date),
  free_shipping_level_gbp decimal(11,4),
  updated_on              timestamp with time zone not null,
  updated_by              varchar(30) not null,
  created_on              timestamp with time zone not null,
  created_by              varchar(30) not null,
primary key(id)         
);

-- catalogs -- 
Create Table catalogs (
  id                 integer not null default nextval('catalogs_id_seq'),
  shop_id            integer not null references shops(id),
  description        varchar(100),
  start_date         date not null,
  end_date           date check (end_date >= start_date),
  clubs_offer_code   varchar(10),  -- prm, changed from catalog_id
  updated_on         timestamp with time zone not null,
  updated_by         varchar(30) not null,
  created_on         timestamp with time zone not null,
  created_by         varchar(30) not null,
primary key(id)
);

-- offers --
Create Table offers (
  id                   integer not null default nextval('offers_id_seq'),
  product_id           integer not null references products(id),
  catalog_id           integer not null references catalogs(id),
  offer_price_gbp      decimal(11,4) not null,
  retail_price_gbp     decimal(11,4),
  discount_code        varchar(20),
  discount_pct         decimal(5,2),
  can_buy_with_voucher boolean not null,
  start_date           date not null,
  end_date             date check (end_date >= start_date),
  clubs_seq_no         integer not null, -- prm, changed from order_id
  mags_source_code     varchar(5),       -- prm, added for magazines  
  search_text          text,     -- prm, made text from varchar(100)
  is_active            boolean not null default 't', -- prm, if for some reason we want to take it off the site
  is_purchasable       boolean not null, -- prm, to hide things like promo items...
  updated_on           timestamp with time zone not null,
  updated_by           varchar(30) not null,
  created_on           timestamp with time zone not null,
  created_by           varchar(30) not null,
primary key(id)
);

create index offers_product_id_idx on offers(product_id);

-- clubsnfairs -- For Clubs and/or Fairs.  Is there a more abstract name for this?  Please tell me.
Create Table clubsnfairs (
  id              integer not null default nextval('clubsnfairs_id_seq'),
  school_id       integer not null references schools(id),
  person_id       integer references people(id),
  kind            varchar(10) not null,
  status          varchar(10) not null,
  delivery_date   date not null,
  start_date      date not null,
  end_date        date not null check (end_date >= start_date),
  collection_date date,
  order_deadline  date,
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- clubsnfair_shops -- Associates a club or fair with an on-line shop.
Create Table clubsnfair_shops (
  cluborfair_id  integer not null references clubsnfairs(id),
  shop_id        integer not null references shops(id),
  theme          varchar(20),
  updated_on     timestamp with time zone not null,
  updated_by     varchar(30) not null,
  created_on     timestamp with time zone not null,
  created_by     varchar(30) not null,
unique(cluborfair_id, shop_id)
);

-- tags --
Create Table tags (
  id              integer not null default nextval('tags_id_seq'),
  name            varchar(10) not null unique,
  description     varchar(50) not null,
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- person_phones --
Create Table person_phones (
  person_id       integer not null references people(id),
  tag_id          integer not null references tags(id),
  number          varchar(20) not null,
  extension       varchar(10),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key (person_id, tag_id)
);

-- person_emails --
Create Table person_emails (
  person_id       integer not null references people(id),
  tag_id          integer not null references tags(id),
  email           varchar(60) not null,
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key (person_id, tag_id)
);

-- school_phones --
Create Table school_phones (
  school_id       integer not null references schools(id),
  tag_id          integer not null references tags(id),
  number          varchar(20) not null,
  extension       varchar(10),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key (school_id, tag_id)
);

-- countries --
-- Contains the list of countries to show on the web site + mappings to the clubs country code
-- United Kingdon is id 1
Create Table countries (
  id                 integer not null default nextval('countries_id_seq'),
  country            varchar(40) not null unique,
  clubs_country_code char(2) not null,
  updated_on         timestamp with time zone not null,
  updated_by         varchar(30) not null,
  created_on         timestamp with time zone not null,
  created_by         varchar(30) not null,
primary key(id)
);

-- addresses -- Addresses... for people (and schools (and publishers)).
Create Table addresses (
  id              integer not null default nextval('addresses_id_seq'),
  address_to      varchar(40),
  address_1       varchar(30) not null,
  address_2       varchar(30),
  address_3       varchar(30),
  city            varchar(30),
  state_prov_reg  varchar(30),
  postal_code     varchar(10),
  country_id      integer not null references countries(id),
  updated_on      timestamp with time zone not null,
  updated_by      varchar(30) not null,
  created_on      timestamp with time zone not null,
  created_by      varchar(30) not null,
primary key(id)
);

-- person_addresses --
Create Table person_addresses (
  address_id  integer not null references addresses(id),
  person_id   integer not null references people(id),
  tag_id      integer not null references tags(id),
  updated_on  timestamp with time zone not null,
  updated_by  varchar(30) not null,
  created_on  timestamp with time zone not null,
  created_by  varchar(30) not null,
primary key (address_id, person_id),
unique (address_id, person_id, tag_id)
);

-- school_addresses --
Create Table school_addresses (
  address_id  integer not null references addresses(id),
  school_id   integer not null references schools(id),
  tag_id      integer not null references tags(id),
  updated_on  timestamp with time zone not null,
  updated_by  varchar(30) not null,
  created_on  timestamp with time zone not null,
  created_by  varchar(30) not null,
primary key (address_id, school_id)
);

-- publisher_addresses --
Create Table publisher_addresses (
  address_id    integer not null references addresses(id),
  publisher_id  varchar(3) not null references publishers(id),
  tag_id        integer not null references tags(id),
  updated_on    timestamp with time zone not null,
  updated_by    varchar(30) not null,
  created_on    timestamp with time zone not null,
  created_by    varchar(30) not null,
primary key (address_id, publisher_id)
);

-- orders --
Create Table orders (
  id                       integer not null default nextval('orders_id_seq'),
  person_id                integer not null references people(id),
  order_date               timestamp with time zone not null,
  extract_date             timestamp with time zone,
  fill_date                date,
  voucher_value_earned_gbp decimal(11,4),
  comments                 varchar(200),  
  bill_to_address_to       varchar(40),
  bill_to_address_id       integer not null references addresses(id),
  ship_to_address_to       varchar(40),
  ship_to_address_id       integer not null references addresses(id),
  shipping_amount_gbp			 decimal(11,4) not null,
  use_elcs								 boolean not null default 'f',
  updated_on               timestamp with time zone not null,
  updated_by               varchar(30) not null,
  created_on               timestamp with time zone not null,
  created_by               varchar(30) not null,
primary key(id)
);

create index orders_person_id_idx on orders(person_id);

-- payments --
Create Table payments (
  id            integer not null default nextval('payments_id_seq'), -- For Rails Use Only!
  order_id      integer not null references orders(id),
  paid_on       timestamp with time zone, -- removed not null otherwise we can't save it
  amount_gbp    decimal(11,4) not null,
  method        varchar(20) not null check (method in ('ccd', 'inv')),
  comments      varchar(200),
	order_number  varchar(20),
  gpg_card_data text,
  updated_on    timestamp with time zone not null,
  updated_by    varchar(30) not null,
  created_on    timestamp with time zone not null,
  created_by    varchar(30) not null,
unique (order_id, paid_on),
primary key(id)
);

-- voucher_redemption --
Create Table voucher_redemption (
  id           integer not null default nextval('voucher_redemptions_id_seq'), -- For Rails Use Only!
  order_id     integer not null references orders(id),
  redeemed_on  timestamp with time zone not null,
  amount_gbp   decimal(11,4) not null,
  voucher_id   varchar(20) not null,
  comments     varchar(200),
  updated_on   timestamp with time zone not null,
  updated_by   varchar(30) not null,
  created_on   timestamp with time zone not null,
  created_by   varchar(30) not null,
unique (order_id, redeemed_on),
primary key(id)
);

-- order_line_items --
Create Table order_line_items (
  offer_id              integer not null references offers(id),
  order_id              integer not null references orders(id),
  quantity              integer not null,
  amount_paid_each_gbp  decimal(11,4) not null,
  comments              varchar(200),  
  ship_date             date,
  shiping_reference     varchar(60),
  updated_on            timestamp with time zone not null,
  updated_by            varchar(30) not null,
  created_on            timestamp with time zone not null,
  created_by            varchar(30) not null,
primary key (order_id, offer_id)
);

-- promos --
Create Table promos (
  id                    integer not null default nextval('promos_id_seq'),
  catalog_id            integer not null references catalogs(id),
  title                 varchar(100) not null,
  short_description     varchar(200), 
  long_description      text,
  promo_kind            varchar(100) not null, -- order discount, item discount, add items
  cond_value            decimal(11,4), -- could be L or % ???
  cond_qty              integer, -- number of items in basket to required to consider looking at the offer?
  cond_code             varchar(20), -- what a user might enter
  result_value          decimal(11,4), -- could be L or % ???
  result_kind           varchar(20), -- percentage, fixed
  restriction           varchar(10), -- order, customer
  start_date            timestamp with time zone not null,
  end_date              timestamp with time zone not null,
  updated_on            timestamp with time zone not null,
  updated_by            varchar(30) not null,
  created_on            timestamp with time zone not null,
  created_by            varchar(30) not null,  
primary key (id)  
);

-- promo_offer_conditions --
Create Table promo_offer_conditions (
  promo_id             integer not null references promos(id),
  offer_id             integer not null references offers(id),
  updated_on           timestamp with time zone not null,
  updated_by           varchar(30) not null,
  created_on           timestamp with time zone not null,
  created_by           varchar(30) not null,
primary key (promo_id, offer_id)
);

-- promo_results --
Create Table promo_results (
  promo_id             integer not null references promos(id),
  offer_id             integer not null references offers(id),
  discount_pct         decimal(3,2) not null,
  updated_on           timestamp with time zone not null,
  updated_by           varchar(30) not null,
  created_on           timestamp with time zone not null,
  created_by           varchar(30) not null,
primary key (promo_id, offer_id)
);

-- order_line_items_promos -- Mapping many order line items to many promotions since 2006.
Create Table order_line_items_promos (
  order_id             integer not null, 
  offer_id             integer not null, 
  promo_id             integer not null references promos(id),
primary key (order_id, offer_id, promo_id),
foreign key (order_id, offer_id) references order_line_items(order_id, offer_id)
);

-- Age groups - different age categorization schemes
-- eg. English, Scottish, Key Stages
create table age_groups (
  id                   integer not null default nextval('age_groups_id_seq'),
  title                varchar(100) not null,
  primary key(id)
);

-- Age group years - map age groups onto age ranges
-- eg. English Nursery -> 0-4
create table age_group_years (
  id                   integer not null default nextval('age_group_years_id_seq'),
  age_group_id         integer not null references age_groups(id),
  title                varchar(100) not null,
  short_description    varchar(200), 
  long_description     text,
  age_start            integer not null,
  age_end              integer not null,
  primary key(id)
);

-- Shipping
--
-- Shops define their own shipping regions
-- If a country is not in a region then we don't ship there
-- A country can only appear in one region per shop
--
--         Shops
--           |
--        Regions     
--         /   \
--  Countries  Shipping

-- Regions
-- eg. UK, Non-UK
create table regions (
  id                   integer not null default nextval('regions_id_seq'),
  shop_id              integer not null references shops(id),
  title                varchar(100) not null,
  primary key(id),
  unique (shop_id, id) -- needed for fk from region_countries
);

-- Region countries
create table region_countries (
  shop_id              integer not null,
  country_id           integer not null references countries(id),
  region_id            integer not null,
  primary key (shop_id, country_id),
  foreign key (shop_id, region_id) references regions(shop_id, id)
);

-- Region shipping
create table region_shipping_charges (
  region_id            integer not null references regions(id),
  order_threshold_gbp  decimal(11,4) not null, -- use zero to mean no threshold
  charge               decimal(11,4) not null,
  kind                 varchar(20) not null, -- gbp, pct of order
  primary key(region_id, order_threshold_gbp)
);

create table logged_exceptions (
  id              integer not null default nextval('logged_exceptions_id_seq'),
  exception_class varchar(255),
  controller_name varchar(255),
  action_name     varchar(255),
  message         varchar(255),
  backtrace       text,
  environment     text,
  request         text,
  created_at      timestamp with time zone not null,
  primary key (id)
);

create table sessions (
  id      integer not null default nextval('sessions_id_seq'),
  sessid  char(32),
  data    text,
  updated_at timestamp with time zone not null,
  primary key (id)
);

-- custmr - Cache of RHLIVDTA/CUSTMR
create table custmr (
       custid			integer not null primary key,
       custyp			char(1),
       saltcs			char(4),
       initcs			char(3),
       snamcs			varchar(30),
       addr1			varchar(30),
       addr2			varchar(30),
       addr3			varchar(30),
       addr4			varchar(30),
       addr5			varchar(30),
       postcd			varchar(20),
       cntry			char(2),
       esttyp			char(2),
       asssch			integer,
       dontml			char(1),
       stcs			char(3),
       joincs			integer,
       lodcs			integer,
       rftp			char(1),
       qordcs			integer,
       leaid			integer,
       phonhm			varchar(20),
       jdesch			integer
);

create index custmr_jdesch_idx on custmr(jdesch) where jdesch <> 0;
create index custmr_asssch_idx on custmr(asssch) where asssch <> 0;
create index custmr_postcd_idx on custmr(translate(upper(postcd), ' ', ''));

-- Current Offers -- Offers that are, well, current; as in within the date ranges for shops, catalogs, and offers.
create or replace view current_offers 
as
select o.*
,      s.id as shop_id
from   offers o
,      catalogs c
,      shops s
where  c.id = o.catalog_id
 and   s.id = c.shop_id
 and   now() between c.start_date and c.end_date
 and   now() between s.start_date and s.end_date
 and   now() between o.start_date and o.end_date
;

-- Current Products -- Products that is_active and are available as current offers, with offer_id and catalog_id.
create or replace view current_products
as
select co.id as offer_id, co.catalog_id, p.*
from   products p
left join current_offers co on co.product_id = p.id
where  p.is_active
;

-- view of person_addresses including tag name and country details
create view person_addresses_flat as
select pa.person_id
,      pa.address_id
,      t.name as tag_name
,      a.address_to
,      a.address_1
,      a.address_2
,      a.address_3
,      a.city
,      a.state_prov_reg
,      a.postal_code
,      c.id as country_id
,      c.country
,      c.clubs_country_code
from   person_addresses pa
,      tags t
,      addresses a
,      countries c
where  pa.address_id = a.id
and    pa.tag_id = t.id
and    c.id = a.country_id
;

-- people view used by ecm listener
create view virtual_people as
select p.id
,      p.prefix
,      p.given_name
,      p.middle_name
,      p.surname
,      trim(coalesce(p.given_name, '') || ' ' || coalesce(p.middle_name, '')) as initials
,      h.address_to as home_address_to
,      h.address_1 as home_address_1
,      h.address_2 as home_address_2
,      h.address_3 as home_address_3
,      h.city as home_city
,      h.state_prov_reg as home_state_prov_reg
,      h.postal_code as home_postal_code
,      h.country_id as home_country_id
,      h.country as home_country
,      h.clubs_country_code as home_clubs_country_code
,      s.jde_an8 as school_jde_an8
,      s.clubs_asssch as school_clubs_asssch
from   people p
         left join person_addresses_flat h on (h.person_id = p.id and h.tag_name = 'home')
         left join school_staff ss on (ss.staff_id = p.id)
         left join schools s on (ss.school_id = s.id)
;

-- queue to store person ids whenever a virtual_person changes
create table listener_queue (
	id		   integer primary key default(nextval('listener_queue_seq'))
,	relation	   text not null	-- the relation which changed
,	relation_pk_value  integer not null     -- the pk of the changed relation record
);

-- this stores the previous values for virtual people
create table listener_state (
	id		   integer primary key default(nextval('listener_state_seq'))
,	relation	   text not null	-- the relation which changed
,	relation_pk_value  integer not null     -- the pk of the changed relation record
,       field              text not null
,       value              text
,       unique (relation, relation_pk_value, field)
);

-- create triggers to populate listener_queue
-- NOTE: make sure all dependents of the virtual_people view have appropriate triggers
-- otherwise we'll miss changes
-- people
create function people_listener() returns trigger as $$
begin
  if (tg_op = 'DELETE') then
    insert into listener_queue (relation, relation_pk_value) values ('people', old.id);
  else
    insert into listener_queue (relation, relation_pk_value) values ('people', new.id);
  end if;
  return null;
end;
$$ language plpgsql;

create trigger people_listener after insert or update or delete on people
  for each row execute procedure people_listener();

-- person_addresses
create function person_addresses_listener() returns trigger as $$
begin
  if (tg_op = 'DELETE') then
    insert into listener_queue (relation, relation_pk_value) values ('people', old.person_id);
  else
    insert into listener_queue (relation, relation_pk_value) values ('people', new.person_id);
  end if;
  return null;
end;
$$ language plpgsql;

create trigger person_addresses_listener after insert or update or delete on person_addresses
  for each row execute procedure person_addresses_listener();

-- addresses
create function addresses_listener() returns trigger as $$
declare
  x_address_id integer;
begin
  if (tg_op = 'DELETE') then
    x_address_id = old.id;
  else
    x_address_id = new.id;
  end if;

  -- Insert queue entries for all the person_ids this address is used on
  insert into listener_queue (relation, relation_pk_value)
    select 'people'
    ,      person_id
    from   person_addresses
    where  address_id = x_address_id;

  return null;
end;
$$ language plpgsql;

create trigger addresses_listener after insert or update or delete on addresses
  for each row execute procedure addresses_listener();

-- school_staff
create function school_staff_listener() returns trigger as $$
begin
  if (tg_op = 'DELETE') then
    insert into listener_queue (relation, relation_pk_value) values ('people', old.staff_id);
  else
    insert into listener_queue (relation, relation_pk_value) values ('people', new.staff_id);
  end if;
  return null;
end;
$$ language plpgsql;

create trigger school_staff_listener after insert or update or delete on school_staff
  for each row execute procedure school_staff_listener();