CREATE DATABASE IF NOT EXISTS farmacia
  CHARACTER SET utf8
  COLLATE utf8_general_ci;

USE farmacia;

CREATE TABLE IF NOT EXISTS providers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_providers_name (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS source_files (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider_id INT UNSIGNED NOT NULL,
  original_filename VARCHAR(255) NOT NULL,
  stored_filename VARCHAR(255) NOT NULL,
  uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_source_files_provider (provider_id),
  CONSTRAINT fk_source_files_provider
    FOREIGN KEY (provider_id) REFERENCES providers(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider_id INT UNSIGNED NOT NULL,
  source_file_id INT UNSIGNED NOT NULL,
  name_raw TEXT NOT NULL,
  name_norm VARCHAR(255) NOT NULL,
  brand VARCHAR(255) NULL,
  presentation VARCHAR(255) NULL,
  price_usd DECIMAL(12,4) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  sheet_name VARCHAR(255) NULL,
  row_number INT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_products_provider (provider_id),
  KEY idx_products_source_file (source_file_id),
  KEY idx_products_name_norm (name_norm),
  CONSTRAINT fk_products_provider
    FOREIGN KEY (provider_id) REFERENCES providers(id)
    ON DELETE RESTRICT,
  CONSTRAINT fk_products_source_file
    FOREIGN KEY (source_file_id) REFERENCES source_files(id)
    ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  note VARCHAR(500) NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  provider_id INT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  qty INT UNSIGNED NOT NULL,
  unit_price_usd DECIMAL(12,4) NULL,
  line_total_usd DECIMAL(12,4) NULL,
  PRIMARY KEY (id),
  KEY idx_order_items_order (order_id),
  KEY idx_order_items_provider (provider_id),
  CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_order_items_provider
    FOREIGN KEY (provider_id) REFERENCES providers(id)
    ON DELETE RESTRICT,
  CONSTRAINT fk_order_items_product
    FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB;
