CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,   
    FirstName VARCHAR(100) NOT NULL,           
    LastName VARCHAR(100) NOT NULL,           
    Email VARCHAR(100) UNIQUE,                   
    Phone VARCHAR(15),                           
    DateOfBirth DATE,                           
    JoinDate DATE NOT NULL    
);


CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY AUTO_INCREMENT,    
    CustomerID INT,                              
    AccountType VARCHAR(50) NOT NULL,            
    Balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    OpenDate DATE NOT NULL,                     
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)  -- Fix here
);


CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY AUTO_INCREMENT, 
    AccountID INT,                                
    TransactionDate DATE NOT NULL,                
    Amount DECIMAL(10, 2) NOT NULL,                
    TransactionType VARCHAR(20) NOT NULL,          
    Description VARCHAR(255),                     
    FOREIGN KEY (AccountID) REFERENCES Accounts(AccountID)
);

INSERT INTO Customers (FirstName, LastName, Email, Phone, DateOfBirth, JoinDate)
VALUES 
('John', 'Doe', 'john.doe@example.com', '123-456-7890', '1990-01-01', '2023-01-01'),
('Jane', 'Smith', 'jane.smith@example.com', '987-654-3210', '1992-05-10', '2023-02-10'),
('Mark', 'Jones', 'mark.jones@example.com', '555-123-4567', '1985-08-23', '2022-12-01');

INSERT INTO Accounts (CustomerID, AccountType, Balance, OpenDate)
VALUES 
(1, 'Checking', 1000.00, '2023-01-01'),
(1, 'Savings', 5000.00, '2023-01-01'),
(2, 'Checking', 1500.00, '2023-02-10'),
(3, 'Checking', 200.00, '2022-12-01');

INSERT INTO Transactions (AccountID, TransactionDate, Amount, TransactionType, Description)
VALUES 
(1, '2023-01-02', 200.00, 'Deposit', 'Initial deposit to checking account'),
(1, '2023-01-03', 50.00, 'Withdrawal', 'ATM withdrawal'),
(2, '2023-02-11', 1000.00, 'Deposit', 'Initial deposit to savings account'),
(3, '2022-12-02', 100.00, 'Deposit', 'Initial deposit to checking account'),
(3, '2022-12-03', 50.00, 'Withdrawal', 'ATM withdrawal');


SELECT * FROM Customers;


SELECT t.TransactionID, t.AccountID, t.TransactionDate, t.Amount, t.TransactionType, t.Description
FROM Transactions t
JOIN Accounts a ON t.AccountID = a.AccountID
WHERE a.CustomerID = 1;


SELECT TransactionID, TransactionDate, Amount, TransactionType, Description
FROM Transactions
WHERE AccountID = 1;

SELECT AccountID, COUNT(*) AS TotalTransactions
FROM Transactions
GROUP BY AccountID;


SELECT AccountID, Balance
FROM Accounts
ORDER BY Balance DESC
LIMIT 1;