-- Nur verwenden, falls du diese Tabellen/Spalten noch nicht angelegt hast.

CREATE TABLE IF NOT EXISTS tax_rates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    rate DECIMAL(5,2) NOT NULL,
    active TINYINT(1) DEFAULT 1
);

INSERT INTO tax_rates (name, rate, active)
SELECT 'Standard 19%', 19.00, 1
WHERE NOT EXISTS (SELECT 1 FROM tax_rates WHERE rate = 19.00);

INSERT INTO tax_rates (name, rate, active)
SELECT 'Ermäßigt 7%', 7.00, 1
WHERE NOT EXISTS (SELECT 1 FROM tax_rates WHERE rate = 7.00);

INSERT INTO tax_rates (name, rate, active)
SELECT 'Steuerfrei 0%', 0.00, 1
WHERE NOT EXISTS (SELECT 1 FROM tax_rates WHERE rate = 0.00);

ALTER TABLE articles
ADD COLUMN tax_rate_id INT NULL AFTER tax_rate;

CREATE TABLE IF NOT EXISTS offers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    offer_number VARCHAR(50) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    offer_date DATE NOT NULL,
    valid_until DATE NULL,
    status ENUM('draft', 'sent', 'accepted', 'rejected') DEFAULT 'draft',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (customer_id) REFERENCES contacts(id)
);

CREATE TABLE IF NOT EXISTS offer_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    offer_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 (offer_id) REFERENCES offers(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (tax_rate_id) REFERENCES tax_rates(id)
);

CREATE TABLE IF NOT EXISTS number_ranges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(50) NOT NULL UNIQUE,
    prefix VARCHAR(20) NOT NULL,
    current_number INT NOT NULL DEFAULT 0,
    padding INT NOT NULL DEFAULT 5
);

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