<?php

namespace app\controllers;

use PDOException;
use Yii;
use yii\web\Controller;
use yii\db\Query;
use yii\web\Response;

class ToolsController extends Controller
{
  public function actionSplit()
  {
    $nop = Yii::$app->request->get('nop');

    $model = new \app\models\Peta;
    $informasi = new \app\models\Info;

    $dataKelurahan = $model->getPetaKelurahan($nop);
    $namaKelurahan = $informasi->getNamaKelurahan($nop);
    //$dataBangunan = $model->getPetaBangunan($nop);
    $dataBlok = $model->getPetaBlok($nop);
    //$dataJalan = $model->getJalan($nop);
    //$dataSungai = $model->getSungai($nop);
    $jumlahObjekPajak = $informasi->getJumlahObjekPajakKelurahan($nop);
    $jumlahSppt = $informasi->getJumlahSpptKelurahan($nop);
    $jumlahPokokPbb = $informasi->getJumlahPokokPbbKelurahan($nop);
    $realisasiPbb = $informasi->getRealisasiPbbKelurahan($nop);
    $zntTertinggi = $informasi->getZntTertinggiKelurahan($nop);
    $zntTerendah = $informasi->getZntTerendahKelurahan($nop);

    $query = new Query();
    $results = $query->select(['ogc_fid', 'd_nop', 'ST_AsGeoJSON(ST_Transform(wkb_geometry, 4326)) as geometry', 'ST_AsGeoJSON(wkb_geometry) as original_geometry'])
      ->from('public."' . $nop . '"')
      ->all();

    // Transform PostGIS results to GeoJSON Feature Collection
    $features = [];
    foreach ($results as $row) {
      $geometry = json_decode($row['geometry'], true);

      // Ensure we're dealing with a MultiPolygon
      if ($geometry['type'] === 'Polygon') {
        // Convert single Polygon to MultiPolygon
        $geometry = [
          'type' => 'MultiPolygon',
          'coordinates' => [$geometry['coordinates']]
        ];
      }

      $features[] = [
        'type' => 'Feature',
        'properties' => [
          'id' => $row['ogc_fid'],
          'nop' => $row['d_nop'],
          'original_srid' => 32751,
          'converted_srid' => 4326
        ],
        'geometry' => $geometry
      ];
    }

    $featureCollection = [
      'type' => 'FeatureCollection',
      'features' => $features
    ];

    return $this->render('split', [
      'dataKelurahan' => $dataKelurahan['json_build_object'],
      'dataSplit' => json_encode($featureCollection),
      'dataBlok' => $dataBlok['row_to_json'],
      'namaKelurahan' => $namaKelurahan['nm_kelurahan'],

    ]);
  }

  public function actionMerge()
  {
    $nop = Yii::$app->request->get('nop');

    $model = new \app\models\Peta;
    $informasi = new \app\models\Info;

    $dataKelurahan = $model->getPetaKelurahan($nop);
    $namaKelurahan = $informasi->getNamaKelurahan($nop);
    //$dataBangunan = $model->getPetaBangunan($nop);
    $dataBlok = $model->getPetaBlok($nop);

    $query = new Query();
    $results = $query->select(['ogc_fid', 'd_nop', 'ST_AsGeoJSON(ST_Transform(wkb_geometry, 4326)) as geometry', 'ST_AsGeoJSON(wkb_geometry) as original_geometry'])
      ->from('public."' . $nop . '"')
      ->all();

    // Transform PostGIS results to GeoJSON Feature Collection
    $features = [];
    foreach ($results as $row) {
      $geometry = json_decode($row['geometry'], true);

      // Ensure we're dealing with a MultiPolygon
      if ($geometry['type'] === 'Polygon') {
        // Convert single Polygon to MultiPolygon
        $geometry = [
          'type' => 'MultiPolygon',
          'coordinates' => [$geometry['coordinates']]
        ];
      }

      $features[] = [
        'type' => 'Feature',
        'properties' => [
          'id' => $row['ogc_fid'],
          'nop' => $row['d_nop'],
          'original_srid' => 32751,
          'converted_srid' => 4326
        ],
        'geometry' => $geometry
      ];
    }

    $featureCollection = [
      'type' => 'FeatureCollection',
      'features' => $features
    ];

    return $this->render('merge', [
      'dataMerge' => json_encode($featureCollection),
      'dataBlok' => $dataBlok['row_to_json'],
      'namaKelurahan' => $namaKelurahan['nm_kelurahan'],

    ]);
  }

