/*******************************************************************************
  MSSQL 학습 트랙 — 실습 데이터베이스

  이 스크립트 하나가 실습에 쓸 데이터베이스를 통째로 만듭니다.
  SSMS 나 Azure Data Studio 에서 열어 그대로 실행하십시오.

  * SQL Server 2019 이상에서 확인했습니다.
  * MssqlLab 데이터베이스가 이미 있으면 <b>지우고 다시 만듭니다.</b> 실습용이므로
    담아 둔 것이 사라져도 되는 이름을 골랐습니다. 예제를 돌리다 자료가 헝클어지면
    언제든 이 스크립트를 다시 실행해 처음 상태로 되돌리십시오.
  * 난수(RAND · NEWID)와 현재 시각(GETDATE)을 쓰지 않습니다. 누가 언제 돌려도
    같은 자료가 만들어져야 단원에 적어 둔 실행 결과와 화면이 맞아떨어집니다.

  만들어지는 것
    스키마 2개   Member · Board
    테이블 6개   USERS · BOARDS · CATEGORIES · POSTS · COMMENTS · FILES
    자료         회원 20 · 게시판 3 · 말머리 10 · 글 507 · 댓글 1,013 · 첨부 100
********************************************************************************/
SET NOCOUNT ON;
GO

/*------------------------------------------------------------------------------
  1. 데이터베이스
------------------------------------------------------------------------------*/
USE master;
GO

IF DB_ID('MssqlLab') IS NOT NULL
BEGIN
	-- 다른 연결이 붙어 있으면 지울 수 없습니다. 잠깐 단독 사용으로 돌립니다.
	ALTER DATABASE MssqlLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE MssqlLab;
END
GO

CREATE DATABASE MssqlLab;
GO

USE MssqlLab;
GO

/*------------------------------------------------------------------------------
  2. 스키마

  테이블을 Board 와 Member 로 나눕니다. 하나에 몰아넣어도 돌아가지만, 나눠 두면
  이름이 짧아지고(POSTS 가 어느 영역의 것인지 이름에 적지 않아도 됩니다)
  권한을 영역째로 줄 수 있습니다. 3.8 에서 다시 다룹니다.
------------------------------------------------------------------------------*/
CREATE SCHEMA Member;
GO
CREATE SCHEMA Board;
GO

/*------------------------------------------------------------------------------
  3. 회원
------------------------------------------------------------------------------*/
CREATE TABLE Member.USERS (
	num			INT				IDENTITY(1, 1)	NOT NULL,
	user_id		VARCHAR(50)						NOT NULL,	-- 로그인 아이디
	nickname	NVARCHAR(50)					NOT NULL,	-- 화면에 보이는 이름
	email		VARCHAR(200)					NULL,		-- 받지 않은 사람이 있어 NULL 을 허용합니다
	reg_date	DATETIME2(0)					NOT NULL,

	CONSTRAINT PK_USERS PRIMARY KEY CLUSTERED (num),
	CONSTRAINT UX_USERS_user_id UNIQUE (user_id)
);
GO

/*------------------------------------------------------------------------------
  4. 게시판
------------------------------------------------------------------------------*/
CREATE TABLE Board.BOARDS (
	num				INT				IDENTITY(1, 1)	NOT NULL,
	code			VARCHAR(20)						NOT NULL,	-- 주소에 쓰는 이름(notice·free·qna)
	name			NVARCHAR(50)					NOT NULL,
	use_category	BIT								NOT NULL
		CONSTRAINT DF_BOARDS_use_category DEFAULT 0,		-- 말머리를 쓰는 게시판인지
	use_reply		BIT								NOT NULL
		CONSTRAINT DF_BOARDS_use_reply DEFAULT 1,			-- 답글을 받는 게시판인지
	reg_date		DATETIME2(0)					NOT NULL,

	CONSTRAINT PK_BOARDS PRIMARY KEY CLUSTERED (num),
	CONSTRAINT UX_BOARDS_code UNIQUE (code)
);
GO

