-- Setup script for separate authentication database (punimtag_auth)
-- This database stores users and pending_identifications separately from the read-only punimtag database
-- Run this script as a PostgreSQL superuser (e.g., postgres user)

-- Step 1: Create the database (if it doesn't exist)
-- Note: This must be run as a superuser
CREATE DATABASE punimtag_auth;

-- Step 2: Connect to the new database
\c punimtag_auth

-- Step 3: 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 4: Create pending_identifications table
-- Note: face_id references faces in the punimtag database, but we can't use a foreign key
-- across databases. The application will validate that faces exist.
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
);

-- Step 5: 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 6: Create a database user for the application (optional, if you want a separate user)
-- Replace 'your_secure_password' with a strong password
-- CREATE USER punimtag_auth_user WITH PASSWORD 'your_secure_password';

-- Step 7: Grant permissions to the database user
-- If you created a separate user above, uncomment and adjust:
-- GRANT CONNECT ON DATABASE punimtag_auth TO punimtag_auth_user;
-- GRANT USAGE ON SCHEMA public TO punimtag_auth_user;
-- GRANT SELECT, INSERT, UPDATE ON TABLE users TO punimtag_auth_user;
-- GRANT SELECT, INSERT, UPDATE ON TABLE pending_identifications TO punimtag_auth_user;
-- GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO punimtag_auth_user;
-- GRANT USAGE, SELECT ON SEQUENCE pending_identifications_id_seq TO punimtag_auth_user;

-- Or if you want to use an existing user (e.g., postgres or your current user):
-- GRANT ALL PRIVILEGES ON DATABASE punimtag_auth TO your_existing_user;
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_existing_user;
-- GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_existing_user;

\echo '✅ Auth database setup complete!'
\echo ''
\echo 'Next steps:'
\echo '1. Add DATABASE_URL_AUTH to your .env file:'
\echo '   DATABASE_URL_AUTH="postgresql://username:password@localhost:5432/punimtag_auth"'
\echo '2. Generate Prisma client for auth: npx prisma generate --schema=prisma/schema-auth.prisma'
\echo '3. Create admin user: npx tsx scripts/create-admin-user.ts'