import express from 'express';
import path from 'path';
import fs from 'fs';
import { createServer as createViteServer } from 'vite';
import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

dotenv.config();

const app = express();
const PORT = 3000;
const DB_FILE = path.join(process.cwd(), 'db.json');

// MySQL Pool Configuration
let pool: any = null;
const useMysql = !!(process.env.DB_HOST && process.env.DB_NAME);

async function ensureSettingsColumns() {
  if (!pool) return;
  const cols = ['hero_bg_url', 'about_bg_url', 'service_bg_url', 'portfolio_bg_url', 'contact_bg_url', 'google_verification_code', 'meta_keywords'];
  for (const col of cols) {
    try {
      await pool.query(`ALTER TABLE \`settings\` ADD COLUMN \`${col}\` TEXT NULL`);
      console.log(`Added column ${col} to settings table.`);
    } catch (e: any) {
      // Ignore ER_DUP_FIELDNAME (Error code 1060 / ER_DUP_FIELDNAME)
      if (e.errno !== 1060 && e.code !== 'ER_DUP_FIELDNAME') {
        console.error(`Error adding column ${col}:`, e);
      }
    }
  }
}

if (useMysql) {
  try {
    pool = mysql.createPool({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0
    });
    console.log('MySQL Database connection pool initialized.');
  } catch (err) {
    console.error('Failed to initialize MySQL pool. Falling back to JSON storage.', err);
    pool = null;
  }
}

// Middleware
app.use(express.json());

