/*
    Create tables, foreign keys, and indexes.
    Created: 2008-10-31
*/
 
-- Drop tables - at first children then parents
IF OBJECT_ID('SearchWord', 'U') IS NOT NULL DROP TABLE SearchWord
GO
IF OBJECT_ID('BookUpdate', 'U') IS NOT NULL DROP TABLE BookUpdate
GO
IF OBJECT_ID('Book', 'U') IS NOT NULL DROP TABLE Book
GO
IF OBJECT_ID('SyncKeyword', 'U') IS NOT NULL DROP TABLE SyncKeyword
GO
IF OBJECT_ID('PublisherSearchWord', 'U') IS NOT NULL DROP TABLE PublisherSearchWord
GO
IF OBJECT_ID('Publisher', 'U') IS NOT NULL DROP TABLE Publisher
GO
IF OBJECT_ID('CategorySearchWord', 'U') IS NOT NULL DROP TABLE CategorySearchWord
GO
IF OBJECT_ID('Category', 'U') IS NOT NULL DROP TABLE Category
GO
IF OBJECT_ID('UserKeyword', 'U') IS NOT NULL DROP TABLE UserKeyword
GO
IF OBJECT_ID('BookType', 'U') IS NOT NULL DROP TABLE BookType
GO
 
-- Create dictionaries (parents)
 
/********************
	Publisher
*********************/
CREATE TABLE Publisher (
    PubId INT IDENTITY(1,1) PRIMARY KEY,
    -- DisplayName of Publisher
    PubName NVARCHAR(250) NOT NULL DEFAULT '',
    -- Publisher name in a form kept in the Amazon database (used for lookups as a key)
    AmazonPublisher NVARCHAR(250) NOT NULL DEFAULT ''
) ON [PRIMARY]
GO
 
/********************
	Category
*********************/
CREATE TABLE Category (
    CatId INT IDENTITY(1,1) PRIMARY KEY,
    -- DisplayName of Category
    CatName NVARCHAR(50) NOT NULL DEFAULT '',
    -- Description of the category provided by an administrator
    CatDesc NVARCHAR(250) NOT NULL DEFAULT '',
    -- Counter of "category popularity"
    CatCounter INT NOT NULL DEFAULT 0
) ON [PRIMARY]
GO
 
/********************
	BookType
*********************/
CREATE TABLE BookType (
    -- Book type id
    BookTypeId INT PRIMARY KEY,
    -- A short description (one or two words) of the book type 
    BookTypeDesc VARCHAR(50) NOT NULL DEFAULT ''
) ON [PRIMARY]
GO
 
-- Create data tables
 
/********************
	SyncKeyword
*********************/
CREATE TABLE SyncKeyword (
    -- Id is used for sorting. It has to be inserted explicitly.
    SyncKeyId INT NOT NULL PRIMARY KEY,
    -- Keyword used to update catalog or find new books in Amazon, e.g. directx, quaternion, 3d
    SyncKey VARCHAR(50) NOT NULL DEFAULT '',
    -- Description of the keyword.
    SyncKeyDesc VARCHAR(100) NOT NULL DEFAULT '',
    -- The Amazon BrowseNodeId of a category related to the keyword.
    -- NOTE: BrowseNodeIds are locale-specific (Books Node Id: CA:927726, US:1000)
    AmazonBrowseNodeId INT NOT NULL DEFAULT 0,
    -- Default category for the sync keyword (optional)
    DefaultCatId INT NOT NULL CONSTRAINT FK_Category_SyncKeyword FOREIGN KEY REFERENCES Category(CatId) DEFAULT 0
) ON [PRIMARY]
GO
 
