-- Data Base Schema
-- アカウント表
CREATE TABLE Account (
    Id INTEGER NOT NULL AUTO_INCREMENT,       -- アカウント ID
    MailAddr CHARACTER VARYING(128) NOT NULL, -- メールアドレス
    Password CHAR(32) NOT NULL,               -- パスワード
    Status ENUM('AVAILABLE','LOCKED','EXPIRED','CREDENTIALS_EXPIRED','DISABLED') NOT NULL, -- ステータス
    PRIMARY KEY (Id),
    UNIQUE (MailAddr)
);

-- 一時的に発行される認証キー
CREATE TABLE TemporaryAuthKey (
    AccountId INTEGER NOT NULL,      -- アカウント ID
    AuthKey CHAR(32) NOT NULL,       -- 一時的に発行した認証キー
    GeneratedDate DATETIME NOT NULL, -- 発行された日時
    PRIMARY KEY (AccountId),
    UNIQUE (AuthKey),
    CONSTRAINT FK_Account_Id FOREIGN KEY (AccountId) REFERENCES Account(Id) ON DELETE CASCADE
);

-- アカウントのロール表
CREATE TABLE AccountRole (
    AccountId INTEGER NOT NULL,                    -- アカウント ID
    Role ENUM('ROLE_ADMIN', 'ROLE_USER') NOT NULL, -- ロール名
    PRIMARY KEY (AccountId, Role),
    CONSTRAINT FK_AccountRole FOREIGN KEY (AccountId) REFERENCES Account(Id) ON DELETE CASCADE
);

-- ユーザ表
CREATE TABLE User (
    Id INTEGER NOT NULL,                 -- ユーザ ID ( アカウント ID と同じ )
    Code CHARACTER VARYING(32) NOT NULL, -- ユーザコード
    Name CHARACTER VARYING(32) NOT NULL, -- ユーザ名
    LastAccessDate DATETIME,             -- 最終アクセス日時
    PRIMARY KEY (Id),
    UNIQUE (Code, Name),
    CONSTRAINT FK_User_Id FOREIGN KEY (Id) REFERENCES Account(Id) ON DELETE CASCADE
);

-- メール
CREATE TABLE Mail (
    Id INTEGER NOT NULL AUTO_INCREMENT,     -- ID
    Code CHAR(32) NOT NULL,                 -- コード
    ToId INTEGER,                           -- 宛先ユーザ ID
    FromId INTEGER,                         -- 差出人ユーザ ID
    SentDate DATETIME NOT NULL,             -- 送信日時
    Subject CHARACTER VARYING(64),          -- 件名
    Body TEXT,                              -- 本文
    IsRead BOOLEAN NOT NULL DEFAULT FALSE,  -- 宛先ユーザが閲覧済みなら 1 、そうでなければ 0
    NextId INTEGER,                         -- このメールに返信したメール ID
    PRIMARY KEY (Id),
    UNIQUE (Code),
    CONSTRAINT FK_Mail_ToId FOREIGN KEY (ToId) REFERENCES User(Id) ON DELETE SET NULL,
    CONSTRAINT FK_Mail_FromId FOREIGN KEY (FromId) REFERENCES User(Id) ON DELETE SET NULL,
    CONSTRAINT FK_Mail_NextId FOREIGN KEY (NextId) REFERENCES Mail(Id) ON DELETE SET NULL
);

-- eメールへのメール転送設定
CREATE TABLE EmailForwarding (
    UserId INTEGER NOT NULL,                    -- ユーザ ID
    Mail BOOLEAN NOT NULL DEFAULT TRUE,         -- メールを転送するなら true 、転送しないなら false
    BlogComment BOOLEAN NOT NULL DEFAULT FALSE, -- ブログコメントを転送するなら true 、転送しないなら false
    PRIMARY KEY(UserId),
    CONSTRAINT FK_Mail_UserId FOREIGN KEY (UserId) REFERENCES User(Id) ON DELETE CASCADE
);

-- ウィジェット（サイドバーに配置される）
CREATE TABLE Widget (
    UserId INTEGER NOT NULL,                   -- ユーザ ID
    Sequence INTEGER NOT NULL AUTO_INCREMENT,  -- 表示順
    Widget ENUM('GOOGLE_SEARCH','ACTIVE_USER_COUNT','PUBLIC_PROFILE','PUBLIC_BLOGS','PUBLIC_COMMUNITIES','FRIEND','FOOTMARK','MAIL','MAIL_ALERT') NOT NULL, -- ウィジェット名
    PRIMARY KEY (Sequence, UserId),
    CONSTRAINT FK_Widget_UserId FOREIGN KEY (UserId) REFERENCES User(Id) ON DELETE CASCADE
);

