-- Factur-X Order System Database Schema
-- This schema supports per-customer database isolation

-- Orders table - stores order information
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(50) NOT NULL UNIQUE,
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(50),
    customer_address TEXT,
    customer_siret VARCHAR(50),
    total_amount DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status ENUM('draft', 'paid', 'invoiced') NOT NULL DEFAULT 'draft',
    validated_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_reference (reference),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Order items table - stores individual order items
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    product_reference VARCHAR(100),
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    total_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_order_id (order_id)
);

-- Invoices table - stores invoice information
CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL UNIQUE,
    invoice_number VARCHAR(50) NOT NULL UNIQUE,
    invoice_date DATE NOT NULL,
    due_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status ENUM('draft', 'sent', 'paid') NOT NULL DEFAULT 'draft',
    facturx_file VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_invoice_number (invoice_number),
    INDEX idx_order_id (order_id),
    INDEX idx_status (status)
);

-- Invoice items table - stores invoice line items
CREATE TABLE invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    product_reference VARCHAR(100),
    quantity DECIMAL(10,2) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    total_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    INDEX idx_invoice_id (invoice_id)
);

-- Payment records table - tracks payment information
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    payment_amount DECIMAL(10,2) NOT NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    transaction_id VARCHAR(255),
    status ENUM('pending', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_order_id (order_id),
    INDEX idx_status (status)
);

-- Audit trail table - for security and compliance
CREATE TABLE audit_trail (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    record_id INT NOT NULL,
    action ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    old_values JSON,
    new_values JSON,
    user_id VARCHAR(255),
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_table_record (table_name, record_id),
    INDEX idx_created_at (created_at)
);

-- Triggers for audit trail
DELIMITER //

CREATE TRIGGER orders_audit_insert AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_trail (table_name, record_id, action, new_values, user_id, ip_address, user_agent)
    VALUES ('orders', NEW.id, 'INSERT', JSON_OBJECT(
        'id', NEW.id,
        'reference', NEW.reference,
        'customer_name', NEW.customer_name,
        'status', NEW.status,
        'total_amount', NEW.total_amount
    ), USER(), CONNECTION_ID(), 'SYSTEM');
END//

CREATE TRIGGER orders_audit_update AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_trail (table_name, record_id, action, old_values, new_values, user_id, ip_address, user_agent)
    VALUES ('orders', NEW.id, 'UPDATE', JSON_OBJECT(
        'status', OLD.status,
        'total_amount', OLD.total_amount
    ), JSON_OBJECT(
        'status', NEW.status,
        'total_amount', NEW.total_amount
    ), USER(), CONNECTION_ID(), 'SYSTEM');
END//

DELIMITER ;

-- Prevent deletion of validated orders
DELIMITER //

CREATE TRIGGER prevent_validated_order_deletion BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status IN ('paid', 'invoiced') THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Cannot delete validated order';
    END IF;
END//

DELIMITER ;

-- Sample data for testing
INSERT INTO orders (reference, customer_name, customer_email, total_amount, tax_amount, status) VALUES
('ORD-2024-001', 'John Doe', 'john@example.com', 109.97, 22.00, 'draft'),
('ORD-2024-002', 'Jane Smith', 'jane@example.com', 49.99, 10.00, 'paid');

INSERT INTO order_items (order_id, product_name, quantity, unit_price, tax_rate, total_price) VALUES
(1, 'Product 1', 2, 29.99, 20.00, 59.98),
(1, 'Product 2', 1, 49.99, 20.00, 49.99),
(2, 'Service A', 1, 49.99, 20.00, 49.99); 
