@startuml Music Aggregator ERD

skinparam linetype ortho
skinparam ranksep 60
skinparam nodesep 40

skinparam entity {
    BackgroundColor White
    BorderColor #333333
}

skinparam package {
    BackgroundColor #FAFAFA
    BorderColor #DDDDDD
}

title Music Aggregator - Database Structure

package "Music Metadata" #E3F2FD {
    entity "artists" {
        * id : UUID <<PK>>
        --
        external_id : VARCHAR(255) <<UNIQUE>>
        name : VARCHAR(500)
        artist_type : VARCHAR(50)
        country : VARCHAR(10)
        genres : TEXT[]
        image_url : TEXT
        --
        created_at : TIMESTAMPTZ
        updated_at : TIMESTAMPTZ
    }

    entity "albums" {
        * id : UUID <<PK>>
        --
        external_id : VARCHAR(255) <<UNIQUE>>
        artist_id : UUID <<FK>>
        --
        title : VARCHAR(500)
        album_type : VARCHAR(50)
        release_date : DATE
        total_tracks : INT
        total_discs : INT
        label : VARCHAR(255)
        genres : TEXT[]
        cover_url : TEXT
        is_monitored : BOOLEAN
        --
        created_at : TIMESTAMPTZ
        updated_at : TIMESTAMPTZ
    }

    entity "tracks" {
        * id : UUID <<PK>>
        --
        external_id : VARCHAR(255) <<UNIQUE>>
        album_id : UUID <<FK>>
        --
        title : VARCHAR(500)
        duration_ms : INT
        isrc : VARCHAR(20)
        disc_number : INT
        track_number : INT
        --
        created_at : TIMESTAMPTZ
    }
}

package "Torrent Catalog" #FFF3E0 {
    entity "torrents" {
        * id : UUID <<PK>>
        --
        album_id : UUID <<FK>>
        info_hash : VARCHAR(40) <<UNIQUE>>
        --
        tracker : VARCHAR(100)
        title : TEXT
        format : VARCHAR(20)
        quality : VARCHAR(20)
        source : VARCHAR(20)
        bit_depth : INT
        sample_rate : INT
        seeders : INT
        peers : INT
        size : BIGINT
        track_count : INT
        has_cover_art : BOOLEAN
        has_cue_sheet : BOOLEAN
        has_rip_log : BOOLEAN
        download_link : TEXT
        torrent_file : BYTEA
        --
        created_at : TIMESTAMPTZ
        updated_at : TIMESTAMPTZ
    }
}

package "Download Management" #E8F5E9 {
    entity "downloads" {
        * id : UUID <<PK>>
        --
        torrent_id : UUID <<FK>>
        album_id : UUID
        format : VARCHAR(20)
        quality : VARCHAR(20)
        --
        state : download_state
        qbit_hash : VARCHAR(64)
        save_path : TEXT
        error_message : TEXT
        --
        queued_at : TIMESTAMPTZ
        started_at : TIMESTAMPTZ
        completed_at : TIMESTAMPTZ
        created_at : TIMESTAMPTZ
        updated_at : TIMESTAMPTZ
    }

    entity "download_files" {
        * id : UUID <<PK>>
        --
        download_id : UUID <<FK>>
        track_id : UUID <<FK NULL>>
        --
        file_path : TEXT
        file_size : BIGINT
        file_type : VARCHAR(20)
        sha256_hash : VARCHAR(64)
        --
        verified_at : TIMESTAMPTZ
        created_at : TIMESTAMPTZ
    }
}

package "Caching & Queue (River)" #F3E5F5 {
    entity "river_job" {
        * id : BIGSERIAL <<PK>>
        --
        kind : TEXT
        state : river_job_state
        queue : TEXT
        args : JSONB
        metadata : JSONB
        --
        attempt : SMALLINT
        max_attempts : SMALLINT
        priority : SMALLINT
        --
        scheduled_at : TIMESTAMPTZ
        attempted_at : TIMESTAMPTZ
        created_at : TIMESTAMPTZ
        finalized_at : TIMESTAMPTZ
    }

    entity "river_queue" {
        * name : TEXT <<PK>>
        --
        metadata : JSONB
        paused_at : TIMESTAMPTZ
        created_at : TIMESTAMPTZ
        updated_at : TIMESTAMPTZ
    }
}

note right of river_job
  Cache refresh jobs:
  kind = "indexer_cache_refresh"
  args = {key, url, ttl_expires, refresh_interval}
  scheduled_at = next refresh time
end note

artists ||--o{ albums : "released"
albums ||--o{ tracks : "contains"
albums ||--o{ torrents : "available on"
torrents ||--o| downloads : "downloaded as"
downloads ||--o{ download_files : "consists of"
tracks ||--o| download_files : "matched to"

@enduml