pinakes/crates/pinakes-migrations/migrations/sqlite/V9__fix_indexes_and_constraints.sql

-- Drop redundant indexes (already covered by UNIQUE constraints)
DROP INDEX IF EXISTS idx_users_username;

DROP INDEX IF EXISTS idx_user_libraries_user_id;

-- Add missing indexes for comments table
CREATE INDEX IF NOT EXISTS idx_comments_media ON comments (media_id);

CREATE INDEX IF NOT EXISTS idx_comments_parent ON comments (parent_comment_id);

-- Remove duplicates before adding unique index (keep the first row)
DELETE FROM external_metadata
WHERE
  rowid NOT IN (
    SELECT
      MIN(rowid)
    FROM
      external_metadata
    GROUP BY
      media_id,
      source
  );

-- Add unique index for external_metadata to prevent duplicates
CREATE UNIQUE INDEX IF NOT EXISTS uq_external_metadata_source ON external_metadata (media_id, source);