OrchestrAD/backend/internal/db/migrations/001_initial_schema.up.sql

-- OrchestrAD Initial Schema Migration
-- All PKs are UUIDv4, all timestamps are UTC

-- Users table for local authentication
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT UNIQUE,
    password_hash TEXT,
    display_name TEXT,
    is_active INTEGER NOT NULL DEFAULT 1,
    is_oidc_user INTEGER NOT NULL DEFAULT 0,
    oidc_provider_id TEXT,
    oidc_subject TEXT,
    last_login_utc TEXT,
    created_utc TEXT NOT NULL,
    updated_utc TEXT NOT NULL,
    deleted_utc TEXT
);

-- Roles table for RBAC
CREATE TABLE IF NOT EXISTS roles (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    is_system_role INTEGER NOT NULL DEFAULT 0,
    created_utc TEXT NOT NULL,
    updated_utc TEXT NOT NULL
);

-- User-Role mapping
CREATE TABLE IF NOT EXISTS user_roles (
    user_id TEXT NOT NULL,
    role_id TEXT NOT NULL,
    created_utc TEXT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

-- Sessions/Refresh tokens
CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    token_hash TEXT NOT NULL UNIQUE,
    expires_utc TEXT NOT NULL,
    created_utc TEXT NOT NULL,
    revoked_utc TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- API Keys (separate from interactive auth)
CREATE TABLE IF NOT EXISTS api_keys (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    name TEXT NOT NULL,
    key_prefix TEXT NOT NULL,
    key_hash TEXT NOT NULL UNIQUE,
    expires_utc TEXT,
    is_enabled INTEGER NOT NULL DEFAULT 1,
    last_used_utc TEXT,
    created_utc TEXT NOT NULL,
    revoked_utc TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- OIDC Providers
CREATE TABLE IF NOT EXISTS oidc_providers (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL,
    issuer_url TEXT NOT NULL,
    client_id TEXT NOT NULL,
    client_secret_encrypted TEXT NOT NULL,
    scopes TEXT NOT NULL DEFAULT 'openid profile email',
    is_enabled INTEGER NOT NULL DEFAULT 1,
    auto_create_users INTEGER NOT NULL DEFAULT 1,
    default_role_id TEXT,
    created_utc TEXT NOT NULL,
    updated_utc TEXT NOT NULL,
    deleted_utc TEXT,
    FOREIGN KEY (default_role_id) REFERENCES roles(id)
);

-- Credentials (reusable, encrypted)
CREATE TABLE IF NOT EXISTS credentials (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    credential_type TEXT NOT NULL,
    username TEXT,
    encrypted_secret TEXT,
    is_enabled INTEGER NOT NULL DEFAULT 1,
    last_tested_utc TEXT,
    last_test_result TEXT,
    created_utc TEXT NOT NULL,
    updated_utc TEXT NOT NULL,
    deleted_utc TEXT
);

-- AD Connections
CREATE TABLE IF NOT EXISTS ad_connections (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    is_enabled INTEGER NOT NULL DEFAULT 1,
    hosts TEXT NOT NULL,
    port INTEGER NOT NULL DEFAULT 389,
    use_tls INTEGER NOT NULL DEFAULT 0,
    use_start_tls INTEGER NOT NULL DEFAULT 0,
    allow_invalid_certs INTEGER NOT NULL DEFAULT 0,
    root_dn TEXT NOT NULL,
    bind_dn TEXT,
    credential_id TEXT,
    default_search_scope TEXT NOT NULL DEFAULT 'Subtree',
    timeout_seconds INTEGER NOT NULL DEFAULT 30,
    paging_enabled INTEGER NOT NULL DEFAULT 1,
    page_size INTEGER NOT NULL DEFAULT 1000,
    last_tested_utc TEXT,
    last_test_result TEXT,
    created_utc TEXT NOT NULL,
    updated_utc TEXT NOT NULL,
    deleted_utc TEXT,
    FOREIGN KEY (credential_id) REFERENCES credentials(id)
);

-- Schedules (reusable)
CREATE TABLE IF NOT EXISTS schedules (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    is_enabled INTEGER NOT NULL DEFAULT 1,
    schedule_kind TEXT NOT NULL,
    easy_interval_value INTEGER,
    easy_interval_unit TEXT,
    cron_expression TEXT,
    timezone_mode TEXT NOT NULL DEFAULT 'UTC',
    next_run_utc TEXT,
    created_utc TEXT NOT NULL,
    updated_utc TEXT NOT NULL,
    deleted_utc TEXT
);

-- Insert default system roles
INSERT INTO roles (id, name, description, is_system_role, created_utc, updated_utc) VALUES
    ('00000000-0000-0000-0000-000000000001', 'SuperAdmin', 'Full system access', 1, datetime('now'), datetime('now')),
    ('00000000-0000-0000-0000-000000000002', 'Admin', 'Administrative access', 1, datetime('now'), datetime('now')),
    ('00000000-0000-0000-0000-000000000003', 'Operator', 'Operational access', 1, datetime('now'), datetime('now')),
    ('00000000-0000-0000-0000-000000000004', 'Viewer', 'Read-only access', 1, datetime('now'), datetime('now'));