/*------------------------------------------------------------------------------
  5. 말머리

  게시판마다 다릅니다. 이름은 게시판 안에서만 겹치지 않으면 되므로
  고유 조건을 (board_num, name) 두 열에 겁니다.
------------------------------------------------------------------------------*/
CREATE TABLE Board.CATEGORIES (
	num			INT				IDENTITY(1, 1)	NOT NULL,
	board_num	INT								NOT NULL,
	name		NVARCHAR(30)					NOT NULL,
	sort_no		INT								NOT NULL,	-- 화면에 늘어놓는 차례

	CONSTRAINT PK_CATEGORIES PRIMARY KEY CLUSTERED (num),
	CONSTRAINT UX_CATEGORIES_name UNIQUE (board_num, name),
	CONSTRAINT FK_CATEGORIES_BOARDS FOREIGN KEY (board_num)
		REFERENCES Board.BOARDS (num) ON DELETE CASCADE
);
GO

/*------------------------------------------------------------------------------
  6. 글

  계층을 담는 열이 넷입니다.
    parent_num	부모 글. 원글이면 NULL
    group_num	원글의 번호. 한 덩어리를 묶습니다
    depth		답글 깊이(원글 0)
    sort_no		덩어리 안에서의 차례

  parent_num 하나만 있어도 계층은 표현됩니다(2.5 재귀 CTE). 그런데 목록 화면은
  글 수천 건을 <b>정렬해서 페이지로 끊어</b> 내야 하고, 그때마다 재귀로 파고들면
  느립니다. 그래서 덩어리(group_num)와 그 안의 차례(sort_no)를 열로 들고 있다가
  인덱스 하나로 정렬합니다. 무엇을 얻고 무엇을 포기했는지는 3.9 에서 견줍니다.

  parent_num 은 자기 테이블을 가리키므로 ON DELETE CASCADE 를 걸 수 없습니다.
  SQL Server 가 순환을 만드는 연쇄를 막습니다(3.3).
------------------------------------------------------------------------------*/
CREATE TABLE Board.POSTS (
	num				INT				IDENTITY(1, 1)	NOT NULL,
	board_num		INT								NOT NULL,
	category_num	INT								NULL,		-- 말머리를 안 쓰는 게시판은 NULL
	user_num		INT								NOT NULL,
	parent_num		INT								NULL,
	group_num		INT								NOT NULL,
	depth			SMALLINT						NOT NULL
		CONSTRAINT DF_POSTS_depth DEFAULT 0,
	sort_no			INT								NOT NULL
		CONSTRAINT DF_POSTS_sort_no DEFAULT 0,
	title			NVARCHAR(200)					NOT NULL,
	content			NVARCHAR(MAX)					NULL,
	hit_count		INT								NOT NULL
		CONSTRAINT DF_POSTS_hit_count DEFAULT 0,
	reg_date		DATETIME2(0)					NOT NULL,
	mod_date		DATETIME2(0)					NULL,		-- 고친 적이 없으면 NULL

	CONSTRAINT PK_POSTS PRIMARY KEY CLUSTERED (num),
	CONSTRAINT CK_POSTS_depth CHECK (depth >= 0),
	CONSTRAINT FK_POSTS_BOARDS FOREIGN KEY (board_num)
		REFERENCES Board.BOARDS (num),
	CONSTRAINT FK_POSTS_CATEGORIES FOREIGN KEY (category_num)
		REFERENCES Board.CATEGORIES (num),
	CONSTRAINT FK_POSTS_USERS FOREIGN KEY (user_num)
		REFERENCES Member.USERS (num),
	CONSTRAINT FK_POSTS_PARENT FOREIGN KEY (parent_num)
		REFERENCES Board.POSTS (num)
);
GO

-- 목록 화면이 사용하는 인덱스입니다. 게시판 하나를 선택해 최신 덩어리부터,
-- 덩어리 안에서는 차례대로 냅니다.
CREATE NONCLUSTERED INDEX IX_POSTS_list
	ON Board.POSTS (board_num, group_num DESC, sort_no)
	INCLUDE (title, user_num, reg_date, hit_count, depth);
