-- SmartTechSA Quotation System
-- Run this script using MySQL Workbench or the mysql command-line client.

CREATE DATABASE IF NOT EXISTS smarttechsa_quotes
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE smarttechsa_quotes;

CREATE TABLE IF NOT EXISTS customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(150) NOT NULL,
  contact_person VARCHAR(150),
  email VARCHAR(150),
  phone VARCHAR(50),
  vat_number VARCHAR(100),
  address TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  category VARCHAR(100),
  description TEXT,
  unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  vat_rate DECIMAL(5,2) NOT NULL DEFAULT 15.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS quotations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quotation_number VARCHAR(50) NOT NULL UNIQUE,
  customer_id INT NOT NULL,
  quotation_date DATE NOT NULL,
  valid_until DATE,
  status VARCHAR(30) NOT NULL DEFAULT 'Draft',
  subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  discount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  vat DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_quotation_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS quotation_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quotation_id INT NOT NULL,
  product_id INT,
  description TEXT,
  quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  vat_rate DECIMAL(5,2) NOT NULL DEFAULT 15.00,
  total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  CONSTRAINT fk_item_quotation
    FOREIGN KEY (quotation_id) REFERENCES quotations(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_item_product
    FOREIGN KEY (product_id) REFERENCES products(id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(150) NOT NULL DEFAULT 'SmartTechSA',
  company_vat VARCHAR(100) DEFAULT '',
  company_email VARCHAR(150) DEFAULT '',
  company_phone VARCHAR(50) DEFAULT '',
  company_address TEXT,
  company_website VARCHAR(255) DEFAULT '',
  company_registration VARCHAR(100) DEFAULT '',
  bank_name VARCHAR(150) DEFAULT '',
  bank_account_name VARCHAR(150) DEFAULT '',
  bank_account_number VARCHAR(100) DEFAULT '',
  bank_account_type VARCHAR(100) DEFAULT '',
  bank_branch_code VARCHAR(50) DEFAULT '',
  quotation_prefix VARCHAR(20) NOT NULL DEFAULT 'QT',
  quotation_validity INT NOT NULL DEFAULT 30,
  quotation_notes TEXT,
  payment_terms TEXT,
  currency VARCHAR(10) NOT NULL DEFAULT 'ZAR',
  currency_symbol VARCHAR(10) NOT NULL DEFAULT 'R',
  vat_rate DECIMAL(5,2) NOT NULL DEFAULT 15.00,
  vat_enabled BOOLEAN NOT NULL DEFAULT TRUE,
  document_footer TEXT,
  document_terms TEXT,
  document_prepared_by VARCHAR(100) DEFAULT 'Prepared by',
  document_thank_you VARCHAR(255) DEFAULT 'Thank you for your business.',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO settings (
  company_name,
  quotation_prefix,
  quotation_validity,
  currency,
  currency_symbol,
  vat_rate,
  vat_enabled,
  document_prepared_by,
  document_thank_you
)
SELECT
  'SmartTechSA', 'QT', 30, 'ZAR', 'R', 15.00, TRUE,
  'Prepared by', 'Thank you for your business.'
WHERE NOT EXISTS (SELECT 1 FROM settings);