-- Drop the database if it exists
DROP DATABASE IF EXISTS ap;

-- Create the database
CREATE DATABASE ap;

USE ap;

-- Creating the vendors Table
CREATE TABLE vendors ( vendorID INT AUTO_INCREMENT PRIMARY KEY, vendorName VARCHAR(45) NOT NULL, vendorAddress VARCHAR(45), vendorCity VARCHAR(45), vendorState CHAR(45), vendorZipCode VARCHAR(10), vendorPhone VARCHAR(20) );

-- Creating the invoices Table
CREATE TABLE invoices ( invoiceID INT AUTO_INCREMENT PRIMARY KEY, vendorID INT NOT NULL, invoiceNumber VARCHAR(50) NOT NULL, invoiceDate DATETIME NOT NULL, invoiceTotal DECIMAL(10, 2) NOT NULL, paymentTotal DECIMAL(10, 2) NOT NULL DEFAULT 0.00, FOREIGN KEY (vendorID) REFERENCES vendors(vendorID) );

-- Creating the lineItems Table
CREATE TABLE lineItems ( lineItemID INT AUTO_INCREMENT PRIMARY KEY, invoiceID INT NOT NULL, description VARCHAR(45) NOT NULL, quantity INT NOT NULL, price INT NOT NULL, lineItemTotal DECIMAL(10, 2) NOT NULL, FOREIGN KEY (invoiceID) REFERENCES invoices(invoiceID) );

-- Index on the foreign key in the invoices table that references vendors(vendorID)
CREATE INDEX idx_vendor_id ON invoices(vendorID);


-- Index on the foreign key in the lineItems table that references invoices(invoiceID)
CREATE INDEX idx_invoice_id ON lineItems(invoiceID);

-- Index for the invoiceNumber column in the invoices table
CREATE INDEX idx_invoice_number ON invoices(invoiceNumber);

-- Replace `your_database_name` with the actual name of your database
-- Replace `app_user` and `password` with your desired username and password
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';

GRANT SELECT, INSERT, UPDATE ON your_database_name.* TO 'app_auser'@'localhost';