DatabaseCreation: console_4.sql

CREATE TEMP TABLE temp_names (name VARCHAR(255));

COPY temp_names (name)
FROM 'C:\temp\names_only_1000.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

SELECT * FROM temp_names LIMIT 5;

CREATE TEMP TABLE temp_surnames (surname VARCHAR(255));

COPY temp_surnames (surname)
FROM 'C:\temp\surnames_only_1000.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

SELECT * FROM temp_surnames LIMIT 5;

INSERT INTO "User" (FirstName, LastName, Username, Email, password)
SELECT
    n.name,
    s.surname,
    LOWER(n.name || '.' || s.surname || row_number() OVER ()) AS Username,
    LOWER(n.name || '.' || s.surname || row_number() OVER () || '@vidi.mk') AS Email,
    'pbkdf2_sha256$260000$standardhash'
FROM temp_names n
CROSS JOIN temp_surnames s
LIMIT 1000000;


INSERT INTO User_Subscription (UserUserID, SubscriptionSubscriptionID, Start_date, Status, Auto_renew)
SELECT
    u.UserID,
    (SELECT SubscriptionID FROM Subscription ORDER BY random() LIMIT 1) AS SubscriptionID,
    CURRENT_DATE - (random() * 30)::int AS Start_date,
    'Active' AS Status,
    (random() > 0.5)::int AS Auto_renew
FROM "User" u
ORDER BY random()
LIMIT 500000;

SELECT Auto_renew, COUNT(*)
FROM User_Subscription
GROUP BY Auto_renew;

UPDATE User_Subscription
SET Auto_renew = (CASE WHEN random() > 0.5 THEN 1 ELSE 0 END);

UPDATE User_Subscription
SET End_date = CURRENT_DATE + (INTERVAL '1 month' + random() * (INTERVAL '11 months'))
WHERE End_date IS NULL;