erDiagram
    auth_users ||--|| Users : "trigger SQL"
    Users ||--o{ Profiles : "possède (1:N)"
    Users ||--o{ UserDevices : "utilise (1:N)"
    Users ||--o{ OTP : "reçoit (1:N)"
    Users ||--o| Admin : "joue rôle (optionnel)"

    auth_users {
        uuid id PK
        string email
        jsonb raw_user_meta_data
    }
    Users {
        uuid id PK,FK
        string username UK
        string email UK
        string phoneNumber UK
        timestamptz trialStartDate
        timestamptz trialEndDate
        timestamptz lastLoginDate
        string lastLoginIp
        string status
        string role
    }
    Profiles {
        bigint id PK
        uuid userID FK
        string profileName
        string profileType
        jsonb preferences
        bool isDefault
        bool isActive
    }
    UserDevices {
        bigint id PK
        uuid userId FK
        string deviceId
        string deviceName
        string deviceType
        string ipAddress
        bool isConnected
        timestamptz lastActive
    }
    OTP {
        bigint id PK
        uuid userId FK
        string code
        string purpose
        timestamptz expiresAt
        bool isUsed
    }
    Admin {
        bigint id PK
        uuid userId FK,UK
        string role
    }
                        

erDiagram
    Users ||--o{ Subscriptions : "souscrit (1:N)"
    Users ||--o{ Payments : "paie (1:N)"
    Plans ||--o{ Subscriptions : "définit (1:N)"
    Payments ||--o| Subscriptions : "finance (optionnel)"

    Users {
        uuid id PK
        string username
        string email
    }
    Plans {
        bigint id PK
        string name UK
        numeric price
        int duration
        int maxProfiles
        int maxDevices
        int maxConcurrentStreams
        bool hasMovies
        bool hasIPTV
        bool allowDownloads
        bool hasAds
    }
    Subscriptions {
        bigint id PK
        uuid userId FK
        bigint planId FK
        bigint paymentId FK
        bool autoRenew
        string status
        timestamptz startDate
        timestamptz endDate
    }
    Payments {
        bigint id PK
        uuid userId FK
        numeric amount
        string status
        string paymentMethod
        text transactionId
        timestamptz transactionDate
    }
                        

erDiagram
    TVShows ||--o{ Seasons : "contient (1:N)"
    Seasons ||--o{ Episodes : "compose (1:N)"

    Movies {
        bigint id PK
        string tmdbId UK
        string title
        text overview
        text posterPath
        text backdropPath
        int year
        numeric rating
        string quality
        text videoPath
        bool isAvailable
    }
    TVShows {
        bigint id PK
        string tmdbId UK
        string title
        text overview
        text posterPath
        text backdropPath
        int year
        numeric rating
        bool isAvailable
    }
    Seasons {
        bigint id PK
        bigint showId FK
        int seasonNumber
        string title
        text overview
        text posterPath
    }
    Episodes {
        bigint id PK
        bigint seasonId FK
        int episodeNumber
        string title
        text overview
        text stillPath
        text videoPath
        bool isAvailable
    }
                        

erDiagram
    Users ||--o{ MovieWatchHistory : "regarde N:N"
    Users ||--o{ EpisodeWatchHistory : "regarde N:N"
    Profiles ||--o{ MovieWatchHistory : "par profil"
    Profiles ||--o{ EpisodeWatchHistory : "par profil"
    Movies ||--o{ MovieWatchHistory : ""
    Episodes ||--o{ EpisodeWatchHistory : ""
    Users ||--o{ FavoritesMovies : "favoris N:N"
    Movies ||--o{ FavoritesMovies : ""
    Users ||--o{ FavoritesShows : "favoris N:N"
    TVShows ||--o{ FavoritesShows : ""
    Users ||--o{ FavoritesChannels : "favoris N:N"
    IPTVChannels ||--o{ FavoritesChannels : ""
    Users ||--o{ MovieComments : "commente N:N"
    Movies ||--o{ MovieComments : ""
    Users ||--o{ ShowComments : "commente N:N"
    TVShows ||--o{ ShowComments : ""

    MovieWatchHistory {
        bigint id PK
        uuid userId FK
        bigint profileId FK
        bigint movieId FK
        int lastWatchedPosition
        bool isCompleted
        timestamptz lastWatchedAt
    }
    EpisodeWatchHistory {
        bigint id PK
        uuid userId FK
        bigint profileId FK
        bigint episodeId FK
        int lastWatchedPosition
        bool isCompleted
        timestamptz lastWatchedAt
    }
    FavoritesMovies {
        bigint id PK
        uuid userId FK
        bigint movieId FK
    }
    FavoritesShows {
        bigint id PK
        uuid userId FK
        bigint showId FK
    }
    FavoritesChannels {
        bigint id PK
        uuid userId FK
        bigint channelId FK
    }
    MovieComments {
        bigint id PK
        uuid userId FK
        bigint movieId FK
        text comment
        smallint rating
    }
    ShowComments {
        bigint id PK
        uuid userId FK
        bigint showId FK
        text comment
        smallint rating
    }
                        

flowchart LR
    U[Users]
    PR[Profiles]
    DEV[UserDevices]
    PL[Plans]
    SUB[Subscriptions]
    PAY[Payments]
    MO[Movies]
    TV[TVShows]
    SE[Seasons]
    EP[Episodes]
    IPTV[IPTVChannels]

    A1{possède}
    A2{utilise}
    A3{souscrit}
    A4{définit}
    A5{paie}
    A6{contient}
    A7{compose}
    A8{regarde film}
    A9{regarde épisode}
    A10{favori film}
    A11{favori série}
    A12{favori chaîne}

    U ---|"(1,1)"| A1
    A1 ---|"(0,N)"| PR
    U ---|"(1,1)"| A2
    A2 ---|"(0,N)"| DEV
    U ---|"(1,1)"| A3
    A3 ---|"(0,N)"| SUB
    PL ---|"(1,1)"| A4
    A4 ---|"(0,N)"| SUB
    U ---|"(1,1)"| A5
    A5 ---|"(0,N)"| PAY
    TV ---|"(1,1)"| A6
    A6 ---|"(0,N)"| SE
    SE ---|"(1,1)"| A7
    A7 ---|"(0,N)"| EP
    U ---|"(0,N)"| A8
    A8 ---|"(0,N)"| MO
    U ---|"(0,N)"| A9
    A9 ---|"(0,N)"| EP
    U ---|"(0,N)"| A10
    A10 ---|"(0,N)"| MO
    U ---|"(0,N)"| A11
    A11 ---|"(0,N)"| TV
    U ---|"(0,N)"| A12
    A12 ---|"(0,N)"| IPTV

    classDef entity fill:#1f1f1f,stroke:#e50914,color:#fff,stroke-width:2px
    classDef assoc fill:#c0504d,stroke:#8c3a37,color:#fff
    class U,PR,DEV,PL,SUB,PAY,MO,TV,SE,EP,IPTV entity
    class A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12 assoc