// Default/Initial Data
const initialData = {
  reasons: [
    {
      title: 'Desain Eksklusif',
      desc: 'Setiap konsep dekorasi dirancang khusus sesuai kepribadian dan selera Anda, menciptakan nuansa yang tak terlupakan.',
      iconName: 'Sparkles'
    },
    {
      title: 'Tim Profesional',
      desc: 'Ditangani langsung oleh dekorator dan desainer lanskap bersertifikat dengan jam terbang tinggi di berbagai venue.',
      iconName: 'Award'
    },
    {
      title: 'Material Berkualitas',
      desc: 'Menggunakan bunga segar pilihan, struktur pelaminan kokoh, serta tanaman hias premium yang sehat dan estetik.',
      iconName: 'Gem'
    },
    {
      title: 'Harga Kompetitif',
      desc: 'Transparansi anggaran tanpa biaya tersembunyi, dengan berbagai paket fleksibel yang menyesuaikan budget Anda.',
      iconName: 'Tag'
    }
  ],
  stats: [
    { number: '300+', label: 'Project Selesai', iconName: 'CheckCircle2' },
    { number: '250+', label: 'Klien Puas', iconName: 'Smile' },
    { number: '8 Tahun', label: 'Pengalaman', iconName: 'Calendar' },
    { number: '20', label: 'Tim Profesional', iconName: 'Users' }
  ],
  mainServices: [
    {
      id: 'wedding-main',
      title: 'Wedding Decoration',
      subtitle: 'Keanggunan & Kemewahan di Hari Bahagia Anda',
      desc: 'Kami menghadirkan rangkaian dekorasi pernikahan mulai dari akad nikah yang sakral, pelaminan elegan, hingga pesta kebun (garden wedding) romantis bernuansa internasional maupun tradisional modern.',
      features: ['Pelaminan & Akad Nikah', 'Garden & Outdoor Wedding', 'Backdrop & Photo Booth Estetik', 'Lighting & Atmosphere Design'],
      imageUrl: 'https://images.unsplash.com/photo-1519741497674-611481863552?auto=format&fit=crop&w=1200&q=80',
      category: 'wedding'
    },
    {
      id: 'landscape-main',
      title: 'Landscape & Garden Decoration',
      subtitle: 'Harmoni Alam di Halaman Rumah Anda',
      desc: 'Ubah lahan kosong rumah Anda menjadi oase hijau yang menenangkan. Kami melayani pembuatan taman minimalis, taman tropis Bali, vertical garden hemat ruang, hingga kolam ikan koi & hardscape.',
      features: ['Taman Minimalis & Tropis', 'Vertical Garden Modern', 'Kolam Ikan Koi & Water Feature', 'Hardscape, Batu Alam & Gazebo'],
      imageUrl: 'https://images.unsplash.com/photo-1585320806297-9794b3e4eeae?auto=format&fit=crop&w=1200&q=80',
      category: 'landscape'
    }
  ],
  detailedServices: [
    {
      id: 'w1',
      category: 'wedding',
      title: 'Dekorasi Pelaminan Elegan',
      shortDesc: 'Pelaminan megah bermaterial kayu ukir modern maupun minimalis floral dengan tata cahaya dramatis.',
      fullDesc: 'Pelaminan adalah pusat perhatian dalam pesta pernikahan. Kami menghadirkan desain pelaminan kustom yang menggabungkan kemewahan bunga segar impor, drape kain sutra lembut, dan penataan pencahayaan presisi.',
      features: ['Lebar panggung pengerjaan hingga 12 meter', 'Bunga segar (Fresh Flowers) pilihan', 'Sofa pengantin premium & kursi orang tua', 'Mini garden panggung depan'],
      imageUrl: 'https://images.unsplash.com/photo-1511795409834-ef04bbd61622?auto=format&fit=crop&w=800&q=80',
      iconName: 'Crown'
    },
    {
      id: 'w2',
      category: 'wedding',
      title: 'Dekorasi Akad Nikah / Pemberkatan',
      shortDesc: 'Suasana sakral, hangat, dan intim untuk prosesi janji suci pernikahan Anda bersama keluarga terdekat.',
      fullDesc: 'Menghadirkan meja akad jati dengan balutan rangkaian bunga putih suci (white lily & rose), kursi tiffany, serta pergola bunga minimalis untuk momen paling menggetarkan hati.',
      features: ['Meja & kursi akad eksklusif', 'Pergola / Arch bunga segar', 'Karpet jalan (Aisle runner) eksklusif', 'Sound system mini akomodasi janji suci'],
      imageUrl: 'https://images.unsplash.com/photo-1465495976277-4387d4b0b4c6?auto=format&fit=crop&w=800&q=80',
      iconName: 'HeartHandshake'
    },
    {
      id: 'w3',
      category: 'wedding',
      title: 'Garden Wedding & Outdoor Event',
      shortDesc: 'Konsep pernikahan menyatu dengan alam terbuka diiringi lampu gantung fairy lights yang syahdu.',
      fullDesc: 'Layanan dekorasi luar ruangan anti-mainstream. Kami mengatur layout meja banquet panjang (long table), lampu gantung Edison festoon gantung, serta tenda transparan elegan.',
      features: ['Fairy lights & festoon bulb canopy', 'Welcome gate & sign board kayu estetik', 'Dekorasi meja tamu VIP (Table Setting)', 'Area santai cocktail & photobooth menyatu alam'],
      imageUrl: 'https://images.unsplash.com/photo-1532712938310-34cb3982ef74?auto=format&fit=crop&w=800&q=80',
      iconName: 'Sun'
    },
    {
      id: 'w4',
      category: 'wedding',
      title: 'Backdrop & Photo Gallery',
      shortDesc: 'Spot foto estetik instagrammable untuk mengabadikan momen kehadiran para tamu undangan.',
      fullDesc: 'Merancang photo booth 3D dengan kombinasi neon sign tulisan nama mempelai, pampas grass kering bergaya bohemian, atau floral wall padat yang menawan.',
      features: ['Kustomisasi Neon Sign nama mempelai', 'Properti foto unik & pigura kenangan', 'Pencahayaan ring-light spot kustom', 'Lantai panggung kayu berlapis karpet'],
      imageUrl: 'https://images.unsplash.com/photo-1522673607200-164d1b6ce486?auto=format&fit=crop&w=800&q=80',
      iconName: 'Camera'
    },
    {
      id: 'l1',
      category: 'landscape',
      title: 'Pembuatan Taman Minimalis Modern',
      shortDesc: 'Taman rumah bergaya kontemporer yang rapi, mudah dirawat, dan memberikan sirkulasi udara segar.',
      fullDesc: 'Taman minimalis dirancang untuk rumah perkotaan. Mengutamakan perpaduan rumput gajah mini atau rumput Jepang yang mulus, tanaman lidah mertua, sikas, serta stepping stone koral putih.',
      features: ['Pola geometri rapi & fungsional', 'Pemilihan tanaman minim gugur daun', 'Sistem drainase bawah tanah anti-becek', 'Pemasangan lampu taman tenaga surya'],
      imageUrl: 'https://images.unsplash.com/photo-1584622650111-993a426fbf0a?auto=format&fit=crop&w=800&q=80',
      iconName: 'LayoutGrid'
    },
    {
      id: 'l2',
      category: 'landscape',
      title: 'Taman Tropis Bali & Resor',
      shortDesc: 'Menghadirkan ketenangan resor mewah ke dalam rumah dengan rimbunnya tanaman tropis dan elemen air.',
      fullDesc: 'Cocok bagi Anda yang mendambakan suasana liburan setiap hari. Menggunakan tanaman monstera raksasa, kamboja kuning (frangipani), pakis, serta patung atau pancuran air batu paras.',
      features: ['Pohon kamboja fosil karakter tinggi', 'Rangkaian tanaman berdaun lebar tropis', 'Gemericik air pancuran alami', 'Nuansa sejuk meredam panas matahari'],
      imageUrl: 'https://images.unsplash.com/photo-1558904541-efa8c3c8cac1?auto=format&fit=crop&w=800&q=80',
      iconName: 'Palmtree'
    },
    {
      id: 'l3',
      category: 'landscape',
      title: 'Vertical Garden Modern',
      shortDesc: 'Solusi penghijauan dinding dinding beton untuk rumah berlahan terbatas atau balkon apartemen.',
      fullDesc: 'Sistem taman tegak menggunakan kantong geotextile khusus berdaya tahan tinggi, dilengkapi sistem penyiraman otomatis (automatic drip irrigation) sehingga tanaman selalu segar tanpa merepotkan.',
      features: ['Sistem irigasi otomatis (Drip System)', 'Kombinasi 6-10 varietas tanaman hias warna', 'Rangka baja ringan anti-karat', 'Garansi hidup tanaman 3 bulan pertama'],
      imageUrl: 'https://images.unsplash.com/photo-1508873696983-2df5293cb92b?auto=format&fit=crop&w=800&q=80',
      iconName: 'Layers'
    },
    {
      id: 'l4',
      category: 'landscape',
      title: 'Hardscape, Kolam Koi & Gazebo',
      shortDesc: 'Struktur taman solid seperti kolam kaca kaca minimalis, deck kayu ulin, dan jalan setapak batu alam.',
      fullDesc: 'Melengkapi keindahan softscape (tanaman) dengan konstruksi keras yang estetis. Kami membangun kolam ikan koi dengan filter mekanis biologi jernih jangka panjang serta lantai dek kayu tahan cuaca.',
      features: ['Kolam koi kaca dengan sistem filter vortex', 'Lantai Kayu Ulin / WPC Outdoor Decking', 'Batu alam Andesit & Koral sikat pola', 'Lampu bawah air (Underwater spot LED)'],
      imageUrl: 'https://images.unsplash.com/photo-1540555700478-4be289fbecef?auto=format&fit=crop&w=800&q=80',
      iconName: 'Compass'
    }
  ],
  workflowSteps: [
    { step: '01', title: 'Konsultasi Gratis', desc: 'Diskusi ide, preferensi tema warna, dan perkiraan besaran anggaran Anda melalui WhatsApp atau tatap muka.' },
    { step: '02', title: 'Survey Lokasi', desc: 'Tim teknis kami mengunjungi venue pernikahan atau halaman rumah Anda untuk pengukuran dimensi akurat.' },
    { step: '03', title: 'Desain & visual 3D', desc: 'Pembuatan sketsa atau render 3D agar Anda bisa memvisualisasikan hasil akhir sebelum produksi dimulai.' },
    { step: '04', title: 'Produksi & Persiapan', desc: 'Perakitan komponen pelaminan di workshop atau pemesanan bibit tanaman unggul langsung dari kebun mitra.' },
    { step: '05', title: 'Instalasi & Tata Rapi', desc: 'Proses pemasangan di lokasi dengan ketepatan waktu tinggi serta standar kebersihan kerja yang ketat.' },
    { step: '06', title: 'Serah Terima Selesai', desc: 'Pengecekan akhir bersama klien untuk memastikan seluruh detail sempurna sesuai kesepakatan kontrak.' }
  ],
  testimonials: [
    {
      id: 't1',
      name: 'Anisa & Dimas Pratama',
      role: 'Mempelai Pengantin - Jakarta',
      comment: 'Luar biasa! Tukang Dekorasi menyulap ballroom hotel kami menjadi taman bunga putih bernuansa sage green yang begitu elegan. Semua tamu memuji pelaminan kami yang terlihat sangat mewah!',
      rating: 5,
      avatarUrl: 'https://images.unsplash.com/photo-1534528741775-53994a69daeb?auto=format&fit=crop&w=200&q=80',
      projectType: 'Grand Ballroom Wedding'
    },
    {
      id: 't2',
      name: 'Bapak Hendra Wijaya',
      role: 'Pemilik Rumah - BSD City',
      comment: 'Halaman belakang rumah saya yang awalnya gersang dan penuh puing bekas bangunan sekarang jadi taman tropis bernuansa Bali lengkap dengan suara air kolam koi. Kerja tim sangat rapi dan bersih.',
      rating: 5,
      avatarUrl: 'https://images.unsplash.com/photo-1507003211169-0a1dd7228f2d?auto=format&fit=crop&w=200&q=80',
      projectType: 'Tropical Landscape & Koi Pond'
    },
    {
      id: 't3',
      name: 'Sarah Kusuma',
      role: 'Event Organizer - Bandung',
      comment: 'Sudah 4 kali kerja bareng Tukang Dekorasi untuk penanganan klien VIP kami. Selalu tepat waktu saat load-in barang malam hari, bunga selalu segar, dan sangat solutif kalau ada kendala mendadak.',
      rating: 5,
      avatarUrl: 'https://images.unsplash.com/photo-1517841905240-472988babdf9?auto=format&fit=crop&w=200&q=80',
      projectType: 'Outdoor Garden Wedding'
    }
  ],
  portfolios: [
    {
      id: 'p1',
      title: 'The Royal White & Sage Symphony',
      category: 'wedding',
      location: 'Ritz-Carlton Mega Kuningan, Jakarta',
      year: '2026',
      shortDesc: 'Dekorasi pelaminan ballroom megah beraksen sage green lembut dengan ribuan bunga mawar putih.',
      fullDesc: 'Proyek pernikahan skala besar menghadirkan pelaminan sepanjang 15 meter. Menggunakan pilar melengkung bernuansa arsitektur Yunani modern, dipadukan dengan juntaian dedaunan eucalyptus dan white hydrangea segar.',
      imageUrl: 'https://images.unsplash.com/photo-1519741497674-611481863552?auto=format&fit=crop&w=1000&q=80',
      isHighlight: true
    },
    {
      id: 'p2',
      title: 'Bohemian Glasshouse Garden Wedding',
      category: 'wedding',
      location: 'Pine Hill, Lembang, Bandung',
      year: '2025',
      shortDesc: 'Resepsi pernikahan semi-outdoor di bawah tenda kaca transparan di tengah hutan pinus yang sejuk.',
      fullDesc: 'Menonjolkan keindahan alam pegunungan. Penataan meja tamu bergaya rustic modern dengan aksen pampas grass, lilin pilar kristal, serta lampu festoon hangat.',
      imageUrl: 'https://images.unsplash.com/photo-1511795409834-ef04bbd61622?auto=format&fit=crop&w=1000&q=80',
      isHighlight: true
    },
    {
      id: 'p3',
      title: 'Zen Japanese Courtyard Garden',
      category: 'garden',
      location: 'Pondok Indah Residences, Jakarta',
      year: '2026',
      shortDesc: 'Taman dalam rumah (courtyard) bernuansa Jepang dengan koral putih, bambu kuning, dan jembatan kayu.',
      fullDesc: 'Menyediakan ketenangan meditatif di tengah rumah. Dilengkapi patung batu Tsukubai yang mengalirkan air jernih secara ritmis.',
      imageUrl: 'https://images.unsplash.com/photo-1584622650111-993a426fbf0a?auto=format&fit=crop&w=1000&q=80',
      isHighlight: true
    },
    {
      id: 'p4',
      title: 'Tropical Sanctuary & Infinity Koi Pond',
      category: 'landscape',
      location: 'Sentul City, Bogor',
      year: '2026',
      shortDesc: 'Lanskap halaman belakang seluas 200m² dengan kolam koi berkonsep tepian kaca melayang.',
      fullDesc: 'Perpaduan sempurna antara arsitektur tropis kontemporer dan keasrian alam. Penataan batu andesit susun sirih serta penanaman pohon kamboja merah fosil.',
      imageUrl: 'https://images.unsplash.com/photo-1558904541-efa8c3c8cac1?auto=format&fit=crop&w=1000&q=80',
      isHighlight: true
    }
  ],
  team: [
    {
      name: 'Bagus Setyawan, S.Ds',
      role: 'Founder & Principal Wedding Decorator',
      experience: '10+ Tahun merancang dekorasi pernikahan kelas atas di seluruh Indonesia.',
      imageUrl: 'https://images.unsplash.com/photo-1534528741775-53994a69daeb?auto=format&fit=crop&w=400&q=80'
    },
    {
      name: 'Ir. Dewa Made Artha',
      role: 'Head of Landscape & Horticulture Engineer',
      experience: 'Spesialis arsitektur lanskap tropis & perancangan ekosistem kolam koi bersertifikat.',
      imageUrl: 'https://images.unsplash.com/photo-1507003211169-0a1dd7228f2d?auto=format&fit=crop&w=400&q=80'
    },
    {
      name: 'Clara Shinta',
      role: 'Senior Floral Stylist & Colorist',
      experience: 'Lulusan sekolah floristry internasional dengan ketajaman estetika paduan warna bunga.',
      imageUrl: 'https://images.unsplash.com/photo-1573496359142-b8d87734a5a2?auto=format&fit=crop&w=400&q=80'
    }
  ],
  timeline: [
    {
      year: '2018',
      title: 'Awal Berdiri',
      desc: 'Dimulai dari workshop dekorasi kecil di Selatan Jakarta melayani pesta kebun dan dekorasi lamaran intim.'
    },
    {
      year: '2020',
      title: 'Ekspansi Divisi Landscape',
      desc: 'Merespons tingginya permintaan penataan taman rumah saat pandemi, kami meresmikan divisi spesialis Landscape & Garden.'
    },
    {
      year: '2023',
      title: 'Mitra Hotel & Ballroom Bintang 5',
      desc: 'Dipercaya sebagai rekanan resmi berbagai hotel mewah di Jakarta, Bogor, dan Bandung untuk grand wedding decoration.'
    },
    {
      year: '2026',
      title: '300+ Milestone & Go-Digital',
      desc: 'Menorehkan pencapaian lebih dari 300 proyek sukses serta meluncurkan platform konsultasi interaktif online Tukang Dekorasi.'
    }
  ],
  aboutInfo: {
    title: 'Tentang Tukang Dekorasi',
    subtitle: 'Wujudkan Harmoni Estetika di Setiap Momen & Sudut Rumah Anda',
    desc: 'Tukang Dekorasi adalah perusahaan kreatif berbadan hukum yang bergerak di bidang Decoration & Landscape. Kami lahir dari kecintaan terhadap keindahan visual dan harmoni alam. Dengan filosofi kerja yang menjunjung tinggi kebersihan, ketepatan waktu, serta selera desain premium yang minimalis, kami hadir menjadi jembatan antara imajinasi klien dan kenyataan visual yang memukau.',
    focusAreas: [
      'Dekorasi Pernikahan (Wedding Decoration)',
      'Dekorasi Pelaminan & Akad Nikah',
      'Dekorasi Event & Gathering Perusahaan',
      'Landscape & Pembuatan Taman Rumah',
      'Vertical Garden & Living Wall',
      'Hardscape, Gazebo & Kolam Ikan Koi',
      'Softscape & Perawatan Berkala Taman'
    ],
    vision: 'Menjadi perusahaan jasa dekorasi pernikahan dan kontraktor lanskap taman terdepan di Indonesia yang dikenal karena keanggunan desain minimalis modern dan pelayanan bintang lima.',
    mission: [
      'Mewujudkan konsep dekorasi kustom yang mencerminkan karakter unik setiap klien dengan presisi tinggi.',
      'Menggunakan material alami terbaik serta tanaman sehat berkelanjutan guna menjaga kelestarian lingkungan.',
      'Menjaga standar kerja bersih (clean site policy), tepat waktu, dan komunikatif dari tahap konsultasi hingga purna instalasi.',
      'Memberikan harga transparan yang kompetitif tanpa mereduksi estetika dan kualitas bahan struktural.'
    ],
    values: [
      { title: 'Integritas & Transparansi', desc: 'Jujur dalam perincian bahan baku dan komitmen kesepakatan spesifikasi.' },
      { title: 'Kesempurnaan Estetika (Craftsmanship)', desc: 'Setiap lipatan kain, sudut pemotongan batu, dan tangkai bunga diperhatikan detailnya.' },
      { title: 'Solutif & Fleksibel', desc: 'Siap beradaptasi dengan kondisi lapangan serta memberikan saran terbaik sesuai anggaran klien.' }
    ]
  },
  settings: {
    websiteName: "Tukang Dekorasi",
    logoUrl: "",
    email: "halo@tukangdekorasi.com",
    whatsapp: "+62 812-3456-7890",
    address: "Jl. Kemang Raya No. 45, Mampang Prapatan, Jakarta Selatan 12730",
    operatingHours: "Senin - Sabtu (08:00 - 17:00 WIB)",
    instagram: "https://instagram.com",
    facebook: "https://facebook.com",
    tiktok: "https://tiktok.com",
    metaTitle: "Tukang Dekorasi - Jasa Dekorasi & Landscape Estetik",
    metaDescription: "Spesialis dekorasi pernikahan mewah dan pengerjaan lanskap taman rumah modern.",
    metaKeywords: "dekorasi pernikahan, landscape taman, dekorasi estetik, tukang dekorasi, jasa dekorasi jakarta, dekorasi wedding, pembuatan taman",
    googleVerificationCode: "",
    heroBgUrl: "https://images.unsplash.com/photo-1519741497674-611481863552?auto=format&fit=crop&w=2000&q=85",
    aboutBgUrl: "https://images.unsplash.com/photo-1513694203232-719a280e022f?auto=format&fit=crop&w=1600&q=80",
    serviceBgUrl: "https://images.unsplash.com/photo-1469371670807-013ccf25f16a?auto=format&fit=crop&w=1600&q=80",
    portfolioBgUrl: "https://images.unsplash.com/photo-1523438885200-e635ba2c371e?auto=format&fit=crop&w=1600&q=80",
    contactBgUrl: "https://images.unsplash.com/photo-1520854221256-17451cc35953?auto=format&fit=crop&w=1600&q=80"
  },
  messages: [
    {
      id: 'm1',
      name: 'Rian Hidayat',
      email: 'rian@gmail.com',
      whatsapp: '081299998888',
      serviceType: 'Wedding Decoration',
      message: 'Halo, saya ingin menanyakan paket Wedding Decoration untuk kapasitas 300 undangan di daerah Bogor Barat. Apakah bisa dibantu brosur harganya?',
      date: '2026-06-29T10:00:00.000Z',
      status: 'Unread'
    },
    {
      id: 'm2',
      name: 'Siska Amelia',
      email: 'siska@gmail.com',
      whatsapp: '085712345678',
      serviceType: 'Landscape & Garden Decoration',
      message: 'Selamat sore, mau tanya untuk pembuatan kolam ikan koi minimalis ukuran 3x1.5 meter kisaran harganya berapa ya? Terima kasih.',
      date: '2026-06-28T15:30:00.000Z',
      status: 'Read'
    }
  ],
  adminProfile: {
    name: "Admin Tukang Dekorasi",
    email: "admin@tukangdekorasi.com",
    password: "admin",
    avatarUrl: "https://images.unsplash.com/photo-1534528741775-53994a69daeb?auto=format&fit=crop&w=200&q=80"
  }
};

