// ================== TASK 4 ==================
// Community Herb Garden Application

// ================== DATABASE SCHEMA ==================

// Table: herbs
CREATE TABLE herbs (
    herb_id INT AUTO_INCREMENT PRIMARY KEY,
    herb_name VARCHAR(100) NOT NULL UNIQUE,
    scientific_name VARCHAR(150),
    description TEXT,
    is_medicinal BOOLEAN DEFAULT FALSE,
    is_culinary BOOLEAN DEFAULT FALSE
);

// Table: households
CREATE TABLE households (
    household_id INT AUTO_INCREMENT PRIMARY KEY,
    household_name VARCHAR(100) NOT NULL,
    contact_email VARCHAR(100),
    contact_phone VARCHAR(20),
    address TEXT
);

// Table: herb_interests
CREATE TABLE herb_interests (
    interest_id INT AUTO_INCREMENT PRIMARY KEY,
    household_id INT,
    herb_id INT,
    registered_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (household_id) REFERENCES households(household_id) ON DELETE CASCADE,
    FOREIGN KEY (herb_id) REFERENCES herbs(herb_id) ON DELETE CASCADE,
    UNIQUE KEY unique_household_herb (household_id, herb_id)
);

// ================== CONFIGURATION (config.php) ==================

define('DB_HOST', 'localhost');
define('DB_USER', 'your_db_username');
define('DB_PASS', 'your_db_password');
define('DB_NAME', 'your_db_name');

function getDBConnection() {
    try {
        $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        if ($conn->connect_error) {
            throw new Exception("Connection failed: " . $conn->connect_error);
        }
        return $conn;
    } catch (Exception $e) {
        die("Database connection error: " . $e->getMessage());
    }
}

// ================== HERBS CONTROLLER (herbs_controller.php) ==================

// Add Herb
if (isset($_POST['add_herb'])) {
    $name = $_POST['herb_name'];
    $scientific = $_POST['scientific_name'];
    $description = $_POST['description'];
    $medicinal = isset($_POST['is_medicinal']) ? 1 : 0;
    $culinary = isset($_POST['is_culinary']) ? 1 : 0;
    
    $stmt = $conn->prepare("INSERT INTO herbs (herb_name, scientific_name, description, is_medicinal, is_culinary) VALUES (?, ?, ?, ?, ?)");
    $stmt->bind_param("sssii", $name, $scientific, $description, $medicinal, $culinary);
    $stmt->execute();
    $stmt->close();
}

// Delete Herb
if (isset($_GET['delete_id'])) {
    $id = $_GET['delete_id'];
    $stmt = $conn->prepare("DELETE FROM herbs WHERE herb_id = ?");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $stmt->close();
}

// Update Herb
if (isset($_POST['update_herb'])) {
    $id = $_POST['herb_id'];
    $name = $_POST['herb_name'];
    $scientific = $_POST['scientific_name'];
    $description = $_POST['description'];
    $medicinal = isset($_POST['is_medicinal']) ? 1 : 0;
    $culinary = isset($_POST['is_culinary']) ? 1 : 0;
    
    $stmt = $conn->prepare("UPDATE herbs SET herb_name=?, scientific_name=?, description=?, is_medicinal=?, is_culinary=? WHERE herb_id=?");
    $stmt->bind_param("sssiii", $name, $scientific, $description, $medicinal, $culinary, $id);
    $stmt->execute();
    $stmt->close();
}

// View Herbs
$result = $conn->query("SELECT * FROM herbs ORDER BY herb_name");
while ($row = $result->fetch_assoc()) {
    // Display herb data
}

// ================== INTERESTS CONTROLLER (interests_controller.php) ==================

// Add Interest
if (isset($_POST['add_interest'])) {
    $householdName = $_POST['household_name'];
    $contactEmail = $_POST['contact_email'];
    $contactPhone = $_POST['contact_phone'];
    $address = $_POST['address'];
    $herbIds = isset($_POST['herb_ids']) ? $_POST['herb_ids'] : [];
    
    if (count($herbIds) == 3) {
        $conn->begin_transaction();
        
        // Insert household
        $stmt = $conn->prepare("INSERT INTO households (household_name, contact_email, contact_phone, address) VALUES (?, ?, ?, ?)");
        $stmt->bind_param("ssss", $householdName, $contactEmail, $contactPhone, $address);
        $stmt->execute();
        $householdId = $conn->insert_id;
        $stmt->close();
        
        // Insert interests
        foreach ($herbIds as $herbId) {
            $stmt = $conn->prepare("INSERT INTO herb_interests (household_id, herb_id) VALUES (?, ?)");
            $stmt->bind_param("ii", $householdId, $herbId);
            $stmt->execute();
            $stmt->close();
        }
        
        $conn->commit();
    }
}

// Delete Interest
if (isset($_GET['delete_id'])) {
    $id = $_GET['delete_id'];
    $stmt = $conn->prepare("DELETE FROM herb_interests WHERE interest_id = ?");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $stmt->close();
}

// View Interests
$interestsQuery = "
    SELECT hi.interest_id, hh.household_name, hh.contact_email, h.herb_name
    FROM herb_interests hi
    JOIN households hh ON hi.household_id = hh.household_id
    JOIN herbs h ON hi.herb_id = h.herb_id
    ORDER BY hh.household_name, h.herb_name
";
$interestsResult = $conn->query($interestsQuery);

// ================== SUMMARY VIEW (summary_view.php) ==================

// Total households
$householdsResult = $conn->query("SELECT COUNT(*) as total FROM households");
$totalHouseholds = $householdsResult->fetch_assoc()['total'];

// Total herbs
$herbsResult = $conn->query("SELECT COUNT(*) as total FROM herbs");
$totalHerbs = $herbsResult->fetch_assoc()['total'];

// Total interests
$interestsResult = $conn->query("SELECT COUNT(*) as total FROM herb_interests");
$totalInterests = $interestsResult->fetch_assoc()['total'];

// Herb popularity
$popularityQuery = "
    SELECT h.herb_name, COUNT(hi.herb_id) as interest_count
    FROM herbs h
    LEFT JOIN herb_interests hi ON h.herb_id = hi.herb_id
    GROUP BY h.herb_id
    ORDER BY interest_count DESC, h.herb_name
";
$popularityResult = $conn->query($popularityQuery);