  public function actionGenerate()
  {
    Yii::$app->response->format = Response::FORMAT_JSON;
    try {
      if (Yii::$app->request->isPost) {
        $feature = json_decode(Yii::$app->request->post('feature'), true);
        $lineGeom = json_decode(Yii::$app->request->post('line'), true);

        // Logging input data
        Yii::error("Feature: " . print_r($feature, true));
        Yii::error("Line: " . print_r($lineGeom, true));

        // Query PostGIS untuk split
        $sql = "
                    WITH split_line AS (
                        SELECT ST_GeomFromGeoJSON(:line) AS geom
                    ),
                    extended_line AS (
                        SELECT ST_AddPoint(
                            ST_AddPoint(
                                geom,
                                ST_MakePoint(
                                    ST_X(ST_StartPoint(geom)) - 1,
                                    ST_Y(ST_StartPoint(geom))
                                ),
                                0
                            ),
                            ST_MakePoint(
                                ST_X(ST_EndPoint(geom)) + 1,
                                ST_Y(ST_EndPoint(geom))
                            )
                        ) AS geom
                        FROM split_line
                    ),
                    split_result AS (
                        SELECT (ST_Dump(
                            ST_Split(
                                ST_GeomFromGeoJSON(:feature_geom),
                                (SELECT geom FROM extended_line)
                            )
                        )).geom AS geom
                    )
                    SELECT json_build_object(
                        'type', 'Feature',
                        'geometry', ST_AsGeoJSON(geom)::json,
                        'properties', '{}'::jsonb
                    ) AS geojson
                    FROM split_result;
                ";

        $connection = Yii::$app->db;

        // Logging query and parameters
        Yii::error("Query: " . $sql);
        Yii::error("Feature Geometry: " . json_encode($feature['geometry']));
        Yii::error("Line Geometry: " . json_encode($lineGeom));

        $command = $connection->createCommand($sql);
        $command->bindValue(':feature_geom', json_encode($feature['geometry']));
        $command->bindValue(':line', json_encode($lineGeom));

        Yii::error("Final Query: " . $command->rawSql);

        $results = $command->queryAll();

        // Logging result
        Yii::error("Result: " . print_r($results, true));

        return [
          'success' => true,
          'features' => array_map(function ($result) {
            return json_decode($result['geojson'], true);
          }, $results)
        ];
      }

      return ['success' => false];
    } catch (PDOException $e) {
      Yii::error("Error: " . $e->getMessage());
      return ['success' => false, 'message' => $e->getMessage()];
    }
  }

  public function actionSavePolygon()
  {
    Yii::$app->response->format = Response::FORMAT_JSON;
    try {
      $connection = Yii::$app->db;
      $transaction = $connection->beginTransaction();
      if (Yii::$app->request->isPost) {
        $nop = Yii::$app->request->post('nop');
        $featureCollection = Yii::$app->request->post('featureCollection');

        $tableName = 'public."' . substr($nop, 0, 10) . '"';

        // Cari data berdasarkan NOP
        $results = (new \yii\db\Query())
          ->select(['d_nop'])
          ->from('public."' . substr($nop, 0, 10) . '"')
          ->where(['d_nop' => $nop])
          ->one();

        if (!$results) {
          // Insert data baru
          $sql = "
              INSERT INTO $tableName (d_nop, wkb_geometry)
              VALUES (:nop, ST_Transform(ST_SetSRID (ST_GeomFromGeoJSON(:features), 4326), 32751))
              RETURNING ogc_fid;
          ";
        } else {
          // Update data yang ada
          $sql = "
              UPDATE $tableName
              SET wkb_geometry = ST_Transform(ST_SetSRID (ST_GeomFromGeoJSON(:features), 4326), 32751)
              WHERE d_nop = :nop
              RETURNING ogc_fid;
          ";
        }


        $connection->createCommand($sql)
          ->bindValue(':nop', $nop)
          ->bindValue(':features', $featureCollection)
          ->execute();

        $transaction->commit();
        return ['success' => true];
      }
    } catch (\Exception $e) {
      $transaction->rollBack();
      return ['success' => false, 'error' => $e->getMessage()];
    }

    return ['success' => false, 'message' => 'Invalid request'];
  }

