-- Customers Table
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders Table
CREATE TABLE Orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE CASCADE
);

-- Inserting Customers
INSERT INTO Customers (customer_name, email, phone) VALUES
('Alice Johnson', '[email protected]', '1234567890'),
('Bob Smith', '[email protected]', '2345678901'),
('Charlie Brown', '[email protected]', '3456789012'),
('David Lee', '[email protected]', '4567890123'),
('Emma Watson', '[email protected]', '5678901234'),
('Frank Castle', '[email protected]', '6789012345'),
('Grace Harper', '[email protected]', '7890123456'),
('Henry Ford', '[email protected]', '8901234567'),
('Ivy Parker', '[email protected]', '9012345678'),
('Jack Ryan', '[email protected]', '0123456789');

-- Orders: Ensuring customers have transactions across both periods
INSERT INTO Orders (customer_id, order_date, total_amount) VALUES
-- Purchases in the last 30 days (relative to CURDATE())
(1, DATE_SUB(CURDATE(), INTERVAL 10 DAY), 120.50),
(2, DATE_SUB(CURDATE(), INTERVAL 15 DAY), 75.30),
(3, DATE_SUB(CURDATE(), INTERVAL 5 DAY), 200.00),
(4, DATE_SUB(CURDATE(), INTERVAL 25 DAY), 99.99),
(5, DATE_SUB(CURDATE(), INTERVAL 12 DAY), 50.00),

-- Purchases in the previous 30-60 days (relative to CURDATE())
(1, DATE_SUB(CURDATE(), INTERVAL 40 DAY), 110.00),
(2, DATE_SUB(CURDATE(), INTERVAL 45 DAY), 90.75),
(3, DATE_SUB(CURDATE(), INTERVAL 50 DAY), 180.00),
(6, DATE_SUB(CURDATE(), INTERVAL 35 DAY), 45.99),
(7, DATE_SUB(CURDATE(), INTERVAL 55 DAY), 210.00),

-- More purchases across different periods
(8, DATE_SUB(CURDATE(), INTERVAL 20 DAY), 95.50),
(9, DATE_SUB(CURDATE(), INTERVAL 28 DAY), 60.30),
(10, DATE_SUB(CURDATE(), INTERVAL 14 DAY), 300.00),
(8, DATE_SUB(CURDATE(), INTERVAL 38 DAY), 120.00),
(9, DATE_SUB(CURDATE(), INTERVAL 42 DAY), 80.75),
(10, DATE_SUB(CURDATE(), INTERVAL 52 DAY), 130.00);
by