-- Pinnacle Decision Portal PostgreSQL Schema
-- Automatically executed when Coolify provisions the PostgreSQL container

CREATE TABLE IF NOT EXISTS pinnacle_store (
    id VARCHAR(50) PRIMARY KEY DEFAULT 'default',
    data JSONB NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_pinnacle_store_updated ON pinnacle_store(updated_at);

-- User Management & Access Control Table
CREATE TABLE IF NOT EXISTS pinnacle_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'viewer',
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    dept VARCHAR(100) NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_pinnacle_users_username ON pinnacle_users(username);