
    q[j7                        d dl mZ d dlZd dlZd dlmZ d dlmZ d dlm	Z	 ddl
mZmZmZ ddlmZ  G d	 d
          ZdS )    )annotationsN)Path)Any)uuid4   )NormalizedEpisodeNormalizedMovieNormalizedServer)strip_season_from_titlec                  f    e Zd Zd#dZd$dZd$dZd%dZd&dZd'dZd(dZ		 d)d*dZ
	 d+d,d Zd-d"ZdS ).MovieDatabasedb_pathstrc                    t          |          j                            dd           t          j        |          | _        t          j        | j        _        |                                  d S )NT)parentsexist_ok)	r   parentmkdirsqlite3connectconnRowrow_factoryinit_schema)selfr   s     moviebot/database.py__init__zMovieDatabase.__init__   sY    W""4$"???OG,,	 '	    returnNonec                8    | j                                          d S N)r   closer   s    r   r#   zMovieDatabase.close   s    	r   c                l    | j                             d           | j                                          d S )Na  
            PRAGMA foreign_keys = ON;
            CREATE TABLE IF NOT EXISTS movies (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                merge_key TEXT NOT NULL UNIQUE,
                tmdb_id TEXT,
                media_type TEXT NOT NULL,
                title TEXT NOT NULL,
                original_title TEXT,
                year TEXT,
                imdb_id TEXT,
                poster_url TEXT,
                backdrop_url TEXT,
                runtime TEXT,
                content TEXT,
                genres_json TEXT,
                countries_json TEXT,
                actors_json TEXT,
                directors_json TEXT,
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
            CREATE TABLE IF NOT EXISTS movie_sources (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                movie_id INTEGER REFERENCES movies(id) ON DELETE CASCADE,
                source TEXT NOT NULL,
                source_slug TEXT NOT NULL,
                raw_json TEXT NOT NULL,
                crawl_status TEXT NOT NULL DEFAULT 'pending',
                retry_count INTEGER NOT NULL DEFAULT 0,
                last_error TEXT,
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(source, source_slug)
            );
            CREATE TABLE IF NOT EXISTS servers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                movie_id INTEGER NOT NULL REFERENCES movies(id) ON DELETE CASCADE,
                source TEXT NOT NULL,
                name TEXT NOT NULL,
                priority INTEGER NOT NULL DEFAULT 0,
                raw_json TEXT NOT NULL DEFAULT '{}',
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(movie_id, source, name)
            );
            CREATE TABLE IF NOT EXISTS episodes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                movie_id INTEGER NOT NULL REFERENCES movies(id) ON DELETE CASCADE,
                server_id INTEGER REFERENCES servers(id) ON DELETE SET NULL,
                source TEXT NOT NULL,
                source_episode_id TEXT,
                title TEXT,
                filename TEXT NOT NULL,
                season INTEGER NOT NULL DEFAULT 1,
                episode INTEGER NOT NULL DEFAULT 1,
                origin_m3u8_url TEXT NOT NULL,
                cleaned_m3u8_path TEXT,
                strm_path TEXT,
                access_key TEXT NOT NULL UNIQUE,
                crawl_status TEXT NOT NULL DEFAULT 'pending',
                retry_count INTEGER NOT NULL DEFAULT 0,
                last_error TEXT,
                raw_json TEXT NOT NULL DEFAULT '{}',
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(movie_id, source, filename, origin_m3u8_url)
            );
            CREATE INDEX IF NOT EXISTS idx_episodes_identity
                ON episodes(movie_id, source, filename, season, episode);
            )r   executescriptcommitr$   s    r   r   zMovieDatabase.init_schema   sB    	BD	
 D	
 D	
J 		r   movier	   intc                   |j         |j        |j        |j        |j        |j        |j        |j        |j        |j	        |j
        t          j        |j        d          t          j        |j        d          t          j        |j        d          t          j        |j        d          f}| j                            d|           | j                            d|j         f                                          }t)          |d                   }| j                            d|f                                          }|rEt+          |d         pd          }||d         k    r"| j                            d	|j        |f           | j                            d
