-- Zellyy Finance 데이터베이스 스키마 스크립트
-- 작성일: 2023년

-- UUID 확장 활성화
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 타임스탬프 함수 생성 (자동 업데이트용)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 1. 사용자 테이블
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    full_name TEXT NOT NULL,
    profile_image_url TEXT,
    monthly_income DECIMAL(12, 2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 사용자 테이블 업데이트 트리거
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- 사용자 테이블 RLS 정책
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY "사용자는 자신의 정보만 볼 수 있음" ON users
    FOR SELECT USING (auth.uid() = id);
CREATE POLICY "사용자는 자신의 정보만 수정할 수 있음" ON users
    FOR UPDATE USING (auth.uid() = id);

-- 2. 카테고리 테이블
CREATE TABLE categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    color TEXT NOT NULL,
    icon TEXT,
    is_income BOOLEAN DEFAULT FALSE,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, name)
);

-- 카테고리 테이블 업데이트 트리거
CREATE TRIGGER update_categories_updated_at
BEFORE UPDATE ON categories
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- 카테고리 테이블 RLS 정책
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
CREATE POLICY "사용자는 자신의 카테고리와 기본 카테고리를 볼 수 있음" ON categories
    FOR SELECT USING (auth.uid() = user_id OR is_default = TRUE);
CREATE POLICY "사용자는 자신의 카테고리만 수정할 수 있음" ON categories
    FOR UPDATE USING (auth.uid() = user_id AND is_default = FALSE);
CREATE POLICY "사용자는 자신의 카테고리만 삭제할 수 있음" ON categories
    FOR DELETE USING (auth.uid() = user_id AND is_default = FALSE);
CREATE POLICY "사용자는 자신의 카테고리만 생성할 수 있음" ON categories
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- 3. 카드 테이블
CREATE TABLE cards (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    card_number TEXT,
    card_type TEXT NOT NULL,
    bank_name TEXT,
    color TEXT,
    billing_day INTEGER,
    payment_day INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, name)
);

-- 카드 테이블 업데이트 트리거
CREATE TRIGGER update_cards_updated_at
BEFORE UPDATE ON cards
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- 카드 테이블 RLS 정책
ALTER TABLE cards ENABLE ROW LEVEL SECURITY;
CREATE POLICY "사용자는 자신의 카드만 볼 수 있음" ON cards
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 카드만 수정할 수 있음" ON cards
    FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 카드만 삭제할 수 있음" ON cards
    FOR DELETE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 카드만 생성할 수 있음" ON cards
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- 4. 한도 테이블
CREATE TABLE limits (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    card_id UUID REFERENCES cards(id) ON DELETE CASCADE,
    category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    amount DECIMAL(12, 2) NOT NULL,
    period TEXT NOT NULL, -- 'monthly', 'weekly', 'daily'
    start_date DATE NOT NULL,
    end_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    CONSTRAINT card_or_category_required CHECK (card_id IS NOT NULL OR category_id IS NOT NULL)
);

-- 한도 테이블 업데이트 트리거
CREATE TRIGGER update_limits_updated_at
BEFORE UPDATE ON limits
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- 한도 테이블 RLS 정책
ALTER TABLE limits ENABLE ROW LEVEL SECURITY;
CREATE POLICY "사용자는 자신의 한도만 볼 수 있음" ON limits
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 한도만 수정할 수 있음" ON limits
    FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 한도만 삭제할 수 있음" ON limits
    FOR DELETE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 한도만 생성할 수 있음" ON limits
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- 5. 지출 테이블
CREATE TABLE expenses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    card_id UUID REFERENCES cards(id) ON DELETE SET NULL,
    category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    amount DECIMAL(12, 2) NOT NULL,
    description TEXT,
    date TIMESTAMP WITH TIME ZONE NOT NULL,
    location TEXT,
    receipt_image_url TEXT,
    is_income BOOLEAN DEFAULT FALSE,
    is_recurring BOOLEAN DEFAULT FALSE,
    recurring_period TEXT, -- 'monthly', 'weekly', 'daily'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 지출 테이블 업데이트 트리거
CREATE TRIGGER update_expenses_updated_at
BEFORE UPDATE ON expenses
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- 지출 테이블 RLS 정책
ALTER TABLE expenses ENABLE ROW LEVEL SECURITY;
CREATE POLICY "사용자는 자신의 지출만 볼 수 있음" ON expenses
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 지출만 수정할 수 있음" ON expenses
    FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 지출만 삭제할 수 있음" ON expenses
    FOR DELETE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 지출만 생성할 수 있음" ON expenses
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- 6. 템플릿 테이블
CREATE TABLE templates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    card_id UUID REFERENCES cards(id) ON DELETE SET NULL,
    category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    amount DECIMAL(12, 2) NOT NULL,
    description TEXT,
    location TEXT,
    is_income BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, name)
);

