CREATE DATABASE quote_invoice_db;
USE quote_invoice_db;

-- Users with roles
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'sales', 'accountant', 'viewer') DEFAULT 'sales',
    full_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Company Settings (for customization)
CREATE TABLE company_settings (
    id INT PRIMARY KEY DEFAULT 1,
    company_name VARCHAR(200),
    logo_url VARCHAR(255),
    address TEXT,
    phone VARCHAR(50),
    email VARCHAR(100),
    website VARCHAR(100),
    primary_color VARCHAR(7) DEFAULT '#007bff',
    currency VARCHAR(10) DEFAULT 'KES'
);

-- Customers
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(50),
    address TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Quotations
CREATE TABLE quotations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quote_number VARCHAR(50) UNIQUE,
    customer_id INT,
    user_id INT,
    date DATE,
    expiry_date DATE,
    subtotal DECIMAL(15,2),
    tax DECIMAL(15,2) DEFAULT 0,
    total DECIMAL(15,2),
    status ENUM('draft', 'sent', 'accepted', 'expired') DEFAULT 'draft',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Quotation Items
CREATE TABLE quote_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quote_id INT,
    description TEXT,
    quantity INT,
    unit_price DECIMAL(15,2),
    total DECIMAL(15,2),
    FOREIGN KEY (quote_id) REFERENCES quotations(id) ON DELETE CASCADE
);

-- Invoices (similar to quotations, with payment fields)
CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) UNIQUE,
    quote_id INT NULL,
    customer_id INT,
    user_id INT,
    date DATE,
    due_date DATE,
    subtotal DECIMAL(15,2),
    tax DECIMAL(15,2) DEFAULT 0,
    total DECIMAL(15,2),
    amount_paid DECIMAL(15,2) DEFAULT 0,
    status ENUM('draft', 'sent', 'paid', 'overdue', 'cancelled') DEFAULT 'draft',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE invoice_items ( ... similar to quote_items ... );

CREATE TABLE IF NOT EXISTS quote_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quote_id INT,
    description TEXT,
    quantity INT,
    unit_price DECIMAL(15,2),
    total DECIMAL(15,2),
    FOREIGN KEY (quote_id) REFERENCES quotations(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT,
    description TEXT,
    quantity INT,
    unit_price DECIMAL(15,2),
    total DECIMAL(15,2),
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
);
-- Add role column to users if it doesn't exist
ALTER TABLE users ADD COLUMN IF NOT EXISTS role VARCHAR(20) DEFAULT 'user';

-- Set your main user as admin (replace with your admin email)
UPDATE users SET role = 'admin' WHERE email = 'admin@example.com';

-- Ensure status supports revenue tracking states (e.g., 'paid', 'sent', 'draft')
ALTER TABLE quotations MODIFY COLUMN status VARCHAR(50) DEFAULT 'draft';
ALTER TABLE invoices MODIFY COLUMN status VARCHAR(50) DEFAULT 'draft';
-- Ensure created_at columns exist on quotations and invoices
ALTER TABLE quotations ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE quotations ADD COLUMN customer_name VARCHAR(255) DEFAULT 'Walk-in';
ALTER TABLE quote_items ADD COLUMN code VARCHAR(100) AFTER quote_id;
-- Create Credit Notes Table
CREATE TABLE IF NOT EXISTS credit_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    credit_note_number VARCHAR(100) UNIQUE NOT NULL,
    invoice_id INT,
    customer_name VARCHAR(255) NOT NULL,
    date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    reason TEXT,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL
);

-- Ensure Invoices table has balance tracking fields if not already present
ALTER TABLE invoices ADD COLUMN IF NOT EXISTS balance DECIMAL(10,2) DEFAULT 0.00;
ALTER TABLE invoices 
ADD COLUMN customer_email VARCHAR(255) NULL,
ADD COLUMN customer_phone VARCHAR(50) NULL,
ADD COLUMN customer_address TEXT NULL,
ADD COLUMN buyers_order_no VARCHAR(100) NULL,
ADD COLUMN terms_of_delivery VARCHAR(100) NULL;
ALTER TABLE invoice_items ADD COLUMN code VARCHAR(50) DEFAULT NULL;

ALTER TABLE company_settings 
ADD COLUMN pin VARCHAR(100) DEFAULT NULL,
ADD COLUMN vat_pin VARCHAR(100) DEFAULT NULL,
ADD COLUMN currency VARCHAR(50) DEFAULT 'KES',
ADD COLUMN logo VARCHAR(255) DEFAULT NULL;

ALTER TABLE company_settings 
ADD COLUMN bank_name VARCHAR(100) DEFAULT NULL,
ADD COLUMN account_name VARCHAR(100) DEFAULT NULL,
ADD COLUMN account_number VARCHAR(100) DEFAULT NULL,
ADD COLUMN branch_name VARCHAR(100) DEFAULT NULL,
ADD COLUMN mpesa_details VARCHAR(255) DEFAULT NULL;
CREATE TABLE IF NOT EXISTS inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_code VARCHAR(50) UNIQUE NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    description TEXT,
    quantity INT NOT NULL DEFAULT 0,
    unit_price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS inventory_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_code VARCHAR(50),
    item_name VARCHAR(255) NOT NULL,
    description TEXT,
    stock_quantity INT DEFAULT 0,
    unit_price DECIMAL(12,2) DEFAULT 0.00,
    cost_price DECIMAL(12,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS inventory_returns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    customer_id INT NULL,
    quantity INT DEFAULT 1,
    reason VARCHAR(255),
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);