1. Mac: Download originals to mac
2. Mac: On the mac, Run cd ~/code/photos; python3 hash_photos.py. This is idempotent and can be run multiple times
3. Mac: Push new files to NAS with python3 push_files.py test —run —limit 1000
   (test is the folder under 00 Unfiled where the new files will go, you might need to run it multiple times or use a very large limit number)
4. Mac: Generate the file name mapping on the mac as follows
5. Copy the sqlite database to a temporary location
   cp /Volumes/T7/… ~/Photos.sqlite
6. Run the following query

sqlite3 -header -csv ~/Photos.sqlite \
"SELECT 
    a.ZFILENAME as hex_name, 
    att.ZORIGINALFILENAME as original_name 
FROM ZASSET a
JOIN ZADDITIONALASSETATTRIBUTES att ON a.Z_PK = att.ZASSET
WHERE a.ZFILENAME IS NOT NULL 
  AND att.ZORIGINALFILENAME IS NOT NULL;" > ~/Desktop/photos_mapping.csv

If this query fails, it is possible that the database schema has been changed by Apple. The following query outputs the schema based on which the query above will need to be changed

# Check columns for the main Asset table
sqlite3 ~/Desktop/photos_meta.sqlite ".schema ZASSET"

# Check columns for the Resource table (where the original names usually live)
sqlite3 ~/Desktop/photos_meta.sqlite ".schema ZINTERNALRESOURCE"

1. Mac: Verify the output as
   head -n 5 ~/Desktop/photos_mapping.csv
