SQLize | PHPize | SQLtest

CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, phone_number VARCHAR(20), address VARCHAR(255), city VARCHAR(100), state VARCHAR(100), country VARCHAR(100), postal_code VARCHAR(20), created_at TIMESTAMP NOT NULL DEFAULT NOW(), UNIQUE (email) ); CREATE TABLE accounts ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id), balance NUMERIC(18, 2) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT NOW(), UNIQUE (user_id) ); CREATE TABLE transactions ( id SERIAL PRIMARY KEY, account_id INTEGER REFERENCES accounts(id), amount NUMERIC(18, 2) NOT NULL, type VARCHAR(50) NOT NULL, description TEXT, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE transfers ( id SERIAL PRIMARY KEY, from_account_id INTEGER REFERENCES accounts(id), to_account_id INTEGER REFERENCES accounts(id), amount NUMERIC(18, 2) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE deposits ( id SERIAL PRIMARY KEY, account_id INTEGER REFERENCES accounts(id), amount NUMERIC(18, 2) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE withdrawals ( id SERIAL PRIMARY KEY, account_id INTEGER REFERENCES accounts(id), amount NUMERIC(18, 2) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE payment_methods ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id), name VARCHAR(100) NOT NULL, type VARCHAR(50) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE payments ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id), payment_method_id INTEGER REFERENCES payment_methods(id), amount NUMERIC(18, 2) NOT NULL, description TEXT, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE invoices ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id), amount NUMERIC(18, 2) NOT NULL, status VARCHAR(50) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE tariffs ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, description TEXT, free BOOLEAN NOT NULL DEFAULT FALSE, price NUMERIC(18, 2) NOT NULL ); CREATE TABLE user_tariffs ( user_id INTEGER REFERENCES users(id), tariff_id INTEGER REFERENCES tariffs(id), start_date TIMESTAMP NOT NULL DEFAULT NOW(), end_date TIMESTAMP, PRIMARY KEY (user_id, tariff_id) ); -- Users INSERT INTO users (id, email, password, first_name, last_name, phone_number, address, city, state, country, postal_code, created_at) VALUES (1, ' **[john.doe@example.com](mailto:john.doe@example.com)** ', 'hashed_password1', 'John', 'Doe', '+1 123-456-7890', '123 Main St', 'Los Angeles', 'California', 'USA', '90001', '2023-01-02 12:34:56'), (2, ' **[jane.doe@example.com](mailto:jane.doe@example.com)** ', 'hashed_password2', 'Jane', 'Doe', '+1 234-567-8901', '456 Maple Ave', 'New York', 'New York', 'USA', '10001', '2023-01-15 08:00:00'), (3, ' **[mike.smith@example.com](mailto:mike.smith@example.com)** ', 'hashed_password3', 'Mike', 'Smith', '+1 345-678-9012', '789 Oak St', 'Chicago', 'Illinois', 'USA', '60601', '2023-01-18 14:30:00'), (4, ' **[emma.jones@example.com](mailto:emma.jones@example.com)** ', 'hashed_password4', 'Emma', 'Jones', '+1 456-789-0123', '234 Elm Dr', 'San Francisco', 'California', 'USA', '94101', '2023-02-01 09:45:00'), (5, ' **[david.johnson@example.com](mailto:david.johnson@example.com)** ', 'hashed_password5', 'David', 'Johnson', '+1 567-890-1234', '345 Pine St', 'Miami', 'Florida', 'USA', '33101', '2023-02-10 11:00:00'), (6, ' **[olivia.brown@example.com](mailto:olivia.brown@example.com)** ', 'hashed_password6', 'Olivia', 'Brown', '+1 678-901-2345', '678 Spruce St', 'Seattle', 'Washington', 'USA', '98101', '2023-02-20 15:45:00'), (7, ' **[james.wilson@example.com](mailto:james.wilson@example.com)** ', 'hashed_password7', 'James', 'Wilson', '+1 789-012-3456', '901 Birch St', 'Houston', 'Texas', 'USA', '77001', '2023-03-05 10:15:00'), (8, ' **[sophia.martinez@example.com](mailto:sophia.martinez@example.com)** ', 'hashed_password8', 'Sophia', 'Martinez', '+1 890-123-4567', '123 Pine St', 'Chicago', 'Illinois', 'USA', '60601', '2023-03-20 11:00:00'), (9, ' **[liam.smith@example.com](mailto:liam.smith@example.com)** ', 'hashed_password9', 'Liam', 'Smith', '+1 901-234-5678', '234 Cedar St', 'Los Angeles', 'California', 'USA', '90001', '2023-04-10 16:30:00'), (10, ' **[isabella.johnson@example.com](mailto:isabella.johnson@example.com)** ', 'hashed_password10', 'Isabella', 'Johnson', '+1 987-654-3210', '345 Oak St', 'San Francisco', 'California', 'USA', '94101', '2023-04-15 12:45:00'); -- Accounts INSERT INTO accounts (id, user_id, balance, created_at) VALUES (1, 1, 1500.00, '2023-01-02 12:35:00'), (2, 2, 3000.00, '2023-01-15 08:00:30'), (3, 3, 2500.00, '2023-01-18 14:30:30'), (4, 4, 3500.00, '2023-02-01 09:45:30'), (5, 5, 4500.00, '2023-02-10 11:00:30'), (6, 6, 2750.00, '2023-02-20 15:46:00'), (7, 7, 3800.00, '2023-03-05 10:16:00'), (8, 8, 2450.00, '2023-03-20 11:01:00'), (9, 9, 3100.00, '2023-04-10 16:31:00'), (10, 10, 1700.00, '2023-04-15 12:46:00'); -- Transactions INSERT INTO transactions (id, account_id, amount, type, description, created_at) VALUES (1, 1, 500.00, 'deposit', 'Initial deposit', '2023-01-02 12:36:00'), (2, 2, 1000.00, 'deposit', 'Initial deposit', '2023-01-15 08:01:00'), (3, 1, 200.00, 'withdrawal', 'ATM withdrawal', '2023-01-05 17:15:30'), (4, 2, 100.00, 'transfer', 'Transfer to John', '2023-01-20 14:22:00'), (5, 3, 750.00, 'deposit', 'Initial deposit', '2023-01-18 14:31:00'), (6, 4, 1250.00, 'deposit', 'Initial deposit', '2023-02-01 09:46:00'), (7, 5, 1750.00, 'deposit', 'Initial deposit', '2023-02-10 11:01:00'), (8, 3, 300.00, 'withdrawal', 'ATM withdrawal', '2023-01-25 10:30:00'), (9, 4, 150.00, 'transfer', 'Transfer to David', '2023-02-05 12:00:00'), (10, 5, 200.00, 'transfer', 'Transfer to Mike', '2023-02-15 15:00:00'), (11, 6, 2250.00, 'deposit', 'Initial deposit', '2023-02-20 15:47:00'), (12, 7, 2800.00, 'deposit', 'Initial deposit', '2023-03-05 10:17:00'), (13, 6, 100.00, 'withdrawal', 'ATM withdrawal', '2023-02-25 18:00:00'), (14, 7, 200.00, 'transfer', 'Transfer to Olivia', '2023-03-08 13:30:00'), (15, 8, 2000.00, 'deposit', 'Initial deposit', '2023-03-20 11:02:00'), (16, 9, 2500.00, 'deposit', 'Initial deposit', '2023-04-10 16:32:00'), (17, 10, 1500.00, 'deposit', 'Initial deposit', '2023-04-15 12:47:00'), (18, 8, 250.00, 'withdrawal', 'ATM withdrawal', '2023-03-25 18:30:00'), (19, 9, 300.00, 'transfer', 'Transfer to Sophia', '2023-04-12 14:00:00'); -- Transfers INSERT INTO transfers (id, from_account_id, to_account_id, amount, created_at) VALUES (1, 2, 1, 100.00, '2023-01-20 14:22:00'), (2, 4, 5, 150.00, '2023-02-05 12:00:00'), (3, 5, 3, 200.00, '2023-02-15 15:00:00'), (4, 7, 6, 200.00, '2023-03-08 13:30:00'), (5, 9, 8, 300.00, '2023-04-12 14:00:00'); -- Deposits INSERT INTO deposits (id, account_id, amount, created_at) VALUES (1, 1, 500.00, '2023-01-02 12:36:00'), (2, 2, 1000.00, '2023-01-15 08:01:00'), (3, 3, 750.00, '2023-01-18 14:31:00'), (4, 4, 1250.00, '2023-02-01 09:46:00'), (5, 5, 1750.00, '2023-02-10 11:01:00'), (6, 6, 2250.00, '2023-02-20 15:47:00'), (7, 7, 2800.00, '2023-03-05 10:17:00'), (8, 8, 2000.00, '2023-03-20 11:02:00'), (9, 9, 2500.00, '2023-04-10 16:32:00'), (10, 10, 1500.00, '2023-04-15 12:47:00'); -- Withdrawals INSERT INTO withdrawals (id, account_id, amount, created_at) VALUES (1, 1, 200.00, '2023-01-05 17:15:30'), (2, 3, 300.00, '2023-01-25 10:30:00'), (3, 6, 100.00, '2023-02-25 18:00:00'), (4, 8, 250.00, '2023-03-25 18:30:00'); -- Payment_methods INSERT INTO payment_methods (id, user_id, name, type, created_at) VALUES (1, 1, 'John Visa', 'credit_card', '2023-01-02 12:38:00'), (2, 2, 'Jane Mastercard', 'credit_card', '2023-01-15 08:02:00'), (3, 3, 'Mike Amex', 'credit_card', '2023-01-18 14:32:00'), (4, 4, 'Emma Discover', 'credit_card', '2023-02-01 09:47:00'), (5, 5, 'David UnionPay', 'credit_card', '2023-02-10 11:02:00'), (6, 6, 'Olivia JCB', 'credit_card', '2023-02-20 15:48:00'), (7, 7, 'James Diners', 'credit_card', '2023-03-05 10:18:00'), (8, 8, 'Sophia Mastercard', 'credit_card', '2023-03-20 11:03:00'), (9, 9, 'Liam Visa', 'credit_card', '2023-04-10 16:33:00'), (10, 10, 'Isabella Amex', 'credit_card', '2023-04-15 12:48:00'); -- Payments INSERT INTO payments (id, user_id, payment_method_id, amount, description, created_at) VALUES (1, 1, 1, 150.00, 'Online shopping', '2023-01-10 19:45:00'), (2, 2, 2, 250.00, 'Grocery shopping', '2023-01-18 16:30:00'), (3, 3, 3, 100.00, 'Electronics purchase', '2023-01-22 10:00:00'), (4, 4, 4, 50.00, 'Clothing purchase', '2023-02-04 14:30:00'), (5, 5, 5, 120.00, 'Sporting goods', '2023-02-12 09:15:00'), (6, 6, 6, 80.00, 'Movie tickets', '2023-02-28 20:00:00'), (7, 7, 7, 120.00, 'Dinner at restaurant', '2023-03-10 19:30:00'), (8, 8, 8, 150.00, 'Concert tickets', '2023-03-27 20:30:00'), (9, 9, 9, 75.00, 'Books', '2023-04-14 15:00:00'), (10, 10, 10, 100.00, 'Groceries', '2023-04-20 18:15:00'); -- Invoices INSERT INTO invoices (id, user_id, amount, status, created_at) VALUES (1, 1, 100.00, 'paid', '2023-01-05 12:00:00'), (2, 2, 200.00, 'unpaid', '2023-01-18 10:00:00'), (3, 3, 150.00, 'paid', '2023-01-25 15:00:00'), (4, 4, 75.00, 'unpaid', '2023-02-05 11:00:00'), (5, 5, 250.00, 'paid', '2023-02-10 16:00:00'), (6, 6, 175.00, 'paid', '2023-02-22 11:00:00'), (7, 7, 300.00, 'unpaid', '2023-03-09 16:00:00'), (8, 8, 220.00, 'paid', '2023-03-22 14:00:00'), (9, 9, 350.00, 'unpaid', '2023-04-11 17:30:00'), (10, 10, 275.00, 'paid', '2023-04-18 12:00:00'); -- Tariffs INSERT INTO tariffs (id, name, description, free, price) VALUES (1, 'Basic', 'Basic features and limited transactions', TRUE, 0.00), (2, 'Premium', 'Unlimited transactions and premium features', FALSE, 9.99); -- User_tariffs INSERT INTO user_tariffs (user_id, tariff_id, start_date, end_date) VALUES (1, 1, '2023-01-02 12:40:00', '2023-02-02 12:40:00'), (1, 2, '2023-02-02 12:40:00', NULL), (2, 1, '2023-01-15 08:05:00', '2023-02-15 08:05:00'), (2, 2, '2023-02-15 08:05:00', NULL), (3, 1, '2023-01-18 14:40:00', NULL), (4, 2, '2023-02-01 09:50:00', NULL), (5, 1, '2023-02-10 11:05:00', '2023-03-10 11:05:00'), (5, 2, '2023-03-10 11:05:00', NULL), (6, 1, '2023-02-20 15:49:00', '2023-03-20 15:49:00'), (6, 2, '2023-03-20 15:49:00', NULL), (7, 2, '2023-03-05 10:20:00', NULL), (8, 1, '2023-03-20 11:04:00', '2023-04-20 11:04:00'), (8, 2, '2023-04-20 11:04:00', NULL), (9, 2, '2023-04-10 16:35:00', NULL), (10, 1, '2023-04-15 12:49:00', '2023-05-15 12:49:00');