DatabaseCreation: console_6.sql

CREATE TEMPORARY TABLE temp_devices1 (
    device_type VARCHAR(255),
    last_login TEXT,
    user_sub_id INT
);

COPY temp_devices1 (device_type, last_login, user_sub_id)
FROM 'C:\temp\devices_data_20250422.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

INSERT INTO Devices (DeviceType, LastLogIn, UserSubscriptionID)
SELECT
    device_type,
    CASE
        WHEN last_login = '2025-02-29' THEN '2025-02-28'::DATE
        ELSE last_login::DATE
    END,
    user_sub_id
FROM temp_devices1;

INSERT INTO User_Devices (UserUserID, DevicesDeviceID)
SELECT UserSubscriptionID, DeviceID
FROM Devices
WHERE DeviceID NOT IN (SELECT DevicesDeviceID FROM User_Devices);


BEGIN;

INSERT INTO Devices (DeviceType, LastLogIn, UserSubscriptionID)
SELECT
    t.device_type,
    (CURRENT_DATE - (random() * 365)::int),
    sub.UserSubscriptionID
FROM user_subscription sub
CROSS JOIN LATERAL (
    SELECT device_type
    FROM temp_devices1
    ORDER BY random()
    LIMIT 1
) t;

COMMIT;

INSERT INTO User_Devices (UserUserID, DevicesDeviceID)
SELECT UserSubscriptionID, DeviceID
FROM Devices
ON CONFLICT DO NOTHING;