테이블 구조

show databases;

create database happy_friday;

drop database happya_friday_db;

use happya_friday_db;

select 

create database if not exists happy_friday_db
default character set utf8mb4
default collate utf8mb4_unicode_ci;
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin1234';
/*CREATE USER 'admin'@'%' IDENTIFIED BY 'admin1234';*/
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'admin1234';
GRANT ALL PRIVILEGES ON happy_friday_db.* TO 'admin'@'localhost';
GRANT ALL PRIVILEGES ON happy_friday_db.* TO 'admin'@'%';
FLUSH PRIVILEGES;


CREATE TABLE authority (
    authority_name VARCHAR(50) NOT NULL,
    description VARCHAR(255) NULL,
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ( authority_name )
) ;

INSERT INTO authority (authority_name, description) VALUES 
('ROLE_USER', '일반 사용자 권한'),
('ROLE_ADMIN', '관리자 권한'),
('ROLE_MANAGER', '매니저 권한');

CREATE TABLE users (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(50) NOT NULL,
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_enabled BOOLEAN DEFAULT TRUE,
    is_locked BOOLEAN DEFAULT FALSE,
    last_login TIMESTAMP NULL,
    INDEX idx_users_email (email),
    INDEX idx_users_name (name),
    INDEX idx_users_created_date (create_date)
) COMMENT = '사용자 정보 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SELECT * FROM users;




drop table user_authority;

CREATE TABLE user_authority (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    authority_name VARCHAR(50) NOT NULL,
    granted_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (authority_name) REFERENCES authority(authority_name) ON DELETE CASCADE,
    UNIQUE KEY uk_user_authority (user_id, authority_name),
    INDEX idx_user_authority_user_id (user_id),
    INDEX idx_user_authority_name (authority_name)
) COMMENT = '사용자-권한 연결 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



INSERT INTO user_authority (user_id, authority_name) VALUES 
(1, 'ROLE_ADMIN'),
(1, 'ROLE_USER'),
(2, 'ROLE_USER'),
(3, 'ROLE_MANAGER'),
(3, 'ROLE_USER');

-- 로그인 이력 테이블 (선택사항)
CREATE TABLE login_history (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    is_success BOOLEAN DEFAULT TRUE,
    failure_reason VARCHAR(255) NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_login_history_user_id (user_id),
    INDEX idx_login_history_time (login_time)
) COMMENT = '로그인 이력 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- JWT 토큰 블랙리스트 테이블 (로그아웃된 토큰 관리)
CREATE TABLE jwt_blacklist (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    token_id VARCHAR(255) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    expired_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_jwt_blacklist_token_id (token_id),
    INDEX idx_jwt_blacklist_user_id (user_id),
    INDEX idx_jwt_blacklist_expired_at (expired_at)
)COMMENT = 'JWT 토큰 블랙리스트 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 기존 users 테이블 수정 (기본 인증 정보만 유지)
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20) NULL,
ADD COLUMN email_verified BOOLEAN DEFAULT FALSE,
ADD COLUMN phone_verified BOOLEAN DEFAULT FALSE,
ADD COLUMN status ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED', 'PENDING') DEFAULT 'PENDING',
ADD COLUMN last_password_change TIMESTAMP NULL,
ADD COLUMN failed_login_attempts INT DEFAULT 0,
ADD COLUMN locked_until TIMESTAMP NULL,
ADD INDEX idx_users_status (status),
ADD INDEX idx_users_phone (phone);


