CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS devices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    device_code VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(128) NOT NULL,
    type VARCHAR(64) NOT NULL,
    user_agent TEXT,
    network_group_key VARCHAR(128),
    public_ip_hash VARCHAR(128),
    is_online BOOLEAN NOT NULL DEFAULT FALSE,
    last_seen_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS rooms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(4) NOT NULL UNIQUE,
    creator_device_id UUID NOT NULL REFERENCES devices(id),
    joiner_device_id UUID REFERENCES devices(id),
    status VARCHAR(32) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    initiator_device_id UUID NOT NULL REFERENCES devices(id),
    target_device_id UUID NOT NULL REFERENCES devices(id),
    room_id UUID REFERENCES rooms(id),
    connect_mode VARCHAR(32) NOT NULL DEFAULT 'p2p',
    status VARCHAR(32) NOT NULL DEFAULT 'connecting',
    fail_reason TEXT,
    connected_at TIMESTAMPTZ,
    closed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS transfers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID REFERENCES sessions(id),
    kind VARCHAR(16) NOT NULL,
    name VARCHAR(255) NOT NULL,
    content TEXT,
    size_bytes BIGINT NOT NULL DEFAULT 0,
    sender_device_id UUID NOT NULL REFERENCES devices(id),
    receiver_device_id UUID NOT NULL REFERENCES devices(id),
    transfer_strategy VARCHAR(32) NOT NULL,
    current_channel VARCHAR(32) NOT NULL DEFAULT 'p2p',
    fallback_allowed BOOLEAN NOT NULL DEFAULT FALSE,
    final_status VARCHAR(32) NOT NULL DEFAULT 'pending',
    fallback_reason TEXT,
    object_key TEXT,
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS fallback_objects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transfer_id UUID NOT NULL UNIQUE REFERENCES transfers(id) ON DELETE CASCADE,
    bucket VARCHAR(128) NOT NULL,
    object_key TEXT NOT NULL UNIQUE,
    size_bytes BIGINT NOT NULL DEFAULT 0,
    cleanup_state VARCHAR(32) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    cleaned_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS admin_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(64) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS system_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    config_key VARCHAR(128) NOT NULL UNIQUE,
    config_value JSONB NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    operator_type VARCHAR(32) NOT NULL,
    operator_id UUID,
    action VARCHAR(128) NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_devices_online ON devices (is_online, last_seen_at DESC);
CREATE INDEX IF NOT EXISTS idx_devices_network_group ON devices (network_group_key);
CREATE INDEX IF NOT EXISTS idx_rooms_status_expires ON rooms (status, expires_at DESC);
CREATE INDEX IF NOT EXISTS idx_sessions_status_created ON sessions (status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_transfers_status_created ON transfers (final_status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_transfers_sender_receiver ON transfers (sender_device_id, receiver_device_id);
CREATE INDEX IF NOT EXISTS idx_fallback_objects_expires ON fallback_objects (cleanup_state, expires_at ASC);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created ON audit_logs (created_at DESC);

INSERT INTO system_configs (config_key, config_value)
VALUES
    ('transfer_policy', '{"max_minio_fallback_size_bytes":10737418240,"minio_capacity_bytes":128849018880,"minio_retention_hours":2,"p2p_connect_timeout_sec":15,"turn_connect_timeout_sec":20,"minio_usage_alert_percent":85,"minio_fallback_enabled":true,"turn_urls":[],"turn_username":"","turn_password":""}'::jsonb)
ON CONFLICT (config_key) DO NOTHING;