CREATE TABLE customers (
    id VARCHAR(50) PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    company VARCHAR(100),
    phone VARCHAR(20),
    country VARCHAR(2),
    language VARCHAR(5)
);

CREATE TABLE orders (
    id VARCHAR(50) PRIMARY KEY,
    reference VARCHAR(50) UNIQUE,
    customer_id VARCHAR(50),
    total DECIMAL(10, 2),
    currency VARCHAR(3),
    order_date DATE,
    payment_method VARCHAR(20),
    invoice_url VARCHAR(255),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) UNIQUE,
    name VARCHAR(255),
    is_subscription BOOLEAN
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(50),
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10, 2),
    subscription_id VARCHAR(50),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE subscriptions (
    id VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    order_id VARCHAR(50),
    product_id INT,
    status VARCHAR(20),
    start_date DATE,
    end_date DATE,
    next_charge_date DATE,
    price DECIMAL(10, 2),
    next_charge_amount DECIMAL(10, 2),
    currency VARCHAR(3),
    interval_unit VARCHAR(10),
    interval_length INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE activation_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_value VARCHAR(100) UNIQUE,
    order_item_id INT,
    application_id VARCHAR(50),
    operating_system VARCHAR(50),
    email VARCHAR(100),
    user_name VARCHAR(100),
    company VARCHAR(100),
    start_date DATE,
    end_date DATE,
    FOREIGN KEY (order_item_id) REFERENCES order_items(id)
);