// Ensure db.json exists
function loadDatabase() {
  if (!fs.existsSync(DB_FILE)) {
    fs.writeFileSync(DB_FILE, JSON.stringify(initialData, null, 2), 'utf-8');
    return initialData;
  }
  try {
    const data = fs.readFileSync(DB_FILE, 'utf-8');
    return JSON.parse(data);
  } catch (err) {
    console.error('Failed to parse db.json, resetting to initialData', err);
    return initialData;
  }
}

function saveDatabase(data: any) {
  fs.writeFileSync(DB_FILE, JSON.stringify(data, null, 2), 'utf-8');
}

// MySQL Load and Sync helpers
async function loadFromMysql() {
  if (!pool) return null;
  try {
    const [reasonsRows]: any = await pool.query('SELECT * FROM reasons');
    const [statsRows]: any = await pool.query('SELECT * FROM stats');
    const [mainServicesRows]: any = await pool.query('SELECT * FROM main_services');
    const [detailedServicesRows]: any = await pool.query('SELECT * FROM detailed_services');
    const [workflowRows]: any = await pool.query('SELECT * FROM workflow_steps');
    const [testimonialRows]: any = await pool.query('SELECT * FROM testimonials');
    const [portfolioRows]: any = await pool.query('SELECT * FROM portfolios');
    const [teamRows]: any = await pool.query('SELECT * FROM team_members');
    const [timelineRows]: any = await pool.query('SELECT * FROM timeline');
    const [aboutRows]: any = await pool.query('SELECT * FROM about_info LIMIT 1');
    const [settingsRows]: any = await pool.query('SELECT * FROM settings LIMIT 1');
    const [messagesRows]: any = await pool.query('SELECT * FROM messages ORDER BY date DESC');
    const [adminRows]: any = await pool.query('SELECT * FROM admin_users LIMIT 1');

    const reasons = reasonsRows.map((r: any) => ({
      title: r.title,
      desc: r.description,
      iconName: r.icon_name
    }));

    const stats = statsRows.map((s: any) => ({
      number: s.number_val,
      label: s.label,
      iconName: s.icon_name
    }));

    const mainServices = mainServicesRows.map((m: any) => ({
      id: m.id,
      title: m.title,
      subtitle: m.subtitle,
      desc: m.description,
      features: JSON.parse(m.features || '[]'),
      imageUrl: m.image_url,
      category: m.category
    }));

    const detailedServices = detailedServicesRows.map((d: any) => ({
      id: d.id,
      category: d.category,
      title: d.title,
      shortDesc: d.short_desc,
      fullDesc: d.full_desc,
      features: JSON.parse(d.features || '[]'),
      imageUrl: d.image_url,
      iconName: d.icon_name
    }));

    const workflowSteps = workflowRows.map((w: any) => ({
      step: w.step_num,
      title: w.title,
      desc: w.description
    }));

    const testimonials = testimonialRows.map((t: any) => ({
      id: t.id,
      name: t.name,
      role: t.role,
      comment: t.comment,
      rating: Number(t.rating || 5),
      avatarUrl: t.avatar_url,
      projectType: t.project_type
    }));

    const portfolios = portfolioRows.map((p: any) => ({
      id: p.id,
      title: p.title,
      category: p.category,
      location: p.location,
      year: p.year,
      shortDesc: p.short_desc,
      fullDesc: p.full_desc,
      imageUrl: p.image_url,
      isHighlight: p.is_highlight === 1 || p.is_highlight === true
    }));

    const team = teamRows.map((tm: any) => ({
      id: tm.id,
      name: tm.name,
      role: tm.role,
      experience: tm.experience,
      imageUrl: tm.image_url
    }));

    const timeline = timelineRows.map((tl: any) => ({
      id: tl.id,
      year: tl.year,
      title: tl.title,
      desc: tl.description
    }));

    let aboutInfo = initialData.aboutInfo;
    if (aboutRows.length > 0) {
      const a = aboutRows[0];
      aboutInfo = {
        title: a.title || initialData.aboutInfo.title,
        subtitle: a.subtitle,
        desc: a.desc,
        focusAreas: JSON.parse(a.focus_areas || '[]'),
        vision: a.vision,
        mission: JSON.parse(a.mission || '[]'),
        values: JSON.parse(a.values || '[]')
      };
    }

    let settings = initialData.settings;
    if (settingsRows.length > 0) {
      const s = settingsRows[0];
      settings = {
        websiteName: s.website_name,
        logoUrl: s.logo_url || '',
        email: s.email,
        whatsapp: s.whatsapp,
        address: s.address,
        operatingHours: s.operating_hours,
        instagram: s.instagram,
        facebook: s.facebook,
        tiktok: s.tiktok,
        metaTitle: s.meta_title,
        metaDescription: s.meta_description,
        metaKeywords: s.meta_keywords || '',
        googleVerificationCode: s.google_verification_code || '',
        heroBgUrl: s.hero_bg_url || initialData.settings.heroBgUrl,
        aboutBgUrl: s.about_bg_url || initialData.settings.aboutBgUrl,
        serviceBgUrl: s.service_bg_url || initialData.settings.serviceBgUrl,
        portfolioBgUrl: s.portfolio_bg_url || initialData.settings.portfolioBgUrl,
        contactBgUrl: s.contact_bg_url || initialData.settings.contactBgUrl
      };
    }

    const messages = messagesRows.map((msg: any) => ({
      id: msg.id,
      name: msg.name,
      email: msg.email,
      whatsapp: msg.whatsapp,
      serviceType: msg.service_type,
      message: msg.message,
      date: new Date(msg.date).toISOString(),
      status: msg.status
    }));

    let adminProfile = initialData.adminProfile;
    if (adminRows.length > 0) {
      const ad = adminRows[0];
      adminProfile = {
        name: ad.name,
        email: ad.email,
        password: ad.password_hash,
        avatarUrl: "https://images.unsplash.com/photo-1534528741775-53994a69daeb?auto=format&fit=crop&w=200&q=80"
      };
    }

    return {
      reasons,
      stats,
      mainServices,
      detailedServices,
      workflowSteps,
      testimonials,
      portfolios,
      team,
      timeline,
      aboutInfo,
      settings,
      messages,
      adminProfile
    };
  } catch (error) {
    console.error('Failed to load data from MySQL, falling back to JSON storage:', error);
    return null;
  }
}