GO

-- 재귀 CTE 가 부모를 따라 내려갈 때 씁니다.
CREATE NONCLUSTERED INDEX IX_POSTS_parent
	ON Board.POSTS (parent_num);
GO

/*------------------------------------------------------------------------------
  7. 댓글
------------------------------------------------------------------------------*/
CREATE TABLE Board.COMMENTS (
	num			INT				IDENTITY(1, 1)	NOT NULL,
	post_num	INT								NOT NULL,
	user_num	INT								NOT NULL,
	content		NVARCHAR(1000)					NOT NULL,
	reg_date	DATETIME2(0)					NOT NULL,

	CONSTRAINT PK_COMMENTS PRIMARY KEY CLUSTERED (num),
	CONSTRAINT FK_COMMENTS_POSTS FOREIGN KEY (post_num)
		REFERENCES Board.POSTS (num) ON DELETE CASCADE,
	CONSTRAINT FK_COMMENTS_USERS FOREIGN KEY (user_num)
		REFERENCES Member.USERS (num)
);
GO

CREATE NONCLUSTERED INDEX IX_COMMENTS_post
	ON Board.COMMENTS (post_num, reg_date);
GO

/*------------------------------------------------------------------------------
  8. 첨부

  첨부가 있는 글이 드뭅니다. 그래서 LEFT JOIN 과 NULL 을 다루는 예제(1.9)에
  쓸 자리가 됩니다 — 안쪽 조인으로 바꾸면 첨부 없는 글이 통째로 사라집니다.
------------------------------------------------------------------------------*/
CREATE TABLE Board.FILES (
	num			INT				IDENTITY(1, 1)	NOT NULL,
	post_num	INT								NOT NULL,
	origin_name	NVARCHAR(260)					NOT NULL,
	size_bytes	BIGINT							NOT NULL,
	reg_date	DATETIME2(0)					NOT NULL,

	CONSTRAINT PK_FILES PRIMARY KEY CLUSTERED (num),
	CONSTRAINT CK_FILES_size CHECK (size_bytes > 0),
	CONSTRAINT FK_FILES_POSTS FOREIGN KEY (post_num)
		REFERENCES Board.POSTS (num) ON DELETE CASCADE
);
GO

CREATE NONCLUSTERED INDEX IX_FILES_post
	ON Board.FILES (post_num);

/*==============================================================================
  여기까지가 테이블 만들기입니다. 아래부터 자료를 채웁니다.
==============================================================================*/

USE MssqlLab;
GO

DECLARE @base DATETIME2(0) = '2026-01-01 09:00:00';

/*------------------------------------------------------------------------------
  0. 번호 상자

  1 부터 1000 까지의 수를 담아 둡니다. 자료를 만들 때 "몇 번째" 를 셈하는 데
  씁니다. 다 쓰고 나면 지웁니다.
------------------------------------------------------------------------------*/
IF OBJECT_ID('tempdb..#Nums') IS NOT NULL DROP TABLE #Nums;

CREATE TABLE #Nums (n INT PRIMARY KEY);

WITH N AS (
	SELECT 1 AS n
	UNION ALL
	SELECT n + 1 FROM N WHERE n < 1000
)
INSERT INTO #Nums (n) SELECT n FROM N OPTION (MAXRECURSION 0);

