-- Create Database
CREATE DATABASE IF NOT EXISTS SRMS;
USE SRMS;

-- Users Table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Customer Table
CREATE TABLE Customer (
    customerNumber INT PRIMARY KEY AUTO_INCREMENT,
    firstName VARCHAR(100) NOT NULL,
    lastName VARCHAR(100) NOT NULL,
    telephone VARCHAR(20) NOT NULL,
    address VARCHAR(255) NOT NULL,
    UNIQUE KEY unique_customer_name (firstName, lastName),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Product Table
CREATE TABLE Product (
    productCode INT PRIMARY KEY AUTO_INCREMENT,
    productName VARCHAR(100) NOT NULL,
    unitPrice DECIMAL(10,2) NOT NULL,
    quantityInStock INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sale Table
CREATE TABLE Sale (
    invoiceNumber INT PRIMARY KEY AUTO_INCREMENT,
    customerNumber INT NOT NULL,
    salesDate DATE NOT NULL,
    paymentMethod VARCHAR(50) NOT NULL,
    totalAmountPaid DECIMAL(10,2) NOT NULL,
    saleItems LONGTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customerNumber) REFERENCES Customer(customerNumber)
);
