const Database = require('better-sqlite3');
const bcrypt = require('bcryptjs');
const path = require('path');
const fs = require('fs');

const dbPath = path.join(__dirname, 'news.db');
const db = new Database(dbPath);

// Initialize Tables
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    full_name TEXT,
    avatar_url TEXT,
    role TEXT DEFAULT 'admin',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    name_en TEXT,
    slug TEXT UNIQUE NOT NULL,
    sort_order INTEGER DEFAULT 0,
    is_home_visible INTEGER DEFAULT 1,
    home_sort_order INTEGER DEFAULT 0,
    home_grid_style INTEGER DEFAULT 0
  );

  CREATE TABLE IF NOT EXISTS articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    body TEXT NOT NULL,
    excerpt TEXT,
    image TEXT,
    category_id INTEGER REFERENCES categories(id),
    user_id INTEGER REFERENCES users(id),
    author TEXT DEFAULT 'ޕަލްސް އެމްވީ',
    is_published INTEGER DEFAULT 1,
    is_featured INTEGER DEFAULT 0,
    is_breaking INTEGER DEFAULT 0,
    is_recommended INTEGER DEFAULT 0,
    is_slider INTEGER DEFAULT 0,
    status TEXT DEFAULT 'published',
    scheduled_at DATETIME,
    views INTEGER DEFAULT 0,
    published_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS advertisements (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    image TEXT,
    link TEXT,
    position TEXT,
    width INTEGER,
    height INTEGER,
    is_active INTEGER DEFAULT 1
  );

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

  CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS pages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    content TEXT NOT NULL,
    is_published INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS menus (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    link TEXT NOT NULL,
    parent_id INTEGER REFERENCES menus(id) ON DELETE CASCADE,
    sort_order INTEGER DEFAULT 0,
    position TEXT DEFAULT 'header' -- 'header', 'footer', 'mobile'
  );

  CREATE TABLE IF NOT EXISTS galleries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS gallery_images (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    gallery_id INTEGER REFERENCES galleries(id) ON DELETE CASCADE,
    image TEXT NOT NULL,
    caption TEXT,
    sort_order INTEGER DEFAULT 0
  );

  CREATE TABLE IF NOT EXISTS contact_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT,
    subject TEXT,
    message TEXT NOT NULL,
    is_read INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS newsletter_subscribers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS languages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    short_form TEXT UNIQUE NOT NULL,
    is_default INTEGER DEFAULT 0,
    status INTEGER DEFAULT 1
  );

  CREATE TABLE IF NOT EXISTS role_permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    role TEXT UNIQUE NOT NULL,
    permissions TEXT NOT NULL
  );

  CREATE TABLE IF NOT EXISTS tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS article_tags (
    article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, tag_id)
  );
