-- SQLアンチパターン 6章 ポリモーフィック関連

-- ■前準備
CREATE TABLE Accounts (
  account_id        SERIAL PRIMARY KEY,
  account_name      VARCHAR(20)
);

CREATE TABLE Bugs (
  bug_id SERIAL     PRIMARY KEY,
  summary           VARCHAR(80),
  reported_by       BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
);

CREATE TABLE Comments (
  comment_id        SERIAL PRIMARY KEY,
  bug_id            BIGINT UNSIGNED NOT NULL,
  author_id         BIGINT UNSIGNED NOT NULL,
  comment_date      DATETIME NOT NULL,
  comment           TEXT NOT NULL,
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (author_id) REFERENCES Accounts(account_id)
);


INSERT INTO Accounts (account_name) VALUES ('Fran'), ('Ollie'), ('Kukla');

INSERT INTO Bugs (summary, reported_by) VALUES 
  ('crash when I save', 1),
  ('increase performance', 2),
  ('screen goes blank', 3),
  ('unknown conflict between products', 1)  ;
  
INSERT INTO Comments (bug_id, author_id, comment_date, comment) values
  (1, 2, CURRENT_DATE, 'ヌルポインターのせいじゃないかな？'               ),
  (1, 1, CURRENT_DATE, 'そうじゃないよ。それは確認済みだ。'               ),
  (2, 3, CURRENT_DATE, '無効な入力を調べてみたら？'                       ),
  (2, 2, CURRENT_DATE, 'そうか、バグの原因はそれだな'                     ),
  (3, 1, CURRENT_DATE, 'よし、じゃあチェック機能を追加してもらえるかな？' ),
  (3, 3, CURRENT_DATE, '了解。修正したよ'                                 );

-- ■6.2.1
CREATE TABLE Comments2 (
  comment_id   SERIAL PRIMARY KEY,
  issue_type   VARCHAR(20), -- 'Bugs' または 'FeatureRequests' が格納される
  issue_id     BIGINT UNSIGNED NOT NULL,
  author_id    BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME,
  comment      TEXT,
  FOREIGN KEY (author_id) REFERENCES Accounts(account_id)
);

INSERT INTO Comments2 (comment_id, issue_type, issue_id, author_id, comment_date, comment) values 
  (6789, 'Bugs'           , 1234, 2, CURRENT_DATE, 'クラッシュします'),
  (9876, 'FeatureRequests', 2345, 1, CURRENT_DATE, 'いいアイデア！');

CREATE TABLE Bugs2 (
  issue_id SERIAL     PRIMARY KEY,
  summary           VARCHAR(80),
  reported_by       BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
);

INSERT INTO Bugs2 (summary, reported_by) VALUES 
  ('crash when I save', 1),
  ('increase performance', 2),
  ('screen goes blank', 3),
  ('unknown conflict between products', 1)  ;
  
INSERT INTO Bugs2 (issue_id, summary, reported_by) VALUES 
  (1234, 'crash', 1)  ;

CREATE TABLE FeatureRequests2 (
  issue_id SERIAL     PRIMARY KEY,
  summary           VARCHAR(80),
  reported_by       BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
);

INSERT INTO FeatureRequests2 (summary, reported_by) VALUES 
  ('feature1', 1),
  ('feature2', 2),
  ('feature3', 3),
  ('feature4', 1)  ;
  
INSERT INTO FeatureRequests2 (issue_id, summary, reported_by) VALUES 
  (2345, 'feature5', 1)  ;
  
-- ■6.2.2
SELECT *
FROM Bugs2 AS b INNER JOIN Comments2 AS c
  ON b.issue_id = c.issue_id AND c.issue_type = 'Bugs'
WHERE b.issue_id = 1234;


SELECT *
FROM Comments2 AS c
  LEFT OUTER JOIN Bugs2 AS b
    ON b.issue_id = c.issue_id AND c.issue_type = 'Bugs'
  LEFT OUTER JOIN FeatureRequests2 AS f
    ON f.issue_id = c.issue_id AND c.issue_type = 'FeatureRequests';

-- ■6.5.2
CREATE TABLE Comments3 (
  comment_id   SERIAL PRIMARY KEY,
  author_id    BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME,
  comment      TEXT,
  FOREIGN KEY (author_id) REFERENCES Accounts(account_id)
);

INSERT INTO Comments3 (comment_id, author_id, comment_date, comment) values 
  (6789, 2, CURRENT_DATE, 'クラッシュします'),
  (9876, 1, CURRENT_DATE, 'いいアイデア！');
  
