-- Database: invoicepro
CREATE DATABASE IF NOT EXISTS invoicepro CHARACTER SET utf8mb4 COLLATE utf8mb4_bulgarian_ci;
USE invoicepro;

-- Table: companies
CREATE TABLE IF NOT EXISTS companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    eik VARCHAR(20) UNIQUE,
    vat_number VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    country VARCHAR(100),
    phone VARCHAR(50),
    email VARCHAR(100),
    mol VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table: partners
CREATE TABLE IF NOT EXISTS partners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    eik_egn VARCHAR(20),
    vat_number VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    country VARCHAR(100),
    phone VARCHAR(50),
    email VARCHAR(100),
    mol VARCHAR(100),
    is_client BOOLEAN DEFAULT TRUE,
    is_supplier BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- Table: documents
CREATE TABLE IF NOT EXISTS documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    partner_id INT,
    document_type ENUM('invoice', 'proforma', 'credit_note', 'debit_note', 'protocol', 'receipt', 'offer') NOT NULL,
    document_number VARCHAR(50) NOT NULL,
    document_date DATE NOT NULL,
    due_date DATE,
    total_amount DECIMAL(15,2) DEFAULT 0.00,
    vat_amount DECIMAL(15,2) DEFAULT 0.00,
    total_with_vat DECIMAL(15,2) DEFAULT 0.00,
    paid_amount DECIMAL(15,2) DEFAULT 0.00,
    status ENUM('draft', 'sent', 'paid', 'overdue', 'cancelled') DEFAULT 'draft',
    description TEXT,
    notes TEXT,
    payment_method VARCHAR(50),
    bank_account VARCHAR(50),
    currency VARCHAR(3) DEFAULT 'BGN',
    is_deleted BOOLEAN DEFAULT FALSE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE SET NULL,
    INDEX idx_company_type (company_id, document_type),
    INDEX idx_number (document_number),
    INDEX idx_date (document_date),
    INDEX idx_status (status),
    INDEX idx_partner (partner_id)
);

-- Table: document_items
CREATE TABLE IF NOT EXISTS document_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL,
    product_id INT,
    description TEXT NOT NULL,
    quantity DECIMAL(10,3) NOT NULL DEFAULT 1.000,
    unit_price DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    discount_percent DECIMAL(5,2) DEFAULT 0.00,
    vat_rate DECIMAL(5,2) DEFAULT 20.00,
    total_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    vat_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    total_with_vat DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    INDEX idx_document (document_id)
);

-- Table: products
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    code VARCHAR(100),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    unit VARCHAR(20) DEFAULT 'бр.',
    purchase_price DECIMAL(15,4),
    sale_price DECIMAL(15,4),
    vat_rate DECIMAL(5,2) DEFAULT 20.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company_code (company_id, code),
    INDEX idx_name (name)
);

-- Table: document_series
CREATE TABLE IF NOT EXISTS document_series (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    document_type ENUM('invoice', 'proforma', 'credit_note', 'debit_note', 'protocol', 'receipt', 'offer') NOT NULL,
    prefix VARCHAR(20) DEFAULT '',
    number_length INT DEFAULT 6,
    current_number INT DEFAULT 1,
    reset_yearly BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_series (company_id, document_type)
);

-- Insert sample data
INSERT INTO companies (name, eik, city, mol) VALUES 
('КОМПЮТЪР ДОКТОРС - ЕООД', '123456789', 'София', 'Станислав Джингов');

INSERT INTO document_series (company_id, document_type, prefix) VALUES 
(1, 'invoice', 'Ф'),
(1, 'proforma', 'ПФ'),
(1, 'credit_note', 'КИ'),
(1, 'debit_note', 'ДИ'),
(1, 'protocol', 'ПР'),
(1, 'receipt', 'РР'),
(1, 'offer', 'ОФ');