-- ─────────────────────────────────────────────────────────────
--  FormBuilder Auth Migration — run once
--  Creates bk_users and links existing bk_customers
-- ─────────────────────────────────────────────────────────────

-- 1. New auth table
CREATE TABLE IF NOT EXISTS bk_users (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email               VARCHAR(200) NOT NULL UNIQUE,
  password_hash       VARCHAR(255) NOT NULL,
  email_verified_at   DATETIME     DEFAULT NULL,
  remember_token      VARCHAR(100) DEFAULT NULL,
  reset_token         VARCHAR(100) DEFAULT NULL,
  reset_token_expires DATETIME     DEFAULT NULL,
  created_at          DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_reset (reset_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Add user_id FK to bk_customers (nullable = backwards-compatible)
ALTER TABLE bk_customers
  ADD COLUMN IF NOT EXISTS user_id INT UNSIGNED DEFAULT NULL AFTER id;

-- Add FK only if not already present (safe to re-run)
SET @fk_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME        = 'bk_customers'
    AND CONSTRAINT_NAME   = 'fk_customer_user'
);

SET @sql = IF(@fk_exists = 0,
  'ALTER TABLE bk_customers ADD CONSTRAINT fk_customer_user FOREIGN KEY (user_id) REFERENCES bk_users(id) ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