CREATE TABLE BugsComments (
  issue_id    BIGINT UNSIGNED NOT NULL,
  comment_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES Bugs2(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments3(comment_id)
);

INSERT INTO BugsComments (issue_id, comment_id) VALUES
  (1234, 6789);

CREATE TABLE FeaturesComments (
  issue_id    BIGINT UNSIGNED NOT NULL,
  comment_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES FeatureRequests2(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments3(comment_id)
);

INSERT INTO FeaturesComments (issue_id, comment_id) VALUES
  (2345, 9876);
  
SELECT * FROM BugsComments AS bc
  INNER JOIN Bugs2 AS b
    ON bc.issue_id = b.issue_id
  INNER JOIN Comments3 AS c
    ON bc.comment_id = c.comment_id
WHERE bc.comment_id = 6789;
  

SELECT * FROM FeaturesComments AS fc
  INNER JOIN FeatureRequests2 AS f
    ON fc.issue_id = f.issue_id
  INNER JOIN Comments3 AS c
    ON fc.comment_id = c.comment_id
WHERE fc.comment_id = 9876;

-- ■6.5.4
SELECT *
FROM BugsComments AS b
  INNER JOIN Comments3 AS c USING (comment_id)
WHERE b.issue_id = 1234;


SELECT *
FROM Comments3 AS c
  LEFT OUTER JOIN (
      BugsComments INNER JOIN Bugs2 AS b
        USING (issue_id)
    ) USING (comment_id)
  LEFT OUTER JOIN (
      FeaturesComments INNER JOIN FeatureRequests2 AS f
        USING (issue_id)
    ) USING (comment_id)
WHERE c.comment_id = 9876;

-- ■6.5.5
SELECT b.issue_id, b.summary, b.reported_by
  FROM Comments3 AS c
    INNER JOIN (
      BugsComments INNER JOIN Bugs2 AS b
        USING (issue_id)
    ) USING (comment_id)
  -- WHERE c.comment_id = 9876
  WHERE c.comment_id = 6789
UNION
  SELECT f.issue_id, f.summary, f.reported_by
  FROM Comments3 AS c
    INNER JOIN (
      FeaturesComments INNER JOIN FeatureRequests2 AS f
        USING (issue_id)
    ) USING (comment_id)
  -- WHERE c.comment_id = 9876;
  WHERE c.comment_id = 6789;
  


SELECT c.*,
  COALESCE(b.issue_id,    f.issue_id      ) AS issue_id,
  COALESCE(b.summary,     f.summary       ) AS summary,
  COALESCE(b.reported_by, f.reported_by   ) AS reported_by
FROM Comments3 AS c
  LEFT OUTER JOIN (
      BugsComments INNER JOIN Bugs2 AS b
        USING (issue_id)
    ) USING (comment_id)
  LEFT OUTER JOIN (
      FeaturesComments INNER JOIN FeatureRequests2 AS f
        USING (issue_id)
    ) USING (comment_id)
WHERE c.comment_id = 9876;


-- ■6.5.6
CREATE TABLE Issues (
  issue_id     SERIAL PRIMARY KEY
);

INSERT INTO Issues (issue_id) VALUES 
  (1234), (2345)  ;

CREATE TABLE Bugs3 (
  issue_id     BIGINT UNSIGNED PRIMARY KEY,
  summary           VARCHAR(80),
  reported_by       BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

INSERT INTO Bugs3 (issue_id, summary, reported_by) VALUES 
  (1234, 'crash', 1)  ;

CREATE TABLE FeatureRequests3 (
  issue_id     BIGINT UNSIGNED PRIMARY KEY,
  summary           VARCHAR(80),
  reported_by       BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

INSERT INTO FeatureRequests3 (issue_id, summary, reported_by) VALUES 
  (2345, 'feature5', 1)  ;

CREATE TABLE Comments4 (
  comment_id   SERIAL PRIMARY KEY,
  issue_id     BIGINT UNSIGNED NOT NULL,
  author_id    BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME,
  comment      TEXT,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
  FOREIGN KEY (author_id) REFERENCES Accounts(account_id)
);

INSERT INTO Comments4 (comment_id, issue_id, author_id, comment_date, comment) values 
  (6789, 1234, 2, CURRENT_DATE, 'クラッシュします'),
  (9876, 2345, 1, CURRENT_DATE, 'いいアイデア！');


SELECT *
FROM Comments4 AS c
  LEFT OUTER JOIN Bugs3 AS b USING (issue_id)
  LEFT OUTER JOIN FeatureRequests3 AS f USING (issue_id)
WHERE c.comment_id = 9876;


SELECT *
FROM Bugs3 AS b
  JOIN Comments4 AS c USING (issue_id)
WHERE b.issue_id = 1234;