DROP DATABASE RECOVER_YOUR_DATA;

CREATE DATABASE yesul;

use yesul;

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'PK',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '아이디',
  `password` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `nickname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `birthday` varchar(8) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `type` char(1) NOT NULL COMMENT '1:일반 2:소셜',
  `status` char(1) NOT NULL DEFAULT '2' COMMENT '1:회원, 2:미인증회원 3:탈퇴',
  `profile` text COMMENT 'img_url',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `email_check_token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `email_check_token_generated_at` datetime DEFAULT NULL,
  `provider` varchar(255) DEFAULT NULL,
  `provider_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `user` (
  `email`, 
  `password`, 
  `name`, 
  `nickname`, 
  `birthday`, 
  `address`, 
  `type`, 
  `status`, 
  `profile`, 
  `created_at`, 
  `updated_at`, 
  `email_check_token`, 
  `email_check_token_generated_at`, 
  `provider`, 
  `provider_id`
) VALUES
('[email protected]', '$2a$10$R9hdyhMzjz3zYq1pQ5zzd.mdbHbYrKGHG1r1fLzB/WYZk5Be8qV9i', 'John Doe', 'johnny', '19900101', '123 Some St.', '1', '1', '<https://profile.url/johndoe.jpg>', NOW(), NOW(), NULL, NULL, NULL, NULL),
('[email protected]', '$2a$10$HgWhQf/oW9jRUjZv6ySC4d6cZrFzFtDPBMOZHV.fPoj0eTeQ1E8h.', 'Jane Doe', 'janie', '19890505', '456 Another Rd.', '1', '1', '<https://profile.url/janedoe.jpg>', NOW(), NOW(), NULL, NULL, NULL, NULL),
('[email protected]', '$2a$10$O3zKHXqUpe9fBnxRb9k/OZTky2ckFJ85d1DhPOaJAFjwAkKNywXYi', 'Tom Lee', 'tommy', '19921212', '789 Old St.', '2', '1', '<https://profile.url/tomlee.jpg>', NOW(), NOW(), NULL, NULL, 'kakao', 'kakao_id_123'),
('[email protected]', '$2a$10$BzA8ZkErHKVdmKbFXhwsMEmFq9QECX5OTMl5PdvG.3jlBcn6ivH9y', 'Sara Kim', 'sara', '19950505', '101 New St.', '2', '1', '<https://profile.url/sarakim.jpg>', NOW(), NOW(), NULL, NULL, 'naver', 'naver_id_456'),
('[email protected]', '$2a$10$xtJ3lqKZqkFyEdhXjkw0wHe1.b3s95BPE7P8pq99Egw7lNEjBw2Zm', 'Michael Park', 'mike', '19930303', '202 Park Ave.', '1', '1', '<https://profile.url/michaelpark.jpg>', NOW(), NOW(), NULL, NULL, NULL, NULL),
('[email protected]', '$2a$10$SKQ6.2I34z4bdVJZ5OZ6P5W4k6dXh6FMvxjeKh9gHwbGqol2wpYti', 'Lisa Lee', 'lisa', '19941201', '303 River Rd.', '2', '1', '<https://profile.url/lisalee.jpg>', NOW(), NOW(), NULL, NULL, 'kakao', 'kakao_id_789'),
('[email protected]', '$2a$10$D2mWdsEYmro0.oI0PL5u.aDqJ4ZgJjXj6gVu9mVpsDhJZCU9FgMMK', 'David Choi', 'david', '19880608', '404 Ocean Blvd.', '2', '1', '<https://profile.url/davidchoi.jpg>', NOW(), NOW(), NULL, NULL, 'naver', 'naver_id_123'),
('[email protected]', '$2a$10$XeBxo9rR.ykWxP2y/xTxA0RJoqmZnHzb3b2pq8PqkbW0yXGb8csNS', 'Alice Seo', 'alice', '19970707', '505 Mountain St.', '1', '1', '<https://profile.url/aliceseo.jpg>', NOW(), NOW(), NULL, NULL, NULL, NULL),
('[email protected]', '$2a$10$Gr4.VXH1a8y6vlOQtWg5u0VvFZGfA7rcxxff72GiGRkYkgGVuEqUu', 'George Lim', 'george', '19900101', '606 Valley Rd.', '2', '1', '<https://profile.url/georgelim.jpg>', NOW(), NOW(), NULL, NULL, 'kakao', 'kakao_id_456'),
('[email protected]', '$2a$10$RknFEQKvPjsV0slrph7qRfa9TQFEqOR02eqnEVZevIfHgq2cVYo6i', 'Sophia Yun', 'sophia', '19961212', '707 Hill St.', '2', '1', '<https://profile.url/sophiayun.jpg>', NOW(), NOW(), NULL, NULL, 'naver', 'naver_id_789');

CREATE TABLE admin (
    id BIGINT PRIMARY KEY,
    login_id VARCHAR(255) NOT NULL,
    login_pwd VARCHAR(255) NOT NULL,
    `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO admin (id, login_id, login_pwd)
VALUES (1, 'admin', '$2a$10$lJ01C0OxqKWg6TAFqGV4JODdmwp9.aXmnm.fai9eaDy98KjhLV3sK');
// decode: password123

CREATE TABLE post (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '게시글 ID',
    user_id BIGINT NOT NULL COMMENT '작성자 고유 ID',
    board_name VARCHAR(40) NOT NULL COMMENT '게시판명',
    title VARCHAR(100) NOT NULL COMMENT '제목',
    content TEXT NOT NULL COMMENT '본문 내용',
    view_count BIGINT DEFAULT 0 COMMENT '조회수',
    thumbnail VARCHAR(2000) DEFAULT NULL COMMENT '게시글 대표 이미지',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성일',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정일',
    
    CONSTRAINT fk_post_user FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

CREATE TABLE post_image (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '이미지 postpostID',
    post_id BIGINT NOT NULL COMMENT '게시글 ID',
    image_url VARCHAR(2000) NOT NULL COMMENT '게시글 이미지',

    CONSTRAINT fk_post_image_post FOREIGN KEY (post_id) REFERENCES post(id) ON DELETE CASCADE
);