-- Merchant organization model: organization → memberships → locations.

CREATE TABLE IF NOT EXISTS merchant_organizations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(80) NOT NULL UNIQUE,
  tax_id VARCHAR(60) NULL,
  website VARCHAR(255) NULL,
  status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_mo_slug (slug),
  INDEX idx_mo_status (status)
);

CREATE TABLE IF NOT EXISTS merchant_memberships (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  role ENUM('owner','admin','staff') NOT NULL DEFAULT 'staff',
  invited_by BIGINT UNSIGNED NULL,
  invitation_token VARCHAR(128) NULL UNIQUE,
  invitation_expires_at DATETIME NULL,
  accepted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_mm_org_user (organization_id, user_id),
  FOREIGN KEY (organization_id) REFERENCES merchant_organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES merchant_users(id) ON DELETE CASCADE,
  FOREIGN KEY (invited_by) REFERENCES merchant_users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS merchant_locations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  organization_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  location_code VARCHAR(40) NOT NULL UNIQUE,
  address_line1 VARCHAR(200) NULL,
  address_city VARCHAR(100) NULL,
  address_state VARCHAR(50) NULL,
  address_zip VARCHAR(20) NULL,
  address_country CHAR(2) NOT NULL DEFAULT 'US',
  phone VARCHAR(30) NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (organization_id) REFERENCES merchant_organizations(id) ON DELETE CASCADE,
  INDEX idx_ml_org (organization_id),
  INDEX idx_ml_code (location_code)
)
