-- Purchase Quotes base tables
CREATE TABLE IF NOT EXISTS purchase_quotes (
  id SERIAL PRIMARY KEY,
  supplier_id INTEGER NOT NULL REFERENCES suppliers(id),
  valid_until DATE NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'open',
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS purchase_quote_items (
  id SERIAL PRIMARY KEY,
  quote_id INTEGER NOT NULL REFERENCES purchase_quotes(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity NUMERIC(14,3) NOT NULL DEFAULT 1,
  price NUMERIC(14,4) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_purchase_quote_items_quote ON purchase_quote_items(quote_id);
CREATE INDEX IF NOT EXISTS idx_purchase_quote_items_product ON purchase_quote_items(product_id);

-- Convites de cotação enviados a fornecedores (com token de resposta)
CREATE TABLE IF NOT EXISTS purchase_quote_invites (
  id SERIAL PRIMARY KEY,
  quote_id INTEGER NOT NULL REFERENCES purchase_quotes(id) ON DELETE CASCADE,
  supplier_id INTEGER NOT NULL REFERENCES suppliers(id),
  email VARCHAR(255) NOT NULL,
  token VARCHAR(100) NOT NULL UNIQUE,
  status VARCHAR(20) NOT NULL DEFAULT 'sent', -- sent, opened, responded, expired, cancelled
  opened_at TIMESTAMP NULL,
  responded_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_purchase_quote_invites_quote ON purchase_quote_invites(quote_id);
CREATE INDEX IF NOT EXISTS idx_purchase_quote_invites_supplier ON purchase_quote_invites(supplier_id);
CREATE INDEX IF NOT EXISTS idx_purchase_quote_invites_status ON purchase_quote_invites(status);

-- Respostas de fornecedores às cotações (payload simples por enquanto)
CREATE TABLE IF NOT EXISTS purchase_quote_responses (
  id SERIAL PRIMARY KEY,
  invite_id INTEGER NOT NULL REFERENCES purchase_quote_invites(id) ON DELETE CASCADE,
  quote_id INTEGER NOT NULL REFERENCES purchase_quotes(id) ON DELETE CASCADE,
  supplier_id INTEGER NOT NULL REFERENCES suppliers(id),
  response_json JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_purchase_quote_responses_quote ON purchase_quote_responses(quote_id);
CREATE INDEX IF NOT EXISTS idx_purchase_quote_responses_supplier ON purchase_quote_responses(supplier_id);