-- Furnify customer account system migration
-- Import this if you already installed a previous Furnify database.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS customers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  full_name VARCHAR(140) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  city VARCHAR(140) NULL,
  address VARCHAR(255) NULL,
  email_verified_at DATETIME NULL,
  phone_verified_at DATETIME NULL,
  email_verification_token_hash VARCHAR(255) NULL,
  email_verification_expires_at DATETIME NULL,
  reset_token_hash VARCHAR(255) NULL,
  reset_expires_at DATETIME NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_customers_email (email),
  INDEX idx_customers_status (status),
  INDEX idx_customers_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE orders ADD COLUMN customer_id INT UNSIGNED NULL AFTER id;
ALTER TABLE orders ADD INDEX idx_orders_customer (customer_id);
ALTER TABLE orders ADD INDEX idx_orders_email (customer_email);

ALTER TABLE reup_requests ADD COLUMN customer_id INT UNSIGNED NULL AFTER id;
ALTER TABLE reup_requests ADD INDEX idx_reup_customer_id (customer_id);
ALTER TABLE reup_requests ADD INDEX idx_reup_email (customer_email);

ALTER TABLE custom_sofa_quotes ADD COLUMN customer_id INT UNSIGNED NULL AFTER id;
ALTER TABLE custom_sofa_quotes ADD INDEX idx_custom_customer_id (customer_id);
ALTER TABLE custom_sofa_quotes ADD INDEX idx_custom_email (customer_email);