/*------------------------------------------------------------------------------
  1. 회원 20명
------------------------------------------------------------------------------*/
INSERT INTO Member.USERS (user_id, nickname, email, reg_date) VALUES
	('hong',	N'홍길동',	'hong@example.com',	DATEADD(DAY, -300, @base)),
	('kim',		N'김철수',	'kim@example.com',	DATEADD(DAY, -295, @base)),
	('lee',		N'이영희',	'lee@example.com',	DATEADD(DAY, -290, @base)),
	('park',	N'박민수',	'park@example.com',	DATEADD(DAY, -285, @base)),
	('choi',	N'최지우',	NULL,				DATEADD(DAY, -280, @base)),
	('jung',	N'정하늘',	'jung@example.com',	DATEADD(DAY, -275, @base)),
	('kang',	N'강바다',	'kang@example.com',	DATEADD(DAY, -270, @base)),
	('yoon',	N'윤서준',	'yoon@example.com',	DATEADD(DAY, -265, @base)),
	('jang',	N'장미래',	NULL,				DATEADD(DAY, -260, @base)),
	('lim',		N'임도윤',	'lim@example.com',	DATEADD(DAY, -255, @base)),
	('han',		N'한소망',	'han@example.com',	DATEADD(DAY, -250, @base)),
	('oh',		N'오준혁',	'oh@example.com',	DATEADD(DAY, -245, @base)),
	('seo',		N'서예린',	'seo@example.com',	DATEADD(DAY, -240, @base)),
	('shin',	N'신우진',	NULL,				DATEADD(DAY, -235, @base)),
	('song',	N'송가온',	'song@example.com',	DATEADD(DAY, -230, @base)),
	('moon',	N'문시우',	'moon@example.com',	DATEADD(DAY, -225, @base)),
	('baek',	N'백나윤',	'baek@example.com',	DATEADD(DAY, -220, @base)),
	('nam',		N'남건우',	'nam@example.com',	DATEADD(DAY, -215, @base)),
	('koo',		N'구하람',	NULL,				DATEADD(DAY, -210, @base)),
	('ahn',		N'안지호',	'ahn@example.com',	DATEADD(DAY, -205, @base));

-- 최지우·장미래·신우진·구하람 넷은 전자 메일이 없습니다(NULL). 1.6·1.9 에서 사용합니다.

/*------------------------------------------------------------------------------
  2. 게시판 3개

  공지는 답글을 받지 않고, 자유·질문만 답글이 달립니다.
  말머리는 자유·질문 게시판에만 있습니다.
------------------------------------------------------------------------------*/
INSERT INTO Board.BOARDS (code, name, use_category, use_reply, reg_date) VALUES
	('notice',	N'공지사항',	0, 0, DATEADD(DAY, -300, @base)),
	('free',	N'자유게시판',	1, 1, DATEADD(DAY, -300, @base)),
	('qna',		N'질문답변',	1, 1, DATEADD(DAY, -300, @base));

/*------------------------------------------------------------------------------
  3. 말머리 10개
------------------------------------------------------------------------------*/
INSERT INTO Board.CATEGORIES (board_num, name, sort_no) VALUES
	(2, N'잡담',	1),
	(2, N'후기',	2),
	(2, N'정보',	3),
	(2, N'질문',	4),
	(2, N'모집',	5),
	(3, N'설치',	1),
	(3, N'쿼리',	2),
	(3, N'성능',	3),
	(3, N'오류',	4),
	(3, N'기타',	5);

/*------------------------------------------------------------------------------
  4. 글 — 원글 350개

  제목은 주제어 20개와 말꼬리 10개를 엮어 만듭니다. 200가지가 나오므로
  겹치는 제목도 생기는데, 실제 게시판이 그렇고 GROUP BY·DISTINCT 예제에도
  그 편이 쓸모 있습니다.
------------------------------------------------------------------------------*/
DECLARE @subject TABLE (idx INT PRIMARY KEY, word NVARCHAR(30));
INSERT INTO @subject VALUES
	(0, N'인덱스'), (1, N'조인'), (2, N'트랜잭션'), (3, N'페이징'),
	(4, N'백업'), (5, N'실행 계획'), (6, N'저장 프로시저'), (7, N'커서'),
	(8, N'임시 테이블'), (9, N'뷰'), (10, N'제약 조건'), (11, N'외래 키'),
	(12, N'정규화'), (13, N'집계 함수'), (14, N'윈도 함수'), (15, N'CTE'),
	(16, N'동적 SQL'), (17, N'격리 수준'), (18, N'전문 검색'), (19, N'계산 열');

