-- you might want to limit the space here (or create the database with
-- the GUI tools)
-- create database bse
--   on (
--   name=bse_dat,
--   filename='e:\msde\data\bse.mdf'
-- )
-- go
drop procedure bse_update_article
go
drop procedure bse_add_article
go
drop proc bse_get_articles
go
drop proc bse_get_an_article
go
drop proc bse_articles_by_level
go
drop proc bse_articles_by_parent
go
drop proc bse_delete_article
go
drop table article
go
CREATE TABLE article (
  id integer NOT NULL identity,
  -- 0 for the entry page
  -- -1 for top-level sections (shown in side menu)
  parentid integer DEFAULT 0 NOT NULL,

  -- the order to display articles in  
  -- used for ordering sibling articles
  displayOrder integer not NULL default 0,
  title varchar(64) DEFAULT '' NOT NULL,
  titleImage varchar(64) not null,
  body text NOT NULL,

  -- thumbnail image
  thumbImage varchar(64) not null default '',
  thumbWidth integer not null,
  thumbHeight integer not null,

  -- position of first image for this article
  imagePos char(2) not null,
  release datetime DEFAULT '2000-01-01 00:00:00' NOT NULL,
  expire datetime DEFAULT '2099-12-31 00:00:00' NOT NULL,
  keyword varchar(255),

  -- the template in $TMPLDIR used to generate this as HTML
  template varchar(127) DEFAULT '' NOT NULL,

  -- a link to the page generated for this article
  -- if this is blank then no page is generated
  -- this is combined with the base of the site to get the file
  -- written to during generation
  link varchar(64) not null,
  admin varchar(64) not null,

  -- if there are more child articles than this, display links/summaries
  -- if the same of fewer, embed the articles
  -- the template can ignore this
  threshold integer not null default 3,

  -- the length of summary to display for this article
  summaryLength integer DEFAULT '200' NOT NULL,

  -- the class whose generate() method generates the page
  generator varchar(20) not null default 'article',

  -- the level of the article, 1 for top-level
  artLevel integer not null,

  -- for listed:
  -- 0 - don't list
  -- 1 - list everywhere
  -- 2 - list in sections, but not on the menu
  listed smallint not null default 1,

  -- date last modified
  lastModified datetime not null default getdate(),
  PRIMARY KEY (id)
)
go
create index article_date_index on article
(release, expire, id)
go
create index article_displayOrder_index on article
(displayOrder)
go
create index article_parentId_index on article
(parentId)
go
create index article_level_index on article
(artLevel, id)
go
drop proc bse_clear_search_index
go
drop proc bse_add_searchindex
go
drop proc bse_search
go
drop proc bse_search_wc
go
DROP TABLE searchindex
go
CREATE TABLE searchindex (
  id varbinary(200) NOT NULL,
  -- a comma-separated lists of article and section ids
  articleIds varchar(255) default '' not null,
  sectionIds varchar(255) default '' not null,
  scores varchar(255) default '' not null,
  PRIMARY KEY (id)
)
go
drop procedure bse_add_image
go
drop procedure bse_update_image
go
drop proc bse_get_images
go
drop proc bse_delete_image
go
drop proc bse_get_article_images
go
DROP TABLE image
go
CREATE TABLE image (
  id integer NOT NULL identity,  
  articleId integer not null,
  image varchar(64) DEFAULT '' NOT NULL,
  alt varchar(255) DEFAULT '[Image]' NOT NULL,
  width integer,
  height integer,
  url varchar(255),
  PRIMARY KEY (id)
)
go
DROP TABLE sessions
go
CREATE TABLE sessions (
  id char(32) not null primary key,
  a_session image,
  -- so we can age this table
 
 whenChanged timestamp
)
go
drop procedure bse_update_product
go
drop procedure bse_add_product
go
drop proc bse_get_products
go
drop proc bse_get_a_product
go
DROP TABLE product
go
create table product (
  -- fkey to article id
  articleId integer not null,

  summary varchar(255) not null,

  -- number of days it typically takes to supply this item
  leadTime integer not null default 0,

  -- prices are in cents
  retailPrice integer not null,
  wholesalePrice integer,

  -- amount of GST on this item
  gst integer not null,

  options varchar(255) not null,
  
  primary key(articleId)
)
go
drop procedure bse_add_order
go
drop procedure bse_update_order
go
drop procedure bse_get_orders
go
drop proc bse_add_order_item
go
drop proc bse_order_items_by_order
go
DROP TABLE order_item;
create table order_item (
  id integer not null identity,
  -- foreign key to product
  productId integer not null,

  -- foreign key to order
  orderId integer not null,
  
  -- how many :)
  units integer not null,

  -- unit prices
  price integer not null,
  wholesalePrice integer not null,
  gst integer not null,

  options varchar(255) not null,
  
  primary key (id)
)
go
create index order_item_order on order_item
(orderId, id)
go
create procedure bse_update_article
   @id integer,
   @parentid integer,
   @displayOrder integer,
   @title varchar(64),
   @titleImage varchar(64),
   @body text,
   @thumbImage varchar(64),
   @thumbWidth integer,
   @thumbHeight integer,
   @imagePos char(2),
   @release datetime,
   @expire datetime,
   @keyword varchar(255),
   @template varchar(127),
   @link varchar(64),
   @admin varchar(64),
   @threshold integer,
   @summaryLength integer,
   @generator varchar(20),
   @artLevel integer,
   @listed integer,
   @lastModified datetime