-- アクセス制御表
CREATE TABLE AccessControl (
    Id INTEGER NOT NULL AUTO_INCREMENT,     -- アクセス制御 ID
    Class CHARACTER VARYING(128) NOT NULL,  -- アクセス制御タイプ名
    PRIMARY KEY (Id)
);

-- メッセージ表
CREATE TABLE Message (
    Id INTEGER NOT NULL AUTO_INCREMENT,    -- メッセージ ID
    Code CHARACTER VARYING(32) NOT NULL,   -- メッセージコード
    AuthorId INTEGER,                      -- 著者のユーザ ID
    OwnerId INTEGER,                       -- 所有者のユーザ ID
    ReadAccessControlId INTEGER NOT NULL,  -- 参照権限 ID
    WriteAccessControlId INTEGER NOT NULL, -- 編集権限 ID
    RegistrationDate DATETIME NOT NULL,    -- 登録日時
    LastUpdateDate DATETIME NOT NULL,      -- 最終更新日時
    PRIMARY KEY (Id),
    UNIQUE (Code),
    CONSTRAINT FK_Message_AuthorId FOREIGN KEY (AuthorId) REFERENCES User(Id) ON DELETE SET NULL,
    CONSTRAINT FK_Message_OwnerId FOREIGN KEY (OwnerId) REFERENCES User(Id) ON DELETE SET NULL,
    CONSTRAINT FK_Message_ReadAccessControlId FOREIGN KEY (ReadAccessControlId) REFERENCES AccessControl(Id),
    CONSTRAINT FK_Message_WriteAccessControlId FOREIGN KEY (ReadAccessControlId) REFERENCES AccessControl(Id)
);

-- メッセージエントリ表
CREATE TABLE MessageEntry (
    MessageId INTEGER NOT NULL,    -- メッセージ ID
    Locale CHAR(2) NOT NULL,       -- 言語
    Subject CHARACTER VARYING(64), -- 件名
    Body TEXT,                     -- 本文
    PRIMARY KEY (MessageId, Locale),
    CONSTRAINT FK_MessageEntry_MessageId FOREIGN KEY (MessageId) REFERENCES Message(Id) ON DELETE CASCADE
);

-- コメント表
CREATE TABLE Comment (
    Id INTEGER NOT NULL AUTO_INCREMENT, -- ID
    CommenterId INTEGER,                -- コメンターのユーザ ID
    Text TEXT NOT NULL,                 -- テキスト
    LastUpdateDate DATETIME NOT NULL,   -- 最終更新日時
    PRIMARY KEY (Id),
    CONSTRAINT FK_Comment_CommenterId FOREIGN KEY (CommenterId) REFERENCES User(Id) ON DELETE SET NULL
);

-- ブログ表
CREATE TABLE Blog (
    Id INTEGER NOT NULL AUTO_INCREMENT,      -- ID
    Code CHARACTER VARYING(32) NOT NULL,     -- コード
    OwnerId INTEGER,                         -- 所有者 ID
    OverviewId INTEGER NOT NULL,             -- 概要（メッセージ ID）
    ReadAccessControlId INTEGER NOT NULL,    -- 参照権限 ID
    WriteAccessControlId INTEGER NOT NULL,   -- 編集権限 ID
    CommentAccessControlId INTEGER NOT NULL, -- コメント投稿権限 ID
    PRIMARY KEY (Id),
    UNIQUE (Code),
    CONSTRAINT FK_Blog_OwnerId FOREIGN KEY (OwnerId) REFERENCES User(Id) ON DELETE SET NULL,
    CONSTRAINT FK_Blog_OverviewId FOREIGN KEY (OverviewId) REFERENCES Message(Id),
    CONSTRAINT FK_Blog_ReadAccessControlId FOREIGN KEY (ReadAccessControlId) REFERENCES AccessControl(Id),
    CONSTRAINT FK_Blog_WriteAccessControlId FOREIGN KEY (WriteAccessControlId) REFERENCES AccessControl(Id),
    CONSTRAINT FK_Blog_CommentAccessControlId FOREIGN KEY (CommentAccessControlId) REFERENCES AccessControl(Id)
);

