CREATE TABLE Logins
(
        Login        VARCHAR(32)    NOT NULL,
        Password     VARCHAR(32)    NOT NULL,
        CreatedAt    DATETIME       NOT NULL,
        LastLoggedAt DATETIME       NULL,
        LostLoggedIP VARCHAR(15)    NULL, 
        AcceptVotes  ENUM('Yes','No') NOT NULL DEFAULT 'Yes',

        PRIMARY KEY (Login),
);

CREATE TABLE Authors
(
        Id        INTEGER      NOT NULL AUTO_INCREMENT,
        Email     VARCHAR(127) NOT NULL,
        Name      VARCHAR(255) NULL,
        Login     VARCHAR(32)  NULL,

        MarksValue      DECIMAL(3,2)    NOT NULL DEFAULT '0.00',
        MarksCount      INTEGER         NOT NULL DEFAULT 0,

        PRIMARY KEY (Id),
        FOREIGN KEY (Login) REFERENCES Logins(Login),
        INDEX  AuthorsLoginIndex       (Login),
        UNIQUE INDEX AuthorsEmailIndex (Email)
);

CREATE TABLE FavoriteAuthors
(
        Id        INTEGER      NOT NULL AUTO_INCREMENT,

        Login     VARCHAR(32)  NOT NULL,
        AuthorId  INTEGER NOT NULL,

        PRIMARY KEY (Id),
        FOREIGN KEY (Login) REFERENCES Logins(Login),
        INDEX  LoginIndex       (Login),
        FOREIGN KEY (AuthorId) REFERENCES Authors(Id),
        INDEX  AuthorIndex      (AuthorId),
        UNIQUE LoginAuthorUniq (Login, AuthorId)
);

CREATE TABLE EmailMessages
(
        Id              INTEGER         NOT NULL AUTO_INCREMENT,
        MsgId           VARCHAR(127)    NOT NULL,
        FTNMsgId        VARCHAR(127)    NULL,
        Header          TEXT            NOT NULL,

        PRIMARY KEY (Id),
        UNIQUE INDEX MsgIdIndex (MsgId),

        INDEX FTNMsgIdIndex(FTNMsgId)
);

CREATE TABLE Threads
(
        Id              INTEGER         NOT NULL AUTO_INCREMENT,
        CreatedAt       DATETIME        NOT NULL,
        Title           VARCHAR(255)    NOT NULL,

        -- To be continued...
        -- is active
        -- access rights 
        -- last modified

        PRIMARY KEY (Id),
);

CREATE TABLE Texts
(
        Id              INTEGER         NOT NULL AUTO_INCREMENT,
        AuthorId        INTEGER         NOT NULL,
        PublishedAt     DATETIME        NOT NULL,

        EmailId         INTEGER         NULL,

        Title           VARCHAR(255)    NULL,
        Bytes           INTEGER         NOT NULL,
        Text            MEDIUMTEXT      NOT NULL,

        Views           INTEGER         NOT NULL,
        MarksValue      DECIMAL(3,2)    NOT NULL DEFAULT '0.00',
        MarksCount      INTEGER         NOT NULL DEFAULT 0,

        CommentThreadId INTEGER         NULL,

        Hidden ENUM('Yes','No') NOT NULL DEFAULT 'No',

        PRIMARY KEY (Id),
        FOREIGN KEY (AuthorId) REFERENCES Authors(Id),
        FOREIGN KEY (EmailId) REFERENCES EmailMessages(Id),
        FOREIGN KEY (CommentThreadId) REFERENCES Threads(Id),

	INDEX   TextAuthorId    (AuthorId,Id),
	INDEX   TextPublishedAt (PublishedAt,Id),
	INDEX   TextEmailId     (EmailId),
	INDEX   TextViews       (Views),
	INDEX   TextCommentThreadId (CommentThreadId)
);

