<?php
// Run this ONCE to create all tables
// Access: http://localhost/bancharee/setup/install.php
// DELETE this file after running!

require_once __DIR__ . '/../config/db.php';

$sql = "
CREATE DATABASE IF NOT EXISTS `bancharee_db`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `bancharee_db`;

CREATE TABLE IF NOT EXISTS `parcels` (
  `id`             INT AUTO_INCREMENT PRIMARY KEY,
  `kml_id`         VARCHAR(50) NOT NULL,
  `zone`           ENUM('0-300m','300-400m','400-900m') NOT NULL,
  `sheet_number`   VARCHAR(50) DEFAULT '',
  `parcel_no`      VARCHAR(30) DEFAULT '',
  `shape_area_sqm` DECIMAL(12,4) DEFAULT 0,
  `grid_map_page`  INT DEFAULT 0,
  `polygon_coords` LONGTEXT,
  `centroid_lat`   DECIMAL(10,8) DEFAULT 0,
  `centroid_lng`   DECIMAL(11,8) DEFAULT 0,
  `survey_status`  ENUM('pending','in_progress','completed') DEFAULT 'pending',
  `created_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `kml_id` (`kml_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `surveyors` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(100) NOT NULL,
  `phone`         VARCHAR(20) DEFAULT '',
  `email`         VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `ward_assigned` VARCHAR(20) DEFAULT '',
  `role`          ENUM('surveyor','supervisor','admin') DEFAULT 'surveyor',
  `is_active`     TINYINT(1) DEFAULT 1,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `surveys` (
  `id`                   INT AUTO_INCREMENT PRIMARY KEY,
  `parcel_id`            INT DEFAULT 0,
  `surveyor_id`          INT DEFAULT 0,
  `ward_no`              VARCHAR(10) DEFAULT '',
  `tole`                 VARCHAR(100) DEFAULT '',
  `survey_date`          DATE,
  `gps_lat`              DECIMAL(10,8) DEFAULT 0,
  `gps_lng`              DECIMAL(11,8) DEFAULT 0,
  `owner_name`           VARCHAR(200) DEFAULT '',
  `citizenship_no`       VARCHAR(50) DEFAULT '',
  `owner_photo`          VARCHAR(255) DEFAULT '',
  `citizenship_photo`    VARCHAR(255) DEFAULT '',
  `land_photo`           VARCHAR(255) DEFAULT '',
  `land_type`            ENUM('residential','agricultural','commercial','other') DEFAULT 'residential',
  `ownership_type`       ENUM('private','joint','leasehold','disputed') DEFAULT 'private',
  `structure_type`       ENUM('house','cowshed','shed','other') DEFAULT 'house',
  `structure_type_other` VARCHAR(100) DEFAULT '',
  `construction_material` VARCHAR(200) DEFAULT '',
  `num_floors`           TINYINT DEFAULT 0,
  `floor_length_m`       DECIMAL(8,2) DEFAULT 0,
  `floor_breadth_m`      DECIMAL(8,2) DEFAULT 0,
  `year_built`           INT DEFAULT 0,
  `structure_condition`  ENUM('good','average','dilapidated') DEFAULT 'good',
  `is_occupied`          TINYINT(1) DEFAULT 0,
  `is_rented`            TINYINT(1) DEFAULT 0,
  `monthly_rent`         DECIMAL(10,2) DEFAULT 0,
  `num_rooms`            TINYINT DEFAULT 0,
  `structure_photo`      VARCHAR(255) DEFAULT '',
  `hoh_name`             VARCHAR(200) DEFAULT '',
  `contact_no`           VARCHAR(20) DEFAULT '',
  `hoh_citizenship`      VARCHAR(50) DEFAULT '',
  `total_members`        TINYINT DEFAULT 0,
  `families_count`       TINYINT DEFAULT 0,
  `vulnerability_flags`  VARCHAR(200) DEFAULT '',
  `residence_status`     ENUM('on_site','displaced','renting_elsewhere') DEFAULT 'on_site',
  `remarks`              TEXT,
  `sync_status`          ENUM('local','synced') DEFAULT 'synced',
  `created_at`           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`           TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `parcel_id` (`parcel_id`),
  KEY `surveyor_id` (`surveyor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
";

CREATE TABLE IF NOT EXISTS `land_owners` (
  `id`                  INT AUTO_INCREMENT PRIMARY KEY,
  `survey_id`           INT NOT NULL,
  `owner_name`          VARCHAR(200) DEFAULT '',
  `citizenship_no`      VARCHAR(50) DEFAULT '',
  `ownership_share_pct` TINYINT DEFAULT 100,
  `owner_photo`         VARCHAR(255) DEFAULT '',
  `citizenship_photo`   VARCHAR(255) DEFAULT '',
  `land_photo`          VARCHAR(255) DEFAULT '',
  `is_primary`          TINYINT(1) DEFAULT 0,
  `sequence_order`      TINYINT DEFAULT 1,
  KEY `survey_id` (`survey_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `survey_structures` (
  `id`                         INT AUTO_INCREMENT PRIMARY KEY,
  `survey_id`                  INT NOT NULL,
  `structure_type`             ENUM('house','cowshed','shed','other') DEFAULT 'house',
  `structure_type_other`       VARCHAR(100) DEFAULT '',
  `construction_material`      VARCHAR(200) DEFAULT '',
  `num_floors`                 TINYINT DEFAULT 0,
  `floor_length_m`             DECIMAL(8,2) DEFAULT 0,
  `floor_breadth_m`            DECIMAL(8,2) DEFAULT 0,
  `year_built`                 INT DEFAULT 0,
  `structure_condition`        ENUM('good','average','dilapidated') DEFAULT 'good',
  `is_occupied`                TINYINT(1) DEFAULT 0,
  `is_rented`                  TINYINT(1) DEFAULT 0,
  `monthly_rent`               DECIMAL(10,2) DEFAULT 0,
  `num_rooms`                  TINYINT DEFAULT 0,
  `structure_photo`            VARCHAR(255) DEFAULT '',
  `structure_photo_2`          VARCHAR(255) DEFAULT '',
  `structure_photo_3`          VARCHAR(255) DEFAULT '',
  `structure_photo_4`          VARCHAR(255) DEFAULT '',
  `owner_type`                 ENUM('land_owner','different') DEFAULT 'land_owner',
  `structure_owner_name`       VARCHAR(200) DEFAULT '',
  `structure_owner_citizenship` VARCHAR(50) DEFAULT '',
  `sequence_order`             TINYINT DEFAULT 1,
  KEY `survey_id` (`survey_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `survey_households` (
  `id`                   INT AUTO_INCREMENT PRIMARY KEY,
  `survey_id`            INT NOT NULL,
  `structure_seq`        TINYINT DEFAULT 1,
  `hoh_name`             VARCHAR(200) DEFAULT '',
  `contact_no`           VARCHAR(20) DEFAULT '',
  `hoh_citizenship`      VARCHAR(50) DEFAULT '',
  `total_members`        TINYINT DEFAULT 0,
  `families_count`       TINYINT DEFAULT 1,
  `vulnerability_flags`  VARCHAR(200) DEFAULT '',
  `residence_status`     ENUM('on_site','displaced','renting_elsewhere') DEFAULT 'on_site',
  `sequence_order`       TINYINT DEFAULT 1,
  KEY `survey_id` (`survey_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
";

// Execute each statement
$statements = array_filter(array_map('trim', explode(';', $sql)));
$errors = [];
foreach ($statements as $s) {
    if (empty($s)) continue;
    try { $pdo->exec($s); } catch (PDOException $e) { $errors[] = $e->getMessage(); }
}

// Insert default admin
try {
    $pdo->prepare("INSERT IGNORE INTO surveyors (name, email, password_hash, role) VALUES (?, ?, ?, 'admin')")
        ->execute(['Admin', 'admin@bancharee.np', password_hash('admin123', PASSWORD_BCRYPT)]);
    $pdo->prepare("INSERT IGNORE INTO surveyors (name, email, password_hash, role, ward_assigned) VALUES (?, ?, ?, 'supervisor', ?)")
        ->execute(['Supervisor', 'supervisor@bancharee.np', password_hash('super123', PASSWORD_BCRYPT), '1']);
    $pdo->prepare("INSERT IGNORE INTO surveyors (name, email, password_hash, role, ward_assigned) VALUES (?, ?, ?, 'surveyor', ?)")
        ->execute(['Test Surveyor', 'surveyor1@bancharee.np', password_hash('survey123', PASSWORD_BCRYPT), '1']);
} catch (Exception $e) { $errors[] = $e->getMessage(); }
?>
<!DOCTYPE html>
<html>
<head><title>Install</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="p-4">
<h3>Bancharee Install</h3>
<?php if ($errors): ?>
  <div class="alert alert-warning"><strong>Some warnings:</strong><ul><?php foreach($errors as $e) echo "<li>$e</li>" ?></ul></div>
<?php else: ?>
  <div class="alert alert-success">All tables created successfully.</div>
<?php endif ?>
<div class="alert alert-info">
  <strong>Default accounts:</strong><br>
  Admin: admin@bancharee.np / admin123<br>
  Supervisor: supervisor@bancharee.np / super123<br>
  Surveyor: surveyor1@bancharee.np / survey123
</div>
<div class="alert alert-danger"><strong>Security:</strong> Delete this file (setup/install.php) after setup!</div>
<a href="../admin/login.php" class="btn btn-primary">Go to Admin Panel</a>
</body>
</html>
