database.js

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./learning_platform.db');

db.serialize(() => {
  db.run(`
    CREATE TABLE IF NOT EXISTS courses (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      instructor TEXT NOT NULL,
      duration TEXT
    )
  `);

  db.run(`
    CREATE TABLE IF NOT EXISTS instructors (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      bio TEXT,
      subjects TEXT,
      contact TEXT,
      image_url TEXT
    )
  `);

  db.run(`
    CREATE TABLE IF NOT EXISTS events (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      description TEXT,
      schedule TEXT NOT NULL
    )
  `);

  db.run(`
    CREATE TABLE IF NOT EXISTS faqs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      question TEXT NOT NULL,
      answer TEXT NOT NULL
    )
  `);

  db.run(`
    CREATE TABLE IF NOT EXISTS contact_submissions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT NOT NULL,
      message TEXT NOT NULL,
      timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);

  const instructorsToInsert = [
    {
      name: 'Prof John Erasmus',
      bio: 'CompTIA guru with extensive experience in IT certifications.',
      subjects: 'CompTIA A+, Network+, Security+',
      contact: 'john.erasmus@techgrow.ac.uk',
      image_url: '/img/john_erasmus.jpg'
    },
    {
      name: 'Prof David Turing',
      bio: 'Cisco and network expert, passionate about sharing knowledge.',
      subjects: 'CCNA, CCNP, Network Automation',
      contact: 'david.turing@techgrow.ac.uk',
      image_url: '/img/david_turing.jpg'
    },
    {
      name: 'Prof Sarah Brightman',
      bio: 'Expert in Network Security and cyber awareness.',
      subjects: 'Network Security Foundation',
      contact: 'sarah.brightman@techgrow.ac.uk',
      image_url: '/img/sarah_brightman.jpg'
    }
  ];

  instructorsToInsert.forEach(instructor => {
    db.get('SELECT id FROM instructors WHERE name = ?', [instructor.name], (err, row) => {
      if (err) return console.error(err.message);
      if (!row) {
        db.run(`
          INSERT INTO instructors (name, bio, subjects, contact, image_url)
          VALUES (?, ?, ?, ?, ?)
        `, [instructor.name, instructor.bio, instructor.subjects, instructor.contact, instructor.image_url]);
      }
    });
  });

  const eventsToInsert = [
    {
      title: 'Live Q&A with Prof John Erasmus',
      description: 'Ask questions about CompTIA certifications.',
      schedule: 'Every Monday at 6 PM'
    },
    {
      title: 'Grow with us Bonus Seminar: My SQL or MongoDB?',
      description: 'Explore the differences between relational and NoSQL databases with Prof David Turing.',
      schedule: '15th March 2025 at 2 PM'
    },
    {
      title: 'Network Security Insights with Sarah Brightman',
      description: 'Join Sarah Brightman for a comprehensive overview of foundational network security principles.',
      schedule: '28th June 2025 at 3 PM'
    }
  ];

  eventsToInsert.forEach(event => {
    db.get('SELECT id FROM events WHERE title = ?', [event.title], (err, row) => {
      if (err) return console.error(err.message);
      if (!row) {
        db.run(`
          INSERT INTO events (title, description, schedule)
          VALUES (?, ?, ?)
        `, [event.title, event.description, event.schedule]);
      }
    });
  });

  const faqsToInsert = [
    {
      question: 'What are the instructor qualifications?',
      answer: 'Our instructors are industry experts with years of experience.'
    },
    {
      question: 'How do I contact instructors?',
      answer: 'Contact details are available on their profile pages.'
    }
  ];

  faqsToInsert.forEach(faq => {
    db.get('SELECT id FROM faqs WHERE question = ?', [faq.question], (err, row) => {
      if (err) return console.error(err.message);
      if (!row) {
        db.run(`
          INSERT INTO faqs (question, answer)
          VALUES (?, ?)
        `, [faq.question, faq.answer]);
      }
    });
  });
});

module.exports = db;