clube67.com/backend/schema.sql

-- Clube de Benefícios - Production Schema
-- Generated for MySQL (sis_cb67)

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS benefit_partner_links;
DROP TABLE IF EXISTS benefit_share_requests;
DROP TABLE IF EXISTS webhooks;
DROP TABLE IF EXISTS plugins;
DROP TABLE IF EXISTS logs;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS favorites;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS benefits;
DROP TABLE IF EXISTS partners;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS oauth_accounts;
DROP TABLE IF EXISTS knex_migrations;
DROP TABLE IF EXISTS knex_migrations_lock;
DROP TABLE IF EXISTS users;

SET FOREIGN_KEY_CHECKS = 1;

-- users
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NULL,
  phone VARCHAR(30) NULL,
  cpf VARCHAR(14) UNIQUE NULL DEFAULT NULL,
  city VARCHAR(100) NULL,
  neighborhood VARCHAR(100) NULL,
  gender ENUM('M','F','O') NULL,
  birth_date DATE NULL,
  avatar_url VARCHAR(500) NULL,
  role ENUM('user','partner_admin','secretary','clinical_manager','finance','super_admin') NOT NULL DEFAULT 'user',
  status ENUM('lead','pending','active','inactive') NOT NULL DEFAULT 'active',
  email_verified BOOLEAN DEFAULT FALSE,
  refresh_token VARCHAR(500) NULL,
  last_login TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_role (role),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- oauth_accounts
CREATE TABLE oauth_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  provider VARCHAR(50) NOT NULL,
  provider_user_id VARCHAR(255) NOT NULL,
  access_token VARCHAR(1000) NULL,
  refresh_token VARCHAR(1000) NULL,
  token_expires_at TIMESTAMP NULL,
  profile_data JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_provider_user (provider, provider_user_id),
  INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- categories
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  slug VARCHAR(100) NOT NULL UNIQUE,
  icon VARCHAR(50) NULL,
  color VARCHAR(50) NULL,
  description TEXT NULL,
  active BOOLEAN DEFAULT TRUE,
  sort_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- partners
CREATE TABLE partners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  cnpj VARCHAR(20) UNIQUE NULL DEFAULT NULL,
  email VARCHAR(255) NULL,
  phone VARCHAR(30) NULL,
  address_street VARCHAR(255) NULL,
  address_number VARCHAR(20) NULL,
  address_neighborhood VARCHAR(100) NULL,
  address_city VARCHAR(100) NULL,
  address_state VARCHAR(2) NULL,
  address_zip VARCHAR(10) NULL,
  type VARCHAR(100) NOT NULL,
  monthly_goal INT DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  gradient_from VARCHAR(50) NULL,
  gradient_to VARCHAR(50) NULL,
  icon VARCHAR(10) NULL,
  description TEXT NULL,
  logo_url VARCHAR(500) NULL,
  images JSON NULL,
  domain VARCHAR(255) NULL,
  subdomain VARCHAR(255) NULL,
  subscription_plan ENUM('basic','pro','enterprise') DEFAULT 'basic',
  owner_user_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_slug (slug),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- benefits
CREATE TABLE benefits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  partner_id INT NOT NULL,
  category_id INT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  rules TEXT NULL,
  contact VARCHAR(255) NULL,
  type VARCHAR(50) NOT NULL,
  delivery_type ENUM('local','external') NOT NULL DEFAULT 'local',
  active BOOLEAN DEFAULT TRUE,
  is_global BOOLEAN DEFAULT FALSE,
  approved_by_admin BOOLEAN DEFAULT FALSE,
  global_request_status ENUM('none','pending','approved') DEFAULT 'none',
  priority INT DEFAULT 0,
  discount_percent DECIMAL(5,2) NULL,
  valid_until DATE NULL,
  featured_image VARCHAR(500) NULL,
  redemption_type ENUM('coupon','qrcode','card','app') DEFAULT 'coupon',
  usage_count INT DEFAULT 0,
  savings_amount DECIMAL(12,2) DEFAULT 0,
  badge VARCHAR(50) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_partner (partner_id),
  INDEX idx_category (category_id),
  INDEX idx_active (active),
  INDEX idx_type (type),
  FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- transactions
CREATE TABLE transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  benefit_id INT NOT NULL,
  partner_id INT NOT NULL,
  type ENUM('redemption','view','favorite','share') NOT NULL,
  status ENUM('pending','completed','cancelled','expired') DEFAULT 'pending',
  redemption_code VARCHAR(100) NULL,
  qr_code_data VARCHAR(500) NULL,
  original_value DECIMAL(12,2) NULL,
  discount_value DECIMAL(12,2) NULL,
  redeemed_at TIMESTAMP NULL,
  expires_at TIMESTAMP NULL,
  metadata JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_benefit (benefit_id),
  INDEX idx_status (status),
  INDEX idx_code (redemption_code),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (benefit_id) REFERENCES benefits(id) ON DELETE CASCADE,
  FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- favorites
CREATE TABLE favorites (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  benefit_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_benefit (user_id, benefit_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (benefit_id) REFERENCES benefits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- sessions
CREATE TABLE sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  expires_at TIMESTAMP NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_token (token_hash),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- logs (audit)
CREATE TABLE logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(100) NULL,
  entity_id INT NULL,
  description TEXT NULL,
  old_data JSON NULL,
  new_data JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_action (action),
  INDEX idx_entity (entity_type),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- plugins
CREATE TABLE plugins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  slug VARCHAR(255) NOT NULL UNIQUE,
  version VARCHAR(20) NOT NULL,
  description TEXT NULL,
  author VARCHAR(255) NULL,
  entry_point VARCHAR(255) NOT NULL,
  active BOOLEAN DEFAULT FALSE,
  dependencies JSON NULL,
  config JSON NULL,
  hooks JSON NULL,
  directory VARCHAR(500) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_slug (slug),
  INDEX idx_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- webhooks
CREATE TABLE webhooks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  partner_id INT NULL,
  url VARCHAR(500) NOT NULL,
  secret VARCHAR(255) NULL,
  events JSON NOT NULL,
  active BOOLEAN DEFAULT TRUE,
  retry_count INT DEFAULT 0,
  max_retries INT DEFAULT 3,
  last_triggered TIMESTAMP NULL,
  last_status ENUM('success','failed','pending') NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_partner (partner_id),
  INDEX idx_active (active),
  FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- benefit_share_requests
CREATE TABLE benefit_share_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  benefit_id INT NOT NULL,
  requesting_partner_id INT NOT NULL,
  owner_partner_id INT NOT NULL,
  status ENUM('pending','approved','rejected') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (benefit_id) REFERENCES benefits(id) ON DELETE CASCADE,
  FOREIGN KEY (requesting_partner_id) REFERENCES partners(id) ON DELETE CASCADE,
  FOREIGN KEY (owner_partner_id) REFERENCES partners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- benefit_partner_links
CREATE TABLE benefit_partner_links (
  id INT AUTO_INCREMENT PRIMARY KEY,
  benefit_id INT NOT NULL,
  partner_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_benefit_partner (benefit_id, partner_id),
  FOREIGN KEY (benefit_id) REFERENCES benefits(id) ON DELETE CASCADE,
  FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;