as
  update article set parentid = @parentid, displayOrder = @displayOrder, 
	title = @title, titleImage = @titleImage, body = @body, 
	thumbImage = @thumbImage, thumbWidth = @thumbWidth, 
	thumbHeight = @thumbHeight, imagePos = @imagePos, 
	release = @release, expire = @expire, keyword = @keyword, 
	template = @template, link = @link, admin = @admin, 
	threshold = @threshold, summaryLength = @summaryLength, 
	generator = @generator, artLevel = @artLevel, listed = @listed, 
	lastModified = @lastModified
  where id = @id
go
create procedure bse_add_article
   @parentid integer,
   @displayOrder integer,
   @title varchar(64),
   @titleImage varchar(64),
   @body text,
   @thumbImage varchar(64),
   @thumbWidth integer,
   @thumbHeight integer,
   @imagePos char(2),
   @release datetime,
   @expire datetime,
   @keyword varchar(255),
   @template varchar(127),
   @link varchar(64),
   @admin varchar(64),
   @threshold integer,
   @summaryLength integer,
   @generator varchar(20),
   @artLevel integer,
   @listed integer,
   @lastModified datetime
as
  insert into article(parentid, displayOrder, title, titleImage, body,
	thumbImage, thumbWidth, thumbHeight, imagePos, release, expire,
	keyword, template, link, admin, threshold, summaryLength, 
	generator, artLevel, listed, lastModified)
  values (@parentid, @displayOrder, @title, @titleImage, @body, @thumbImage, 
	@thumbWidth, @thumbHeight, @imagePos, @release, @expire, @keyword, 
	@template, @link, @admin, @threshold, @summaryLength, @generator, 
	@artLevel, @listed, @lastModified)
go
create proc bse_get_articles
as
  select * from article
go
create proc bse_get_an_article
  @id integer
as
  select * from article where id = @id
go
create proc bse_articles_by_level
  @artLevel integer
as
  select * from article where artLevel = @artLevel
go
create proc bse_articles_by_parent
  @parentid integer
as
  select * from article where parentid = @parentid
go
create proc bse_delete_article
  @id integer
as
  delete from article where id = @id
go
create proc bse_get_images
as
  select * from image
go
create proc bse_delete_image
  @id integer
as
  delete from image where id = @id
go
create procedure bse_update_image
  @id integer,
  @articleId integer,
  @image varchar(64),
  @alt varchar(255),
  @width integer,
  @height integer
as
  update image
   set articleId = @articleId, image = @image, alt = @alt, width = @width, 
	height = @height
   where id = @id
go
create procedure bse_add_image
  @articleId integer,
  @image varchar(64),
  @alt varchar(255),
  @width integer,
  @height integer
as
  insert into image(articleId, image, alt, width, height)
	values(@articleId, @image, @alt, @width, @height)
go
create proc bse_get_article_images
  @articleId integer
as
  select * from image where articleId = @articleId
go
create procedure bse_update_product
  @articleId integer,
  @summary varchar(255),
  @leadTime integer,
  @retailPrice integer,
  @wholesalePrice integer,
  @gst integer,
  @options varchar(255)
as
  update product
   set summary = @summary, leadTime = @leadTime, retailPrice = @retailPrice, 
	wholesalePrice = @wholesalePrice, gst = @gst, options = @options
   where articleId = @articleId
go
create procedure bse_add_product
  @articleId integer,
  @summary varchar(255),
  @leadTime integer,
  @retailPrice integer,
  @wholesalePrice integer,
  @gst integer,
  @options varchar(255)
as
  insert into product(articleId, summary, leadTime, retailPrice, 
	wholesalePrice, gst, options)
  values(@articleId, @summary, @leadTime, @retailPrice, 
	@wholesalePrice, @gst, @options)
go
create proc bse_get_products
as
  select article.*, product.* from article, product where id = articleId
go
create proc bse_get_a_product
  @id integer
as
  select article.*, product.* from article, product 
  where id=@id and articleId = id
go

create proc bse_clear_search_index
as
  delete from searchindex
go
create proc bse_add_searchindex
  @phrase varchar(200),
  @articleIds varchar(255),
  @sectionIds varchar(255),
  @scores varchar(255)
as
  insert into searchindex values(cast(@phrase as varbinary(200)), @articleIds, @sectionIds, @scores)
go
create proc bse_search
  @phrase varchar(200)
as
  select * from searchindex where id = cast(@phrase as varbinary)
go
create proc bse_search_wc
  @phrase varbinary(200)
as
  select * from searchindex where id like @phrase
go
create proc bse_add_order_item
  @productId integer,
  @orderId integer,
  @units integer,
  @price integer,
  @wholesalePrice integer,
  @gst integer,
  @options varchar(255)
as
  insert into order_item(productId, orderId, units, price, 
	wholesalePrice, gst, options)
   values(@productId, @orderId, @units, @price, @gst, @wholesalePrice,
	@options)
go
create proc bse_order_items_by_order
  @orderId integer
as
 select * from order_item where orderId = @orderId
go