`);

// Lightweight schema migration for existing databases
const categoryColumns = db.prepare("PRAGMA table_info(categories)").all();
if (!categoryColumns.some(col => col.name === 'is_home_visible')) {
  db.exec('ALTER TABLE categories ADD COLUMN is_home_visible INTEGER DEFAULT 1');
}
if (!categoryColumns.some(col => col.name === 'home_sort_order')) {
  db.exec('ALTER TABLE categories ADD COLUMN home_sort_order INTEGER DEFAULT 0');
}
if (!categoryColumns.some(col => col.name === 'home_grid_style')) {
  db.exec('ALTER TABLE categories ADD COLUMN home_grid_style INTEGER DEFAULT 0');
}
db.exec('UPDATE categories SET home_sort_order = sort_order WHERE home_sort_order IS NULL OR home_sort_order = 0');
db.exec('UPDATE categories SET is_home_visible = 1 WHERE is_home_visible IS NULL');
db.exec('UPDATE categories SET home_grid_style = 0 WHERE home_grid_style IS NULL OR home_grid_style NOT IN (0, 1, 2, 3, 4)');

const userColumns = db.prepare("PRAGMA table_info(users)").all();
if (!userColumns.some(col => col.name === 'full_name')) {
  db.exec('ALTER TABLE users ADD COLUMN full_name TEXT');
}
if (!userColumns.some(col => col.name === 'avatar_url')) {
  db.exec('ALTER TABLE users ADD COLUMN avatar_url TEXT');
}
if (!userColumns.some(col => col.name === 'twitter_url')) {
  db.exec('ALTER TABLE users ADD COLUMN twitter_url TEXT');
}
db.exec("UPDATE users SET full_name = username WHERE full_name IS NULL OR trim(full_name) = ''");

const articleColumns = db.prepare("PRAGMA table_info(articles)").all();
if (!articleColumns.some(col => col.name === 'is_breaking')) {
  db.exec('ALTER TABLE articles ADD COLUMN is_breaking INTEGER DEFAULT 0');
}
if (!articleColumns.some(col => col.name === 'is_recommended')) {
  db.exec('ALTER TABLE articles ADD COLUMN is_recommended INTEGER DEFAULT 0');
}
if (!articleColumns.some(col => col.name === 'is_slider')) {
  db.exec('ALTER TABLE articles ADD COLUMN is_slider INTEGER DEFAULT 0');
}
if (!articleColumns.some(col => col.name === 'status')) {
  db.exec("ALTER TABLE articles ADD COLUMN status TEXT DEFAULT 'published'");
}
if (!articleColumns.some(col => col.name === 'scheduled_at')) {
  db.exec('ALTER TABLE articles ADD COLUMN scheduled_at DATETIME');
}

const adColumns = db.prepare("PRAGMA table_info(advertisements)").all();
if (!adColumns.some(col => col.name === 'width')) {
  db.exec('ALTER TABLE advertisements ADD COLUMN width INTEGER');
}
if (!adColumns.some(col => col.name === 'height')) {
  db.exec('ALTER TABLE advertisements ADD COLUMN height INTEGER');
}

const tableList = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='phrases'").get();
if (!tableList) {
    db.exec(`CREATE TABLE IF NOT EXISTS phrases (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        key TEXT UNIQUE NOT NULL,
        value TEXT NOT NULL,
        language TEXT DEFAULT 'dv'
    )`);
}

// Ensure core UI phrase keys exist in Dhivehi
const ensurePhrase = db.prepare('INSERT OR IGNORE INTO phrases (key, value, language) VALUES (?, ?, ?)');
ensurePhrase.run('latest_news', 'އެންމެ ފަހުގެ ޚަބަރު', 'dv');
ensurePhrase.run('read_more', 'އިތުރަށް ކިޔުމަށް', 'dv');
ensurePhrase.run('search_placeholder', 'ޚަބަރެއް ލިޔުއްވާ...', 'dv');
ensurePhrase.run('contact_us', 'ގުޅުމަށް', 'dv');
ensurePhrase.run('subscribe_newsletter', 'ނިއުސްލެޓާ ސަބްސްކްރައިބް ކުރައްވާ', 'dv');
ensurePhrase.run('email_address', 'އީމެއިލް އެޑްރެސް', 'dv');
ensurePhrase.run('subscribe', 'ސަބްސްކްރައިބް', 'dv');

async function seed() {
  // Seed Admin User
  const adminExists = db.prepare('SELECT * FROM users WHERE username = ?').get('admin');
  if (!adminExists) {
    const hashedPassword = await bcrypt.hash('admin123', 10);
    db.prepare('INSERT INTO users (username, password, full_name) VALUES (?, ?, ?)').run('admin', hashedPassword, 'އެޑްމިން');
    console.log('އެޑްމިން ޔޫޒަރު ހެދިއްޖެ: admin / admin123');
  }

  // Seed Categories
  const categoriesCount = db.prepare('SELECT COUNT(*) as count FROM categories').get();
  if (categoriesCount.count === 0) {
    const categories = [
      ['ޚަބަރު', 'News', 'news', 1],
      ['ރާއްޖެ', 'Maldives', 'maldives', 2],
      ['ދުނިޔެ', 'World', 'world', 3],
      ['ކުޅިވަރު', 'Sports', 'sports', 4],
      ['އިޤްތިޞާދު', 'Economy', 'economy', 5],
      ['ފަންނު', 'Entertainment', 'entertainment', 6],
      ['ސިއްޙަތު', 'Health', 'health', 7],
      ['ޓެކްނޮލޮޖީ', 'Technology', 'technology', 8]
    ];
    const insertCat = db.prepare('INSERT INTO categories (name, name_en, slug, sort_order) VALUES (?, ?, ?, ?)');
    categories.forEach(cat => insertCat.run(...cat));
    console.log('ކެޓަގަރީތައް ހެދިއްޖެ.');
  }

  // Seed settings
  const siteTitle = db.prepare("SELECT * FROM settings WHERE key = 'site_title'").get();
  if (!siteTitle) {
    db.prepare("INSERT INTO settings (key, value) VALUES (?, ?)").run('site_title', 'ޕަލްސް އެމްވީ');
    console.log('ސެޓިންގްސް ހެދިއްޖެ.');
  }

  // Seed Articles
  const articlesCount = db.prepare('SELECT COUNT(*) as count FROM articles').get();
  if (articlesCount.count === 0) {
    const admin = db.prepare('SELECT id FROM users WHERE username = ?').get('admin');
    const cats = db.prepare('SELECT id FROM categories').all();
    const insertArticle = db.prepare('INSERT INTO articles (title, slug, body, excerpt, category_id, user_id, is_featured, image) VALUES (?, ?, ?, ?, ?, ?, ?, ?)');
    
    for (let i = 1; i <= 10; i++) {
      const isFeatured = i <= 3 ? 1 : 0;
      const catId = cats[Math.floor(Math.random() * cats.length)].id;
      insertArticle.run(
        `ޚަބަރުގެ ސުރުޚީ ${i}: ދިވެހިބަހުން ލިޔެފައިވާ ނަމޫނާ ޚަބަރެއް`,
        `article-${i}`,
        `<p>މިއީ ދިވެހިބަހުން ލިޔެފައިވާ ނަމޫނާ ޚަބަރެއްގެ މައިގަނޑު ބައެވެ. މި ސައިޓަކީ ފުލް-ސްޓެކް ނިއުސް ޕޯޓަލްއެކެވެ. ${i}</p>`,
        `މިއީ ޚަބަރުގެ ކުރު ޚުލާސާއެކެވެ. ${i}`,
        catId,
        admin.id,
        isFeatured,
        null // No image for samples initially
      );
    }
    console.log('ނަމޫނާ ޚަބަރުތައް ހެދިއްޖެ.');
  }

  // Seed Pages
  const pagesCount = db.prepare('SELECT COUNT(*) as count FROM pages').get();
  if (pagesCount.count === 0) {
    db.prepare('INSERT INTO pages (title, slug, content) VALUES (?, ?, ?)').run(
      'ޕަލްސް އެމްވީ އާ ބެހޭ',
      'about-us',
      '<p>މިއީ ޕަލްސް އެމްވީ އާ ބެހޭ މަޢުލޫމާތު ހިމެނޭ ޕޭޖެކެވެ.</p>'
    );
    console.log('ނަމޫނާ ޕޭޖް ހެދިއްޖެ.');
  }

  // Seed Menus
  const menusCount = db.prepare('SELECT COUNT(*) as count FROM menus').get();
  if (menusCount.count === 0) {
    const cats = db.prepare('SELECT * FROM categories').all();
    const insertMenu = db.prepare('INSERT INTO menus (title, link, position, sort_order) VALUES (?, ?, ?, ?)');
    
    // Header Menus from categories
    insertMenu.run('މައި ޞަފްޙާ', '#home', 'header', 0);
    cats.forEach((cat, i) => {
      insertMenu.run(cat.name, `#category/${cat.slug}`, 'header', i + 1);
    });
    
    // Footer Menus
    insertMenu.run('ޕަލްސް އެމްވީ އާ ބެހޭ', '#page/about-us', 'footer', 1);
    insertMenu.run('ގުޅުމަށް', '#contact', 'footer', 2);
    
    console.log('ނަމޫނާ މެނޫތައް ހެދިއްޖެ.');
  }

  // Seed Advertisements (only if each slot is empty)
  const ensureAdByPosition = db.prepare('SELECT COUNT(*) as count FROM advertisements WHERE position = ?');
  const insertAd = db.prepare('INSERT INTO advertisements (name, image, link, position, width, height, is_active) VALUES (?, ?, ?, ?, ?, ?, ?)');
  const sampleAds = [
    ['Sample Top Banner', '/images/sample-ad-top.svg', 'https://example.com', 'top', 1180, 250, 1],
    ['Sample Sidebar Ad', '/images/sample-ad-sidebar.svg', 'https://example.com', 'sidebar', 1180, 250, 1],
    ['Sample Mid Grid Ad', '/images/sample-ad-mid-grid.svg', 'https://example.com', 'mid-grid', 1180, 250, 1],
    ['Sample Article Inline Ad', '/images/sample-ad-mid-grid.svg', 'https://example.com', 'article-below-tags', 1180, 250, 1]
  ];
  sampleAds.forEach((ad) => {
    const pos = ad[3];
    if (ensureAdByPosition.get(pos).count === 0) {
      insertAd.run(...ad);
    }
  });
  db.exec(`
    UPDATE advertisements SET width = 1180, height = 250 WHERE position = 'top' AND (width IS NULL OR height IS NULL);
    UPDATE advertisements SET width = 1180, height = 250 WHERE position = 'sidebar' AND (width IS NULL OR height IS NULL);
    UPDATE advertisements SET width = 1180, height = 250 WHERE position = 'mid-grid' AND (width IS NULL OR height IS NULL);
    UPDATE advertisements SET width = 1180, height = 250 WHERE position = 'article-below-tags' AND (width IS NULL OR height IS NULL);
  `);

  // Seed Phrases
  const phrasesCount = db.prepare('SELECT COUNT(*) as count FROM phrases').get();
  if (phrasesCount.count === 0) {
    const insertPhrase = db.prepare('INSERT INTO phrases (key, value) VALUES (?, ?)');
    const defaultPhrases = [
        ['latest_news', 'އެންމެ ފަހުގެ ޚަބަރު'],
        ['read_more', 'އިތުރަށް ކިޔުމަށް'],
        ['search_placeholder', 'ޚަބަރެއް ލިޔުއްވާ...'],
        ['contact_us', 'ގުޅުމަށް']
    ];
    defaultPhrases.forEach(p => insertPhrase.run(...p));
    console.log('ނަމޫނާ ޕްރޭޒްތައް ހެދިއްޖެ.');
  }

  // Seed Languages
  const langsCount = db.prepare('SELECT COUNT(*) as count FROM languages').get();
  if (langsCount.count === 0) {
    db.prepare('INSERT INTO languages (name, short_form, is_default, status) VALUES (?, ?, ?, ?)').run('Dhivehi', 'dv', 1, 1);
    db.prepare('INSERT INTO languages (name, short_form, is_default, status) VALUES (?, ?, ?, ?)').run('English', 'en', 0, 1);
    console.log('ނަމޫނާ ބަސްތައް ހެދިއްޖެ.');
  }

  // Seed Role Permissions
  const rolesCount = db.prepare('SELECT COUNT(*) as count FROM role_permissions').get();
  if (rolesCount.count === 0) {
    const seedRole = db.prepare('INSERT INTO role_permissions (role, permissions) VALUES (?, ?)');
    seedRole.run('admin', JSON.stringify(['*']));
    seedRole.run('editor', JSON.stringify(['articles', 'categories', 'comments']));
    seedRole.run('writer', JSON.stringify(['articles.own']));
    console.log('ނަމޫނާ ރޯލް ޕާމިޝަންސް ހެދިއްޖެ.');
  }
}

seed().catch(console.error);

module.exports = db;
