schema.sql

-- Database Schema for SWC Scraper
-- Creates tables for storing scraped user data and audit trail

-- Create database (optional - uncomment if needed)
-- CREATE DATABASE IF NOT EXISTS `swc-holiday-spree_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE `swc-holiday-spree_db`;

-- Table: swc_data
-- Stores scraped user data
CREATE TABLE IF NOT EXISTS `swc_data` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY COMMENT 'UUID v7 primary key',
  `id_external` INT NOT NULL UNIQUE COMMENT 'External user ID from API',
  `full_name` VARCHAR(255) NOT NULL COMMENT 'User full name',
  `email_address` VARCHAR(255) NOT NULL COMMENT 'User email address',
  `number_of_entries` INT NOT NULL DEFAULT 0 COMMENT 'Number of entries',
  `accumulated_amount` DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'Accumulated amount',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation timestamp',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Record last update timestamp',

  INDEX `idx_id_external` (`id_external`),
  INDEX `idx_email_address` (`email_address`),
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores scraped user data';

-- Table: audit_trail
-- Tracks all changes to user data for compliance and debugging
CREATE TABLE IF NOT EXISTS `audit_trail` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY COMMENT 'UUID v7 primary key',
  `id_external` INT NOT NULL COMMENT 'External user ID (references swc_data)',
  `field_name` VARCHAR(100) NOT NULL COMMENT 'Name of the field that changed',
  `old_value` TEXT COMMENT 'Previous value of the field',
  `new_value` TEXT COMMENT 'New value of the field',
  `change_type` ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL COMMENT 'Type of change',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of the change',

  INDEX `idx_id_external` (`id_external`),
  INDEX `idx_field_name` (`field_name`),
  INDEX `idx_change_type` (`change_type`),
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_external_field` (`id_external`, `field_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Audit trail for data changes';

-- Add foreign key constraint (optional - uncomment if strict referential integrity needed)
-- ALTER TABLE `audit_trail`
-- ADD CONSTRAINT `fk_audit_external_id`
-- FOREIGN KEY (`id_external`) REFERENCES `swc_data` (`id_external`)
-- ON DELETE CASCADE
-- ON UPDATE CASCADE;