async function syncToMysqlIfEnabled(newData: any) {
  if (!pool) return;
  try {
    if (newData.reasons) {
      await pool.query('DELETE FROM reasons');
      for (const r of newData.reasons) {
        await pool.query('INSERT INTO reasons (title, description, icon_name) VALUES (?, ?, ?)', [r.title, r.desc, r.iconName]);
      }
    }
    if (newData.stats) {
      await pool.query('DELETE FROM stats');
      for (const s of newData.stats) {
        await pool.query('INSERT INTO stats (number_val, label, icon_name) VALUES (?, ?, ?)', [s.number, s.label, s.iconName]);
      }
    }
    if (newData.mainServices) {
      await pool.query('DELETE FROM main_services');
      for (const m of newData.mainServices) {
        await pool.query('INSERT INTO main_services (id, title, subtitle, description, features, image_url, category) VALUES (?, ?, ?, ?, ?, ?, ?)', [
          m.id, m.title, m.subtitle, m.desc, JSON.stringify(m.features), m.imageUrl, m.category
        ]);
      }
    }
    if (newData.detailedServices) {
      await pool.query('DELETE FROM detailed_services');
      for (const d of newData.detailedServices) {
        await pool.query('INSERT INTO detailed_services (id, category, title, short_desc, full_desc, features, image_url, icon_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?)', [
          d.id, d.category, d.title, d.shortDesc, d.fullDesc, JSON.stringify(d.features), d.imageUrl, d.iconName
        ]);
      }
    }
    if (newData.workflowSteps) {
      await pool.query('DELETE FROM workflow_steps');
      for (const w of newData.workflowSteps) {
        await pool.query('INSERT INTO workflow_steps (step_num, title, description) VALUES (?, ?, ?)', [w.step, w.title, w.desc]);
      }
    }
    if (newData.testimonials) {
      await pool.query('DELETE FROM testimonials');
      for (const t of newData.testimonials) {
        await pool.query('INSERT INTO testimonials (id, name, role, comment, rating, avatar_url, project_type) VALUES (?, ?, ?, ?, ?, ?, ?)', [
          t.id, t.name, t.role, t.comment, t.rating, t.avatarUrl, t.projectType
        ]);
      }
    }
    if (newData.portfolios) {
      await pool.query('DELETE FROM portfolios');
      for (const p of newData.portfolios) {
        await pool.query('INSERT INTO portfolios (id, title, category, location, year, short_desc, full_desc, image_url, is_highlight) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)', [
          p.id, p.title, p.category, p.location, p.year, p.shortDesc, p.fullDesc, p.imageUrl, p.isHighlight ? 1 : 0
        ]);
      }
    }
    if (newData.team) {
      await pool.query('DELETE FROM team_members');
      for (const tm of newData.team) {
        await pool.query('INSERT INTO team_members (name, role, experience, image_url) VALUES (?, ?, ?, ?)', [
          tm.name, tm.role, tm.experience, tm.imageUrl
        ]);
      }
    }
    if (newData.timeline) {
      await pool.query('DELETE FROM timeline');
      for (const tl of newData.timeline) {
        await pool.query('INSERT INTO timeline (year, title, description) VALUES (?, ?, ?)', [
          tl.year, tl.title, tl.desc
        ]);
      }
    }
    if (newData.aboutInfo) {
      await pool.query('DELETE FROM about_info');
      const a = newData.aboutInfo;
      await pool.query('INSERT INTO about_info (subtitle, desc, focus_areas, vision, mission, `values`) VALUES (?, ?, ?, ?, ?, ?)', [
        a.subtitle, a.desc, JSON.stringify(a.focusAreas), a.vision, JSON.stringify(a.mission), JSON.stringify(a.values)
      ]);
    }
    if (newData.settings) {
      await pool.query('DELETE FROM settings');
      const s = newData.settings;
      await pool.query('INSERT INTO settings (website_name, logo_url, email, whatsapp, address, operating_hours, instagram, facebook, tiktok, meta_title, meta_description, hero_bg_url, about_bg_url, service_bg_url, portfolio_bg_url, contact_bg_url, google_verification_code, meta_keywords) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [
        s.websiteName, s.logoUrl || '', s.email, s.whatsapp, s.address, s.operatingHours, s.instagram, s.facebook, s.tiktok, s.metaTitle, s.metaDescription, s.heroBgUrl || '', s.aboutBgUrl || '', s.serviceBgUrl || '', s.portfolioBgUrl || '', s.contactBgUrl || '', s.googleVerificationCode || '', s.metaKeywords || ''
      ]);
    }
  } catch (error) {
    console.error('Failed to sync to MySQL:', error);
  }
}

