-- Billing (Bachs.io). The backend is the only authority for subscription status.

CREATE TABLE plans (
  code VARCHAR(32) PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  price_amount DECIMAL(10,2) NOT NULL,
  currency CHAR(3) NOT NULL,
  billing_interval ENUM('month','year') NOT NULL,
  billing_frequency INT NOT NULL DEFAULT 1,
  bachs_product_id_sandbox VARCHAR(64) NULL,
  bachs_product_id_live VARCHAR(64) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO plans (code, name, price_amount, currency, billing_interval, billing_frequency, sort_order, updated_at) VALUES
  ('monthly', 'HiWhatsApp AI Assistant – Evidence, Contacts & Messaging Premium – Monthly', 5.99, 'USD', 'month', 1, 1, UTC_TIMESTAMP()),
  ('yearly',  'HiWhatsApp AI Assistant – Evidence, Contacts & Messaging Premium – Yearly',  49.00, 'USD', 'year',  1, 2, UTC_TIMESTAMP());

CREATE TABLE billing_customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  environment ENUM('sandbox','live') NOT NULL,
  bachs_customer_id VARCHAR(64) NOT NULL,
  email VARCHAR(254) NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_bc_customer (environment, bachs_customer_id),
  UNIQUE KEY uq_bc_user (environment, user_id),
  CONSTRAINT fk_bc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE checkout_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  environment ENUM('sandbox','live') NOT NULL,
  plan_code VARCHAR(32) NOT NULL,
  bachs_checkout_id VARCHAR(64) NOT NULL,
  checkout_url VARCHAR(1024) NOT NULL,
  return_ref CHAR(24) NOT NULL,
  status ENUM('open','completed','expired','abandoned','failed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  expires_at DATETIME NULL,
  UNIQUE KEY uq_checkout (environment, bachs_checkout_id),
  UNIQUE KEY uq_checkout_ref (return_ref),
  KEY ix_checkout_user (user_id, status),
  CONSTRAINT fk_checkout_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  environment ENUM('sandbox','live') NOT NULL,
  bachs_subscription_id VARCHAR(64) NOT NULL,
  bachs_customer_id VARCHAR(64) NULL,
  bachs_product_id VARCHAR(64) NULL,
  plan_code VARCHAR(32) NULL,
  provider_status VARCHAR(32) NOT NULL,
  cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0,
  current_period_start DATETIME NULL,
  current_period_end DATETIME NULL,
  canceled_at DATETIME NULL,
  ended_reason VARCHAR(64) NULL,
  refunded_at DATETIME NULL,
  first_paid_at DATETIME NULL,
  last_paid_at DATETIME NULL,
  last_payment_failed_at DATETIME NULL,
  expired_at DATETIME NULL,
  provider_updated_at DATETIME NULL,
  last_synced_at DATETIME NULL,
  raw_json JSON NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_sub (environment, bachs_subscription_id),
  KEY ix_sub_user (user_id),
  KEY ix_sub_status (provider_status, current_period_end),
  CONSTRAINT fk_sub_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  subscription_id BIGINT UNSIGNED NULL,
  environment ENUM('sandbox','live') NOT NULL,
  kind ENUM('invoice_paid','invoice_failed','refund') NOT NULL,
  provider_ref VARCHAR(64) NOT NULL,
  provider_invoice_id VARCHAR(64) NULL,
  provider_payment_id VARCHAR(64) NULL,
  amount DECIMAL(12,2) NULL,
  currency CHAR(3) NULL,
  status VARCHAR(32) NOT NULL,
  failure_reason VARCHAR(255) NULL,
  occurred_at DATETIME NOT NULL,
  source_event_id VARCHAR(64) NULL,
  raw_json JSON NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_payment (environment, kind, provider_ref),
  KEY ix_payments_user (user_id, occurred_at),
  KEY ix_payments_kind (kind, occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE webhook_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider ENUM('bachs','meta') NOT NULL,
  environment VARCHAR(16) NULL,
  event_id VARCHAR(128) NOT NULL,
  type VARCHAR(80) NOT NULL,
  provider_created_at DATETIME NULL,
  received_at DATETIME NOT NULL,
  processed_at DATETIME NULL,
  status ENUM('received','processed','ignored','failed') NOT NULL DEFAULT 'received',
  attempts INT NOT NULL DEFAULT 0,
  error TEXT NULL,
  payload JSON NOT NULL,
  UNIQUE KEY uq_event (provider, event_id),
  KEY ix_event_type (type, received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE usage_counters (
  user_id BIGINT UNSIGNED NOT NULL,
  feature VARCHAR(48) NOT NULL,
  period CHAR(7) NOT NULL,
  used INT UNSIGNED NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (user_id, feature, period),
  CONSTRAINT fk_usage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE usage_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  feature VARCHAR(48) NOT NULL,
  requested INT UNSIGNED NOT NULL,
  granted INT UNSIGNED NOT NULL,
  client_ref VARCHAR(64) NULL,
  plan_state VARCHAR(24) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_usage_client_ref (user_id, feature, client_ref),
  KEY ix_usage_events_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