DECLARE @tail TABLE (idx INT PRIMARY KEY, word NVARCHAR(50));
INSERT INTO @tail VALUES
	(0, N'질문드립니다'), (1, N'정리해 봤습니다'), (2, N'이렇게 쓰는 게 맞나요'),
	(3, N'실무에서 겪은 일'), (4, N'초보자가 자주 하는 실수'), (5, N'속도가 느립니다'),
	(6, N'개념이 헷갈립니다'), (7, N'예제 모음'), (8, N'문서를 읽어도 모르겠습니다'),
	(9, N'이제야 이해했습니다');

INSERT INTO Board.POSTS
	(board_num, category_num, user_num, parent_num, group_num, depth, sort_no,
	 title, content, hit_count, reg_date, mod_date)
SELECT
	B.board_num,
	-- 말머리를 쓰는 게시판만 값을 넣습니다. 나머지는 NULL 로 두어
	-- LEFT JOIN 과 NULL 예제(1.9)에 쓸 자리를 만듭니다.
	CASE WHEN B.board_num = 2 THEN ((x.n % 5) + 1)
		 WHEN B.board_num = 3 THEN ((x.n % 5) + 6)
		 ELSE NULL END,
	(x.n % 20) + 1,
	NULL,
	0,				-- group_num 은 6번에서 채웁니다
	0,
	0,
	S.word + N' ' + T.word,
	N'본문입니다. ' + S.word + N'에 대해 적었습니다.' + CHAR(13) + CHAR(10)
		+ N'여러 줄로 이루어진 글이라고 생각하고 보시면 됩니다.',
	(x.n * 7) % 500,
	DATEADD(HOUR, x.n * 3, @base),
	-- 여덟 번째 글마다 고친 적이 있는 것으로 둡니다. 나머지는 NULL 입니다.
	CASE WHEN x.n % 8 = 0 THEN DATEADD(HOUR, (x.n * 3) + 30, @base) ELSE NULL END
FROM #Nums AS x
	CROSS APPLY (
		SELECT CASE WHEN x.n % 10 = 0 THEN 1				-- 공지 35건
					WHEN x.n % 10 BETWEEN 1 AND 6 THEN 2	-- 자유 210건
					ELSE 3 END AS board_num					-- 질문 105건
	) AS B
	INNER JOIN @subject AS S ON S.idx = x.n % 20
	INNER JOIN @tail AS T ON T.idx = (x.n / 20) % 10
WHERE x.n <= 350;

/*------------------------------------------------------------------------------
  5. 글 — 답글

  공지(1번 게시판)는 답글을 받지 않으므로 뺍니다.
  원글 셋마다 하나씩 답글을 달고, 그 답글 둘마다 하나씩 답답글을 답니다.
------------------------------------------------------------------------------*/
-- 깊이 1
INSERT INTO Board.POSTS
	(board_num, category_num, user_num, parent_num, group_num, depth, sort_no,
	 title, content, hit_count, reg_date, mod_date)
SELECT
	P.board_num, P.category_num, ((P.num * 3) % 20) + 1, P.num,
	0, 1, 0,
	N'[답글] ' + P.title,
	N'답글입니다. 윗글의 내용을 보고 적었습니다.',
	(P.num * 3) % 200,
	DATEADD(HOUR, 5 + (P.num % 20), P.reg_date),
	NULL
FROM Board.POSTS AS P
WHERE P.board_num <> 1
	AND P.depth = 0
	AND P.num % 3 = 0;

-- 깊이 2
INSERT INTO Board.POSTS
	(board_num, category_num, user_num, parent_num, group_num, depth, sort_no,
	 title, content, hit_count, reg_date, mod_date)
SELECT
	P.board_num, P.category_num, ((P.num * 5) % 20) + 1, P.num,
	0, 2, 0,
	N'[답글] ' + P.title,
	N'답글의 답글입니다.',
	(P.num * 2) % 100,
	DATEADD(HOUR, 3 + (P.num % 12), P.reg_date),
	NULL
FROM Board.POSTS AS P
WHERE P.depth = 1
	AND P.num % 2 = 0;