// Routes
// 1. Get entire content
app.get('/api/content', async (req, res) => {
  let db = null;
  if (pool) {
    db = await loadFromMysql();
  }
  if (!db) {
    db = loadDatabase();
  }
  // Don't send password in general fetch
  const { adminProfile, ...publicData } = db;
  res.json(publicData);
});

// 2. Save entire public content or sub-fields
app.post('/api/content', async (req, res) => {
  const db = loadDatabase();
  const newData = req.body;

  // Selective merge to avoid overwriting password or adminProfile completely
  if (newData.reasons) db.reasons = newData.reasons;
  if (newData.stats) db.stats = newData.stats;
  if (newData.mainServices) db.mainServices = newData.mainServices;
  if (newData.detailedServices) db.detailedServices = newData.detailedServices;
  if (newData.workflowSteps) db.workflowSteps = newData.workflowSteps;
  if (newData.testimonials) db.testimonials = newData.testimonials;
  if (newData.portfolios) db.portfolios = newData.portfolios;
  if (newData.team) db.team = newData.team;
  if (newData.timeline) db.timeline = newData.timeline;
  if (newData.aboutInfo) db.aboutInfo = newData.aboutInfo;
  if (newData.settings) db.settings = newData.settings;

  saveDatabase(db);
  
  if (pool) {
    await syncToMysqlIfEnabled(newData);
  }
  
  res.json({ success: true, message: 'Content updated successfully' });
});

