#!/bin/bash
# Script to generate migration-compatible database SQL file
# This combines the structure from huladb.sql with compatibility ALTER statements

echo "Generating migration-compatible database SQL file..."

# Create the output file with header
cat > migration-compatible-db.sql << 'EOF'
-- phpMyAdmin SQL Dump
-- Migration Compatible Database Structure
-- This file creates a database structure compatible with both old and new database schemas
-- Generated: $(date)
--
-- INSTRUCTIONS:
-- 1. This file contains the complete database structure from huladb.sql
-- 2. Plus ALTER TABLE statements to add missing columns from old-database.sql
-- 3. Run this file to create a database that can accept data from both old and new databases
--

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

EOF

# Extract structure from huladb.sql (CREATE TABLE and ALTER TABLE, but skip INSERT)
echo "Extracting structure from huladb.sql..."
grep -v "^INSERT INTO" huladb.sql | grep -v "^-- Dumping data" >> migration-compatible-db.sql

# Add the compatibility ALTER statements before COMMIT
echo "Adding compatibility ALTER statements..."
sed -i '/^COMMIT;/i\
-- ============================================================\
-- ADD MISSING COLUMNS FROM OLD DATABASE\
-- ============================================================\
\
-- Add missing columns to users table\
ALTER TABLE `users`\
  ADD COLUMN IF NOT EXISTS `password_copy` varchar(191) DEFAULT NULL AFTER `password`,\
  ADD COLUMN IF NOT EXISTS `billing_title` varchar(191) DEFAULT NULL AFTER `updated_at`,\
  ADD COLUMN IF NOT EXISTS `billing_text` text DEFAULT NULL AFTER `billing_title`,\
  ADD COLUMN IF NOT EXISTS `billing_stage` varchar(191) DEFAULT NULL AFTER `billing_text`;\
\
-- Add missing column to investments table\
ALTER TABLE `investments`\
  ADD COLUMN IF NOT EXISTS `reinvested` varchar(191) DEFAULT NULL AFTER `duration`;\
' migration-compatible-db.sql

echo "Migration SQL file created: migration-compatible-db.sql"
echo "You can now import this file to create a compatible database structure."

