CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    offer_id INT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE NULL,
    status ENUM('draft', 'open', 'paid', 'overdue', 'cancelled') DEFAULT 'draft',
    notes TEXT,
    paid_at DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (customer_id) REFERENCES contacts(id),
    FOREIGN KEY (offer_id) REFERENCES offers(id)
);

CREATE TABLE IF NOT EXISTS invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    article_id INT NULL,
    position_type ENUM('article', 'text') NOT NULL DEFAULT 'article',
    description TEXT NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    unit VARCHAR(50),
    unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    tax_rate_id INT NULL,
    tax_rate_percent DECIMAL(5,2) NOT NULL DEFAULT 19.00,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (tax_rate_id) REFERENCES tax_rates(id)
);

INSERT INTO number_ranges (type, prefix, current_number, padding)
SELECT 'invoice', 'RE-', 0, 5
WHERE NOT EXISTS (SELECT 1 FROM number_ranges WHERE type = 'invoice');

INSERT INTO settings (name, value) VALUES
('invoice_payment_terms', 'Zahlbar innerhalb von 14 Tagen ohne Abzug.'),
('invoice_footer', 'Vielen Dank für Ihren Auftrag.'),
('invoice_place', 'Musterstadt')
ON DUPLICATE KEY UPDATE name = name;
