-- 1. Create Agency Table
CREATE TABLE Agency (
    agency_id INT AUTO_INCREMENT PRIMARY KEY,
    agency_name VARCHAR(100) NOT NULL,
    city VARCHAR(100)
);

-- 2. Create Development Table
CREATE TABLE Development (
    dev_id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT,
    dev_name VARCHAR(100) NOT NULL,
    location VARCHAR(100),
    year_opened INT,
    height_stories INT,
    FOREIGN KEY (agency_id) REFERENCES Agency(agency_id) ON DELETE CASCADE
);

-- 3. Create Household Table
CREATE TABLE Household (
    household_id INT AUTO_INCREMENT PRIMARY KEY,
    head_member_id INT NULL -- Will be linked after member insertion
);

-- 4. Create HousingUnit Table
CREATE TABLE HousingUnit (
    unit_id INT AUTO_INCREMENT PRIMARY KEY,
    dev_id INT,
    household_id INT NULL,
    unit_number VARCHAR(20) NOT NULL,
    num_bedrooms INT,
    num_bathrooms INT,
    has_kitchen ENUM('Y', 'N'),
    has_living_room ENUM('Y', 'N'),
    sq_footage INT,
    FOREIGN KEY (dev_id) REFERENCES Development(dev_id) ON DELETE CASCADE,
    FOREIGN KEY (household_id) REFERENCES Household(household_id) ON DELETE SET NULL
);

-- 5. Create Members Table
CREATE TABLE Members (
    member_id INT AUTO_INCREMENT PRIMARY KEY,
    household_id INT,
    member_name VARCHAR(100) NOT NULL,
    dob DATE,
    sex ENUM('M', 'F', 'O'),
    is_head ENUM('Y', 'N'),
    FOREIGN KEY (household_id) REFERENCES Household(household_id) ON DELETE CASCADE
);

-- 6. Create UnitTransferHistory Table
CREATE TABLE UnitTransferHistory (
    transfer_id INT AUTO_INCREMENT PRIMARY KEY,
    household_id INT,
    from_unit_id INT,
    to_unit_id INT,
    transfer_date DATE NOT NULL,
    FOREIGN KEY (household_id) REFERENCES Household(household_id) ON DELETE CASCADE,
    FOREIGN KEY (from_unit_id) REFERENCES HousingUnit(unit_id) ON DELETE CASCADE,
    FOREIGN KEY (to_unit_id) REFERENCES HousingUnit(unit_id) ON DELETE CASCADE
);

-- 7. Insert Dummy Seed Data
INSERT INTO Agency (agency_name, city) VALUES ('Kolkata Housing Board', 'Kolkata');

INSERT INTO Development (agency_id, dev_name, location, year_opened, height_stories) 
VALUES (1, 'Greenwood Acres', 'North District', 2010, 5),
       (1, 'Riverside Towers', 'South Waterfront', 2018, 12);

INSERT INTO Household (head_member_id) VALUES (NULL), (NULL);

INSERT INTO HousingUnit (dev_id, household_id, unit_number, num_bedrooms, num_bathrooms, has_kitchen, has_living_room, sq_footage)
VALUES (1, 1, 'A-101', 2, 1, 'Y', 'Y', 750),
       (1, NULL, 'B-202', 3, 2, 'Y', 'Y', 1100),
       (2, 2, 'T1-404', 1, 1, 'Y', 'N', 500),
       (2, NULL, 'T2-501', 2, 2, 'Y', 'Y', 900);

INSERT INTO Members (household_id, member_name, dob, sex, is_head)
VALUES (1, 'Amit Mukherjee', '1984-04-12', 'M', 'Y'),
       (2, 'Priya Sharma', '1991-08-23', 'F', 'Y');

-- Complete the circular reference loop for Heads
UPDATE Household SET head_member_id = 1 WHERE household_id = 1;
UPDATE Household SET head_member_id = 2 WHERE household_id = 2;
const express = require('express');
const mysql = require('mysql2/promise');
const cors = require('cors');

const app = express();
app.use(cors());
app.use(express.json());

// XAMPP Default MySQL Connection Config
const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: '', 
    database: 'housing_agency'
};

// 1. Dropdown Lookup: Get all developments
app.get('/api/developments', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute('SELECT dev_id, dev_name FROM Development ORDER BY dev_name');
        await connection.end();
        res.json(rows);
    } catch (err) {
        res.status(500).send(err.message);
    }
});

// 2. Dropdown Lookup: Get all housing units
app.get('/api/units', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute('SELECT unit_id, dev_id, unit_number FROM HousingUnit');
        await connection.end();
        res.json(rows);
    } catch (err) {
        res.status(500).send(err.message);
    }
});

// 3. Dropdown Lookup: Get all household head members
app.get('/api/heads', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute("SELECT h.household_id, m.member_name FROM Household h JOIN Members m ON h.head_member_id = m.member_id WHERE m.is_head = 'Y' ORDER BY m.member_name");
        await connection.end();
        res.json(rows);
    } catch (err) {
        res.status(500).send(err.message);
    }
});

