DROP TABLE UserChannel;

DROP TABLE Read;

DROP TABLE User
;

DROP TABLE Channel
;

DROP TABLE Item
;




CREATE TABLE Channel (
FeedURL VARCHAR(1024) NOT NULL PRIMARY KEY,
title VARCHAR(1024) NOT NULL,
link VARCHAR(1024) NOT NULL,
description VARCHAR(16384) NOT NULL,
ttl INTEGER DEFAULT '5',
lastBuildDate INTEGER
)
;

CREATE TABLE Item (
link VARCHAR(1024) NOT NULL PRIMARY KEY,
guid VARCHAR(1024) UNIQUE,
pubDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
source VARCHAR(1024),
CONSTRAINT Item_FK FOREIGN KEY (source) 
REFERENCES Channel (FeedURL)
);

CREATE TABLE User (
UserID VARCHAR(16) NOT NULL PRIMARY KEY,
CurrentChannel VARCHAR(1024),
CONSTRAINT User_FK FOREIGN KEY (CurrentChannel) 
REFERENCES Channel (FeedURL)
);

CREATE TABLE Read (
UserID VARCHAR(16) NOT NULL,
link VARCHAR(1024) NOT NULL,
channel VARCHAR(1024) NOT NULL,
CONSTRAINT Read_PK PRIMARY KEY (UserId, link),
CONSTRAINT Read_FK FOREIGN KEY (channel) 
REFERENCES Channel (FeedURL)
);

CREATE TABLE UserChannel (
UserID VARCHAR(16) NOT NULL,
Channel VARCHAR(1024) NOT NULL,
CONSTRAINT UserChannel_PK PRIMARY KEY (UserId, Channel),
CONSTRAINT UserChannel_FK FOREIGN KEY (Channel) 
REFERENCES Channel (FeedURL)
);