"""
alter fancards table
"""

from yoyo import step

__depends__ = {'20240128_00_ANI72-add-resuming_at-to-jobs'}

steps = [
    step("""
CREATE TEMPORARY TABLE temp_unique_fancards AS
SELECT DISTINCT ON (user_id, file_id)
    id,
    user_id,
    file_id
FROM fanbox_fancards
ORDER BY user_id, file_id, id DESC;


TRUNCATE TABLE fanbox_fancards;

INSERT INTO fanbox_fancards (id, user_id, file_id)
SELECT id, user_id, file_id
FROM temp_unique_fancards;

DROP TABLE temp_unique_fancards;
"""),
    step("""
ALTER TABLE public.fanbox_fancards ADD COLUMN last_checked_at timestamp DEFAULT CURRENT_TIMESTAMP;
"""),
    step("""
ALTER TABLE public.fanbox_fancards ADD COLUMN price text NOT NULL DEFAULT '' ;
"""),
    step("""
ALTER TABLE public.fanbox_fancards ALTER COLUMN file_id DROP NOT NULL;
"""),
    step("""
ALTER TABLE fanbox_fancards DROP CONSTRAINT IF EXISTS fanbox_fancards_user_id_file_id_key;
ALTER TABLE fanbox_fancards ADD CONSTRAINT fanbox_fancards_user_id_file_id_price_unique_idx UNIQUE (user_id, file_id, price);
CREATE UNIQUE INDEX fanbox_fancards_null_file_id_user_id_price_unique_idx ON fanbox_fancards (user_id, price) WHERE file_id IS NULL;

    """),
    step("""
UPDATE public.fanbox_fancards
SET last_checked_at = f.added
FROM public.files f
WHERE fanbox_fancards.file_id = f.id;
"""),
]