// 4. POST Endpoint to log a fresh unit transfer tracking entry
app.post('/api/transfer', async (req, res) => {
    const { household_id, from_unit_id, to_unit_id, transfer_date } = req.body;
    try {
        const connection = await mysql.createConnection(dbConfig);
        
        // Transaction to add history log and update current active unit assignment
        await connection.beginTransaction();

        const insertLogSql = `INSERT INTO UnitTransferHistory (household_id, from_unit_id, to_unit_id, transfer_date) VALUES (?, ?, ?, ?)`;
        await connection.execute(insertLogSql, [household_id, from_unit_id, to_unit_id, transfer_date]);

        const clearOldUnitSql = `UPDATE HousingUnit SET household_id = NULL WHERE unit_id = ?`;
        await connection.execute(clearOldUnitSql, [from_unit_id]);

        const assignNewUnitSql = `UPDATE HousingUnit SET household_id = ? WHERE unit_id = ?`;
        await connection.execute(assignNewUnitSql, [household_id, to_unit_id]);

        await connection.commit();
        await connection.end();
        res.json({ success: true });
    } catch (err) {
        res.status(500).send(err.message);
    }
});

// 5. GET Endpoint to fetch a date-wise report of head of household movements
app.get('/api/report', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        const sql = `
            SELECT m.member_name, u1.unit_number AS from_unit, u2.unit_number AS to_unit, t.transfer_date
            FROM UnitTransferHistory t
            JOIN Household h ON t.household_id = h.household_id
            JOIN Members m ON h.head_member_id = m.member_id
            JOIN HousingUnit u1 ON t.from_unit_id = u1.unit_id
            JOIN HousingUnit u2 ON t.to_unit_id = u2.unit_id
            ORDER BY t.transfer_date ASC
        `;
        const [rows] = await connection.execute(sql);
        await connection.end();
        res.json(rows);
    } catch (err) {
        res.status(500).send(err.message);
    }
});