2. Mac: Copy the csv file over to NAS
3. NAS: (through ssh), now run the following to import the csv mapping``` TRUNCATE TABLE apple_names;

-- Adjust the path to where you uploaded the CSV on the NAS \copy apple_names FROM '/path/to/photos_mapping.csv' WITH (FORMAT csv, HEADER true);

4. **Mac:** Copy files from Mac to NAS```
python3 push_files.py test --limit 1000 --run  (moves to /volume1/photo/00 Unfiled

1. NAS: Do what ever file movement you need to do
2. NAS: Run a healing script on NAS as``` python3 hash_photos.py --run (in the venv)

SQL Code:

CREATE OR REPLACE FUNCTION get_mac_files_not_on_nas(mac_base TEXT, nas_base TEXT)
RETURNS TABLE (
    mac_relative_path TEXT,
    readable_size TEXT,
    modified_at TIMESTAMP WITH TIME ZONE,
    file_hash TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        replace(m.path, mac_base, '') AS mac_relative_path,
        CASE
            WHEN m.size = 0 THEN '0 B'
            WHEN m.size < 1024 THEN m.size || ' B'
            WHEN m.size < 1048576 THEN round(m.size / 1024.0, 2) || ' KB'
            WHEN m.size < 1073741824 THEN round(m.size / 1048576.0, 2) || ' MB'
            ELSE round(m.size / 1073741824.0, 2) || ' GB'
        END AS readable_size,
        TO_TIMESTAMP(m.mtime) AS modified_at,
        m.hash AS file_hash
    FROM file_cache m
    WHERE m.path LIKE mac_base || '%'
      AND NOT EXISTS (
          SELECT 1
          FROM file_cache n
          WHERE n.hash = m.hash
            AND n.path LIKE nas_base || '%'
            -- We ignore is_deleted so rejected files aren't re-pushed
      )
    ORDER BY m.size DESC;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.get_duplicate_details(base_path text) 
RETURNS TABLE(hash text, relative_path text, readable_size text, modified_at timestamp with time zone)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH dupe_hashes AS (
        SELECT f.hash
        FROM file_cache f
        WHERE f.path LIKE base_path || '%' 
          AND f.size <> 0
          AND f.is_deleted = FALSE  -- Only look at active files
        GROUP BY f.hash
        HAVING COUNT(*) > 1
    )
    SELECT 
        f.hash,
        replace(f.path, base_path, '') AS relative_path,
        CASE 
            WHEN f.size = 0 THEN '0 B'
            WHEN f.size < 1024 THEN f.size || ' B'
            WHEN f.size < 1048576 THEN round(f.size / 1024.0, 2) || ' KB'
            WHEN f.size < 1073741824 THEN round(f.size / 1048576.0, 2) || ' MB'
            ELSE round(f.size / 1073741824.0, 2) || ' GB'
        END AS readable_size,
        TO_TIMESTAMP(f.mtime) AS modified_at
    FROM file_cache f
    JOIN dupe_hashes dh ON f.hash = dh.hash
    WHERE f.path LIKE base_path || '%'
      AND f.is_deleted = FALSE      -- Only display active files
    ORDER BY f.hash, f.size DESC;
END;
$$;

DROP VIEW IF EXISTS public.nas_duplicate_detail;

CREATE VIEW public.nas_duplicate_detail AS
 WITH nas_dupe_hashes AS (
         SELECT file_cache.hash
           FROM public.file_cache
          WHERE file_cache.path LIKE '/volume1/photo/%'
            AND file_cache.size <> 0
            AND file_cache.is_deleted = FALSE -- Ignore flagged/deleted records
          GROUP BY file_cache.hash
         HAVING count(*) > 1
        )
 SELECT f.hash,
    substring(f.path, '.*/volume1/photo/(.*)') AS relative_path,
        CASE
            WHEN (f.size = 0) THEN '0 B'
            WHEN (f.size < 1024) THEN (f.size || ' B')
            WHEN (f.size < 1048576) THEN (round(((f.size)::numeric / 1024.0), 2) || ' KB')
            WHEN (f.size < 1073741824) THEN (round(((f.size)::numeric / 1048576.0), 2) || ' MB')
            ELSE (round(((f.size)::numeric / 1073741824.0), 2) || ' GB')
        END AS size,
    to_timestamp(f.mtime) AS modified_at
   FROM public.file_cache f
   JOIN nas_dupe_hashes mdh ON f.hash = mdh.hash
  WHERE f.path LIKE '/volume1/photo/%'
    AND f.size <> 0
    AND f.is_deleted = FALSE -- Only show files currently on the disk
  ORDER BY f.hash, f.size DESC;

-- 1. Remove the old view to refresh the connection to the updated function
DROP VIEW IF EXISTS public.mac_duplicates_lookup;

-- 2. Recreate using your specific T7 master path
CREATE VIEW public.mac_duplicates_lookup AS
 SELECT hash,
    relative_path,
    readable_size,
    modified_at
   FROM public.get_duplicate_details('/Volumes/T7/Pictures/Photos Library.photoslibrary/originals/') 
   AS get_duplicate_details(hash, relative_path, readable_size, modified_at);

CREATE OR REPLACE FUNCTION find_dupes_in_path(path_prefix TEXT)
RETURNS TABLE(file_hash TEXT, file_count BIGINT, total_size_mb NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        hash, 
        COUNT(*), 
        -- Calculate total size of duplicates excluding the 'deleted' records
        round((SUM(size) / (1024.0 * 1024.0)), 2) AS total_size_mb
    FROM file_cache
    WHERE path LIKE path_prefix || '%'
      AND is_deleted = FALSE  -- Only count files physically on the disk
      AND size > 0            -- Exclude empty files
    GROUP BY hash
    HAVING COUNT(*) > 1;
END;
$$ LANGUAGE plpgsql;

-- 1. Drop the old version to ensure the return type and logic are fresh
DROP FUNCTION IF EXISTS get_duplicate_details(TEXT);

-- 2. Create the corrected version with Soft-Delete awareness
CREATE OR REPLACE FUNCTION get_duplicate_details(base_path TEXT)
RETURNS TABLE (
    hash TEXT,
    relative_path TEXT,
    readable_size TEXT,
    modified_at TIMESTAMP WITH TIME ZONE
) AS $$
BEGIN
    RETURN QUERY
    WITH dupe_hashes AS (
        SELECT f.hash
        FROM file_cache f
        WHERE f.path LIKE base_path || '%'
          AND f.size <> 0
          AND f.is_deleted = FALSE  -- Ignore files that have been moved/deleted
        GROUP BY f.hash
        HAVING COUNT(*) > 1
    )
    SELECT
        f.hash,
        replace(f.path, base_path, '') AS relative_path,
        CASE
            WHEN f.size = 0 THEN '0 B'
            WHEN f.size < 1024 THEN f.size || ' B'
            WHEN f.size < 1048576 THEN round(f.size / 1024.0, 2) || ' KB'
            WHEN f.size < 1073741824 THEN round(f.size / 1048576.0, 2) || ' MB'
            ELSE round(f.size / 1073741824.0, 2) || ' GB'
        END AS readable_size,
        TO_TIMESTAMP(f.mtime) AS modified_at
    FROM file_cache f
    JOIN dupe_hashes dh ON f.hash = dh.hash
    WHERE f.path LIKE base_path || '%'
      AND f.is_deleted = FALSE      -- Ensure only active files are returned
    ORDER BY f.hash, f.size DESC;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW mac_to_nas_migration_list AS SELECT * FROM get_mac_files_not_on_nas( '/Volumes/T7/Pictures/Photos Library.photoslibrary/originals/', '/volume1/photo/' );