-- Drop tables if they already exist
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS invoice_items;
DROP TABLE IF EXISTS invoices;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;

-- Create tables
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0
);

CREATE TABLE invoices (
    invoice_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    invoice_date DATE,
    total_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'Pending'
);

CREATE TABLE invoice_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2)
);

CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_id INT,
    payment_date DATE,
    amount DECIMAL(10,2),
    payment_method VARCHAR(50)
);

-- Insert sample data
INSERT INTO customers (customer_name, email, phone) VALUES
('Rahul Sharma', 'rahul@email.com', '9876543210'),
('Priya Patel', 'priya@email.com', '9123456780'),
('Amit Kumar', 'amit@email.com', '9988776655');

INSERT INTO products (product_name, price, stock) VALUES
('Laptop', 55000.00, 15),
('Mouse', 500.00, 100),
('Keyboard', 1200.00, 50),
('Monitor', 12000.00, 25);

INSERT INTO invoices (customer_id, invoice_date, total_amount, status) VALUES
(1, '2025-08-10', 56200.00, 'Paid'),
(2, '2025-08-15', 13200.00, 'Pending'),
(3, '2025-09-01', 55000.00, 'Paid');

INSERT INTO invoice_items (invoice_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 55000.00),
(1, 2, 2, 500.00),
(2, 4, 1, 12000.00),
(2, 3, 1, 1200.00),
(3, 1, 1, 55000.00);

INSERT INTO payments (invoice_id, payment_date, amount, payment_method) VALUES
(1, '2025-08-11', 56200.00, 'UPI'),
(3, '2025-09-02', 55000.00, 'Credit Card');