-- Complete setup script for authentication
-- Run this as PostgreSQL superuser (e.g., postgres user)
-- Make sure you're connected to the punimtag database

-- Step 1: Create users table
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    password_hash VARCHAR(255) NOT NULL,
    is_admin BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Create pending_identifications table
CREATE TABLE IF NOT EXISTS pending_identifications (
    id SERIAL PRIMARY KEY,
    face_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    middle_name VARCHAR(255),
    maiden_name VARCHAR(255),
    date_of_birth DATE,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (face_id) REFERENCES faces(id)
);

-- Step 3: Create indexes
CREATE INDEX IF NOT EXISTS idx_pending_identifications_face_id ON pending_identifications(face_id);
CREATE INDEX IF NOT EXISTS idx_pending_identifications_user_id ON pending_identifications(user_id);
CREATE INDEX IF NOT EXISTS idx_pending_identifications_status ON pending_identifications(status);

-- Step 4: Create admin user (password: admin, hashed with bcrypt)
-- The hash for 'admin' password is: $2a$10$rOzJ8Z8Z8Z8Z8Z8Z8Z8Z8eZ8Z8Z8Z8Z8Z8Z8Z8Z8Z8Z8Z8Z8Z8Z8
-- We'll use a proper bcrypt hash - this is a placeholder that needs to be generated
-- For now, we'll insert and you can update the password hash after running the Node script

-- Step 5: Grant permissions
-- Regular users: INSERT only on pending_identifications
-- Admin users: UPDATE on pending_identifications (for approval)

-- Grant INSERT to all authenticated users (they can create pending identifications)
-- Note: In PostgreSQL, we can't easily restrict UPDATE per user without row-level security
-- For now, we'll grant UPDATE to the write user, and handle admin checks in application code

-- If using viewer_write user:
DO $$
BEGIN
    IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'viewer_write') THEN
        -- Grant permissions to write user
        GRANT SELECT, INSERT, UPDATE ON TABLE users TO viewer_write;
        GRANT SELECT, INSERT, UPDATE ON TABLE pending_identifications TO viewer_write;
        GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO viewer_write;
        GRANT USAGE, SELECT ON SEQUENCE pending_identifications_id_seq TO viewer_write;
        RAISE NOTICE 'Permissions granted to viewer_write';
    END IF;
END $$;

-- If using viewer_readonly with write permissions:
DO $$
BEGIN
    IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'viewer_readonly') THEN
        -- Grant permissions to readonly user (if it has write permissions)
        GRANT SELECT, INSERT, UPDATE ON TABLE users TO viewer_readonly;
        GRANT SELECT, INSERT, UPDATE ON TABLE pending_identifications TO viewer_readonly;
        GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO viewer_readonly;
        GRANT USAGE, SELECT ON SEQUENCE pending_identifications_id_seq TO viewer_readonly;
        RAISE NOTICE 'Permissions granted to viewer_readonly';
    END IF;
END $$;

-- Display success message
\echo '✅ Tables created successfully!'
\echo '⚠️  Next step: Run the Node.js script to create admin user with proper password hash:'
\echo '   npx tsx scripts/create-admin-user.ts'