app.listen(3000, () => console.log('XAMPP Backend microservice running on port 3000'));
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Public Housing Management Portal (XAMPP)</title>
    <style>
        :root {
            --primary: #0f766e;
            --primary-hover: #115e59;
            --bg: #f4fbfb;
            --card-bg: #ffffff;
            --text: #334155;
            --border: #cbd5e1;
        }
        body {
            font-family: system-ui, -apple-system, sans-serif;
            background-color: var(--bg);
            color: var(--text);
            max-width: 850px;
            margin: 40px auto;
            padding: 0 20px;
        }
        .card {
            background: var(--card-bg);
            padding: 30px;
            border-radius: 12px;
            box-shadow: 0 4px 6px -1px rgb(0 0 0 / 0.05);
            margin-bottom: 30px;
        }
        h2, h3 {
            margin-top: 0;
            color: #111827;
            border-bottom: 2px solid #ccfbf1;
            padding-bottom: 10px;
        }
        .form-row {
            display: flex;
            gap: 20px;
            margin-bottom: 15px;
        }
        .form-group {
            flex: 1;
            margin-bottom: 15px;
        }
        label {
            display: block;
            margin-bottom: 6px;
            font-weight: 600;
            font-size: 0.9rem;
        }
        select, input, button {
            width: 100%;
            padding: 10px 14px;
            border-radius: 6px;
            border: 1px solid var(--border);
            font-size: 1rem;
            box-sizing: border-box;
        }
        button {
            background-color: var(--primary);
            color: white;
            border: none;
            font-weight: 600;
            cursor: pointer;
            margin-top: 10px;
        }
        button:hover { background-color: var(--primary-hover); }
        .alert {
            background-color: #ccfbf1;
            color: #115e59;
            padding: 12px;
            border-radius: 6px;
            font-weight: 600;
            margin-bottom: 20px;
            text-align: center;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 15px;
        }
        th, td {
            padding: 12px;
            text-align: left;
            border-bottom: 1px solid #e2e8f0;
        }
        th { background-color: #f1f5f9; font-weight: 600; }
    </style>
</head>
<body>

    <div class="card">
        <h2>Household Unit Transfer Form</h2>
        <div id="successMessage" class="alert" style="display:none;">
            Transfer records successfully logged in XAMPP MySQL database!
        </div>

        <form id="transferForm">
            <div class="form-row">
                <!-- Step 1: Select Development -->
                <div class="form-group">
                    <label for="development">1. Select Housing Development:</label>
                    <select id="development" required>
                        <option value="">Loading Developments...</option>
                    </select>
                </div>
                <!-- Step 2: Select Head of Household -->
                <div class="form-group">
                    <label for="headMember">2. Head of Household Involved:</label>
                    <select id="headMember" required disabled>
                        <option value="">-- Select Development First --</option>
                    </select>
                </div>
            </div>

            <div class="form-row">
                <!-- Step 3: Select From Unit -->
                <div class="form-group">
                    <label for="fromUnit">3. From Unit:</label>
                    <select id="fromUnit" required disabled>
                        <option value="">-- Select Household First --</option>
                    </select>
                </div>
                <!-- Step 4: Select To Unit -->
                <div class="form-group">
                    <label for="toUnit">4. To Unit:</label>
                    <select id="toUnit" required disabled>
                        <option value="">-- Select Household First --</option>
                    </select>
                </div>
            </div>

            <div class="form-row">
                <!-- Step 5: Transfer Date -->
                <div class="form-group">
                    <label for="transferDate">5. Transfer Effective Date:</label>
                    <input type="date" id="transferDate" required disabled>
                </div>
            </div>

            <button type="submit">Execute Unit Transfer Log</button>
        </form>
    </div>

    <div class="card">
        <h3>Household Relocation & Transfer Report</h3>
        <table id="reportTable">
            <thead>
                <tr>
                    <th>Head of Household</th>
                    <th>From Unit</th>
                    <th>To Unit</th>
                    <th>Transfer Date</th>
                </tr>
            </thead>
            <tbody></tbody>
        </table>
    </div>

<script>
    const API_URL = 'http://localhost:3000/api';
    document.getElementById('transferDate').valueAsDate = new Date();

    const devEl = document.getElementById('development');
    const fromUnitEl = document.getElementById('fromUnit');
    const toUnitEl = document.getElementById('toUnit');
    const headEl = document.getElementById('headMember');
    const dateEl = document.getElementById('transferDate');

    let cachedUnits = [];

    async function initLookupData() {
        try {
            const resDevs = await fetch(`${API_URL}/developments`);
            const developments = await resDevs.json();
            devEl.innerHTML = '<option value="">-- Select Development --</option>';
            developments.forEach(row => {
                devEl.innerHTML += `<option value="${row.dev_id}">${row.dev_name}</option>`;
            });

            const resUnits = await fetch(`${API_URL}/units`);
            cachedUnits = await resUnits.json();
        } catch (err) {
            console.error("Initialization failure:", err);
        }
    }

    devEl.addEventListener('change', async function() {
        const devId = parseInt(this.value);
        if(devId) {
            try {
                const resHeads = await fetch(`${API_URL}/heads`);
                const heads = await resHeads.json();
                headEl.innerHTML = '<option value="">-- Choose Head of Household --</option>';
                heads.forEach(row => {
                    headEl.innerHTML += `<option value="${row.household_id}">${row.member_name}</option>`;
                });
                headEl.disabled = false;
            } catch (err) { console.error(err); }
        } else {
            headEl.disabled = true;
            fromUnitEl.disabled = true;
            toUnitEl.disabled = true;
            dateEl.disabled = true;
        }
    });

    headEl.addEventListener('change', function() {
        const devId = parseInt(devEl.value);
        if(this.value && devId) {
            const optionsStr = cachedUnits
                .filter(row => row.dev_id === devId)
                .map(row => `<option value="${row.unit_id}">${row.unit_number}</option>`)
                .join('');
            
            fromUnitEl.innerHTML = '<option value="">-- Select Current Unit --</option>' + optionsStr;
            toUnitEl.innerHTML = '<option value="">-- Select New Unit --</option>' + optionsStr;
            
            fromUnitEl.disabled = false;
            toUnitEl.disabled = false;
            dateEl.disabled = false;
        } else {
            fromUnitEl.disabled = true;
            toUnitEl.disabled = true;
            dateEl.disabled = true;
        }
    });

    document.getElementById('transferForm').addEventListener('submit', async (e) => {
        e.preventDefault();
        if(fromUnitEl.value === toUnitEl.value) {
            return alert("Source and destination units cannot be identical.");
        }

        const payload = {
            household_id: headEl.value,
            from_unit_id: fromUnitEl.value,
            to_unit_id: toUnitEl.value,
            transfer_date: dateEl.value
        };

        const res = await fetch(`${API_URL}/transfer`, {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify(payload)
        });

        if (res.ok) {
            document.getElementById('transferForm').reset();
            fromUnitEl.disabled = true;
            toUnitEl.disabled = true;
            headEl.disabled = true;
            dateEl.disabled = true;
            dateEl.valueAsDate = new Date();
            
            const msg = document.getElementById('successMessage');
            msg.style.display = 'block';
            setTimeout(() => msg.style.display = 'none', 3000);
            
            loadReportTable();
        } else {
            alert('Database transaction rejected.');
        }
    });

    async function loadReportTable() {
        try {
            const res = await fetch(`${API_URL}/report`);
            const rows = await res.json();
            const tbody = document.querySelector('#reportTable tbody');
            tbody.innerHTML = '';

            rows.forEach(row => {
                // Formatting clean JavaScript ISO dates properly
                const cleanDate = new Date(row.transfer_date).toISOString().split('T')[0];
                tbody.innerHTML += `
                    <tr>
                        <td><strong>${row.member_name}</strong></td>
                        <td>${row.from_unit}</td>
                        <td>${row.to_unit}</td>
                        <td>${cleanDate}</td>
                    </tr>
                `;
            });
        } catch (err) {
            console.error("Failed to load historical transfer metrics:", err);
        }
    }

    initLookupData();
    loadReportTable();
</script>
</body>
</html>