-- MSSQL 초기화 스크립트
-- 데이터베이스와 사용자 생성

USE master;
GO

-- wace 데이터베이스 생성
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'wace')
BEGIN
    CREATE DATABASE wace
    COLLATE Korean_Wansung_CI_AS;
END
GO

-- wace 사용자 생성 및 권한 부여
USE wace;
GO

-- 로그인 생성
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'wace')
BEGIN
    CREATE LOGIN wace WITH PASSWORD = 'wace0909!!';
END
GO

-- 사용자 생성
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = 'wace')
BEGIN
    CREATE USER wace FOR LOGIN wace;
    ALTER ROLE db_owner ADD MEMBER wace;
END
GO

-- 샘플 테이블 생성
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'users')
BEGIN
    CREATE TABLE users (
        id INT IDENTITY(1,1) PRIMARY KEY,
        username NVARCHAR(50) NOT NULL UNIQUE,
        email NVARCHAR(100) NOT NULL UNIQUE,
        password_hash NVARCHAR(255) NOT NULL,
        created_at DATETIME2 DEFAULT GETDATE(),
        updated_at DATETIME2 DEFAULT GETDATE()
    );
END
GO

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'user_profiles')
BEGIN
    CREATE TABLE user_profiles (
        id INT IDENTITY(1,1) PRIMARY KEY,
        user_id INT NOT NULL,
        first_name NVARCHAR(50),
        last_name NVARCHAR(50),
        phone NVARCHAR(20),
        address NVARCHAR(MAX),
        created_at DATETIME2 DEFAULT GETDATE(),
        updated_at DATETIME2 DEFAULT GETDATE(),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );
END
GO

-- 샘플 데이터 삽입
IF NOT EXISTS (SELECT * FROM users WHERE username = 'admin')
BEGIN
    INSERT INTO users (username, email, password_hash) VALUES 
    ('admin', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi'),
    ('testuser', 'test@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');
    
    INSERT INTO user_profiles (user_id, first_name, last_name, phone) VALUES 
    (1, N'관리자', N'시스템', '010-1234-5678'),
    (2, N'테스트', N'사용자', '010-9876-5432');
END
GO

PRINT 'MSSQL wace 데이터베이스 초기화 완료';
GO