-- ブログ記事表
CREATE TABLE BlogEntry (
    EntryId INTEGER NOT NULL,  -- 記事 ID
    BlogId INTEGER NOT NULL,   -- ブログ ID
    PRIMARY KEY (EntryId),
    INDEX(BlogId),
    CONSTRAINT FK_BlogEntry_EntryId FOREIGN KEY (EntryId) REFERENCES Message(Id) ON DELETE CASCADE,
    CONSTRAINT FK_BlogEntry_BlogId FOREIGN KEY (BlogId) REFERENCES Blog(Id)
);

-- ブログコメント表
CREATE TABLE BlogComment (
    CommentId INTEGER NOT NULL,  -- コメント ID
    EntryId INTEGER NOT NULL,    -- ブログ ID
    PRIMARY KEY (CommentId),
    INDEX(EntryId),
    CONSTRAINT FK_BlogComment_CommentId FOREIGN KEY (CommentId) REFERENCES Comment(Id) ON DELETE CASCADE,
    CONSTRAINT FK_BlogComment_EntryId FOREIGN KEY (EntryId) REFERENCES BlogEntry(EntryId)
);

-- スレッド
CREATE TABLE Thread (
    Id INTEGER NOT NULL AUTO_INCREMENT,          -- ID
    Status ENUM('AVAILABLE','LOCKED') NOT NULL,  -- ステータス
    OwnerId INTEGER NOT NULL,                    -- 所有者 ID
    OverviewId INTEGER NOT NULL,                 -- 概要（メッセージ ID）
    ReadAccessControlId INTEGER NOT NULL,        -- 参照権限 ID
    WriteAccessControlId INTEGER NOT NULL,       -- 編集権限 ID
    PostAccessControlId INTEGER NOT NULL,        -- 投稿権限 ID
    PRIMARY KEY (Id),
    CONSTRAINT FK_Thread_OverviewId FOREIGN KEY (OverviewId) REFERENCES Message(Id),
    CONSTRAINT FK_Thread_ReadAccessControlId FOREIGN KEY (ReadAccessControlId) REFERENCES AccessControl(Id),
    CONSTRAINT FK_Thread_WriteAccessControlId FOREIGN KEY (WriteAccessControlId) REFERENCES AccessControl(Id),
    CONSTRAINT FK_Thread_PostAccessControlId FOREIGN KEY (PostAccessControlId) REFERENCES AccessControl(Id)
);

-- 記事
CREATE TABLE Entry (
    Id INTEGER NOT NULL,        -- 記事 ID
    ThreadId INTEGER NOT NULL,  -- スレッド ID
    PRIMARY KEY (Id),
    INDEX(ThreadId),
    CONSTRAINT FK_Entry_Id FOREIGN KEY (Id) REFERENCES Message(Id) ON DELETE CASCADE,
    CONSTRAINT FK_Entry_ThreadId FOREIGN KEY (ThreadId) REFERENCES Thread(Id)
);

-- コミュニティ表
CREATE TABLE Community (
    Id INTEGER NOT NULL AUTO_INCREMENT,     -- ID
    Class CHARACTER VARYING(128) NOT NULL,  -- タイプ
    Code CHARACTER VARYING(32) NOT NULL,    -- コード
    OwnerId INTEGER,                        -- 所有者 ID
    OverviewId INTEGER NOT NULL,            -- 概要（メッセージ ID）
    ReadAccessControlId INTEGER NOT NULL,   -- 参照権限 ID
    WriteAccessControlId INTEGER NOT NULL,  -- 編集権限 ID
    PostAccessControlId INTEGER NOT NULL,   -- 投稿権限 ID
    PRIMARY KEY (Id),
    UNIQUE (Code),
    CONSTRAINT FK_Community_OwnerId FOREIGN KEY (OwnerId) REFERENCES User(Id) ON DELETE SET NULL,
    CONSTRAINT FK_Community_OverviewId FOREIGN KEY (OverviewId) REFERENCES Message(Id),
    CONSTRAINT FK_Community_ReadAccessControlId FOREIGN KEY (ReadAccessControlId) REFERENCES AccessControl(Id),
    CONSTRAINT FK_Community_WriteAccessControlId FOREIGN KEY (WriteAccessControlId) REFERENCES AccessControl(Id),
    CONSTRAINT FK_Community_PostAccessControlId FOREIGN KEY (PostAccessControlId) REFERENCES AccessControl(Id)
);

