-- Granite Invoicing System - Initial Database Schema

CREATE DATABASE IF NOT EXISTS granite_invoicing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE granite_invoicing;

CREATE TABLE branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(255),
    phone VARCHAR(50)
);

CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(50),
    email VARCHAR(100),
    address VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    unit_price DECIMAL(10,2) NOT NULL,
    is_active TINYINT(1) DEFAULT 1
);

CREATE TABLE quotations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quote_number VARCHAR(30) UNIQUE,
    branch_id INT NOT NULL,
    client_id INT NOT NULL,
    quote_date DATE NOT NULL,
    status ENUM('draft','sent','accepted','rejected') DEFAULT 'draft',
    notes TEXT,
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE quotation_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quotation_id INT NOT NULL,
    product_id INT,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    line_total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (quotation_id) REFERENCES quotations(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(30) UNIQUE,
    branch_id INT NOT NULL,
    client_id INT NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('unpaid','partially_paid','paid','cancelled') DEFAULT 'unpaid',
    source ENUM('direct','quotation','layby') DEFAULT 'direct',
    source_id INT NULL,
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    product_id INT,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    line_total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE invoice_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    payment_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    method ENUM('cash','card','eft','other') DEFAULT 'cash',
    reference VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
);

CREATE TABLE laybys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    layby_number VARCHAR(30) UNIQUE,
    branch_id INT NOT NULL,
    client_id INT NOT NULL,
    start_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    deposit_amount DECIMAL(10,2) NOT NULL,
    status ENUM('active','completed','cancelled','overdue') DEFAULT 'active',
    notes TEXT,
    created_by INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE layby_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    layby_id INT NOT NULL,
    product_id INT,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    line_total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (layby_id) REFERENCES laybys(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE layby_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    layby_id INT NOT NULL,
    payment_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    method ENUM('cash','card','eft','other') DEFAULT 'cash',
    reference VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (layby_id) REFERENCES laybys(id)
);

-- Seed data
INSERT INTO branches (name, address, phone) VALUES
('Head Office', 'Main Branch Address', '010 000 0000'),
('Branch 2', 'Branch 2 Address', '010 000 0001'),
('Branch 3', 'Branch 3 Address', '010 000 0002'),
('Branch 4', 'Branch 4 Address', '010 000 0003');