CREATE TABLE IF NOT EXISTS procurement_articles (
  id TEXT PRIMARY KEY,
  reference TEXT NOT NULL UNIQUE,
  description TEXT NOT NULL,
  category TEXT NOT NULL DEFAULT '',
  brand TEXT NOT NULL DEFAULT '',
  manufacturer_reference TEXT NOT NULL DEFAULT '',
  unit TEXT NOT NULL DEFAULT 'U',
  tax_rate_bps INTEGER NOT NULL DEFAULT 2000,
  active INTEGER NOT NULL DEFAULT 1,
  created_by TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS supplier_article_prices (
  id TEXT PRIMARY KEY,
  article_id TEXT NOT NULL,
  supplier_id TEXT NOT NULL,
  unit_price_cents INTEGER NOT NULL,
  currency TEXT NOT NULL DEFAULT 'MAD',
  minimum_quantity REAL NOT NULL DEFAULT 1,
  lead_time_days INTEGER NOT NULL DEFAULT 0,
  valid_from TEXT NOT NULL DEFAULT '',
  valid_until TEXT NOT NULL DEFAULT '',
  homologated INTEGER NOT NULL DEFAULT 0,
  supplier_reference TEXT NOT NULL DEFAULT '',
  notes TEXT NOT NULL DEFAULT '',
  created_by TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (article_id) REFERENCES procurement_articles(id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_supplier_article_price_unique ON supplier_article_prices(article_id,supplier_id);
CREATE INDEX IF NOT EXISTS idx_supplier_article_price_selection ON supplier_article_prices(article_id,homologated,valid_until,unit_price_cents);
CREATE INDEX IF NOT EXISTS idx_procurement_articles_active ON procurement_articles(active,reference);
PRAGMA optimize;