const { DatabaseSync } = require('node:sqlite');
const path = require('node:path');
const fs = require('node:fs');
const crypto = require('crypto');

const dbPath = path.join(__dirname, 'hotel.db');
const db = new DatabaseSync(dbPath);

function initDatabase() {
  // Enable foreign keys
  db.exec('PRAGMA foreign_keys = ON;');

  // Create tables
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      role TEXT NOT NULL,
      name TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS floors (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE
    );

    CREATE TABLE IF NOT EXISTS room_types (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      base_price REAL NOT NULL
    );

    CREATE TABLE IF NOT EXISTS rooms (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      room_number TEXT UNIQUE NOT NULL,
      room_type_id INTEGER NOT NULL,
      floor_id INTEGER NOT NULL,
      status TEXT DEFAULT 'Vacant',
      FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE RESTRICT,
      FOREIGN KEY (floor_id) REFERENCES floors(id) ON DELETE RESTRICT
    );

    CREATE TABLE IF NOT EXISTS guests (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      photo TEXT, -- base64 representation of guest photo
      doc_type TEXT, -- Passport / Aadhaar / etc.
      doc_number TEXT,
      mobile TEXT NOT NULL UNIQUE,
      email TEXT,
      address TEXT,
      company_name TEXT,
      gst_number TEXT,
      vehicle_number TEXT,
      loyalty_points INTEGER DEFAULT 0,
      is_vip INTEGER DEFAULT 0,
      is_blacklisted INTEGER DEFAULT 0,
      blacklist_reason TEXT,
      assigned_staff_id INTEGER DEFAULT NULL,
      reply_bot_enabled INTEGER DEFAULT 1,
      FOREIGN KEY (assigned_staff_id) REFERENCES users(id) ON DELETE SET NULL
    );

    CREATE TABLE IF NOT EXISTS bookings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      booking_uid TEXT UNIQUE NOT NULL,
      guest_id INTEGER NOT NULL,
      check_in TEXT NOT NULL,
      check_out TEXT NOT NULL,
      status TEXT DEFAULT 'Confirmed', -- Confirmed, Checked-In, Checked-Out, Cancelled
      total_amount REAL NOT NULL,
      paid_amount REAL DEFAULT 0,
      gst_amount REAL DEFAULT 0,
      invoice_number TEXT UNIQUE,
      created_at TEXT NOT NULL,
      FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE RESTRICT
    );

    CREATE TABLE IF NOT EXISTS booking_rooms (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      booking_id INTEGER NOT NULL,
      room_id INTEGER NOT NULL,
      price_per_night REAL NOT NULL,
      FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE,
      FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE RESTRICT
    );

    CREATE TABLE IF NOT EXISTS room_pricing (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      room_type_id INTEGER NOT NULL,
      date TEXT NOT NULL, -- YYYY-MM-DD
      price REAL NOT NULL,
      UNIQUE(room_type_id, date),
      FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS housekeeping (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      room_id INTEGER NOT NULL,
      staff_id INTEGER,
      date TEXT NOT NULL,
      status TEXT DEFAULT 'Pending', -- Pending, Cleaning, Cleaned, Maintenance
      notes TEXT,
      FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
      FOREIGN KEY (staff_id) REFERENCES users(id) ON DELETE SET NULL
    );

    CREATE TABLE IF NOT EXISTS lost_found (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      item TEXT NOT NULL,
      description TEXT,
      date TEXT NOT NULL,
      location TEXT,
      status TEXT DEFAULT 'Reported', -- Reported, Claimed
      contact_name TEXT,
      contact_phone TEXT
    );

    CREATE TABLE IF NOT EXISTS inventory (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT UNIQUE NOT NULL,
      quantity INTEGER NOT NULL DEFAULT 0,
      min_quantity INTEGER NOT NULL DEFAULT 0,
      category TEXT NOT NULL -- Amenities, Linens, Cleaning, Food & Beverage
    );

    CREATE TABLE IF NOT EXISTS attendance (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      date TEXT NOT NULL, -- YYYY-MM-DD
      status TEXT NOT NULL, -- Present, Absent, Late, Leave
      clock_in TEXT,
      clock_out TEXT,
      UNIQUE(user_id, date),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS salaries (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER NOT NULL,
      month TEXT NOT NULL, -- YYYY-MM
      base_salary REAL NOT NULL,
      bonus REAL DEFAULT 0,
      deductions REAL DEFAULT 0,
      paid_date TEXT,
      UNIQUE(user_id, month),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS ota_bookings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      channel TEXT NOT NULL,
      ota_booking_uid TEXT UNIQUE NOT NULL,
      guest_name TEXT NOT NULL,
      guest_mobile TEXT NOT NULL,
      check_in TEXT NOT NULL,
      check_out TEXT NOT NULL,
      room_type_id INTEGER NOT NULL,
      amount REAL NOT NULL,
      status TEXT DEFAULT 'Pending',
      FOREIGN KEY (room_type_id) REFERENCES room_types(id)
    );

    CREATE TABLE IF NOT EXISTS customer_reviews (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      source TEXT NOT NULL,
      guest_name TEXT NOT NULL,
      rating INTEGER NOT NULL,
      comment TEXT,
      reply TEXT,
      timestamp TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS audit_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      action TEXT NOT NULL,
      timestamp TEXT NOT NULL,
      details TEXT,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    );

    CREATE TABLE IF NOT EXISTS settings (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS user_sessions (
      token TEXT PRIMARY KEY,
      user_id INTEGER NOT NULL,
      created_at TEXT NOT NULL,
      expires_at TEXT NOT NULL,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS whatsapp_messages (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      wamid TEXT UNIQUE,
      guest_id INTEGER NOT NULL,
      direction TEXT NOT NULL,
      sender_type TEXT NOT NULL,
      body TEXT,
      media_type TEXT DEFAULT 'text',
      media_url TEXT,
      status TEXT DEFAULT 'sent',
      timestamp TEXT NOT NULL,
      starred INTEGER DEFAULT 0,
      archived INTEGER DEFAULT 0,
      FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS email_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      guest_id INTEGER NOT NULL,
      recipient_email TEXT NOT NULL,
      cc_emails TEXT,
      bcc_emails TEXT,
      subject TEXT,
      body TEXT,
      attachment_name TEXT,
      status TEXT DEFAULT 'sent',
      error_message TEXT,
      timestamp TEXT NOT NULL,
      FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS whatsapp_phone_numbers (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      phone_number_id TEXT UNIQUE NOT NULL,
      display_number TEXT NOT NULL,
      display_name TEXT NOT NULL,
      waba_id TEXT NOT NULL,
      access_token TEXT NOT NULL,
      status TEXT DEFAULT 'Disconnected',
      messaging_tier TEXT DEFAULT 'Tier 1',
      quality_rating TEXT DEFAULT 'High',
      is_default INTEGER DEFAULT 0,
      disabled INTEGER DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS background_jobs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      job_type TEXT NOT NULL,
      payload TEXT NOT NULL,
      status TEXT DEFAULT 'pending',
      retry_count INTEGER DEFAULT 0,
      max_retries INTEGER DEFAULT 3,
      error_message TEXT,
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS whatsapp_automation_map (
      event_key TEXT PRIMARY KEY,
      template_name TEXT,
      enabled INTEGER DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS google_locations (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      location_id TEXT UNIQUE NOT NULL,
      account_id TEXT NOT NULL,
      business_name TEXT NOT NULL,
      primary_category TEXT,
      email TEXT,
      access_token TEXT NOT NULL,
      refresh_token TEXT NOT NULL,
      status TEXT DEFAULT 'Connected',
      address TEXT,
      phone TEXT,
      website TEXT,
      rating REAL DEFAULT 0.0,
      total_reviews INTEGER DEFAULT 0,
      completion_score INTEGER DEFAULT 100,
      seo_health_score INTEGER DEFAULT 100
    );

    CREATE TABLE IF NOT EXISTS google_reviews (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      review_id TEXT UNIQUE NOT NULL,
      location_id TEXT NOT NULL,
      reviewer_name TEXT NOT NULL,
      reviewer_photo TEXT,
      rating INTEGER NOT NULL,
      comment TEXT,
      reply_text TEXT,
      reply_time TEXT,
      review_time TEXT NOT NULL,
      status TEXT DEFAULT 'Not Replied',
      assigned_staff_id INTEGER,
      is_handled INTEGER DEFAULT 0,
      FOREIGN KEY (location_id) REFERENCES google_locations(location_id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS google_posts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      post_id TEXT UNIQUE,
      location_id TEXT NOT NULL,
      title TEXT,
      summary TEXT NOT NULL,
      post_type TEXT DEFAULT 'whats_new',
      image_url TEXT,
      call_to_action TEXT,
      action_url TEXT,
      start_date TEXT,
      end_date TEXT,
      coupon_code TEXT,
      terms_conditions TEXT,
      schedule_time TEXT,
      status TEXT DEFAULT 'draft',
      created_at TEXT NOT NULL,
      published_at TEXT,
      FOREIGN KEY (location_id) REFERENCES google_locations(location_id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS google_keywords (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      location_id TEXT NOT NULL,
      keyword TEXT NOT NULL,
      category TEXT DEFAULT 'primary',
      search_volume INTEGER DEFAULT 0,
      competition TEXT DEFAULT 'low',
      ranking_position INTEGER DEFAULT 0,
      click_potential INTEGER DEFAULT 0,
      FOREIGN KEY (location_id) REFERENCES google_locations(location_id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS google_insights (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      location_id TEXT NOT NULL,
      metric_date TEXT NOT NULL,
      search_views INTEGER DEFAULT 0,
      maps_views INTEGER DEFAULT 0,
      website_clicks INTEGER DEFAULT 0,
      phone_calls INTEGER DEFAULT 0,
      direction_requests INTEGER DEFAULT 0,
      booking_clicks INTEGER DEFAULT 0,
      photo_views INTEGER DEFAULT 0,
      UNIQUE(location_id, metric_date),
      FOREIGN KEY (location_id) REFERENCES google_locations(location_id) ON DELETE CASCADE
    );

    CREATE TABLE IF NOT EXISTS google_competitors (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      location_id TEXT NOT NULL,
      name TEXT NOT NULL,
      rating REAL DEFAULT 0.0,
      total_reviews INTEGER DEFAULT 0,
      primary_category TEXT,
      search_visibility INTEGER DEFAULT 0,
      FOREIGN KEY (location_id) REFERENCES google_locations(location_id) ON DELETE CASCADE
    );
  `);

  // Alter rooms table dynamically to add price column if not exists
  try {
    db.exec('ALTER TABLE rooms ADD COLUMN price REAL;');
    // Set custom prices based on room types as defaults
    db.exec(`
      UPDATE rooms 
      SET price = (
        SELECT base_price FROM room_types 
        WHERE room_types.id = rooms.room_type_id
      )
      WHERE price IS NULL;
    `);
  } catch (e) {
    // Column already exists, safe to ignore
  }

  // Alter guests table dynamically
  try {
    db.exec('ALTER TABLE guests ADD COLUMN assigned_staff_id INTEGER DEFAULT NULL;');
  } catch (e) {}
  try {
    db.exec('ALTER TABLE guests ADD COLUMN reply_bot_enabled INTEGER DEFAULT 1;');
  } catch (e) {}
  try {
    db.exec('ALTER TABLE guests ADD COLUMN is_starred INTEGER DEFAULT 0;');
  } catch (e) {}
  try {
    db.exec('ALTER TABLE guests ADD COLUMN is_archived INTEGER DEFAULT 0;');
  } catch (e) {}

  // Alter users table dynamically
  try {
    db.exec('ALTER TABLE users ADD COLUMN salt TEXT;');
  } catch (e) {}

  // Alter google_posts table dynamically
  try {
    db.exec('ALTER TABLE google_posts ADD COLUMN title TEXT;');
  } catch (e) {}
  try {
    db.exec('ALTER TABLE google_posts ADD COLUMN coupon_code TEXT;');
  } catch (e) {}
  try {
    db.exec('ALTER TABLE google_posts ADD COLUMN terms_conditions TEXT;');
  } catch (e) {}

  // Insert default users (stored as secure SHA256 hashes with unique salts)
  const userCount = db.prepare('SELECT COUNT(*) as count FROM users').get().count;
  if (userCount === 0) {
    const insertUser = db.prepare('INSERT INTO users (username, password, role, name, salt) VALUES (?, ?, ?, ?, ?)');
    
    const seedUser = (username, plainPassword, role, name) => {
      const salt = crypto.randomBytes(16).toString('hex');
      const hash = crypto.createHmac('sha256', salt).update(plainPassword).digest('hex');
      insertUser.run(username, hash, role, name, salt);
    };

    seedUser('admin', 'admin123', 'Admin', 'System Administrator');
    seedUser('manager', 'manager123', 'Manager', 'Hotel Manager');
    seedUser('reception', 'reception123', 'Reception', 'Reception Desk Staff');
    seedUser('hk', 'hk123', 'Housekeeping', 'Housekeeping Lead');
  } else {
    // Migration: hash any existing plain text passwords
    const users = db.prepare('SELECT id, password, salt FROM users').all();
    const updateUser = db.prepare('UPDATE users SET password = ?, salt = ? WHERE id = ?');
    users.forEach(u => {
      if (!u.salt) {
        const salt = crypto.randomBytes(16).toString('hex');
        const hash = crypto.createHmac('sha256', salt).update(u.password).digest('hex');
        updateUser.run(hash, salt, u.id);
      }
    });
  }

  // Seed Floors
  const floorCount = db.prepare('SELECT COUNT(*) as count FROM floors').get().count;
  if (floorCount === 0) {
    const insertFloor = db.prepare('INSERT INTO floors (name) VALUES (?)');
    insertFloor.run('Floor 1');
    insertFloor.run('Floor 2');
    insertFloor.run('Floor 3');
  }

  // Seed Room Types
  const typeCount = db.prepare('SELECT COUNT(*) as count FROM room_types').get().count;
  if (typeCount === 0) {
    const insertType = db.prepare('INSERT INTO room_types (name, base_price) VALUES (?, ?)');
    insertType.run('Deluxe AC', 4000);
    insertType.run('Super Deluxe AC', 5500);
    insertType.run('Executive Suite', 8000);
    insertType.run('Standard Non-AC', 2000);
  }

  // Seed Rooms
  const roomCount = db.prepare('SELECT COUNT(*) as count FROM rooms').get().count;
  if (roomCount === 0) {
    const insertRoom = db.prepare('INSERT INTO rooms (room_number, room_type_id, floor_id, status) VALUES (?, ?, ?, ?)');
    // Get floor IDs
    const f1 = db.prepare("SELECT id FROM floors WHERE name = 'Floor 1'").get().id;
    const f2 = db.prepare("SELECT id FROM floors WHERE name = 'Floor 2'").get().id;
    const f3 = db.prepare("SELECT id FROM floors WHERE name = 'Floor 3'").get().id;

    // Get room type IDs
    const tDeluxe = db.prepare("SELECT id FROM room_types WHERE name = 'Deluxe AC'").get().id;
    const tSuper = db.prepare("SELECT id FROM room_types WHERE name = 'Super Deluxe AC'").get().id;
    const tSuite = db.prepare("SELECT id FROM room_types WHERE name = 'Executive Suite'").get().id;
    const tStandard = db.prepare("SELECT id FROM room_types WHERE name = 'Standard Non-AC'").get().id;

    // Floor 1 Rooms
    insertRoom.run('101', tDeluxe, f1, 'Vacant');
    insertRoom.run('102', tDeluxe, f1, 'Vacant');
    insertRoom.run('103', tDeluxe, f1, 'Vacant');
    insertRoom.run('104', tDeluxe, f1, 'Vacant');
    insertRoom.run('105', tStandard, f1, 'Vacant');

    // Floor 2 Rooms
    insertRoom.run('201', tSuper, f2, 'Vacant');
    insertRoom.run('202', tSuper, f2, 'Vacant');
    insertRoom.run('203', tDeluxe, f2, 'Vacant');
    insertRoom.run('204', tSuite, f2, 'Vacant');
    insertRoom.run('205', tSuite, f2, 'Vacant');

    // Floor 3 Rooms
    insertRoom.run('301', tSuper, f3, 'Vacant');
    insertRoom.run('302', tSuper, f3, 'Vacant');
    insertRoom.run('303', tSuper, f3, 'Vacant');
    insertRoom.run('304', tSuite, f3, 'Vacant');
    insertRoom.run('305', tSuite, f3, 'Vacant');
  }

  // Seed Inventory
  const invCount = db.prepare('SELECT COUNT(*) as count FROM inventory').get().count;
  if (invCount === 0) {
    const insertInv = db.prepare('INSERT INTO inventory (name, quantity, min_quantity, category) VALUES (?, ?, ?, ?)');
    insertInv.run('Soap', 150, 20, 'Amenities');
    insertInv.run('Shampoo', 150, 20, 'Amenities');
    insertInv.run('Water Bottle', 200, 30, 'Food & Beverage');
    insertInv.run('Tea', 300, 50, 'Food & Beverage');
    insertInv.run('Coffee', 300, 50, 'Food & Beverage');
    insertInv.run('Milk Powder', 300, 50, 'Food & Beverage');
    insertInv.run('Towels', 80, 15, 'Linens');
    insertInv.run('Bedsheets', 60, 10, 'Linens');
    insertInv.run('Pillows', 40, 8, 'Linens');
    insertInv.run('Cleaning Materials', 50, 5, 'Cleaning');
  }

  // Seed Settings individually
  const insertSetting = db.prepare('INSERT OR IGNORE INTO settings (key, value) VALUES (?, ?)');
  insertSetting.run('hotel_name', 'Shaima Hotel Management');
  insertSetting.run('wifi_password', 'Shaima@2026');
  insertSetting.run('reception_phone', '9876543210');
  insertSetting.run('gst_rate', '5'); // 5% GST
  insertSetting.run('hotel_address', '123 Beach Road, Chennai, Tamil Nadu, India');
  insertSetting.run('hotel_gstin', '33AAAAA1111A1Z1');
  insertSetting.run('upi_id', 'shaimahotel@upi');
  insertSetting.run('backup_dir', path.join(__dirname, 'backups'));

  // Templates for WhatsApp auto messages
  const whatsappTemplates = {
    booking_confirmation: 'Dear {guest_name},\nYour booking is confirmed.\nBooking ID : {booking_uid}\nCheck-In : {check_in}\nCheck-Out : {check_out}\nRoom : {room_type}\nThank you.\nShaima Hotel Management',
    welcome: 'Welcome to Shaima Hotel Management.\nRoom Number : {room_numbers}\nWiFi Password : {wifi_password}\nReception : {reception_phone}\nEnjoy your stay.',
    invoice: 'Thank you for staying with us.\nInvoice Amount : ₹{amount}\nInvoice PDF Attached.\nVisit Again.',
    checkout: 'Thank you for visiting.\nPlease rate us on Google.\n⭐⭐⭐⭐⭐',
    birthday: 'Happy Birthday.\nWe wish you a wonderful year.\nEnjoy 20% discount on your next stay.'
  };
  insertSetting.run('whatsapp_templates', JSON.stringify(whatsappTemplates));

  // Seed default automation mapping
  const events = [
    'booking_confirmation', 'advance_payment_request', 'payment_confirmation', 'reservation_voucher',
    'checkin_reminder', 'welcome_message', 'room_ready_notification', 'upgrade_notification',
    'early_checkin', 'late_checkout', 'checkout_reminder', 'invoice', 'receipt',
    'thank_you_message', 'feedback_request', 'review_request'
  ];
  
  let hasMapping = false;
  try {
    const row = db.prepare("SELECT COUNT(*) as count FROM whatsapp_automation_map").get();
    if (row.count > 0) hasMapping = true;
  } catch (e) {}

  if (!hasMapping) {
    const insertMap = db.prepare("INSERT OR IGNORE INTO whatsapp_automation_map (event_key, template_name, enabled) VALUES (?, ?, 0)");
    events.forEach(ev => {
      insertMap.run(ev, '');
    });
  }

  // Seed Google Business Profile Demo Data
  let hasGmbLocation = false;
  try {
    const row = db.prepare("SELECT COUNT(*) as count FROM google_locations").get();
    if (row.count > 0) hasGmbLocation = true;
  } catch (e) {}

  if (!hasGmbLocation) {
    db.prepare(`
      INSERT INTO google_locations (
        location_id, account_id, business_name, primary_category, email, access_token, refresh_token, status, address, phone, website, rating, total_reviews, completion_score, seo_health_score
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      'locations/67890',
      'accounts/12345',
      'Shaima Hotel & Marina Residency',
      'Hotel',
      'seo@shaimahotels.com',
      'mock_access_token_123',
      'mock_refresh_token_123',
      'Connected',
      '123 Beach Road, Chennai, Tamil Nadu, India',
      '+91 98765 43210',
      'https://shaimahotel.com',
      4.8,
      142,
      95,
      92
    );

    const insertReview = db.prepare(`
      INSERT INTO google_reviews (review_id, location_id, reviewer_name, reviewer_photo, rating, comment, reply_text, reply_time, review_time, status, assigned_staff_id, is_handled)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    
    const today = new Date();
    const d1 = new Date(today.getTime() - 2 * 24 * 60 * 60 * 1000).toISOString();
    const d2 = new Date(today.getTime() - 5 * 24 * 60 * 60 * 1000).toISOString();
    const d3 = new Date(today.getTime() - 10 * 24 * 60 * 60 * 1000).toISOString();

    insertReview.run('rev_001', 'locations/67890', 'John Doe', '', 5, 'Absolutely loved my stay at Shaima Hotel. The room rent was very reasonable and the housekeeping staff kept everything sparkling clean!', 'Thank you John! We hope to welcome you again soon.', new Date().toISOString(), d1, 'Replied', null, 1);
    insertReview.run('rev_002', 'locations/67890', 'Alice Smith', '', 4, 'Nice hotel, good location close to Marina beach. The wifi connection could be faster though.', null, null, d2, 'Not Replied', null, 0);
    insertReview.run('rev_003', 'locations/67890', 'Bob Johnson', '', 2, 'Terrible experience, check-out took forever and the reception desk staff was unhelpful.', null, null, d3, 'Not Replied', null, 0);

    const insertPost = db.prepare(`
      INSERT INTO google_posts (post_id, location_id, summary, post_type, image_url, call_to_action, action_url, start_date, end_date, schedule_time, status, created_at, published_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    insertPost.run('post_001', 'locations/67890', 'Special Weekend Deal! Book standard AC rooms at 15% discount. Offer valid till Sunday.', 'offer', '/uploads/deal_banner.jpg', 'BOOK', 'https://shaimahotel.com/book', '2026-07-10', '2026-07-12', null, 'published', new Date().toISOString(), new Date().toISOString());
    insertPost.run('post_002', 'locations/67890', 'Celebrate Monsoon Season with our special holiday package. 3 Nights stay including free breakfast.', 'whats_new', '/uploads/monsoon_package.jpg', 'LEARN_MORE', 'https://shaimahotel.com/monsoon', null, null, null, 'draft', new Date().toISOString(), null);

    const insertKeyword = db.prepare(`
      INSERT INTO google_keywords (location_id, keyword, category, search_volume, competition, ranking_position, click_potential)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);
    insertKeyword.run('locations/67890', 'hotel in chennai', 'primary', 12000, 'high', 3, 85);
    insertKeyword.run('locations/67890', 'budget hotel beach road', 'primary', 3400, 'medium', 2, 90);
    insertKeyword.run('locations/67890', 'marina residency booking', 'secondary', 800, 'low', 1, 95);
    insertKeyword.run('locations/67890', 'hotel with swimming pool chennai', 'long_tail', 1800, 'medium', 5, 70);

    const insertCompetitor = db.prepare(`
      INSERT INTO google_competitors (location_id, name, rating, total_reviews, primary_category, search_visibility)
      VALUES (?, ?, ?, ?, ?, ?)
    `);
    insertCompetitor.run('locations/67890', 'The Leela Palace Chennai', 4.7, 9438, 'Hotel', 95);
    insertCompetitor.run('locations/67890', 'Taj Coromandel', 4.6, 7820, 'Hotel', 88);
    insertCompetitor.run('locations/67890', 'ITC Grand Chola', 4.8, 18450, 'Hotel', 97);

    const insertInsight = db.prepare(`
      INSERT INTO google_insights (location_id, metric_date, search_views, maps_views, website_clicks, phone_calls, direction_requests, booking_clicks, photo_views)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    for (let i = 6; i >= 0; i--) {
      const dt = new Date(today.getTime() - i * 24 * 60 * 60 * 1000).toISOString().split('T')[0];
      insertInsight.run(
        'locations/67890',
        dt,
        Math.floor(100 + Math.random() * 50),
        Math.floor(200 + Math.random() * 100),
        Math.floor(10 + Math.random() * 15),
        Math.floor(5 + Math.random() * 10),
        Math.floor(15 + Math.random() * 20),
        Math.floor(2 + Math.random() * 5),
        Math.floor(50 + Math.random() * 50)
      );
    }
  }

  // Create backup directory if it doesn't exist
  const backupDir = path.join(__dirname, 'backups');
  if (!fs.existsSync(backupDir)) {
    fs.mkdirSync(backupDir, { recursive: true });
  }
}

module.exports = {
  db,
  initDatabase,
  dbPath
};
