mirror of
https://github.com/myronblair/tomsjavajive-app
synced 2026-06-30 17:50:56 -05:00
97 lines
4.4 KiB
SQL
97 lines
4.4 KiB
SQL
-- Migration: Add tables for push notifications, loyalty program, and integration settings
|
|
-- Run this in phpMyAdmin
|
|
|
|
-- Push notification subscriptions
|
|
CREATE TABLE IF NOT EXISTS `push_subscriptions` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`customer_id` VARCHAR(50) DEFAULT NULL,
|
|
`endpoint` TEXT NOT NULL,
|
|
`p256dh_key` VARCHAR(255) NOT NULL,
|
|
`auth_key` VARCHAR(255) NOT NULL,
|
|
`is_active` TINYINT(1) DEFAULT 1,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_customer` (`customer_id`),
|
|
INDEX `idx_active` (`is_active`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Push notifications queue
|
|
CREATE TABLE IF NOT EXISTS `push_notifications` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`notification_id` VARCHAR(50) NOT NULL UNIQUE,
|
|
`subscription_endpoint` TEXT NOT NULL,
|
|
`payload` TEXT NOT NULL,
|
|
`status` ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
|
|
`error_message` TEXT DEFAULT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`sent_at` TIMESTAMP NULL DEFAULT NULL,
|
|
INDEX `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Loyalty transactions history
|
|
CREATE TABLE IF NOT EXISTS `loyalty_transactions` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`transaction_id` VARCHAR(50) NOT NULL UNIQUE,
|
|
`customer_id` VARCHAR(50) NOT NULL,
|
|
`points` INT NOT NULL,
|
|
`type` ENUM('earn', 'redeem', 'tier_upgrade', 'birthday_bonus', 'referral_bonus', 'referral_welcome', 'adjustment', 'expiry') NOT NULL,
|
|
`description` VARCHAR(255) DEFAULT NULL,
|
|
`reference_amount` DECIMAL(10,2) DEFAULT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX `idx_customer` (`customer_id`),
|
|
INDEX `idx_type` (`type`),
|
|
INDEX `idx_created` (`created_at`),
|
|
FOREIGN KEY (`customer_id`) REFERENCES `customers`(`customer_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Add lifetime_points and loyalty_tier to customers
|
|
ALTER TABLE `customers`
|
|
ADD COLUMN IF NOT EXISTS `lifetime_points` INT DEFAULT 0 AFTER `reward_points`,
|
|
ADD COLUMN IF NOT EXISTS `loyalty_tier` ENUM('bronze', 'silver', 'gold', 'platinum') DEFAULT 'bronze' AFTER `lifetime_points`,
|
|
ADD COLUMN IF NOT EXISTS `birthday` DATE DEFAULT NULL AFTER `loyalty_tier`,
|
|
ADD COLUMN IF NOT EXISTS `referral_code` VARCHAR(20) DEFAULT NULL AFTER `birthday`,
|
|
ADD COLUMN IF NOT EXISTS `referred_by` VARCHAR(50) DEFAULT NULL AFTER `referral_code`;
|
|
|
|
-- Add unique index on referral_code
|
|
ALTER TABLE `customers` ADD UNIQUE INDEX IF NOT EXISTS `idx_referral_code` (`referral_code`);
|
|
|
|
-- Update settings table with integration keys (INSERT IGNORE to not overwrite existing)
|
|
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`, `updated_at`) VALUES
|
|
('sendgrid_api_key', '', NOW()),
|
|
('sendgrid_from_email', 'noreply@tomsjavajive.com', NOW()),
|
|
('sendgrid_from_name', 'Tom''s Java Jive', NOW()),
|
|
('twilio_account_sid', '', NOW()),
|
|
('twilio_auth_token', '', NOW()),
|
|
('twilio_phone_number', '', NOW()),
|
|
('vapid_public_key', '', NOW()),
|
|
('vapid_private_key', '', NOW()),
|
|
('loyalty_enabled', '1', NOW()),
|
|
('email_notifications_enabled', '1', NOW()),
|
|
('sms_notifications_enabled', '0', NOW()),
|
|
('push_notifications_enabled', '1', NOW());
|
|
|
|
-- Add Stripe checkout session column to orders
|
|
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `stripe_checkout_session` VARCHAR(255) DEFAULT NULL AFTER `stripe_payment_intent`;
|
|
|
|
-- Payment transactions table for tracking payment attempts
|
|
CREATE TABLE IF NOT EXISTS `payment_transactions` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`transaction_id` VARCHAR(50) NOT NULL UNIQUE,
|
|
`order_id` VARCHAR(50) NOT NULL,
|
|
`customer_id` VARCHAR(50) DEFAULT NULL,
|
|
`amount` DECIMAL(10,2) NOT NULL,
|
|
`currency` VARCHAR(3) DEFAULT 'USD',
|
|
`payment_method` VARCHAR(50) DEFAULT 'stripe',
|
|
`stripe_session_id` VARCHAR(255) DEFAULT NULL,
|
|
`stripe_payment_intent` VARCHAR(255) DEFAULT NULL,
|
|
`status` ENUM('initiated', 'pending', 'processing', 'succeeded', 'failed', 'cancelled', 'refunded') DEFAULT 'initiated',
|
|
`metadata` JSON DEFAULT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX `idx_order` (`order_id`),
|
|
INDEX `idx_customer` (`customer_id`),
|
|
INDEX `idx_status` (`status`),
|
|
INDEX `idx_stripe_session` (`stripe_session_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|