/********************
    Book
 
    Remarks: We do not store ASIN here as the TaraRara Catalog does not allow books without ISBN. 
    For books with ISBN, ASIN is the same as the ISBN10.
*********************/
CREATE TABLE Book (
    BookId INT IDENTITY(1,1) PRIMARY KEY,
    -- Publisher of the Book
    PubId INT NOT NULL CONSTRAINT FK_Publisher_Book FOREIGN KEY REFERENCES Publisher(PubId),
    -- Category the Book belongs to
    CatId INT NOT NULL CONSTRAINT FK_Category_Book FOREIGN KEY REFERENCES Category(CatId),
    -- Full tile (together with subtitle) as in Amazon (should not be modified as it is used as a key in 
    -- the case of a missing ISBN)
    Title NVARCHAR(250) NOT NULL,
    -- A comma-separated list of authors as in Amazon
    Authors NVARCHAR(520) NOT NULL,
    -- 10-digit ISBN (no spaces or separators)
    Isbn10 CHAR(10) NOT NULL,
    -- 13-digit ISBN (no spaces or separators). 
    -- ISBN13 may be artificially created by prepending ASIN with "978" if a book does not have an ISBN.
    -- In such a case the IsAsin flag is 1 and ISBN13 should not be used to identify the book.
    Isbn13 CHAR(13) NOT NULL,
    -- Date of publication
    PubDate DATETIME NOT NULL,
    -- Some publication dates may miss the day part
    IsDayMissing BIT NOT NULL DEFAULT 0,
    -- Some publication dates (especially for old books) may miss the day and the month part
    IsMonthMissing BIT NOT NULL DEFAULT 0,
    -- Price of the Book
    Price MONEY NOT NULL,
    -- Average user rating (from 0.0 to 5.0)
    -- 2 (precision) - total number of decimal digits
    -- 1 (scale) - the number of decimal digits that can be stored to the right of the decimal point
    Rating DECIMAL(2,1) NOT NULL DEFAULT 0.0,
    -- Number of user ratings
    NumRatings INT NOT NULL DEFAULT 0,
    -- Number of pages
    NumPages INT NOT NULL DEFAULT 0,
    -- Hyperlink to the Book on the Amazon website
    AmazonBookLink VARCHAR(250) NOT NULL DEFAULT '',
    -- Hyperlink to the image of the Book on the Amazon website
    AmazonImageLink VARCHAR(250) NOT NULL DEFAULT '',
    -- DVD filepath to the Book (optional - for internal purposes)
    EBookPath VARCHAR(100) NOT NULL DEFAULT '',
    -- Keyword that was used to find the books in Amazon, e.g. directx, quaternion, 3d
    SyncKeyId INT NOT NULL CONSTRAINT FK_SyncKeyword_Book FOREIGN KEY REFERENCES SyncKeyword(SyncKeyId),
    -- BookType - default value is None
    BookTypeId INT NOT NULL CONSTRAINT FK_BookType_Book FOREIGN KEY REFERENCES BookType(BookTypeId) DEFAULT 0,
    -- Comment provided by an administrator during book evaluation
    Comment VARCHAR(250) NOT NULL DEFAULT '',
    -- The date and time the book was fetched from Amazon
    DateAdded DATETIME NOT NULL DEFAULT GETDATE(),
    -- The last date the book was updated.
    DateUpdated DATETIME NOT NULL DEFAULT GETDATE(),
    -- Indicates if the Isbn10 column contains ASIN or a real ISBN.
    IsAsin BIT NOT NULL DEFAULT 0,
    -- BookStatus: 1-New, 2-Private, 3-Public
    BookStatus INT NOT NULL DEFAULT 1 CHECK (BookStatus IN (1, 2, 3))
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX UX_Books_Isbn10 ON Book(Isbn10) -- used to request books from Amazon
CREATE UNIQUE INDEX UX_Books_Isbn13 ON Book(Isbn13) -- just for informational purposes
GO
 
/********************
	SearchWord
*********************/
CREATE TABLE SearchWord (
    WordId INT IDENTITY(1,1) PRIMARY KEY,
    -- Referenced Book
    BookId INT NOT NULL CONSTRAINT FK_Book_SearchWord FOREIGN KEY REFERENCES Book(BookId),
    -- A source field of the word (just for informational purposes): 
    -- 1-Book.Title, 2-Book.Author, 3-Book.Isbn10/13, 10-Publisher.PubName, 20-Category.CatName
    SearchWordType INT NOT NULL CHECK (SearchWordType IN (1, 2, 3, 10, 20)),
    -- Actual text of the search word
    Word NVARCHAR(50)
) ON [PRIMARY]
GO
 
/********************
	PublisherSearchWord
*********************/
CREATE TABLE PublisherSearchWord (
    WordId INT IDENTITY(1,1) PRIMARY KEY,
    -- Referenced Publisher
    PubId INT NOT NULL CONSTRAINT FK_Publisher_PublisherSearchWord FOREIGN KEY REFERENCES Publisher(PubId),
    -- Actual text of the search word
    Word NVARCHAR(50)
) ON [PRIMARY]
GO
 
/********************
	CategorySearchWord
*********************/
CREATE TABLE CategorySearchWord (
    WordId INT IDENTITY(1,1) PRIMARY KEY,
    -- Referenced Publisher
    CatId INT NOT NULL CONSTRAINT FK_Category_CategorySearchWord FOREIGN KEY REFERENCES Category(CatId),
    -- Actual text of the search word
    Word NVARCHAR(50)
) ON [PRIMARY]
GO
 
/*****************************
	BookUpdate
******************************/
CREATE TABLE BookUpdate (
    UpdateId INT IDENTITY(1,1) PRIMARY KEY,
    -- BookId from Book table 
    BookId INT NOT NULL CONSTRAINT FK_Book_BookUpdate FOREIGN KEY REFERENCES Book(BookId),
    -- Schema name of the updated attribute.
    -- AttrName can contain any attribute from the Book table. 
    -- Attributes of most interest are: PubDate, Price, Rating, and NumRatings.
    AttrName VARCHAR(50) NOT NULL DEFAULT '',
    -- Previous value of the updated attribute.
    -- NVARCHAR(520) is the type of Book.Authors
    OldValue NVARCHAR(520) NOT NULL DEFAULT '',
    -- Date/time the book's attribute has been updated
    DateUpdated DATETIME NOT NULL DEFAULT GETDATE()
) ON [PRIMARY]
GO
 
/*****************************
	UserKeyword
******************************/
CREATE TABLE UserKeyword (
    UserKeywordId INT IDENTITY(1,1) PRIMARY KEY,
    -- Keywords as entered by a user
    UserKeywords NVARCHAR(1000) NOT NULL DEFAULT '',
    -- Keywords entered by a user after formatting (i.e. converted to SearchWords). They are used for searching.
    FormattedKeywords NVARCHAR(1000) NOT NULL DEFAULT '',
    -- Date/time a user issued the search
    DateEntered DATETIME NOT NULL DEFAULT GETDATE(),
    -- User's ASP.NET SessionId
    SessionId CHAR(20) NOT NULL
) ON [PRIMARY]
GO
 
/*****************************
    Create triggers
******************************/
 
-- IMPORTANT: Book_RecordBookUpdates assumes that only a single row is updated at a time.
-- Remarks: We can't use the UPDATE() function as it indicates only that the column has been updated 
-- not if it has another value.
 
-- Triggers are dropped together with tables. To drop a trigger explicitly use the following code:
-- IF OBJECT_ID('Book_RecordBookUpdates', 'TR') IS NOT NULL DROP TRIGGER Book_RecordBookUpdates
-- GO
 
CREATE TRIGGER Book_RecordBookUpdates ON Book
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON
 
    -- DateUpdated is used by all fields.
    DECLARE @DateUpdated DATETIME
    SELECT @DateUpdated=DateUpdated FROM Inserted
 
    -- No need to check if the publisher, title, or author changed to a different value as this is already tested
    -- in the business layer in order to determine search words.
    IF UPDATE(PubId)
        INSERT INTO BookUpdate(del.BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'PubName', pub.PubName, @DateUpdated 
        FROM Deleted del
        JOIN Publisher pub ON pub.PubId=del.PubId
 
    IF UPDATE(Title) 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'Title', Title, @DateUpdated 
        FROM Deleted
 
    IF UPDATE(Authors) 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'Authors', Authors, @DateUpdated 
        FROM Deleted
 
    IF UPDATE(Isbn13) 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'Isbn13', Isbn13, @DateUpdated 
        FROM Deleted
 
    DECLARE @NewPubDate DATETIME, @OldPubDate DATETIME
    SELECT @NewPubDate=PubDate FROM Inserted
    SELECT @OldPubDate=PubDate FROM Deleted
 
    IF @NewPubDate<>@OldPubDate 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'PubDate', CONVERT(NVARCHAR(520), PubDate, 101), @DateUpdated 
        FROM Deleted -- use 'mm/dd/yyyy' format
 
    DECLARE @NewIsDayMissing BIT, @OldIsDayMissing BIT
    SELECT @NewIsDayMissing=IsDayMissing FROM Inserted
    SELECT @OldIsDayMissing=IsDayMissing FROM Deleted
 
    IF @NewIsDayMissing<>@OldIsDayMissing 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'IsDayMissing', IsDayMissing, @DateUpdated 
        FROM Deleted
 
    DECLARE @NewIsMonthMissing BIT, @OldIsMonthMissing BIT
    SELECT @NewIsMonthMissing=IsMonthMissing FROM Inserted
    SELECT @OldIsMonthMissing=IsMonthMissing FROM Deleted
 
    IF @NewIsMonthMissing<>@OldIsMonthMissing 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'IsMonthMissing', IsMonthMissing, @DateUpdated 
        FROM Deleted
 
    DECLARE @NewPrice MONEY, @OldPrice MONEY
    SELECT @NewPrice=Price FROM Inserted
    SELECT @OldPrice=Price FROM Deleted
 
    IF @NewPrice<>@OldPrice 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'Price', CONVERT(NVARCHAR(520), Price), @DateUpdated 
        FROM Deleted -- default formatting 42342.34
 
    DECLARE @NewRating DECIMAL(2,1), @OldRating DECIMAL(2,1)
    SELECT @NewRating=Rating FROM Inserted
    SELECT @OldRating=Rating FROM Deleted
 
    IF @NewRating<>@OldRating 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'Rating', CONVERT(NVARCHAR(520), Rating), @DateUpdated 
        FROM Deleted
 
    DECLARE @NewNumRatings INT, @OldNumRatings INT
    SELECT @NewNumRatings=NumRatings FROM Inserted
    SELECT @OldNumRatings=NumRatings FROM Deleted
 
    IF @NewNumRatings<>@OldNumRatings 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'NumRatings', CONVERT(NVARCHAR(520), NumRatings), @DateUpdated 
        FROM Deleted
 
    DECLARE @NewNumPages INT, @OldNumPages INT
    SELECT @NewNumPages=NumPages FROM Inserted
    SELECT @OldNumPages=NumPages FROM Deleted
 
    IF @NewNumPages<>@OldNumPages 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'NumPages', CONVERT(NVARCHAR(520), NumPages), @DateUpdated 
        FROM Deleted
 
    DECLARE @NewAmazonBookLink VARCHAR(250), @OldAmazonBookLink VARCHAR(250)
    SELECT @NewAmazonBookLink=AmazonBookLink FROM Inserted
    SELECT @OldAmazonBookLink=AmazonBookLink FROM Deleted
 
    IF @NewAmazonBookLink<>@OldAmazonBookLink 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'AmazonBookLink', AmazonBookLink, @DateUpdated 
        FROM Deleted
 
    DECLARE @NewAmazonImageLink VARCHAR(250), @OldAmazonImageLink VARCHAR(250)
    SELECT @NewAmazonImageLink=AmazonImageLink FROM Inserted
    SELECT @OldAmazonImageLink=AmazonImageLink FROM Deleted
 
    IF @NewAmazonImageLink<>@OldAmazonImageLink 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'AmazonImageLink', AmazonImageLink, @DateUpdated 
        FROM Deleted
 
    DECLARE @NewIsAsin BIT, @OldIsAsin BIT
    SELECT @NewIsAsin=IsAsin FROM Inserted
    SELECT @OldIsAsin=IsAsin FROM Deleted
 
    IF @NewIsAsin<>@OldIsAsin 
        INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) 
        SELECT BookId, 'IsAsin', IsAsin, @DateUpdated 
        FROM Deleted
 
    SET NOCOUNT OFF
END
GO
 
/*****************************
	Insert book types
******************************/
SET NOCOUNT ON
 
INSERT INTO BookType VALUES (2, 'Foreign')
INSERT INTO BookType VALUES (3, 'Not Available')
INSERT INTO BookType VALUES (4, 'Research Paper / Article')
INSERT INTO BookType VALUES (5, 'Duplicate')
INSERT INTO BookType VALUES (6, 'Out Of Print')
INSERT INTO BookType VALUES (7, 'eBook')
INSERT INTO BookType VALUES (8, 'Box Set')
INSERT INTO BookType VALUES (9, 'Future Category') -- books that can't be classified
INSERT INTO BookType VALUES (10, 'Investigate')
INSERT INTO BookType VALUES (11, 'Lecture Notes / Lab Manual')
 
SET NOCOUNT OFF
 
/*****************************
    Insert book categories and SyncKeywords
******************************/
DECLARE @CatId INT
 
-- XNA
EXEC @CatId = tar_AddCategory 'XNA Studio', 'XNA', 'xna'
INSERT INTO SyncKeyword VALUES (10, 'xna', 'XNA (key)', 0, @CatId)
 
-- DirectX
EXEC @CatId = tar_AddCategory 'DirectX', 'DirectX', 'directx'
INSERT INTO SyncKeyword VALUES (20, 'directx', 'DirectX', 3922, @CatId)
INSERT INTO SyncKeyword VALUES (21, '', 'DirectX (obj)', 3922, @CatId)
INSERT INTO SyncKeyword VALUES (22, 'directx', 'DirectX (key)', 0, @CatId)
 
-- Open GL
EXEC @CatId = tar_AddCategory 'Open GL', 'Open GL', 'open gl opengl'
INSERT INTO SyncKeyword VALUES (30, '', 'Open GL (obj)', 3935, @CatId)
INSERT INTO SyncKeyword VALUES (31, 'opengl', 'Open GL (key)', 0, @CatId)
 
-- C++
EXEC @CatId = tar_AddCategory 'C++', 'C++ programming language', 'c++ programming language'
INSERT INTO SyncKeyword VALUES (400, 'c++ language', 'C++ Language', 3961, @CatId)
INSERT INTO SyncKeyword VALUES (405, 'c++', 'C++ Language (obj)', 3961, @CatId)
INSERT INTO SyncKeyword VALUES (410, 'c++', 'C++ Algorithms (obj)', 3957, @CatId)
INSERT INTO SyncKeyword VALUES (420, 'c++ algorithms', 'C++ Algorithms (key)', 0, @CatId)
INSERT INTO SyncKeyword VALUES (490, 'c++ programming language', 'C++ All', 3956, @CatId) -- all books on C/C++
INSERT INTO SyncKeyword VALUES (492, 'c++', 'C++ All (obj)', 3956, @CatId) -- all books on C/C++
 
-- Game Programming
EXEC @CatId = tar_AddCategory 'Game Programming', 'Game programming', 'game games programming'
INSERT INTO SyncKeyword VALUES (500, 'game programming', 'Game Programming', 15375251, @CatId)
INSERT INTO SyncKeyword VALUES (501, '', 'Game Programming (obj)', 15375251, @CatId)
 
/*****************************
    Insert [Not Specified] records
******************************/
 
-- Insert "dummy" category
SET IDENTITY_INSERT Category ON
INSERT INTO Category(CatId, CatName, CatDesc)
VALUES(0, '[Not Specified]', 'No category specified')
SET IDENTITY_INSERT Category OFF
 
-- Insert "dummy" syncKey
INSERT INTO SyncKeyword VALUES (0, '[Not Specified]', '[Not Specified]', 0, 0)
 
-- Insert "dummy" bookType
INSERT INTO BookType VALUES (0, '[Not Specified]')