||j        |j        t          j        |j        d          f           | j                                         |S )NFensure_asciia  
            INSERT INTO movies (
                merge_key, tmdb_id, media_type, title, original_title, year, imdb_id,
                poster_url, backdrop_url, runtime, content, genres_json, countries_json,
                actors_json, directors_json
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(merge_key) DO UPDATE SET
                title=CASE WHEN excluded.tmdb_id != '' THEN excluded.title ELSE COALESCE(NULLIF(movies.title, ''), excluded.title) END,
                original_title=CASE WHEN excluded.tmdb_id != '' THEN excluded.original_title ELSE COALESCE(NULLIF(movies.original_title, ''), excluded.original_title) END,
                year=CASE WHEN excluded.tmdb_id != '' THEN excluded.year ELSE COALESCE(NULLIF(movies.year, ''), excluded.year) END,
                imdb_id=COALESCE(NULLIF(excluded.imdb_id, ''), movies.imdb_id),
                poster_url=CASE WHEN excluded.tmdb_id != '' THEN excluded.poster_url ELSE COALESCE(NULLIF(movies.poster_url, ''), excluded.poster_url) END,
                backdrop_url=CASE WHEN excluded.tmdb_id != '' THEN excluded.backdrop_url ELSE COALESCE(NULLIF(movies.backdrop_url, ''), excluded.backdrop_url) END,
                runtime=CASE WHEN excluded.tmdb_id != '' THEN excluded.runtime ELSE COALESCE(NULLIF(movies.runtime, ''), excluded.runtime) END,
                content=CASE WHEN excluded.tmdb_id != '' THEN excluded.content ELSE COALESCE(NULLIF(movies.content, ''), excluded.content) END,
                genres_json=CASE WHEN excluded.tmdb_id != '' THEN excluded.genres_json ELSE COALESCE(NULLIF(movies.genres_json, '[]'), excluded.genres_json) END,
                countries_json=COALESCE(NULLIF(movies.countries_json, '[]'), excluded.countries_json),
                actors_json=COALESCE(NULLIF(movies.actors_json, '[]'), excluded.actors_json),
                directors_json=COALESCE(NULLIF(movies.directors_json, '[]'), excluded.directors_json),
                updated_at=CURRENT_TIMESTAMP
            z)SELECT id FROM movies WHERE merge_key = ?idz%SELECT title FROM movies WHERE id = ?title zHUPDATE movies SET title = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?a  
            INSERT INTO movie_sources (movie_id, source, source_slug, raw_json, crawl_status)
            VALUES (?, ?, ?, ?, 'success')
            ON CONFLICT(source, source_slug) DO UPDATE SET
                movie_id=excluded.movie_id,
                raw_json=excluded.raw_json,
                crawl_status='success',
                last_error=NULL,
                updated_at=CURRENT_TIMESTAMP
            )	merge_keytmdb_id