  public function actionGenerateMerge()
  {
    Yii::$app->response->format = Response::FORMAT_JSON;

    $polygonIds = Yii::$app->request->post('polygon_ids');
    $tableName = 'public."' . Yii::$app->request->post('tableName') . '"';

    if (empty($polygonIds)) {
      return ['success' => false, 'message' => 'No polygons selected'];
    }

    try {
      $sql = "WITH merged AS (
              SELECT ST_Multi(ST_Transform(ST_Union(ST_Transform(wkb_geometry, 4326)), 32751)) as wkb_geometry
              FROM " . $tableName . "
              WHERE ogc_fid IN (" . implode(',', array_map('intval', $polygonIds)) . ")
          ),
          others AS (
              SELECT ST_Transform(wkb_geometry, 32751) as wkb_geometry, d_nop
              FROM " . $tableName . "
              WHERE ogc_fid NOT IN (" . implode(',', array_map('intval', $polygonIds)) . ")
          )
          SELECT ST_AsGeoJSON(ST_Transform(wkb_geometry, 4326)) as geojson, 'Merged Area' as name 
          FROM merged
          UNION ALL
          SELECT ST_AsGeoJSON(ST_Transform(wkb_geometry, 4326)) as geojson, d_nop 
          FROM others;";

      $results = Yii::$app->db->createCommand($sql)->queryAll();

      // 2. Update database - Insert hasil merge
      $sqlInsertMerged = "
            INSERT INTO " . $tableName . " (wkb_geometry, d_nop)
            SELECT ST_Multi(ST_Transform(ST_Union(ST_Transform(wkb_geometry, 4326)), 32751)) as wkb_geometry,
                   'Merged Area' as name
            FROM " . $tableName . "
            WHERE ogc_fid IN (" . implode(',', array_map('intval', $polygonIds)) . ")
            RETURNING ogc_fid";

      $newId = Yii::$app->db->createCommand($sqlInsertMerged)->queryScalar();

      // 3. Hapus data lama yang sudah di-merge
      $sqlDeleteOld = "
            DELETE FROM " . $tableName . "
            WHERE ogc_fid IN (" . implode(',', array_map('intval', $polygonIds)) . ")";

      Yii::$app->db->createCommand($sqlDeleteOld)->execute();

      $features = [];
      foreach ($results as $row) {
        $geometry = json_decode($row['geojson'], true);

        // Ensure we're dealing with a MultiPolygon
        if ($geometry['type'] === 'Polygon') {
          // Convert single Polygon to MultiPolygon
          $geometry = [
            'type' => 'MultiPolygon',
            'coordinates' => [$geometry['coordinates']]
          ];
        }

        $features[] = [
          'type' => 'Feature',
          'properties' => [
            'nop' => $row['name'],
            'original_srid' => 32751,
            'converted_srid' => 4326
          ],
          'geometry' => $geometry
        ];
      }

      $featureCollection = [
        'type' => 'FeatureCollection',
        'features' => $features
      ];

      return [
        'success' => true,
        'new_id' => $newId,
        'merged' => $featureCollection
      ];
    } catch (\Exception $e) {
      return ['success' => false, 'message' => $e->getMessage()];
    }
  }

  public function actionUpdateNop()
  {
    Yii::$app->response->format = Response::FORMAT_JSON;

    $nop_old = Yii::$app->request->post('nop_old');
    $nop_new = Yii::$app->request->post('nop_new');
    $tableName = 'public."' . Yii::$app->request->post('tableName') . '"';

    if (empty($nop_old) || empty($nop_new)) {
      return ['success' => false, 'message' => 'Invalid NOP'];
    }

    try {
      $sql = "
          UPDATE " . $tableName . "
          SET d_nop = :nop_new
          WHERE d_nop = :nop_old
          RETURNING ogc_fid;
      ";

      $results = Yii::$app->db->createCommand($sql)
        ->bindValue(':nop_old', $nop_old)
        ->bindValue(':nop_new', $nop_new)
        ->queryAll();

      return ['success' => true, 'results' => $results];
    } catch (\Exception $e) {
      return ['success' => false, 'message' => $e->getMessage()];
    }
  }
}