CREATE DATABASE IF NOT EXISTS shopify_devpos;
USE shopify_devpos;

CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shopify_order_id VARCHAR(50) UNIQUE NOT NULL,
    shopify_order_number VARCHAR(50),
    status ENUM('pending', 'processing', 'fiscalized', 'failed', 'cancelled') DEFAULT 'pending',
    customer_email VARCHAR(255),
    customer_name VARCHAR(255),
    customer_nipt VARCHAR(50),
    total_amount DECIMAL(10, 2),
    currency VARCHAR(10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_shopify_order_id (shopify_order_id),
    INDEX idx_status (status)
);

CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    devpos_invoice_id INT,
    invoice_number VARCHAR(100),
    invoice_type INT DEFAULT 0,
    iic VARCHAR(100),
    eic VARCHAR(100),
    fisc_number VARCHAR(100),
    verification_url TEXT,
    total_price DECIMAL(10, 2),
    total_vat DECIMAL(10, 2),
    total_price_without_vat DECIMAL(10, 2),
    is_electronic BOOLEAN DEFAULT FALSE,
    status ENUM('created', 'sent', 'fiscalized', 'failed') DEFAULT 'created',
    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_order_id (order_id),
    INDEX idx_iic (iic)
);

CREATE TABLE IF NOT EXISTS invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    product_name VARCHAR(255),
    barcode VARCHAR(100),
    quantity DECIMAL(10, 3),
    unit_price DECIMAL(10, 2),
    vat_rate DECIMAL(5, 2) DEFAULT 20.00,
    total_price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    INDEX idx_invoice_id (invoice_id)
);

CREATE TABLE IF NOT EXISTS tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service VARCHAR(50),
    access_token TEXT,
    token_type VARCHAR(50),
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_service (service)
);

CREATE TABLE IF NOT EXISTS webhook_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    webhook_type VARCHAR(100),
    payload JSON,
    headers JSON,
    status ENUM('received', 'processed', 'failed') DEFAULT 'received',
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    INDEX idx_webhook_type (webhook_type),
    INDEX idx_status (status)
);

CREATE TABLE IF NOT EXISTS sync_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sync_type VARCHAR(50),
    start_time TIMESTAMP,
    end_time TIMESTAMP NULL,
    status ENUM('running', 'completed', 'failed') DEFAULT 'running',
    orders_processed INT DEFAULT 0,
    orders_failed INT DEFAULT 0,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
