Vim Patior

Edit file

File name : database_schema.sql

Content :

-- Main business settings table
CREATE TABLE `business_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) DEFAULT 'InvoiceFlow',
  `logo` TEXT,
  `email` VARCHAR(255),
  `phone` VARCHAR(50),
  `address` TEXT,
  `city_state_zip` VARCHAR(255),
  `country` VARCHAR(100),
  `primary_color` VARCHAR(50) DEFAULT '217 91% 60%'
);

-- Customers table
CREATE TABLE `customers` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255),
  `address` TEXT,
  `phone` VARCHAR(50),
  `logo` TEXT
);

-- Invoices table
CREATE TABLE `invoices` (
  `id` VARCHAR(50) PRIMARY KEY,
  `customer_id` VARCHAR(50) NOT NULL,
  `invoice_date` DATE NOT NULL,
  `due_date` DATE NOT NULL,
  `status` ENUM('Paid', 'Due', 'Overdue', 'Draft') NOT NULL,
  `paid_at` DATETIME,
  `viewed_at` DATETIME,
  `paymentMethod` VARCHAR(50),
  `paymentAccount` VARCHAR(100),
  `notes` TEXT,
  `subtotal` DECIMAL(10, 2) NOT NULL,
  `tax` DECIMAL(5, 2) NOT NULL,
  `discount` DECIMAL(10, 2) DEFAULT 0.00,
  `total` DECIMAL(10, 2) NOT NULL,
  `payment_history` JSON,
  `amount_paid` DECIMAL(10, 2) DEFAULT 0.00,
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE CASCADE
);

-- Invoice line items
CREATE TABLE `invoice_items` (
  `id` VARCHAR(50) PRIMARY KEY,
  `invoice_id` VARCHAR(50) NOT NULL,
  `description` TEXT NOT NULL,
  `quantity` INT NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE CASCADE
);

-- Estimates table (similar to invoices)
CREATE TABLE `estimates` (
  `id` VARCHAR(50) PRIMARY KEY,
  `customer_id` VARCHAR(50) NOT NULL,
  `estimate_date` DATE NOT NULL,
  `expiry_date` DATE NOT NULL,
  `status` ENUM('Draft', 'Sent', 'Accepted', 'Declined') NOT NULL,
  `viewed_at` DATETIME,
  `notes` TEXT,
  `subtotal` DECIMAL(10, 2) NOT NULL,
  `tax` DECIMAL(5, 2) NOT NULL,
  `total` DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE CASCADE
);

-- Estimate line items
CREATE TABLE `estimate_items` (
  `id` VARCHAR(50) PRIMARY KEY,
  `estimate_id` VARCHAR(50) NOT NULL,
  `description` TEXT NOT NULL,
  `quantity` INT NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (`estimate_id`) REFERENCES `estimates`(`id`) ON DELETE CASCADE
);

-- Products & Services table
CREATE TABLE `items` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `price` DECIMAL(10, 2) NOT NULL,
  `sell` BOOLEAN DEFAULT TRUE,
  `incomeAccountId` VARCHAR(100),
  `buy` BOOLEAN DEFAULT FALSE,
  `salesTax` VARCHAR(50)
);

-- Expenses table
CREATE TABLE `expenses` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `amount` DECIMAL(10, 2) NOT NULL,
  `date` DATE NOT NULL,
  `category` ENUM('Subscriptions', 'Gas', 'Internet', 'Bank fees', 'Payroll', 'Other') NOT NULL,
  `type` ENUM('one-time', 'monthly') NOT NULL
);

-- Payroll table
CREATE TABLE `payroll` (
  `id` VARCHAR(50) PRIMARY KEY,
  `employeeName` VARCHAR(255) NOT NULL,
  `baseSalary` DECIMAL(10, 2) NOT NULL,
  `payFrequency` ENUM('weekly', 'bi-weekly', 'monthly') NOT NULL,
  `bonus` DECIMAL(10, 2) DEFAULT 0.00,
  `advanceDeduction` DECIMAL(10, 2) DEFAULT 0.00,
  `socialSecurity` DECIMAL(10, 2) DEFAULT 0.00,
  `employerSocialSecurity` DECIMAL(10, 2) DEFAULT 0.00,
  `paye` DECIMAL(10, 2) DEFAULT 0.00,
  `grossSalary` DECIMAL(10, 2) DEFAULT 0.00,
  `netSalary` DECIMAL(10, 2) DEFAULT 0.00,
  `status` ENUM('Pending', 'Paid') NOT NULL DEFAULT 'Pending',
  `bankName` VARCHAR(100),
  `accountNumber` VARCHAR(100)
);

-- Admin users table
CREATE TABLE `admins` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL
);