init.sql

-- Database initialization script for Lab Schedule
-- This script will be run when PostgreSQL container starts for the first time

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    avatar VARCHAR(500),
    role VARCHAR(50) NOT NULL DEFAULT 'user',
    discord_id VARCHAR(255) UNIQUE,
    discord_username VARCHAR(255),
    email VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create events table
CREATE TABLE IF NOT EXISTS events (
    id VARCHAR(255) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL CHECK (type IN ('vacation', 'meeting', 'conference', 'business_trip', 'study', 'other')),
    start_date TIMESTAMP WITH TIME ZONE NOT NULL,
    end_date TIMESTAMP WITH TIME ZONE NOT NULL,
    participants TEXT[], -- Array of participant names
    location VARCHAR(255),
    description TEXT,
    created_by VARCHAR(255) NOT NULL,
    reminder_enabled BOOLEAN DEFAULT false,
    reminder_hours INTEGER DEFAULT 24 CHECK (reminder_hours >= 1 AND reminder_hours <= 24),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_events_start_date ON events(start_date);
CREATE INDEX IF NOT EXISTS idx_events_end_date ON events(end_date);
CREATE INDEX IF NOT EXISTS idx_events_type ON events(type);
CREATE INDEX IF NOT EXISTS idx_events_created_by ON events(created_by);
CREATE INDEX IF NOT EXISTS idx_users_discord_id ON users(discord_id);

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create triggers for updated_at
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_events_updated_at ON events;
CREATE TRIGGER update_events_updated_at
    BEFORE UPDATE ON events
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Insert default admin user (optional)
INSERT INTO users (id, name, avatar, role, discord_id, discord_username, email)
VALUES (
    'admin',
    '관리자',
    '/placeholder.svg?height=32&width=32',
    'admin',
    'admin_discord_id',
    'admin_user',
    'admin@lab.com'
) ON CONFLICT (id) DO NOTHING;

-- Insert sample events (optional, for testing)
INSERT INTO events (id, title, type, start_date, end_date, participants, location, description, created_by)
VALUES 
    (
        'sample-1',
        '정기 미팅',
        'meeting',
        CURRENT_TIMESTAMP + INTERVAL '1 day',
        CURRENT_TIMESTAMP + INTERVAL '1 day' + INTERVAL '2 hours',
        ARRAY['관리자'],
        '회의실 A',
        '주간 진행사항 공유',
        'admin'
    ),
    (
        'sample-2',
        '학회 참석',
        'conference',
        CURRENT_TIMESTAMP + INTERVAL '7 days',
        CURRENT_TIMESTAMP + INTERVAL '9 days',
        ARRAY['관리자'],
        'COEX',
        'AI 국제학회 참석',
        'admin'
    )
ON CONFLICT (id) DO NOTHING;

-- Grant permissions (if using specific user)
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_app_user;
-- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_app_user;