USE comptaai_maroc;

CREATE TABLE IF NOT EXISTS saas_invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subscription_id BIGINT UNSIGNED NOT NULL,
  invoice_number VARCHAR(60) NOT NULL UNIQUE,
  billing_period_start DATE NOT NULL,
  billing_period_end DATE NOT NULL,
  amount_ht DECIMAL(12,2) NOT NULL DEFAULT 0,
  tva_rate DECIMAL(5,2) NOT NULL DEFAULT 20.00,
  tva_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  amount_ttc DECIMAL(12,2) NOT NULL DEFAULT 0,
  due_date DATE NOT NULL,
  status ENUM('draft','unpaid','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY saas_invoices_subscription_index (subscription_id),
  KEY saas_invoices_status_index (status),
  KEY saas_invoices_due_date_index (due_date),
  KEY saas_invoices_period_index (billing_period_start, billing_period_end),
  CONSTRAINT saas_invoices_subscription_fk FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE,
  CONSTRAINT saas_invoices_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS saas_payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  payment_date DATE NOT NULL,
  amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  payment_method ENUM('cash','bank_transfer','card','cheque','other') NOT NULL DEFAULT 'bank_transfer',
  reference VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY saas_payments_invoice_index (invoice_id),
  KEY saas_payments_date_index (payment_date),
  CONSTRAINT saas_payments_invoice_fk FOREIGN KEY (invoice_id) REFERENCES saas_invoices(id) ON DELETE CASCADE,
  CONSTRAINT saas_payments_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;