create_sleep_db.py

#!/usr/bin/env python3
"""
Create sleep database tables in Cloud SQL
"""
import os
import pymysql
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

# Cloud SQL connection details
HOST = "34.22.107.76"  # Primary address from gcloud sql instances list
PORT = 3306
USER = "root"
PASSWORD = ""  # No password set
DATABASE = "sleep"

def create_tables():
    """Create all required tables in the sleep database"""
    
    # SQL statements for creating tables
    create_tables_sql = [
        """
        USE `sleep`;
        """,
        """
        CREATE TABLE IF NOT EXISTS `sleep_sessions` (
            `sleep_session_no` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_no` BIGINT NOT NULL,
            `finished_at` DATETIME NULL,
            `created_at` DATETIME NOT NULL,
            INDEX `idx_sleep_sessions_user_no` (`user_no`),
            INDEX `idx_sleep_sessions_created_at` (`created_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        """
        CREATE TABLE IF NOT EXISTS `daily_reports` (
            `sleep_session_no` BIGINT PRIMARY KEY,
            `memo` VARCHAR(255) NULL,
            `user_no` BIGINT NOT NULL,
            `created_at` DATETIME NOT NULL,
            FOREIGN KEY (`sleep_session_no`) REFERENCES `sleep_sessions`(`sleep_session_no`) ON DELETE CASCADE,
            INDEX `idx_daily_reports_user_no` (`user_no`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        """
        CREATE TABLE IF NOT EXISTS `sleep_time_details` (
            `sleep_session_no` BIGINT PRIMARY KEY,
            `deep_sleep_minutes` SMALLINT UNSIGNED NOT NULL,
            `light_sleep_minutes` SMALLINT UNSIGNED NOT NULL,
            `rem_sleep_minutes` SMALLINT UNSIGNED NOT NULL,
            `deep_sleep_ratio` DECIMAL(4,1) NOT NULL,
            `light_sleep_ratio` DECIMAL(4,1) NOT NULL,
            `rem_sleep_ratio` DECIMAL(4,1) NOT NULL,
            FOREIGN KEY (`sleep_session_no`) REFERENCES `daily_reports`(`sleep_session_no`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        """
        CREATE TABLE IF NOT EXISTS `analysis_details` (
            `analysis_detail_no` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `sleep_session_no` BIGINT NOT NULL,
            `title` VARCHAR(255) NOT NULL,
            `description` VARCHAR(255) NOT NULL,
            `difficulty` ENUM('EASY', 'MEDIUM', 'HARD') NOT NULL,
            `effect` ENUM('LOW', 'MEDIUM', 'HIGH') NOT NULL,
            FOREIGN KEY (`sleep_session_no`) REFERENCES `daily_reports`(`sleep_session_no`) ON DELETE CASCADE,
            INDEX `idx_analysis_details_sleep_session_no` (`sleep_session_no`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        """
        CREATE TABLE IF NOT EXISTS `analysis_steps` (
            `analysis_step_no` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `analysis_detail_no` BIGINT NOT NULL,
            `step_index` SMALLINT UNSIGNED NOT NULL,
            `content` VARCHAR(255) NOT NULL,
            FOREIGN KEY (`analysis_detail_no`) REFERENCES `analysis_details`(`analysis_detail_no`) ON DELETE CASCADE,
            INDEX `idx_analysis_steps_analysis_detail_no` (`analysis_detail_no`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        """
        CREATE TABLE IF NOT EXISTS `periodic_reports` (
            `periodic_report_no` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `user_no` BIGINT NOT NULL,
            `sleep_session_count` SMALLINT UNSIGNED NOT NULL,
            `duration_type` ENUM('WEEKLY', 'MONTHLY') NOT NULL,
            `period_started_at` DATE NOT NULL,
            `total_score` SMALLINT UNSIGNED NOT NULL,
            `total_sleep_time` SMALLINT UNSIGNED NOT NULL,
            `total_bed_time_minutes` SMALLINT UNSIGNED NOT NULL,
            `total_deep_sleep_time_minutes` SMALLINT UNSIGNED NOT NULL,
            `total_light_sleep_time_minutes` SMALLINT UNSIGNED NOT NULL,
            `total_rem_sleep_time_minutes` SMALLINT UNSIGNED NOT NULL,
            `improvement` VARCHAR(500) NULL,
            `weakness` VARCHAR(500) NULL,
            `recommendation` VARCHAR(500) NULL,
            `score_prediction_description` VARCHAR(500) NULL,
            INDEX `idx_periodic_reports_user_no` (`user_no`),
            INDEX `idx_periodic_reports_period_started_at` (`period_started_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        """
        CREATE TABLE IF NOT EXISTS `score_prediction_points` (
            `score_prediction_point_no` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `periodic_report_no` BIGINT NOT NULL,
            `date_index` DATE NOT NULL,
            `score` SMALLINT NOT NULL,
            FOREIGN KEY (`periodic_report_no`) REFERENCES `periodic_reports`(`periodic_report_no`) ON DELETE CASCADE,
            INDEX `idx_score_prediction_points_periodic_report_no` (`periodic_report_no`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        """
        CREATE TABLE IF NOT EXISTS `analyzed_sound_events` (
            `analyzed_sound_event_no` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `sleep_session_no` BIGINT NULL,
            `event` ENUM('SNORE', 'BABY_CRYING', 'COUGH', 'MOUTH_BREATHING', 'ANIMAL_NOISE', 'CAR_HORN') NULL,
            `recorded_at` DATETIME NOT NULL,
            FOREIGN KEY (`sleep_session_no`) REFERENCES `sleep_sessions`(`sleep_session_no`),
            INDEX `idx_analyzed_sound_events_sleep_session_no` (`sleep_session_no`),
            INDEX `idx_analyzed_sound_events_recorded_at` (`recorded_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        """
        CREATE TABLE IF NOT EXISTS `analyzed_sleep_levels` (
            `analyzed_sleep_level_no` BIGINT AUTO_INCREMENT PRIMARY KEY,
            `sleep_session_no` BIGINT NULL,
            `level` SMALLINT UNSIGNED NULL,
            `recorded_at` DATETIME NOT NULL,
            FOREIGN KEY (`sleep_session_no`) REFERENCES `sleep_sessions`(`sleep_session_no`),
            CONSTRAINT `chk_analyzed_sleep_levels_level` CHECK (`level` >= 0 AND `level` <= 6),
            UNIQUE KEY `uq_analyzed_sleep_levels_session_time` (`sleep_session_no`, `recorded_at`),
            INDEX `idx_analyzed_sleep_levels_sleep_session_no` (`sleep_session_no`),
            INDEX `idx_analyzed_sleep_levels_recorded_at` (`recorded_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
    ]
    
    try:
        # Connect to Cloud SQL
        connection = pymysql.connect(
            host=HOST,
            port=PORT,
            user=USER,
            password=PASSWORD,
            database=DATABASE,
            charset='utf8mb4',
            ssl_disabled=True  # For Cloud SQL, SSL might not be required for internal connections
        )
        
        print(f"Connected to Cloud SQL instance: {HOST}")
        
        with connection.cursor() as cursor:
            for i, sql in enumerate(create_tables_sql, 1):
                try:
                    print(f"Executing SQL statement {i}...")
                    cursor.execute(sql)
                    connection.commit()
                    print(f"✓ SQL statement {i} executed successfully")
                except Exception as e:
                    print(f"✗ Error executing SQL statement {i}: {e}")
                    # Continue with other statements
                    continue
        
        # Show created tables
        print("\nChecking created tables...")
        cursor.execute("SHOW TABLES;")
        tables = cursor.fetchall()
        print(f"Created {len(tables)} tables:")
        for table in tables:
            print(f"  - {table[0]}")
            
    except Exception as e:
        print(f"Error connecting to database: {e}")
        return False
    finally:
        if 'connection' in locals():
            connection.close()
            print("Database connection closed.")
    
    return True

if __name__ == "__main__":
    print("Creating sleep database tables in Cloud SQL...")
    success = create_tables()
    if success:
        print("✓ Database tables created successfully!")
    else:
        print("✗ Failed to create database tables.")