-- Create tables for authentication and pending identifications
-- Run this script as a PostgreSQL superuser (e.g., postgres user)
-- Make sure you're connected to the punimtag database

-- 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_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 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)
);

-- 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);

-- Grant permissions to write user (if using separate write user)
-- Uncomment and adjust if using viewer_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;

-- Or grant to existing user (if using viewer_readonly with 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;

-- Display success message
\echo 'Authentication tables created successfully!'
\echo 'Remember to grant appropriate permissions to your database user.'