// 3. Admin Login
app.post('/api/auth/login', async (req, res) => {
  const { email, password } = req.body;
  
  if (pool) {
    try {
      const [rows]: any = await pool.query('SELECT * FROM admin_users WHERE email = ?', [email]);
      if (rows.length > 0) {
        const user = rows[0];
        if (user.password_hash === password) {
          return res.json({
            success: true,
            token: 'mock-session-jwt-token-12345',
            user: {
              name: user.name,
              email: user.email,
              avatarUrl: "https://images.unsplash.com/photo-1534528741775-53994a69daeb?auto=format&fit=crop&w=200&q=80"
            }
          });
        }
      }
    } catch (err) {
      console.error('MySQL authentication error:', err);
    }
  }

  // Fallback to db.json
  const db = loadDatabase();
  if (db.adminProfile.email === email && db.adminProfile.password === password) {
    res.json({
      success: true,
      token: 'mock-session-jwt-token-12345',
      user: {
        name: db.adminProfile.name,
        email: db.adminProfile.email,
        avatarUrl: db.adminProfile.avatarUrl
      }
    });
  } else {
    res.status(401).json({ success: false, message: 'Email atau password salah' });
  }
});

// 4. Update Profile
app.post('/api/admin/profile', async (req, res) => {
  const { name, email, password, avatarUrl } = req.body;
  const db = loadDatabase();
  if (name) db.adminProfile.name = name;
  if (email) db.adminProfile.email = email;
  if (password) db.adminProfile.password = password;
  if (avatarUrl) db.adminProfile.avatarUrl = avatarUrl;
  saveDatabase(db);

  if (pool) {
    try {
      const [rows]: any = await pool.query('SELECT * FROM admin_users LIMIT 1');
      if (rows.length > 0) {
        const u = rows[0];
        const newName = name || u.name;
        const newEmail = email || u.email;
        const newPassword = password || u.password_hash;
        await pool.query('UPDATE admin_users SET name = ?, email = ?, password_hash = ? WHERE id = ?', [
          newName, newEmail, newPassword, u.id
        ]);
      } else {
        await pool.query('INSERT INTO admin_users (name, email, password_hash) VALUES (?, ?, ?)', [
          name || 'Admin', email || 'admin@tukangdekorasi.com', password || 'admin'
        ]);
      }
    } catch (err) {
      console.error('MySQL profile update error:', err);
    }
  }

  res.json({ success: true, user: { name: db.adminProfile.name, email: db.adminProfile.email, avatarUrl: db.adminProfile.avatarUrl } });
});

