CREATE TABLE IF NOT EXISTS members (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    userid VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nickname VARCHAR(50) NOT NULL,
    grade ENUM('member', 'admin') NOT NULL DEFAULT 'member',
    points INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS boards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(50) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    board_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NULL,
    member_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    notice_type VARCHAR(50) NULL,
    meeting_type ENUM('regular', 'lightning', 'reservation') NULL,
    meeting_date DATETIME NULL,
    meeting_region VARCHAR(50) NULL,
    meeting_location VARCHAR(255) NULL,
    meeting_place_status ENUM('fixed', 'discuss', 'same_day') NOT NULL DEFAULT 'fixed',
    restaurant_name VARCHAR(100) NULL,
    restaurant_address VARCHAR(255) NULL,
    restaurant_phone VARCHAR(50) NULL,
    restaurant_menu VARCHAR(255) NULL,
    restaurant_price_range VARCHAR(100) NULL,
    restaurant_solo_level VARCHAR(50) NULL,
    restaurant_hours VARCHAR(100) NULL,
    restaurant_images TEXT NULL,
    restaurant_menu_image VARCHAR(255) NULL,
    restaurant_menu_board_image VARCHAR(255) NULL,
    restaurant_has_parking TINYINT(1) NOT NULL DEFAULT 0,
    restaurant_parking_capacity VARCHAR(100) NULL,
    restaurant_has_wifi TINYINT(1) NOT NULL DEFAULT 0,
    restaurant_has_restroom TINYINT(1) NOT NULL DEFAULT 0,
    restaurant_has_group_dining TINYINT(1) NOT NULL DEFAULT 0,
    restaurant_has_reservation TINYINT(1) NOT NULL DEFAULT 0,
    require_rsvp TINYINT(1) NOT NULL DEFAULT 0,
    recruitment_status ENUM('open', 'closed') NOT NULL DEFAULT 'open',
    views INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_posts_board_created (board_id, created_at),
    INDEX idx_posts_member (member_id),
    CONSTRAINT fk_posts_board FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE,
    CONSTRAINT fk_posts_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    member_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    content TEXT NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_comments_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_comments_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS post_likes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY uq_post_like (post_id, member_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS comment_likes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, comment_id BIGINT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY uq_comment_like (comment_id, member_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS post_categories (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, board_id BIGINT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY uq_post_categories (board_id, name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS post_attendees (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, status ENUM('yes','late','no') NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, UNIQUE KEY uq_post_attendees_post_member (post_id, member_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS post_meeting_rounds (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, round_number INT UNSIGNED NOT NULL, title VARCHAR(100) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS post_attendee_rounds (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, attendee_id BIGINT UNSIGNED NOT NULL, round_number INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY uq_attendee_round (attendee_id, round_number)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS post_settlements (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL UNIQUE, payer_member_id BIGINT UNSIGNED NOT NULL, total_amount INT UNSIGNED NOT NULL, bank_name VARCHAR(50) NULL, account_number VARCHAR(80) NULL, account_holder VARCHAR(50) NULL, memo VARCHAR(255) NULL, status ENUM('open','closed') NOT NULL DEFAULT 'open', closed_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS post_settlement_rounds (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, settlement_id BIGINT UNSIGNED NOT NULL, round_number INT UNSIGNED NOT NULL, title VARCHAR(100) NOT NULL, amount INT UNSIGNED NOT NULL, receipt_path VARCHAR(255) NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS post_settlement_round_members (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, round_id BIGINT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY uq_round_member (round_id, member_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS post_settlement_payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, settlement_id BIGINT UNSIGNED NOT NULL, member_id BIGINT UNSIGNED NOT NULL, paid_at DATETIME NOT NULL, UNIQUE KEY uq_post_settlement_payments_member (settlement_id, member_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;