/*------------------------------------------------------------------------------
  6. 계층 열 채우기 — group_num · sort_no

  여기까지는 parent_num 만 맞춰 넣었습니다. 이제 그 관계를 따라 내려가며
  덩어리 번호와 덩어리 안의 차례를 계산합니다.

  재귀 CTE 로 뿌리부터의 경로를 문자열로 만든 뒤 그 경로로 줄을 세웁니다.
  번호를 다섯 자리로 채워 붙이는 것은 문자열끼리 비교하기 때문입니다 —
  그냥 붙이면 '10' 이 '9' 보다 앞에 옵니다. 2.5 에서 다시 다룹니다.
------------------------------------------------------------------------------*/
WITH tree AS (
	SELECT
		num,
		num AS group_num,
		CAST(RIGHT('00000' + CAST(num AS VARCHAR(10)), 5) AS VARCHAR(900)) AS path
	FROM Board.POSTS
	WHERE parent_num IS NULL

	UNION ALL

	SELECT
		C.num,
		T.group_num,
		CAST(T.path + '.' + RIGHT('00000' + CAST(C.num AS VARCHAR(10)), 5) AS VARCHAR(900))
	FROM Board.POSTS AS C
		INNER JOIN tree AS T ON T.num = C.parent_num
),
ordered AS (
	SELECT
		num,
		group_num,
		ROW_NUMBER() OVER (PARTITION BY group_num ORDER BY path) - 1 AS sort_no
	FROM tree
)
UPDATE P
SET P.group_num = O.group_num,
	P.sort_no = O.sort_no
FROM Board.POSTS AS P
	INNER JOIN ordered AS O ON O.num = P.num
OPTION (MAXRECURSION 0);

/*------------------------------------------------------------------------------
  7. 댓글

  글마다 0~4개입니다. 댓글이 하나도 없는 글이 있어야 LEFT JOIN 과
  COUNT 의 차이(2.2)를 보여 줄 수 있습니다.
------------------------------------------------------------------------------*/
INSERT INTO Board.COMMENTS (post_num, user_num, content, reg_date)
SELECT
	P.num,
	((P.num * 7 + x.n) % 20) + 1,
	N'댓글 ' + CAST(x.n AS NVARCHAR(2)) + N'번입니다. 잘 읽었습니다.',
	DATEADD(HOUR, x.n * 2, P.reg_date)
FROM Board.POSTS AS P
	INNER JOIN #Nums AS x ON x.n <= (P.num % 5);

/*------------------------------------------------------------------------------
  8. 첨부

  다섯 글에 하나꼴로 답니다. 첨부 없는 글이 훨씬 많은 것이 정상이고,
  그래야 LEFT JOIN 을 INNER JOIN 으로 바꿨을 때 무엇이 사라지는지 보입니다.
------------------------------------------------------------------------------*/
INSERT INTO Board.FILES (post_num, origin_name, size_bytes, reg_date)
SELECT TOP (100)
	P.num,
	N'첨부_' + CAST(P.num AS NVARCHAR(10))
		+ CASE P.num % 4 WHEN 0 THEN N'.png' WHEN 1 THEN N'.pdf'
						 WHEN 2 THEN N'.xlsx' ELSE N'.zip' END,
	(P.num * 1024) + 2048,
	P.reg_date
FROM Board.POSTS AS P
WHERE P.num % 5 = 0
ORDER BY P.num;

DROP TABLE #Nums;

/*------------------------------------------------------------------------------
  9. 만들어진 것 확인
------------------------------------------------------------------------------*/
SELECT 'Member.USERS' AS [테이블], COUNT(*) AS [행 수] FROM Member.USERS
UNION ALL SELECT 'Board.BOARDS',		COUNT(*) FROM Board.BOARDS
UNION ALL SELECT 'Board.CATEGORIES',	COUNT(*) FROM Board.CATEGORIES
UNION ALL SELECT 'Board.POSTS',			COUNT(*) FROM Board.POSTS
UNION ALL SELECT 'Board.COMMENTS',		COUNT(*) FROM Board.COMMENTS
UNION ALL SELECT 'Board.FILES',			COUNT(*) FROM Board.FILES;
GO

PRINT '자료를 채웠습니다.';
GO