// 5. Submit Message from website
app.post('/api/messages', async (req, res) => {
  const { name, email, whatsapp, serviceType, message } = req.body;
  if (!name || !whatsapp) {
    return res.status(400).json({ success: false, message: 'Nama dan WhatsApp wajib diisi' });
  }
  const db = loadDatabase();
  const newMessage = {
    id: 'm_' + Date.now(),
    name,
    email: email || '',
    whatsapp,
    serviceType: serviceType || 'Umum',
    message: message || '',
    date: new Date().toISOString(),
    status: 'Unread'
  };
  db.messages.unshift(newMessage);
  saveDatabase(db);

  if (pool) {
    try {
      await pool.query('INSERT INTO messages (id, name, email, whatsapp, service_type, message, status) VALUES (?, ?, ?, ?, ?, ?, ?)', [
        newMessage.id, newMessage.name, newMessage.email, newMessage.whatsapp, newMessage.serviceType, newMessage.message, newMessage.status
      ]);
    } catch (err) {
      console.error('MySQL message insertion error:', err);
    }
  }

  res.json({ success: true, message: 'Pesan berhasil dikirim', data: newMessage });
});

// 6. Manage Messages (Update/Delete)
app.get('/api/messages', async (req, res) => {
  let messages = null;
  if (pool) {
    try {
      const [rows]: any = await pool.query('SELECT * FROM messages ORDER BY date DESC');
      messages = rows.map((msg: any) => ({
        id: msg.id,
        name: msg.name,
        email: msg.email,
        whatsapp: msg.whatsapp,
        serviceType: msg.service_type,
        message: msg.message,
        date: new Date(msg.date).toISOString(),
        status: msg.status
      }));
    } catch (err) {
      console.error('MySQL messages fetch error:', err);
    }
  }

  if (!messages) {
    const db = loadDatabase();
    messages = db.messages;
  }
  
  res.json(messages);
});