CREATE TABLE user_profiles (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL UNIQUE,
    -- 개인 정보
    first_name VARCHAR(50) NULL,
    last_name VARCHAR(50) NULL,
    nickname VARCHAR(50) NULL,
    display_name VARCHAR(100) NULL,
    bio TEXT NULL,
    -- 생년월일 및 성별
    birth_date DATE NULL,
    gender ENUM('MALE', 'FEMALE', 'OTHER', 'PREFER_NOT_TO_SAY') NULL,
    -- 국적 및 언어
    nationality VARCHAR(50) NULL,
    preferred_language VARCHAR(10) DEFAULT 'ko',
    timezone VARCHAR(50) DEFAULT 'Asia/Seoul',
    -- 프로필 이미지
    profile_image_url VARCHAR(500) NULL,
    profile_image_filename VARCHAR(255) NULL,
    cover_image_url VARCHAR(500) NULL,
    cover_image_filename VARCHAR(255) NULL,
    -- 공개 설정
    profile_visibility ENUM('PUBLIC', 'FRIENDS_ONLY', 'PRIVATE') DEFAULT 'PUBLIC',
    show_email BOOLEAN DEFAULT FALSE,
    show_phone BOOLEAN DEFAULT FALSE,
    show_birth_date BOOLEAN DEFAULT FALSE,
    -- 타임스탬프
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_profiles_user_id (user_id),
    INDEX idx_user_profiles_nickname (nickname),
    INDEX idx_user_profiles_display_name (display_name)
)     COMMENT = '사용자 프로필 상세 정보 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 사용자 주소 정보 테이블 (다중 주소 지원)
CREATE TABLE user_addresses (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    -- 주소 타입
    address_type ENUM('HOME', 'WORK', 'BILLING', 'SHIPPING', 'OTHER') NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    -- 주소 정보
    country VARCHAR(100) NOT NULL,
    state_province VARCHAR(100) NULL,
    city VARCHAR(100) NOT NULL,
    district VARCHAR(100) NULL,
    street_address TEXT NOT NULL,
    postal_code VARCHAR(20) NULL,
    -- 추가 정보
    address_label VARCHAR(100) NULL, -- 사용자 지정 라벨 (예: '우리집', '회사')
    recipient_name VARCHAR(100) NULL, -- 수령인 이름
    recipient_phone VARCHAR(20) NULL,
    -- 좌표 정보 (선택사항)
    latitude DECIMAL(10, 8) NULL,
    longitude DECIMAL(11, 8) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_addresses_user_id (user_id),
    INDEX idx_user_addresses_type (address_type),
    INDEX idx_user_addresses_default (user_id, is_default)
) COMMENT = '사용자 주소 정보 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 사용자 개별 설정 테이블
CREATE TABLE user_settings (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    -- 알림 설정
    email_notifications BOOLEAN DEFAULT TRUE,
    sms_notifications BOOLEAN DEFAULT TRUE,
    push_notifications BOOLEAN DEFAULT TRUE,
    marketing_emails BOOLEAN DEFAULT FALSE,
    -- 보안 설정
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    login_notifications BOOLEAN DEFAULT TRUE,
    session_timeout_minutes INT DEFAULT 60,
    -- UI/UX 설정
    theme ENUM('LIGHT', 'DARK', 'SYSTEM') DEFAULT 'SYSTEM',
    language VARCHAR(10) DEFAULT 'ko',
    date_format VARCHAR(20) DEFAULT 'YYYY-MM-DD',
    time_format VARCHAR(10) DEFAULT '24H',
    -- 개인정보 설정
    data_sharing_consent BOOLEAN DEFAULT FALSE,
    analytics_tracking BOOLEAN DEFAULT TRUE,
    personalized_ads BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uk_user_settings_user_id (user_id)
) COMMENT = '사용자 개별 설정 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 소셜 미디어 계정 연결 테이블
CREATE TABLE user_social_accounts (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    -- 소셜 플랫폼 정보
    platform ENUM('GOOGLE', 'FACEBOOK', 'TWITTER', 'LINKEDIN', 'GITHUB', 'KAKAO', 'NAVER') NOT NULL,
    social_id VARCHAR(255) NOT NULL, -- 소셜 플랫폼의 사용자 ID
    social_email VARCHAR(255) NULL,
    social_username VARCHAR(255) NULL,
    -- 연결 상태
    is_verified BOOLEAN DEFAULT FALSE,
    is_primary BOOLEAN DEFAULT FALSE, -- 주 계정 여부
    -- 추가 정보
    access_token TEXT NULL,
    refresh_token TEXT NULL,
    token_expires_at TIMESTAMP NULL,
    -- 프로필 정보
    avatar_url VARCHAR(500) NULL,
    profile_url VARCHAR(500) NULL,
    connected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uk_social_platform_id (platform, social_id),
    INDEX idx_user_social_user_id (user_id),
    INDEX idx_user_social_platform (platform)
)COMMENT = '소셜 미디어 계정 연결 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 사용자 파일 관리 테이블
CREATE TABLE user_files (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    -- 파일 기본 정보
    file_name VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_type ENUM('PROFILE_IMAGE', 'COVER_IMAGE', 'DOCUMENT', 'ATTACHMENT', 'OTHER') NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size BIGINT NOT NULL, -- bytes
    -- 파일 경로 및 URL
    file_path VARCHAR(500) NOT NULL,
    file_url VARCHAR(500) NULL,
    thumbnail_url VARCHAR(500) NULL,
    -- 파일 상태
    upload_status ENUM('UPLOADING', 'COMPLETED', 'FAILED') DEFAULT 'UPLOADING',
    is_public BOOLEAN DEFAULT FALSE,
    -- 메타데이터
    alt_text VARCHAR(255) NULL,
    description TEXT NULL,
    tags JSON NULL,
    -- 이미지 전용 필드
    width INT NULL,
    height INT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_files_user_id (user_id),
    INDEX idx_user_files_type (file_type),
    INDEX idx_user_files_status (upload_status)
) COMMENT = '사용자 파일 관리 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 사용자 활동 로그 테이블
CREATE TABLE user_activity_logs (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    -- 활동 정보
    activity_type ENUM('LOGIN', 'LOGOUT', 'PROFILE_UPDATE', 'PASSWORD_CHANGE', 'EMAIL_CHANGE', 
                      'PHONE_CHANGE', 'ADDRESS_UPDATE', 'SETTINGS_CHANGE', 'FILE_UPLOAD', 
                      'SOCIAL_CONNECT', 'SOCIAL_DISCONNECT', 'ACCOUNT_VERIFICATION', 'OTHER') NOT NULL,
    activity_description TEXT NULL,
    -- 세부 정보
    changed_fields JSON NULL, -- 변경된 필드들
    old_values JSON NULL,     -- 이전 값들
    new_values JSON NULL,     -- 새로운 값들
    -- 접속 정보
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    device_type VARCHAR(50) NULL,
    browser VARCHAR(100) NULL,
    os VARCHAR(100) NULL,
    location VARCHAR(255) NULL,
    -- 결과
    is_success BOOLEAN DEFAULT TRUE,
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_activity_logs_user_id (user_id),
    INDEX idx_activity_logs_type (activity_type),
    INDEX idx_activity_logs_created_at (created_at),
    INDEX idx_activity_logs_user_type (user_id, activity_type)
) COMMENT = '사용자 활동 로그 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 사용자 선호도 및 관심사 테이블
CREATE TABLE user_preferences (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    -- 선호도 카테고리
    category VARCHAR(100) NOT NULL, -- 예: 'interests', 'skills', 'hobbies', 'dietary'
    preference_key VARCHAR(100) NOT NULL,
    preference_value VARCHAR(255) NOT NULL,
    -- 가중치 및 우선순위
    weight DECIMAL(3,2) DEFAULT 1.00, -- 0.00 ~ 1.00
    priority INT DEFAULT 0,
    -- 메타데이터
    source ENUM('USER_INPUT', 'SYSTEM_INFERRED', 'IMPORTED', 'SURVEY') DEFAULT 'USER_INPUT',
    confidence_score DECIMAL(3,2) DEFAULT 1.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY uk_user_preferences (user_id, category, preference_key),
    INDEX idx_user_preferences_user_id (user_id),
    INDEX idx_user_preferences_category (category)
) COMMENT = '사용자 선호도 및 관심사 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 프로필 변경 히스토리 테이블
CREATE TABLE profile_change_history (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    -- 변경 정보
    table_name VARCHAR(100) NOT NULL, -- 변경된 테이블명
    field_name VARCHAR(100) NOT NULL, -- 변경된 필드명
    old_value TEXT NULL,
    new_value TEXT NULL,
    -- 변경 사유
    change_reason VARCHAR(255) NULL,
    changed_by ENUM('USER', 'ADMIN', 'SYSTEM') DEFAULT 'USER',
    admin_user_id BIGINT NULL, -- 관리자가 변경한 경우
    -- 승인 관련
    requires_approval BOOLEAN DEFAULT FALSE,
    approval_status ENUM('PENDING', 'APPROVED', 'REJECTED') DEFAULT 'APPROVED',
    approved_by BIGINT NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_profile_history_user_id (user_id),
    INDEX idx_profile_history_table (table_name),
    INDEX idx_profile_history_approval (approval_status)
)COMMENT = '프로필 변경 히스토리 테이블' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 성능 최적화를 위한 추가 인덱스
CREATE INDEX idx_users_email_status ON users(email, status);
CREATE INDEX idx_user_profiles_visibility ON user_profiles(profile_visibility);
CREATE INDEX idx_activity_logs_recent ON user_activity_logs(user_id, created_at DESC);
CREATE INDEX idx_files_user_type ON user_files(user_id, file_type);

-- 복합 인덱스
CREATE INDEX idx_addresses_user_default ON user_addresses(user_id, is_default, address_type);
CREATE INDEX idx_social_user_platform ON user_social_accounts(user_id, platform, is_verified);