-- ==============================================================================
-- UCall Database Schema (MySQL / MariaDB Compatible)
-- Migration 0001: Core Architecture & Identity
-- ==============================================================================

-- 1. Base Users Table
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(36) PRIMARY KEY,
    status SMALLINT NOT NULL DEFAULT 1,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Atomic Sequence Generator for 12-Digit Universal ID
CREATE TABLE IF NOT EXISTS ucall_sequences (
    name VARCHAR(64) PRIMARY KEY,
    current_val BIGINT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO ucall_sequences (name, current_val) VALUES ('universal_id', 100000);

-- 3. 12-Digit Numeric Universal ID Registry
CREATE TABLE IF NOT EXISTS universal_ids (
    numeric_id BIGINT PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL UNIQUE,
    allocated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    CONSTRAINT fk_universal_ids_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_universal_ids_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. User Credentials (Phone, Email, OAuth Idempotent Identity)
CREATE TABLE IF NOT EXISTS user_credentials (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    cred_type VARCHAR(32) NOT NULL DEFAULT 'phone',
    identifier VARCHAR(256) NOT NULL,
    display_name VARCHAR(128),
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_credential (cred_type, identifier),
    INDEX idx_user_credentials_user_id (user_id),
    CONSTRAINT fk_credentials_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Developer Organizations
CREATE TABLE IF NOT EXISTS organizations (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    billing_email VARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Applications Registry
CREATE TABLE IF NOT EXISTS applications (
    id VARCHAR(36) PRIMARY KEY,
    org_id VARCHAR(36) NOT NULL,
    app_id VARCHAR(32) UNIQUE NOT NULL,
    app_name VARCHAR(255) NOT NULL,
    client_id VARCHAR(64) UNIQUE NOT NULL,
    client_secret_hash VARCHAR(255) NOT NULL,
    sdk_key_hash VARCHAR(255) NOT NULL,
    webhook_url VARCHAR(1024),
    webhook_secret VARCHAR(255),
    platforms JSON NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    CONSTRAINT fk_applications_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Application to User Identity Mapping
CREATE TABLE IF NOT EXISTS application_users (
    id VARCHAR(36) PRIMARY KEY,
    app_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) NOT NULL,
    local_user_id VARCHAR(255) NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_app_user (app_id, user_id),
    UNIQUE KEY uq_app_local_user (app_id, local_user_id),
    CONSTRAINT fk_app_users_app FOREIGN KEY (app_id) REFERENCES applications(id) ON DELETE CASCADE,
    CONSTRAINT fk_app_users_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Physical Hardware Devices
CREATE TABLE IF NOT EXISTS devices (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    hardware_id VARCHAR(255) NOT NULL,
    platform VARCHAR(32) NOT NULL,
    model VARCHAR(255),
    os_version VARCHAR(64),
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_user_device (user_id, hardware_id),
    CONSTRAINT fk_devices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Communication Endpoints
CREATE TABLE IF NOT EXISTS endpoints (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    app_id VARCHAR(36) NOT NULL,
    device_id VARCHAR(36) NOT NULL,
    push_token TEXT,
    push_provider VARCHAR(32),
    status VARCHAR(32) NOT NULL DEFAULT 'offline',
    last_seen_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_app_device (app_id, device_id),
    INDEX idx_endpoints_routing (user_id, status, last_seen_at),
    CONSTRAINT fk_endpoints_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_endpoints_app FOREIGN KEY (app_id) REFERENCES applications(id) ON DELETE CASCADE,
    CONSTRAINT fk_endpoints_device FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Call Sessions
CREATE TABLE IF NOT EXISTS call_sessions (
    id VARCHAR(36) PRIMARY KEY,
    caller_user_id VARCHAR(36) NOT NULL,
    callee_user_id VARCHAR(36) NOT NULL,
    target_endpoint_id VARCHAR(36),
    call_type VARCHAR(16) NOT NULL,
    status VARCHAR(32) NOT NULL,
    started_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    connected_at DATETIME(6),
    ended_at DATETIME(6),
    duration_seconds INT DEFAULT 0,
    end_reason VARCHAR(64),
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_call_sessions_users (caller_user_id, callee_user_id, started_at),
    CONSTRAINT fk_calls_caller FOREIGN KEY (caller_user_id) REFERENCES users(id),
    CONSTRAINT fk_calls_callee FOREIGN KEY (callee_user_id) REFERENCES users(id),
    CONSTRAINT fk_calls_endpoint FOREIGN KEY (target_endpoint_id) REFERENCES endpoints(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Direct Messages
CREATE TABLE IF NOT EXISTS messages (
    id VARCHAR(36) PRIMARY KEY,
    sender_user_id VARCHAR(36) NOT NULL,
    recipient_user_id VARCHAR(36) NOT NULL,
    app_id VARCHAR(36) NOT NULL,
    message_type VARCHAR(32) NOT NULL DEFAULT 'text',
    payload TEXT NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'accepted',
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    delivered_at DATETIME(6),
    read_at DATETIME(6),
    INDEX idx_messages_recipient_status (recipient_user_id, status),
    CONSTRAINT fk_messages_sender FOREIGN KEY (sender_user_id) REFERENCES users(id),
    CONSTRAINT fk_messages_recipient FOREIGN KEY (recipient_user_id) REFERENCES users(id),
    CONSTRAINT fk_messages_app FOREIGN KEY (app_id) REFERENCES applications(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Audit Logging Ledger
CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(64) NOT NULL,
    actor_id VARCHAR(36),
    actor_type VARCHAR(32) NOT NULL,
    target_id VARCHAR(36),
    ip_address VARCHAR(45),
    metadata JSON NOT NULL,
    created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_audit_logs_event_created (event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;