-- コミュニティスレッド
CREATE TABLE CommunityThread (
    CommunityId INTEGER NOT NULL, -- コミュニティ ID
    ThreadId INTEGER NOT NULL,    -- スレッド ID
    PRIMARY KEY (CommunityId, ThreadId),
    UNIQUE (ThreadId),
    CONSTRAINT FK_CommunityThread_CommunityId FOREIGN KEY (CommunityId) REFERENCES Community(Id) ON DELETE CASCADE,
    CONSTRAINT FK_CommunityThread_ThreadId FOREIGN KEY (ThreadId) REFERENCES Thread(Id) ON DELETE CASCADE
);

-- 参加中のコミュニティ
CREATE TABLE MyCommunity (
    UserId INTEGER NOT NULL,
    CommunityId INTEGER NOT NULL,
    PRIMARY KEY (UserId, CommunityId),
    CONSTRAINT FK_MyCommunity_UserId FOREIGN KEY (UserId) REFERENCES User(Id) ON DELETE CASCADE,
    CONSTRAINT FK_MyCommunity_CommunityId FOREIGN KEY (CommunityId) REFERENCES Community(Id) ON DELETE CASCADE
);

-- プロフィール表
CREATE TABLE Profile (
    OwnerId INTEGER NOT NULL,               -- 所有者のユーザ ID
    ReadAccessControlId INTEGER NOT NULL,   -- 参照権限 ID
    WriteAccessControlId INTEGER NOT NULL,  -- 編集権限 ID
    PRIMARY KEY (OwnerId),
    CONSTRAINT FK_Profile_OwnerId FOREIGN KEY (OwnerId) REFERENCES User(Id) ON DELETE CASCADE,
    CONSTRAINT FK_Profile_ReadAccessControlId FOREIGN KEY (ReadAccessControlId) REFERENCES AccessControl(Id),
    CONSTRAINT FK_Profile_WriteAccessControlId FOREIGN KEY (WriteAccessControlId) REFERENCES AccessControl(Id)
);

-- 自己紹介表
CREATE TABLE SelfIntroduction (
    OwnerId INTEGER NOT NULL,    -- 所有者のユーザ ID
    MessageId INTEGER NOT NULL,  -- 自己紹介のメッセージ ID
    PRIMARY KEY (OwnerId, MessageId),
    CONSTRAINT FK_SelfIntroduction_OwnerId FOREIGN KEY (OwnerId) REFERENCES User(Id) ON DELETE CASCADE,
    CONSTRAINT FK_SelfIntroduction_MessageId FOREIGN KEY (MessageId) REFERENCES Message(Id)
);

-- 友達申し込み表
CREATE TABLE FriendSubscription (
    FromId INTEGER NOT NULL,  -- 申し込み元ユーザ ID
    ToId INTEGER NOT NULL,    -- 申し込み先ユーザ ID
    PRIMARY KEY (FromId, ToId),
    CONSTRAINT FK_FriendSubscription_FromId FOREIGN KEY (FromId) REFERENCES User(Id) ON DELETE CASCADE,
    CONSTRAINT FK_FriendSubscription_ToId FOREIGN KEY (ToId) REFERENCES User(Id) ON DELETE CASCADE
);

-- 友達表
CREATE TABLE Friend (
    UserId INTEGER NOT NULL,   -- ユーザ ID
    FriendId INTEGER NOT NULL, -- 友達 ID
    PRIMARY KEY (UserId, FriendId),
    CONSTRAINT FK_Friend_UserId FOREIGN KEY (UserId) REFERENCES User(Id) ON DELETE CASCADE,
    CONSTRAINT FK_Friend_FriendId FOREIGN KEY (FriendId) REFERENCES User(Id) ON DELETE CASCADE
);

-- 足跡表
CREATE TABLE Footmark (
    Id INTEGER NOT NULL AUTO_INCREMENT,  -- ID
    ProfileOwnerId INTEGER NOT NULL,     -- プロフィールの所有者 ID
    UserId INTEGER NOT NULL,             -- 足跡をつけたユーザ ID
    Date DATETIME NOT NULL,              -- 日時
    PRIMARY KEY (Id),
    CONSTRAINT FK_Footmark_ProfileOwnerId FOREIGN KEY (ProfileOwnerId) REFERENCES Profile(OwnerId) ON DELETE CASCADE,
    CONSTRAINT FK_Footmark_UserId FOREIGN KEY (UserId) REFERENCES User(Id) ON DELETE CASCADE
);
