USE BeezifiIOM;

ALTER TABLE tenants
    ADD COLUMN IF NOT EXISTS base_currency CHAR(3) NOT NULL DEFAULT 'AUD' AFTER name;

CREATE TABLE IF NOT EXISTS exchange_rates (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    base_currency   CHAR(3) NOT NULL,
    target_currency CHAR(3) NOT NULL,
    rate            DECIMAL(20, 8) NOT NULL,
    fetched_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_pair (base_currency, target_currency)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_prices (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id  INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    currency   CHAR(3) NOT NULL,
    price      DECIMAL(15, 4) NOT NULL,
    cost       DECIMAL(15, 4) NULL,
    INDEX idx_tenant (tenant_id),
    UNIQUE KEY uq_product_currency (product_id, currency),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;