CREATE TABLE Comments
(
        Id              INTEGER         NOT NULL AUTO_INCREMENT,
        AuthorId        INTEGER         NOT NULL,
        PublishedAt     DATETIME        NOT NULL,

        EmailId         INTEGER         NULL,

        Title           VARCHAR(255)    NULL,
        Bytes           INTEGER         NOT NULL,
        Text            MEDIUMTEXT      NOT NULL,

        ThreadId        INTEGER         NOT NULL,

        ThreadPosition  SMALLINT        NOT NULL, 
        ThreadOffset    SMALLINT        NOT NULL, 

        Hidden ENUM('Yes','No') NOT NULL DEFAULT 'No',

        PRIMARY KEY (Id),
        FOREIGN KEY (AuthorId) REFERENCES Authors(Id)       ,
        FOREIGN KEY (EmailId) REFERENCES  EmailMessages(Id) ,
        FOREIGN KEY (ThreadId) REFERENCES Threads(Id)       ,
	INDEX   CommentAuhtorId    (AuthorId),
	INDEX   CommentThreadId    (ThreadId, Id),
	INDEX   CommentEmailId     (EmailId)
);

CREATE TABLE UnsolvedMsgIdReferences
(
        ThreadId        INTEGER         NOT NULL,
        MsgId           VARCHAR(127)    NOT NULL,
        CreatedAt       TIMESTAMP,

        FOREIGN KEY (ThreadId) REFERENCES Threads(Id) ,
        INDEX       UMRefThreadIdIndex   (ThreadId)   ,
        INDEX       UMRefMsgIdIndex      (MsgId),
        INDEX       UMRefCreatedIndex    (CreatedAt)
);

CREATE TABLE UnsolvedFTNMsgIdReferences
(
        ThreadId        INTEGER         NOT NULL,
        FTNMsgId        VARCHAR(127)    NOT NULL,
        CreatedAt       TIMESTAMP,

        FOREIGN KEY (ThreadId) REFERENCES Threads(Id) ,
        INDEX        UFRefThreadIdIndex   (ThreadId)  ,
        INDEX        UFRefMsgIdIndex      (FTNMsgId)  ,
        INDEX        UFRefCreatedIndex    (CreatedAt)
);

CREATE TABLE Categories
(
        Id           INTEGER         NOT NULL AUTO_INCREMENT,
        Name         VARCHAR(127)    NOT NULL,

        PRIMARY KEY (Id),
);

CREATE TABLE Groups
(
        Id           INTEGER         NOT NULL AUTO_INCREMENT,
        CategoryId   INTEGER         NOT NULL,
        Name         VARCHAR(127)    NOT NULL,

        PRIMARY KEY (Id),
        FOREIGN KEY  (CategoryId)  REFERENCES Categories(Id),
        INDEX         GroupCategoryIdIndex (CategoryId)
);

CREATE TABLE TextGroups
(
        TextId        INTEGER         NOT NULL,
        GroupId       INTEGER         NOT NULL,

        PRIMARY KEY (TextId, GroupId),
        FOREIGN KEY (TextId)  REFERENCES Texts(Id) ,
        FOREIGN KEY (GroupId) REFERENCES Groups(Id) ,
        INDEX        TextGroupTextIdIndex    (TextId)  ,
        INDEX        TextGroupGroupIdIndex   (GroupId)  
);

CREATE TABLE PendingEmailConfimations 
(
  Login         VARCHAR(32)     NOT NULL,
  AuthorId      INTEGER         NOT NULL,
  CreatedAt     DATETIME        NOT NULL,
  Password      VARCHAR(32)     NOT NULL,

  PRIMARY KEY   (Login, AuthorId),
  FOREIGN KEY   (Login)         REFERENCES Logins(Login) ,
  FOREIGN KEY   (AuthorId)      REFERENCES Authors(Id),
);

CREATE TABLE Marks
(
  Login         VARCHAR(32)     NOT NULL,
  TextId        INTEGER         NOT NULL,

  Value         INTEGER         NOT NULL DEFAULT 0,
  CreatedAt     DATETIME        NOT NULL,

  PRIMARY KEY   (Login, TextId),
  FOREIGN KEY   (Login)         REFERENCES Logins(Login) ,
  FOREIGN KEY   (TextId)        REFERENCES Texts(Id) ,
);