app.patch('/api/messages/:id', async (req, res) => {
  const { id } = req.params;
  const { status } = req.body;
  
  const db = loadDatabase();
  const msgIdx = db.messages.findIndex((m: any) => m.id === id);
  if (msgIdx !== -1) {
    db.messages[msgIdx].status = status;
    saveDatabase(db);

    if (pool) {
      try {
        await pool.query('UPDATE messages SET status = ? WHERE id = ?', [status, id]);
      } catch (err) {
        console.error('MySQL message update error:', err);
      }
    }

    return res.json({ success: true, message: 'Status pesan berhasil diubah', data: db.messages[msgIdx] });
  }
  res.status(404).json({ success: false, message: 'Pesan tidak ditemukan' });
});

app.delete('/api/messages/:id', async (req, res) => {
  const { id } = req.params;
  const db = loadDatabase();
  const initialCount = db.messages.length;
  db.messages = db.messages.filter((m: any) => m.id !== id);
  if (db.messages.length < initialCount) {
    saveDatabase(db);

    if (pool) {
      try {
        await pool.query('DELETE FROM messages WHERE id = ?', [id]);
      } catch (err) {
        console.error('MySQL message deletion error:', err);
      }
    }

    return res.json({ success: true, message: 'Pesan berhasil dihapus' });
  }
  res.status(404).json({ success: false, message: 'Pesan tidak ditemukan' });
});

// Vite Setup
async function startServer() {
  if (useMysql && pool) {
    try {
      console.log('Verifying MySQL Database connection...');
      // Run a quick query to test the connection (timeout after 3 seconds)
      await Promise.race([
        pool.query('SELECT 1'),
        new Promise((_, reject) => setTimeout(() => reject(new Error('Database connection timed out (3s)')), 3000))
      ]);
      console.log('MySQL Database connection verified successfully.');
      await ensureSettingsColumns();
    } catch (err: any) {
      console.warn('MySQL connection test failed. Disabling MySQL and falling back to JSON storage (db.json). Reason:', err.message);
      pool = null; // Disable pool entirely so that all endpoints fallback to JSON database cleanly
    }
  }

  if (process.env.NODE_ENV !== "production") {
    const vite = await createViteServer({
      server: { middlewareMode: true },
      appType: "spa",
    });
    app.use(vite.middlewares);
  } else {
    const distPath = path.join(process.cwd(), 'dist');
    app.use(express.static(distPath));
    app.get('*', (req, res) => {
      res.sendFile(path.join(distPath, 'index.html'));
    });
  }

  app.listen(PORT, "0.0.0.0", () => {
    console.log(`Server running on port ${PORT}`);
  });
}

startServer();
