<?php
header('Content-Type: application/json');

// Database connection
$servername = "localhost";
$username = "dexterso_adhd_lnapp_user";
$password = "M-L8ehOLaFOm";
$dbname = "dexterso_adhd_app";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    echo json_encode([
        'status' => 'error',
        'message' => 'Database connection failed'
    ]);
    exit;
}

// Query to join video_transcriptions and tbl_videos tables
$sql = "SELECT vt.video_id, vt.summary, vt.transcript, les.lesson_name, v.video_id
        FROM video_transcriptions vt 
        INNER JOIN tbl_videos v ON vt.video_id = v.video_id
        INNER JOIN lessons les ON les.lesson_id = v.lesson_id";

$result = $conn->query($sql);

if ($result) {
    $notes = [];
    while ($row = $result->fetch_assoc()) {
        $notes[] = [
            'video_id' => $row['video_id'],
            'lesson_name' => $row['lesson_name'],
            'summary' => $row['summary'],
            'transcript' => $row['transcript']
        ];
    }
    
    echo json_encode([
        'status' => 'success',
        'data' => $notes
    ]);
} else {
    echo json_encode([
        'status' => 'error',
        'message' => 'Failed to fetch notes: ' . $conn->error
    ]);
}

$conn->close();
?> 