-- Account lifecycle tables for NexPanel
-- This file is separate from schema.sql and can be imported manually.

USE nexpanel;

-- =====================================================
-- 1) Renewed accounts log
-- =====================================================
CREATE TABLE IF NOT EXISTS vpn_account_renewals (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  vpn_account_id BIGINT UNSIGNED NOT NULL,

  renewed_by_user_id BIGINT UNSIGNED NOT NULL,
  owner_user_id BIGINT UNSIGNED NULL,

  old_expires_at DATETIME NULL,
  new_expires_at DATETIME NULL,

  old_initial_quota BIGINT UNSIGNED NULL,
  new_initial_quota BIGINT UNSIGNED NULL,

  old_duration_days INT UNSIGNED NULL,
  added_duration_days INT UNSIGNED NULL,
  new_duration_days INT UNSIGNED NULL,

  old_account_cost DECIMAL(18,4) NULL,
  renewal_cost DECIMAL(18,4) NULL,

  notes VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (vpn_account_id) REFERENCES vpn_accounts(id) ON DELETE CASCADE,
  FOREIGN KEY (renewed_by_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_vpn_account_renewals_account ON vpn_account_renewals(vpn_account_id);
CREATE INDEX idx_vpn_account_renewals_user ON vpn_account_renewals(renewed_by_user_id);
CREATE INDEX idx_vpn_account_renewals_created ON vpn_account_renewals(created_at);


-- =====================================================
-- 2) Deleted accounts log (audit trail)
-- =====================================================
CREATE TABLE IF NOT EXISTS vpn_account_deletions (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

  -- Keep a reference when the account row still exists (soft delete),
  -- or store null if the row is physically deleted first.
  vpn_account_id BIGINT UNSIGNED NULL,

  deleted_by_user_id BIGINT UNSIGNED NOT NULL,
  owner_user_id BIGINT UNSIGNED NULL,

  delete_reason VARCHAR(255) NULL,

  -- Snapshot fields for future reporting after hard delete
  account_uuid CHAR(36) NULL,
  username VARCHAR(150) NULL,
  server_id BIGINT UNSIGNED NULL,
  account_type ENUM('normal','test') DEFAULT 'normal',
  status_before_delete VARCHAR(32) NULL,

  initial_quota BIGINT UNSIGNED NULL,
  used_quota BIGINT UNSIGNED NULL,
  expires_at DATETIME NULL,
  account_cost DECIMAL(18,4) NULL,

  snapshot_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (vpn_account_id) REFERENCES vpn_accounts(id) ON DELETE SET NULL,
  FOREIGN KEY (deleted_by_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_vpn_account_deletions_account ON vpn_account_deletions(vpn_account_id);
CREATE INDEX idx_vpn_account_deletions_user ON vpn_account_deletions(deleted_by_user_id);
CREATE INDEX idx_vpn_account_deletions_created ON vpn_account_deletions(created_at);
CREATE INDEX idx_vpn_account_deletions_username ON vpn_account_deletions(username);