-- Customers Table
CREATE TABLE Customers (
    CustomerID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATE,
    HomeCity VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    PhoneNumber VARCHAR(20) UNIQUE,
    CreatedAt DATETIME
);

-- Transactions Table
CREATE TABLE Transactions (
    TransactionID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    CustomerID BIGINT UNSIGNED,
    Amount DECIMAL(12,2),
    TransactionDate DATETIME,
    TransactionType VARCHAR(20),
    LocationCity VARCHAR(100),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Loans Table
CREATE TABLE Loans (
    LoanID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    CustomerID BIGINT UNSIGNED,
    LoanAmount DECIMAL(12,2),
    InterestRate DECIMAL(5,2),
    RemainingBalance DECIMAL(12,2),
    StartDate DATE,
    EndDate DATE,
    LoanType VARCHAR(30),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insert Customers
INSERT INTO Customers (FirstName, LastName, DateOfBirth, HomeCity, Email, PhoneNumber, CreatedAt)
VALUES
('Alice', 'Johnson', '1985-04-12', 'Chicago', 'alice.johnson@gmail.com', '3125551234', NOW()),
('Bob', 'Smith', '1990-08-23', 'New York', 'bob.smith@yahoo.com', '9175555678', NOW()),
('Carol', 'Davis', '1978-11-30', 'Los Angeles', 'carol.davis@hotmail.com', '2135557890', NOW()),
('David', 'Lee', '1995-06-15', 'Houston', 'david.lee@gmail.com', '8325554321', NOW()),
('Eva', 'Martinez', '1982-02-05', 'Miami', 'eva.martinez@gmail.com', '3055552468', NOW());

-- Insert Transactions (omit TransactionID)
INSERT INTO Transactions (CustomerID, Amount, TransactionDate, TransactionType, LocationCity)
VALUES
(1, 2500.00, '2025-09-10 14:30:00', 'deposit', 'Chicago'),
(2, 6000.00, '2025-09-15 10:00:00', 'withdrawal', 'Boston'),
(3, 1200.50, '2025-09-20 16:45:00', 'transfer', 'Los Angeles'),
(4, 7500.00, '2025-10-01 09:15:00', 'deposit', 'Dallas'),
(5, 300.00, '2025-10-05 11:00:00', 'withdrawal', 'Orlando'),
(1, 5200.00, '2025-10-06 13:20:00', 'transfer', 'Detroit');

-- Insert Loans
INSERT INTO Loans (CustomerID, LoanAmount, InterestRate, RemainingBalance, StartDate, EndDate, LoanType)
VALUES
(1, 50000.00, 4.75, 32000.00, '2023-03-01', '2028-03-01', 'home'),
(2, 15000.00, 6.25, 9000.00, '2024-06-15', '2027-06-15', 'auto'),
(3, 10000.00, 5.50, 4500.00, '2023-11-01', '2026-11-01', 'personal'),
(4, 25000.00, 7.10, 18000.00, '2025-01-10', '2030-01-10', 'home'),
(5, 8000.00, 6.00, 6000.00, '2024-09-01', '2027-09-01', 'personal');



SELECT Customers.FirstName, Customers.LastName, Transactions.TransactionType 
FROM Customers 
JOIN Transactions ON Customers.CustomerID = Transactions.CustomerID; 

SELECT Customers.FirstName, Customers.LastName, Transactions.LocationCity  
FROM Customers 
JOIN Transactions ON Customers.CustomerID = Transactions.CustomerID; 

SELECT Customers.FirstName, Customers.LastName, Transactions.TransactionDate 
FROM Customers 
JOIN Transactions ON Customers.CustomerID = Transactions.CustomerID;