-- Invoices (simplified B2C) linked to Stripe PaymentIntent

CREATE TABLE IF NOT EXISTS invoice_counters (
  year INT NOT NULL PRIMARY KEY,
  next_seq INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  capsule_id CHAR(36) NOT NULL,

  stripe_payment_intent_id VARCHAR(255) NOT NULL,

  invoice_number VARCHAR(64) NOT NULL,
  issued_at DATETIME NOT NULL,

  currency VARCHAR(16) NOT NULL DEFAULT 'eur',
  amount_net_cents INT NOT NULL,
  amount_vat_cents INT NOT NULL,
  vat_percent DECIMAL(6,3) NOT NULL,
  amount_total_cents INT NOT NULL,

  s3_bucket VARCHAR(255) NOT NULL,
  s3_key VARCHAR(1024) NOT NULL,
  pdf_sha256 CHAR(64) NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uq_invoices_payment_intent (stripe_payment_intent_id),
  UNIQUE KEY uq_invoices_invoice_number (invoice_number),
  KEY idx_invoices_user_id_issued_at (user_id, issued_at),
  KEY idx_invoices_capsule_id (capsule_id),
  CONSTRAINT fk_invoices_capsules FOREIGN KEY (capsule_id) REFERENCES capsules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