-- 템플릿 테이블 업데이트 트리거
CREATE TRIGGER update_templates_updated_at
BEFORE UPDATE ON templates
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- 템플릿 테이블 RLS 정책
ALTER TABLE templates ENABLE ROW LEVEL SECURITY;
CREATE POLICY "사용자는 자신의 템플릿만 볼 수 있음" ON templates
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 템플릿만 수정할 수 있음" ON templates
    FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 템플릿만 삭제할 수 있음" ON templates
    FOR DELETE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 템플릿만 생성할 수 있음" ON templates
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- 7. 알림 테이블
CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    message TEXT NOT NULL,
    type TEXT NOT NULL, -- 'limit_warning', 'payment_due', 'tip', 'system'
    related_id UUID, -- 관련된 항목의 ID (카드, 한도 등)
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 알림 테이블 업데이트 트리거
CREATE TRIGGER update_notifications_updated_at
BEFORE UPDATE ON notifications
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- 알림 테이블 RLS 정책
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;
CREATE POLICY "사용자는 자신의 알림만 볼 수 있음" ON notifications
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 알림만 수정할 수 있음" ON notifications
    FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 알림만 삭제할 수 있음" ON notifications
    FOR DELETE USING (auth.uid() = user_id);
CREATE POLICY "시스템만 알림을 생성할 수 있음" ON notifications
    FOR INSERT WITH CHECK (auth.uid() = user_id OR auth.uid() IS NULL);

-- 8. 분석 설정 테이블
CREATE TABLE analysis_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    budget_period TEXT DEFAULT 'monthly', -- 'monthly', 'weekly', 'daily'
    saving_goal DECIMAL(12, 2),
    analysis_period INTEGER DEFAULT 3, -- 분석에 사용할 과거 데이터 기간 (개월)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id)
);

-- 분석 설정 테이블 업데이트 트리거
CREATE TRIGGER update_analysis_settings_updated_at
BEFORE UPDATE ON analysis_settings
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- 분석 설정 테이블 RLS 정책
ALTER TABLE analysis_settings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "사용자는 자신의 분석 설정만 볼 수 있음" ON analysis_settings
    FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 분석 설정만 수정할 수 있음" ON analysis_settings
    FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 분석 설정만 삭제할 수 있음" ON analysis_settings
    FOR DELETE USING (auth.uid() = user_id);
CREATE POLICY "사용자는 자신의 분석 설정만 생성할 수 있음" ON analysis_settings
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- 기본 카테고리 데이터 삽입
INSERT INTO categories (name, color, icon, is_income, is_default) VALUES
('식비', '#FF5722', 'restaurant', FALSE, TRUE),
('교통', '#2196F3', 'directions_car', FALSE, TRUE),
('주거/통신', '#9C27B0', 'home', FALSE, TRUE),
('쇼핑', '#4CAF50', 'shopping_bag', FALSE, TRUE),
('의료/건강', '#F44336', 'local_hospital', FALSE, TRUE),
('문화/여가', '#FFEB3B', 'movie', FALSE, TRUE),
('교육', '#795548', 'school', FALSE, TRUE),
('경조사/기부', '#607D8B', 'card_giftcard', FALSE, TRUE),
('기타', '#9E9E9E', 'more_horiz', FALSE, TRUE),
('급여', '#4CAF50', 'payments', TRUE, TRUE),
('용돈', '#8BC34A', 'savings', TRUE, TRUE),
('상여금', '#CDDC39', 'card_giftcard', TRUE, TRUE),
('환불', '#FFC107', 'replay', TRUE, TRUE),
('투자수익', '#00BCD4', 'trending_up', TRUE, TRUE),
('기타수입', '#9E9E9E', 'more_horiz', TRUE, TRUE);

-- 인덱스 생성
CREATE INDEX idx_expenses_user_id ON expenses(user_id);
CREATE INDEX idx_expenses_date ON expenses(date);
CREATE INDEX idx_expenses_category_id ON expenses(category_id);
CREATE INDEX idx_expenses_card_id ON expenses(card_id);
CREATE INDEX idx_limits_user_id ON limits(user_id);
CREATE INDEX idx_cards_user_id ON cards(user_id);
CREATE INDEX idx_categories_user_id ON categories(user_id);
CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_is_read ON notifications(is_read);