media_typer.   original_titleyearimdb_id
poster_urlbackdrop_urlruntimecontentjsondumpsgenres	countriesactors	directorsr   executefetchoner)   r   sourcesource_slugrawr'   )r   r(   payloadrowmovie_idcurrentclean_titles          r   upsert_moviezMovieDatabase.upsert_movie_   s   OMK JMMMJu|%888JuU;;;Ju|%888JuU;;;
" 		* -	
 	
 	
0 i KeoM_``iikks4y>>)##$Kh[YYbbdd 	G1''2B2HbIIKgg...	!!"lotoz  }E  oF  G  G  G		 u|U%6
59[`8a8a8ab	
 	
 	
 		r   rG   c                   | j                             d|f                                          }|s|S |d         p|j        |_        |d         p|j        |_        |d         p|j        |_        |d         p|j        |_        |d         p|j        |_        |d         p|j        |_        |d         p|j	        |_	        |d	         p|j
        |_
        |d
         p|j        |_        |d         p|j        |_        t          j        |d         pd          |_        t          j        |d         pd          |_        t          j        |d         pd          |_        t          j        |d         pd          |_        |S )Nz!SELECT * FROM movies WHERE id = ?r.   r3   r4   r2   r1   r5   r6   r7   r8   r9   genres_jsonz[]countries_jsonactors_jsondirectors_json)r   r@   rA   r.   r3   r4   r2   r1   r5   r6   r7   r8   r9   r:   loadsr<   r=   r>   r?   )r   rG   r(   rF   s       r   apply_canonical_moviez#MovieDatabase.apply_canonical_movie   si   i Ch[QQZZ\\ 	L'l1ek"#34L8L[.EJ
|,@0@I7%-I7%-|,@0@ 0FE4FI7%-I7%-z#m"4"<==*S)9%:%BdCCz#m"4"<==*S)9%:%BdCCr   rB   slugerrorc                t    | j                             d|||f           | j                                          d S )Na  
            INSERT INTO movie_sources (movie_id, source, source_slug, raw_json, crawl_status, retry_count, last_error)
            VALUES (NULL, ?, ?, '{}', 'failed', 1, ?)
            ON CONFLICT(source, source_slug) DO UPDATE SET
                crawl_status='failed',
                retry_count=retry_count + 1,
                last_error=excluded.last_error,
                updated_at=CURRENT_TIMESTAMP
            r   r@   r'   )r   rB   rR   rS   s       r   mark_source_failedz MovieDatabase.mark_source_failed   sI    	 T5!	
 	
 	
 		r   serverr
   c                N   | j                             d|||j        |j        t	          j        |j        d          f           | j                             d|||j        f                                          }| j                                          t          |d                   S )Na@  
            INSERT INTO servers (movie_id, source, name, priority, raw_json)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(movie_id, source, name) DO UPDATE SET
                priority=excluded.priority,
                raw_json=excluded.raw_json,
                updated_at=CURRENT_TIMESTAMP
            Fr+   zESELECT id FROM servers WHERE movie_id = ? AND source = ? AND name = ?r-   )
r   r@   namepriorityr:   r;   rD   rA   r'   r)   )r   rG   rB   rW   rF   s        r   upsert_serverzMovieDatabase.upsert_server   s    	 vv{FOTZ
af=g=g=gh
	
 
	
 
	
 iSvv{+
 
 (** 	 		3t9~~r   pending	server_idepisoder   statusc                   | j                             d|||j        |j        |j        f                                          }|r|d         |j        k    }|rdn|d         }| j                             d||j        |j        |j        |j        |j        |t          j
        |j        d          |rdnd	|d
         f
           | j                                          t          |d
                   S t          t                                }	| j                             d||||j        |j        |j        |j        |j        |j        |	|t          j
        |j        d          f           | j                             d|||j        |j        f                                          }
| j                                          t          |
d
                   S )Nz
            SELECT id, access_key, origin_m3u8_url, crawl_status FROM episodes
            WHERE movie_id = ? AND source = ? AND filename = ? AND season = ? AND episode = ?
            ORDER BY updated_at DESC, id DESC
            LIMIT 1
            origin_m3u8_urlr\   crawl_statusat  
                UPDATE episodes
                SET server_id = ?, source_episode_id = ?, title = ?, season = ?, episode = ?,
                    origin_m3u8_url = ?, crawl_status = ?, raw_json = ?,
                    last_error = CASE WHEN ? THEN NULL ELSE last_error END,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                Fr+   r   r   r-   a  
            INSERT INTO episodes (
                movie_id, server_id, source, source_episode_id, title, filename, season,
                episode, origin_m3u8_url, access_key, crawl_status, raw_json
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(movie_id, source, filename, origin_m3u8_url) DO UPDATE SET
                server_id=excluded.server_id,
                source_episode_id=excluded.source_episode_id,
                title=excluded.title,
                season=excluded.season,
                episode=excluded.episode,
                crawl_status=excluded.crawl_status,
                raw_json=excluded.raw_json,
                updated_at=CURRENT_TIMESTAMP
            z
            SELECT id FROM episodes
            WHERE movie_id = ? AND source = ? AND filename = ? AND origin_m3u8_url = ?
            )r   r@   filenameseasonr^   rA   m3u8_urlsource_episode_idr.   r:   r;   rD   r'   r)   r   r   )r   rG   r]   rB   r^   r_   existinglink_changednext_status
access_keyrF   s              r   upsert_episodezMovieDatabase.upsert_episode   s    9$$ vw/Q
 
 (** 	  	'#$56':JJL'3Q)).9QKI -MNO$Jw{???%,AA1TN  , Ix~&&&\\
	  )  
7;U;;;!	
 	
 	
> i vw/1AB
 
 (** 	 		3t9~~r   N
episode_idcleaned_m3u8_path
str | None	strm_pathc           	     z    | j                             d||||||f           | j                                          d S )Na  
            UPDATE episodes
            SET cleaned_m3u8_path = ?, strm_path = ?, crawl_status = ?,
                last_error = ?, retry_count = retry_count + CASE WHEN ? = 'failed' THEN 1 ELSE 0 END,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
            rU   )r   rl   rm   ro   r_   rS   s         r   update_episode_filesz"MovieDatabase.update_episode_files-  sQ     		 	65&*M		
 		
 		
 		r   sqlite3.Rowc                    | j                             d|f                                          }|st          d|           |S )Nz#SELECT * FROM episodes WHERE id = ?zEpisode not found: )r   r@   rA   KeyError)r   rl   rF   s      r   get_episodezMovieDatabase.get_episodeA  sL    i E
}UU^^`` 	?===>>>
r   )r   r   )r   r    )r(   r	   r   r)   )rG   r)   r(   r	   r   r	   )rB   r   rR   r   rS   r   r   r    )rG   r)   rB   r   rW   r
   r   r)   )r\   )rG   r)   r]   r)   rB   r   r^   r   r_   r   r   r)   r"   )rl   r)   rm   rn   ro   rn   r_   r   rS   rn   r   r    )rl   r)   r   rr   )__name__
__module____qualname__r   r#   r   rJ   rQ   rV   r[   rk   rq   ru    r   r   r   r      s              F F F FP? ? ? ?B   (      2  U U U U Uz !    (     r   r   )
__future__r   r:   r   pathlibr   typingr   uuidr   modelsr   r	   r
   utilsr   r   ry   r   r   <module>r      s    " " " " " "                     H H H H H H H H H H * * * * * *x x x x x x x x x xr   