1  -- Migration 003: Timestamp Consistency
 2  -- Convert USERS table integer timestamps to TIMESTAMP type
 3  -- This aligns USERS with tracks and playlists tables which already use TIMESTAMP
 4  
 5  -- Step 1: Add new TIMESTAMP columns
 6  ALTER TABLE "USERS" ADD COLUMN IF NOT EXISTS "created-date-new" TIMESTAMP;
 7  ALTER TABLE "USERS" ADD COLUMN IF NOT EXISTS "last-login-new" TIMESTAMP;
 8  
 9  -- Step 2: Convert existing epoch integers to timestamps
10  -- Only convert non-null values; epoch 0 or very old dates indicate no real value
11  UPDATE "USERS" 
12  SET "created-date-new" = TO_TIMESTAMP("created-date")
13  WHERE "created-date" IS NOT NULL 
14    AND "created-date" > 0;
15  
16  UPDATE "USERS"
17  SET "last-login-new" = TO_TIMESTAMP("last-login")
18  WHERE "last-login" IS NOT NULL
19    AND "last-login" > 0;
20  
21  -- Step 3: Drop old integer columns
22  ALTER TABLE "USERS" DROP COLUMN IF EXISTS "created-date";
23  ALTER TABLE "USERS" DROP COLUMN IF EXISTS "last-login";
24  
25  -- Step 4: Rename new columns to original names
26  ALTER TABLE "USERS" RENAME COLUMN "created-date-new" TO "created-date";
27  ALTER TABLE "USERS" RENAME COLUMN "last-login-new" TO "last-login";
28  
29  -- Step 5: Set default for created-date (new users get current timestamp)
30  ALTER TABLE "USERS" ALTER COLUMN "created-date" SET DEFAULT CURRENT_TIMESTAMP;
31  
32  -- Verification query (run manually to check results):
33  -- SELECT _id, username, "created-date", "last-login" FROM "USERS";