-- =====================================================
-- 부산교통공사 1호선 고장코드 시스템 - 데이터베이스 스키마
-- =====================================================

-- 1. 부서 테이블 (departments)
-- 소속 부서 관리 및 권한 제어를 위한 테이블
CREATE TABLE IF NOT EXISTS public.departments (
    id SERIAL PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL,  -- 부서 코드 (예: SPC001, NPC001)
    name VARCHAR(100) NOT NULL,        -- 부서명 (예: 신평차량, 노포차량, 차량처)
    description TEXT,                   -- 부서 설명
    is_active BOOLEAN DEFAULT true,    -- 활성화 여부
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 부서 코드에 인덱스 생성 (검색 성능 향상)
CREATE INDEX IF NOT EXISTS idx_departments_code ON public.departments(code);
CREATE INDEX IF NOT EXISTS idx_departments_is_active ON public.departments(is_active);

-- 부서 테이블에 초기 데이터 삽입
INSERT INTO public.departments (code, name, description) VALUES
    ('SPC', '신평차량', '신평차량사업소'),
    ('NPC', '노포차량', '노포차량사업소'),
    ('VHD', '차량처', '차량처')
ON CONFLICT (code) DO NOTHING;

-- 2. 사용자 테이블 (users)
-- 회원 정보 및 인증 정보를 저장하는 테이블
CREATE TABLE IF NOT EXISTS public.users (
    id SERIAL PRIMARY KEY,
    auth_id UUID UNIQUE,                -- Supabase Auth의 사용자 ID (외래키 개념)
    email VARCHAR(255) UNIQUE NOT NULL, -- 이메일 (humetro.busan.kr 도메인만)
    employee_id VARCHAR(50) UNIQUE NOT NULL,  -- 사번
    name VARCHAR(100) NOT NULL,         -- 이름
    department_id INTEGER NOT NULL REFERENCES public.departments(id) ON DELETE RESTRICT,  -- 소속 부서
    is_active BOOLEAN DEFAULT true,     -- 계정 활성화 여부
    last_login_at TIMESTAMPTZ,          -- 마지막 로그인 시각
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 사용자 테이블 인덱스
CREATE INDEX IF NOT EXISTS idx_users_auth_id ON public.users(auth_id);
CREATE INDEX IF NOT EXISTS idx_users_email ON public.users(email);
CREATE INDEX IF NOT EXISTS idx_users_employee_id ON public.users(employee_id);
CREATE INDEX IF NOT EXISTS idx_users_department_id ON public.users(department_id);
CREATE INDEX IF NOT EXISTS idx_users_is_active ON public.users(is_active);

-- 3. 부서별 데이터 접근 권한 테이블 (department_permissions)
-- 향후 부서별 고장코드 조회/수정 권한 관리를 위한 테이블
CREATE TABLE IF NOT EXISTS public.department_permissions (
    id SERIAL PRIMARY KEY,
    department_id INTEGER NOT NULL REFERENCES public.departments(id) ON DELETE CASCADE,
    resource_type VARCHAR(50) NOT NULL,  -- 'fault_code', 'signal', 'mmi_code' 등
    can_read BOOLEAN DEFAULT true,       -- 조회 권한
    can_write BOOLEAN DEFAULT false,     -- 수정 권한
    can_delete BOOLEAN DEFAULT false,    -- 삭제 권한
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(department_id, resource_type)
);

-- 권한 테이블 인덱스
CREATE INDEX IF NOT EXISTS idx_dept_permissions_dept_id ON public.department_permissions(department_id);

-- 부서별 기본 권한 설정 (모든 부서에 조회 권한 부여)
INSERT INTO public.department_permissions (department_id, resource_type, can_read, can_write, can_delete)
SELECT d.id, 'fault_code', true, false, false
FROM public.departments d
ON CONFLICT (department_id, resource_type) DO NOTHING;

INSERT INTO public.department_permissions (department_id, resource_type, can_read, can_write, can_delete)
SELECT d.id, 'signal', true, false, false
FROM public.departments d
ON CONFLICT (department_id, resource_type) DO NOTHING;

INSERT INTO public.department_permissions (department_id, resource_type, can_read, can_write, can_delete)
SELECT d.id, 'mmi_code', true, false, false
FROM public.departments d
ON CONFLICT (department_id, resource_type) DO NOTHING;

-- 4. 생체인증 credential 테이블 (biometric_credentials)
-- WebAuthn/생체인증을 위한 credential 저장
CREATE TABLE IF NOT EXISTS public.biometric_credentials (
    id SERIAL PRIMARY KEY,
    employee_id VARCHAR(50) NOT NULL REFERENCES public.users(employee_id) ON DELETE CASCADE,
    credential_id VARCHAR(255) UNIQUE NOT NULL,  -- WebAuthn credential ID
    credential_data JSONB NOT NULL,              -- Credential 전체 데이터
    created_at TIMESTAMPTZ DEFAULT NOW(),
    last_used_at TIMESTAMPTZ,
    UNIQUE(employee_id, credential_id)
);

-- 생체인증 인덱스
CREATE INDEX IF NOT EXISTS idx_biometric_employee_id ON public.biometric_credentials(employee_id);
CREATE INDEX IF NOT EXISTS idx_biometric_credential_id ON public.biometric_credentials(credential_id);

-- 5. 감사 로그 테이블 (audit_logs) - 선택사항
-- 사용자의 중요한 작업을 기록하기 위한 테이블
CREATE TABLE IF NOT EXISTS public.audit_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES public.users(id) ON DELETE SET NULL,
    action VARCHAR(50) NOT NULL,        -- 'login', 'logout', 'create', 'update', 'delete'
    resource_type VARCHAR(50),          -- 'fault_code', 'user', 'department' 등
    resource_id VARCHAR(100),           -- 대상 리소스의 ID
    details JSONB,                      -- 추가 상세 정보
    ip_address VARCHAR(45),             -- 사용자 IP 주소
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 감사 로그 인덱스
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON public.audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_action ON public.audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON public.audit_logs(created_at);

-- 5. 트리거: updated_at 자동 업데이트
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- departments 테이블에 트리거 적용
DROP TRIGGER IF EXISTS update_departments_updated_at ON public.departments;
CREATE TRIGGER update_departments_updated_at
    BEFORE UPDATE ON public.departments
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- users 테이블에 트리거 적용
DROP TRIGGER IF EXISTS update_users_updated_at ON public.users;
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON public.users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- department_permissions 테이블에 트리거 적용
DROP TRIGGER IF EXISTS update_dept_permissions_updated_at ON public.department_permissions;
CREATE TRIGGER update_dept_permissions_updated_at
    BEFORE UPDATE ON public.department_permissions
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 6. Row Level Security (RLS) 정책 - 선택사항
-- Supabase에서 RLS를 활성화하면 부서별 데이터 접근을 자동으로 제어할 수 있습니다

-- RLS 활성화
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.departments ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.department_permissions ENABLE ROW LEVEL SECURITY;

-- 부서 테이블: 모든 인증된 사용자가 조회 가능
CREATE POLICY "부서 목록은 모든 사용자가 조회 가능" ON public.departments
    FOR SELECT
    TO authenticated
    USING (is_active = true);

-- 사용자 테이블: 자기 자신의 정보만 조회 가능
CREATE POLICY "사용자는 본인 정보만 조회 가능" ON public.users
    FOR SELECT
    TO authenticated
    USING (auth.uid() = auth_id);

-- 사용자 테이블: 자기 자신의 정보만 업데이트 가능 (특정 필드만)
CREATE POLICY "사용자는 본인 정보만 수정 가능" ON public.users
    FOR UPDATE
    TO authenticated
    USING (auth.uid() = auth_id)
    WITH CHECK (auth.uid() = auth_id);

-- 7. 뷰 생성: 사용자 정보와 부서 정보를 조인한 뷰
CREATE OR REPLACE VIEW public.users_with_department AS
SELECT 
    u.id,
    u.auth_id,
    u.email,
    u.employee_id,
    u.name,
    u.department_id,
    d.code as department_code,
    d.name as department_name,
    u.is_active,
    u.last_login_at,
    u.created_at,
    u.updated_at
FROM public.users u
LEFT JOIN public.departments d ON u.department_id = d.id;

-- =====================================================
-- 사용 예시 및 참고사항
-- =====================================================

-- 1. 새로운 부서 추가
-- INSERT INTO public.departments (code, name, description) 
-- VALUES ('NEW001', '새로운부서', '부서 설명');

-- 2. 사용자 조회 (부서 정보 포함)
-- SELECT * FROM public.users_with_department WHERE email = 'user@humetro.busan.kr';

-- 3. 부서별 사용자 수 조회
-- SELECT d.name, COUNT(u.id) as user_count
-- FROM public.departments d
-- LEFT JOIN public.users u ON d.id = u.department_id
-- GROUP BY d.id, d.name
-- ORDER BY user_count DESC;

-- 4. 특정 부서의 권한 조회
-- SELECT d.name, dp.resource_type, dp.can_read, dp.can_write, dp.can_delete
-- FROM public.department_permissions dp
-- JOIN public.departments d ON dp.department_id = d.id
-- WHERE d.code = 'SPC';

-- =====================================================
-- 마이그레이션 롤백 (필요시)
-- =====================================================

-- DROP VIEW IF EXISTS public.users_with_department;
-- DROP TABLE IF EXISTS public.audit_logs CASCADE;
-- DROP TABLE IF EXISTS public.department_permissions CASCADE;
-- DROP TABLE IF EXISTS public.users CASCADE;
-- DROP TABLE IF EXISTS public.departments CASCADE;
-- DROP FUNCTION IF EXISTS update_updated_at_column() CASCADE;