<?php

function initDatabase() {
    $db = getDB();

    $db->exec("
        CREATE TABLE IF NOT EXISTS sliders (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255),
            subtitle TEXT,
            image_url TEXT NOT NULL,
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS sections (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL UNIQUE,
            description TEXT,
            image_url TEXT,
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS announcements (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            youtube_url TEXT,
            description TEXT,
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS resources (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL UNIQUE,
            image_url TEXT,
            link_url TEXT,
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS facilities (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255),
            image_url TEXT NOT NULL,
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS pages (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL UNIQUE,
            content TEXT,
            meta_title VARCHAR(255),
            meta_description TEXT,
            meta_keywords VARCHAR(500),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS site_settings (
            id SERIAL PRIMARY KEY,
            setting_key VARCHAR(255) NOT NULL UNIQUE,
            setting_value TEXT
        );

        CREATE TABLE IF NOT EXISTS admin_users (
            id SERIAL PRIMARY KEY,
            username VARCHAR(255) NOT NULL UNIQUE,
            password_hash VARCHAR(255) NOT NULL,
            role VARCHAR(50) DEFAULT 'admin',
            login_attempts INT DEFAULT 0,
            locked_until TIMESTAMP,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS contact_messages (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) NOT NULL,
            subject VARCHAR(255),
            message TEXT NOT NULL,
            read_status BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS services (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL UNIQUE,
            short_description TEXT,
            description TEXT,
            image_url TEXT,
            price VARCHAR(100),
            icon VARCHAR(50) DEFAULT 'fas fa-flask',
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            meta_title VARCHAR(255),
            meta_description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS workshops (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL UNIQUE,
            short_description TEXT,
            description TEXT,
            objectives TEXT,
            syllabus TEXT,
            image_url TEXT,
            start_date DATE,
            end_date DATE,
            duration VARCHAR(100),
            price VARCHAR(100),
            location VARCHAR(255),
            max_participants INT DEFAULT 30,
            active BOOLEAN DEFAULT TRUE,
            sort_order INT DEFAULT 0,
            meta_title VARCHAR(255),
            meta_description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS workshop_registrations (
            id SERIAL PRIMARY KEY,
            workshop_id INT REFERENCES workshops(id) ON DELETE CASCADE,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) NOT NULL,
            phone VARCHAR(50),
            organization VARCHAR(255),
            message TEXT,
            status VARCHAR(50) DEFAULT 'pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS article_categories (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL UNIQUE,
            sort_order INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS articles (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL UNIQUE,
            excerpt TEXT,
            content TEXT,
            image_url TEXT,
            category_id INT REFERENCES article_categories(id) ON DELETE SET NULL,
            author VARCHAR(255) DEFAULT 'Alpha Genomics',
            published BOOLEAN DEFAULT TRUE,
            views INT DEFAULT 0,
            meta_title VARCHAR(255),
            meta_description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS team_members (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            position VARCHAR(255),
            bio TEXT,
            image_url TEXT,
            email VARCHAR(255),
            linkedin VARCHAR(500),
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS alumni_team (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            position VARCHAR(255),
            bio TEXT,
            image_url TEXT,
            email VARCHAR(255),
            linkedin VARCHAR(500),
            twitter VARCHAR(500),
            facebook VARCHAR(500),
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS newsletter_subscribers (
            id SERIAL PRIMARY KEY,
            email VARCHAR(255) NOT NULL UNIQUE,
            name VARCHAR(255),
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS audit_logs (
            id SERIAL PRIMARY KEY,
            admin_user VARCHAR(255),
            action VARCHAR(255) NOT NULL,
            entity_type VARCHAR(100),
            entity_id INT,
            details TEXT,
            ip_address VARCHAR(50),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS seo_metadata (
            id SERIAL PRIMARY KEY,
            page_path VARCHAR(255) NOT NULL UNIQUE,
            meta_title VARCHAR(255),
            meta_description TEXT,
            meta_keywords VARCHAR(500),
            og_image TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS menu_items (
            id SERIAL PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            url VARCHAR(500) NOT NULL,
            parent_id INT DEFAULT NULL,
            sort_order INT DEFAULT 0,
            target VARCHAR(20) DEFAULT '_self',
            icon VARCHAR(100),
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS gallery_categories (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            slug VARCHAR(255) NOT NULL UNIQUE,
            description TEXT,
            cover_image TEXT,
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS gallery_images (
            id SERIAL PRIMARY KEY,
            category_id INT REFERENCES gallery_categories(id) ON DELETE CASCADE,
            title VARCHAR(255) NOT NULL,
            description TEXT,
            image_url TEXT NOT NULL,
            sort_order INT DEFAULT 0,
            active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
    ");

    $db->exec("
        ALTER TABLE admin_users ADD COLUMN IF NOT EXISTS role VARCHAR(50) DEFAULT 'admin';
        ALTER TABLE admin_users ADD COLUMN IF NOT EXISTS login_attempts INT DEFAULT 0;
        ALTER TABLE admin_users ADD COLUMN IF NOT EXISTS locked_until TIMESTAMP;
        ALTER TABLE pages ADD COLUMN IF NOT EXISTS meta_title VARCHAR(255);
        ALTER TABLE pages ADD COLUMN IF NOT EXISTS meta_description TEXT;
        ALTER TABLE pages ADD COLUMN IF NOT EXISTS meta_keywords VARCHAR(500);
        ALTER TABLE team_members ADD COLUMN IF NOT EXISTS twitter VARCHAR(500);
        ALTER TABLE team_members ADD COLUMN IF NOT EXISTS facebook VARCHAR(500);
        ALTER TABLE admin_users ADD COLUMN IF NOT EXISTS email VARCHAR(255);
        ALTER TABLE admin_users ADD COLUMN IF NOT EXISTS full_name VARCHAR(255);
        ALTER TABLE admin_users ADD COLUMN IF NOT EXISTS last_login TIMESTAMP;
        ALTER TABLE admin_users ADD COLUMN IF NOT EXISTS active BOOLEAN DEFAULT TRUE;
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS admin_roles (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL UNIQUE,
            description TEXT,
            permissions TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
    ");

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM admin_users");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $hash = password_hash('admin123', PASSWORD_DEFAULT);
        $db->prepare("INSERT INTO admin_users (username, password_hash, role) VALUES (?, ?, ?)")
           ->execute(['admin', $hash, 'super_admin']);
    }

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM site_settings");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $settings = [
            ['site_name', 'Alpha Genomics'],
            ['site_tagline', 'Pioneer in Life Sciences Research'],
            ['address', 'Plot 4-C, Danyal Plaza, Block A, Main Double Road, PWD, Islamabad, Pakistan'],
            ['email', 'info.alphagenomics@gmail.com'],
            ['phone', '+92 51 5421056'],
            ['about_title', 'About Us'],
            ['about_text', 'Alpha Genomics (Pvt) Ltd. is the pioneer organisation in Pakistan working in life sciences research as a private sector business entity. We are committed to transform basic science research to the real-time impact on the society. Our state-of-the-art facility offers technical support to the labs in academia. Alpha Genomics is working on different themes as displayed in our Sections. We aim to inculcate and develop a research culture and critical thinking among science students and professionals through our diverse programs. Through our Innovation Club, we help the science graduates to spell out their ideas on different platforms confidently.'],
            ['mission', 'To transform basic science research into real-time impact on society through innovative genomics solutions and comprehensive life sciences services.'],
            ['vision', 'To be the leading life sciences research organization in Pakistan, driving innovation and excellence in genomics, biotechnology, and scientific education.'],
            ['history', 'Alpha Genomics (Pvt) Ltd. was established as the pioneer organization in Pakistan working in life sciences research as a private sector business entity. Over the years, we have expanded our services to include next-generation sequencing, immunology, nanotechnology, and comprehensive training programs.'],
            ['footer_text', '© 2026 Alpha Genomics (Pvt) Ltd. All Rights Reserved.'],
            ['youtube_channel', 'https://www.youtube.com/channel/UCRkoaRN6hnENnW-A6sxd0rg'],
            ['facebook_url', '#'],
            ['twitter_url', '#'],
            ['linkedin_url', '#'],
        ];
        $stmt = $db->prepare("INSERT INTO site_settings (setting_key, setting_value) VALUES (?, ?)");
        foreach ($settings as $s) {
            $stmt->execute($s);
        }
    }

    seedRoles($db);
    seedNewSettings($db);
    seedDefaultContent($db);
    seedNewContent($db);
    seedAlumniData($db);
    seedMenuItems($db);
    seedGalleryData($db);

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM menu_items WHERE url = '/gallery'");
    if ($stmt->fetch()['cnt'] == 0) {
        $db->exec("INSERT INTO menu_items (title, url, parent_id, sort_order, target, icon, active) VALUES ('Gallery', '/gallery', NULL, 8, '_self', 'fas fa-images', TRUE)");
    }
}

function seedRoles($db) {
    $stmt = $db->query("SELECT COUNT(*) as cnt FROM admin_roles");
    if ($stmt->fetch()['cnt'] == 0) {
        $roles = [
            ['super_admin', 'Super Administrator', 'all'],
            ['admin', 'Administrator', 'content,users,settings'],
            ['editor', 'Content Editor', 'content'],
            ['viewer', 'Viewer', 'view'],
        ];
        $stmt = $db->prepare("INSERT INTO admin_roles (name, description, permissions) VALUES (?, ?, ?)");
        foreach ($roles as $r) {
            $stmt->execute($r);
        }
    }
}

function seedNewSettings($db) {
    $newSettings = [
        'mission' => 'To transform basic science research into real-time impact on society through innovative genomics solutions and comprehensive life sciences services.',
        'vision' => 'To be the leading life sciences research organization in Pakistan, driving innovation and excellence in genomics, biotechnology, and scientific education.',
        'history' => 'Alpha Genomics (Pvt) Ltd. was established as the pioneer organization in Pakistan working in life sciences research as a private sector business entity. Over the years, we have expanded our services to include next-generation sequencing, immunology, nanotechnology, and comprehensive training programs.',
    ];
    $check = $db->prepare("SELECT COUNT(*) as cnt FROM site_settings WHERE setting_key = ?");
    $ins = $db->prepare("INSERT INTO site_settings (setting_key, setting_value) VALUES (?, ?)");
    foreach ($newSettings as $k => $v) {
        $check->execute([$k]);
        if ($check->fetch()['cnt'] == 0) {
            $ins->execute([$k, $v]);
        }
    }
}

function seedDefaultContent($db) {
    $stmt = $db->query("SELECT COUNT(*) as cnt FROM sections");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $sections = [
            ['Next-Generation Sequencing', 'next-generation-sequencing', 'We offer Next generation / high-throughput sequencing and data analyses services for various research applications including whole genome sequencing, targeted sequencing, RNA-Seq, and metagenomics.', 'https://images.unsplash.com/photo-1532187863486-abf9dbad1b69?w=400&h=300&fit=crop', 1],
            ['Immunology/Flowcytometry', 'immunology', 'Alpha Genomics (Pvt) Ltd. facilitates immunology research through its state-of-the-art flow cytometry facility, offering comprehensive cell analysis and sorting services.', 'https://images.unsplash.com/photo-1579154204601-01588f351e67?w=400&h=300&fit=crop', 2],
            ['Technology Development', 'technology-development', 'Alpha Genomics (Pvt) Ltd. is emphatically committed to create real-time impact through innovative technology development in life sciences and biotechnology.', 'https://images.unsplash.com/photo-1518770660439-4636190af475?w=400&h=300&fit=crop', 3],
            ['Trainings and Workshops', 'trainings-and-workshops', 'Alpha Genomics is striving hard to inculcate research culture among science students and professionals through comprehensive training programs and hands-on workshops.', 'https://images.unsplash.com/photo-1524178232363-1fb2b075b655?w=400&h=300&fit=crop', 4],
            ['Biological Manuscript Editing Services', 'biological-manuscript-editing', 'Are your manuscripts often returned for ENGLISH language correction or improvement? Our professional editing team ensures your scientific manuscripts meet international journal standards.', 'https://images.unsplash.com/photo-1456324504439-367cee3b3c32?w=400&h=300&fit=crop', 5],
            ['Nanotechnology (Alpha Nanogen Lab)', 'nanotechnology', 'We offer solutions in nanotechnology research through our Alpha Nanogen Lab, providing cutting-edge research capabilities in nanomaterial synthesis and characterization.', 'https://images.unsplash.com/photo-1532094349884-543bc11b234d?w=400&h=300&fit=crop', 6],
            ['Molecular Biology Reference Lab', 'molecular-biology-reference-lab', 'Alpha Genomics (Pvt) Ltd. established the Molecular Biology Reference Laboratory to provide accurate and reliable molecular diagnostic services.', 'https://images.unsplash.com/photo-1576086213369-97a306d36557?w=400&h=300&fit=crop', 7],
        ];
        $stmt = $db->prepare("INSERT INTO sections (title, slug, description, image_url, sort_order) VALUES (?, ?, ?, ?, ?)");
        foreach ($sections as $s) {
            $stmt->execute($s);
        }
    }

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM announcements");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $announcements = [
            ['PCR Calculations - PCR Reagent Mixture Calculation', 'https://www.youtube.com/watch?v=n1ybefFNaW8', 'How To: PCR Calculations - Final Batch - Part 03', 1],
            ['NGS Data Analysis for Beginners', 'https://www.youtube.com/watch?v=AIyJpr1jQCk', 'NGS Data Analysis - Next-Generation Sequencing Data Analysis', 2],
            ['DNA Quantification with Spectrophotometer', 'https://www.youtube.com/watch?v=WYlGaMMlZoU', 'How to Quantify DNA with a Spectrophotometer', 3],
            ['Real-Time PCR Explanation', 'https://www.youtube.com/watch?v=mAmdzfcII0w', 'Detail Explanation of Rt-PCR - Alpha Genomics', 4],
        ];
        $stmt = $db->prepare("INSERT INTO announcements (title, youtube_url, description, sort_order) VALUES (?, ?, ?, ?)");
        foreach ($announcements as $a) {
            $stmt->execute($a);
        }
    }

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM resources");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $resources = [
            ['Forms', 'forms', 'https://images.unsplash.com/photo-1554224155-6726b3ff858f?w=400&h=300&fit=crop', '/resources/forms', 1],
            ['Courseware', 'courseware', 'https://images.unsplash.com/photo-1513475382585-d06e58bcb0e0?w=400&h=300&fit=crop', '/resources/courseware', 2],
            ['Publications', 'publications', 'https://images.unsplash.com/photo-1481627834876-b7833e8f5570?w=400&h=300&fit=crop', '/resources/publications', 3],
        ];
        $stmt = $db->prepare("INSERT INTO resources (title, slug, image_url, link_url, sort_order) VALUES (?, ?, ?, ?, ?)");
        foreach ($resources as $r) {
            $stmt->execute($r);
        }
    }

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM facilities");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $facilities = [
            ['Gel Documentation System', 'https://images.unsplash.com/photo-1582719471384-894fbb16e074?w=300&h=200&fit=crop', 1],
            ['Analytical Balance', 'https://images.unsplash.com/photo-1532187863486-abf9dbad1b69?w=300&h=200&fit=crop', 2],
            ['PCR Thermal Cycler', 'https://images.unsplash.com/photo-1576086213369-97a306d36557?w=300&h=200&fit=crop', 3],
            ['Flow Cytometer', 'https://images.unsplash.com/photo-1579154204601-01588f351e67?w=300&h=200&fit=crop', 4],
            ['Microplate Spectrophotometer', 'https://images.unsplash.com/photo-1532094349884-543bc11b234d?w=300&h=200&fit=crop', 5],
            ['Centrifuge', 'https://images.unsplash.com/photo-1518770660439-4636190af475?w=300&h=200&fit=crop', 6],
            ['Microscope', 'https://images.unsplash.com/photo-1516728778615-2d590ea1855e?w=300&h=200&fit=crop', 7],
            ['Dry Bath Block Heater', 'https://images.unsplash.com/photo-1530026405186-ed1f139313f8?w=300&h=200&fit=crop', 8],
        ];
        $stmt = $db->prepare("INSERT INTO facilities (title, image_url, sort_order) VALUES (?, ?, ?)");
        foreach ($facilities as $f) {
            $stmt->execute($f);
        }
    }

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM sliders");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $sliders = [
            ['Welcome to Alpha Genomics', 'Pioneer in Life Sciences Research', 'https://images.unsplash.com/photo-1532187863486-abf9dbad1b69?w=1200&h=500&fit=crop', 1],
            ['Next-Generation Sequencing', 'Advanced Genomics Solutions', 'https://images.unsplash.com/photo-1576086213369-97a306d36557?w=1200&h=500&fit=crop', 2],
            ['Research & Innovation', 'Transforming Science into Impact', 'https://images.unsplash.com/photo-1507003211169-0a1dd7228f2d?w=1200&h=500&fit=crop', 3],
        ];
        $stmt = $db->prepare("INSERT INTO sliders (title, subtitle, image_url, sort_order) VALUES (?, ?, ?, ?)");
        foreach ($sliders as $s) {
            $stmt->execute($s);
        }
    }
}

function seedNewContent($db) {
    $stmt = $db->query("SELECT COUNT(*) as cnt FROM services");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $services = [
            ['Genomic Testing', 'genomic-testing', 'Comprehensive genomic testing services for research and clinical applications.', 'We provide state-of-the-art genomic testing services including whole genome sequencing, exome sequencing, and targeted panel testing. Our laboratory uses the latest Illumina and Oxford Nanopore platforms to deliver accurate and timely results for both research and clinical applications.', 'https://images.unsplash.com/photo-1532187863486-abf9dbad1b69?w=600&h=400&fit=crop', 'Contact for pricing', 'fas fa-dna', 1],
            ['Bioinformatics', 'bioinformatics', 'Advanced computational analysis of biological and genomic data.', 'Our bioinformatics team provides comprehensive data analysis services including genome assembly, variant calling, differential gene expression analysis, metagenomics analysis, and custom pipeline development. We work with researchers to translate raw sequencing data into meaningful biological insights.', 'https://images.unsplash.com/photo-1518770660439-4636190af475?w=600&h=400&fit=crop', 'Contact for pricing', 'fas fa-laptop-code', 2],
            ['Sequencing Services', 'sequencing-services', 'High-throughput sequencing on multiple platforms for diverse applications.', 'Alpha Genomics offers a complete range of sequencing services including Sanger sequencing, next-generation sequencing (NGS), and third-generation long-read sequencing. We support various applications from simple gene sequencing to complex metagenomic and transcriptomic studies.', 'https://images.unsplash.com/photo-1576086213369-97a306d36557?w=600&h=400&fit=crop', 'Starting from PKR 5,000', 'fas fa-microscope', 3],
            ['Microbiology Support', 'microbiology-support', 'Microbiological testing and research support services.', 'Our microbiology division provides comprehensive testing services including culture and sensitivity testing, molecular identification of microorganisms, antimicrobial resistance profiling, and environmental monitoring. We support both research institutions and diagnostic laboratories.', 'https://images.unsplash.com/photo-1579154204601-01588f351e67?w=600&h=400&fit=crop', 'Contact for pricing', 'fas fa-bacterium', 4],
            ['Research Assistance', 'research-assistance', 'End-to-end support for your research projects.', 'We offer comprehensive research assistance including experimental design, sample processing, data analysis, manuscript preparation, and statistical consulting. Our team of experienced scientists works closely with researchers at all stages of their projects to ensure successful outcomes.', 'https://images.unsplash.com/photo-1532094349884-543bc11b234d?w=600&h=400&fit=crop', 'Custom packages available', 'fas fa-user-graduate', 5],
        ];
        $stmt = $db->prepare("INSERT INTO services (title, slug, short_description, description, image_url, price, icon, sort_order) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        foreach ($services as $s) {
            $stmt->execute($s);
        }
    }

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM workshops");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $workshops = [
            ['Bioinformatics for Beginners', 'bioinformatics-beginners', 'Learn the fundamentals of bioinformatics and genomic data analysis.', 'This comprehensive workshop covers the essential concepts and tools used in bioinformatics. Participants will learn about sequence alignment, genome assembly, phylogenetics, and basic programming for biological data analysis.', 'Understand sequence databases and formats; Learn alignment algorithms (BLAST, ClustalW); Introduction to R/Python for bioinformatics; Genome annotation basics; Hands-on data analysis exercises', 'Day 1: Introduction to Bioinformatics & Databases; Day 2: Sequence Alignment & Analysis; Day 3: Genome Assembly & Annotation; Day 4: Programming for Bioinformatics; Day 5: Project Work & Presentations', 'https://images.unsplash.com/photo-1524178232363-1fb2b075b655?w=600&h=400&fit=crop', NULL, NULL, '5 Days', 'PKR 25,000', 'Alpha Genomics Lab, Islamabad', 25, 1],
            ['Next-Generation Sequencing Workshop', 'ngs-workshop', 'Hands-on training in NGS library preparation and data analysis.', 'This intensive workshop provides hands-on training in next-generation sequencing technologies. Participants will learn about library preparation, sequencing protocols, quality control, and basic data analysis using industry-standard tools.', 'NGS platform overview and comparison; Library preparation techniques; Quality control and troubleshooting; Data processing pipelines; Variant calling and annotation', 'Day 1: NGS Technology Overview; Day 2: Library Preparation Lab; Day 3: Sequencing Run Setup; Day 4: Data Analysis Pipeline; Day 5: Advanced Topics & Certification', 'https://images.unsplash.com/photo-1576086213369-97a306d36557?w=600&h=400&fit=crop', NULL, NULL, '5 Days', 'PKR 35,000', 'Alpha Genomics Lab, Islamabad', 20, 2],
            ['PCR & Molecular Diagnostics', 'pcr-molecular-diagnostics', 'Master PCR techniques and molecular diagnostic methods.', 'This practical workshop focuses on PCR-based techniques used in molecular diagnostics. Participants will gain hands-on experience with conventional PCR, real-time PCR, and advanced molecular diagnostic methods used in clinical and research settings.', 'Conventional and real-time PCR techniques; Primer design and optimization; Quality assurance in molecular diagnostics; Troubleshooting common PCR issues; Clinical application of molecular tests', 'Day 1: PCR Fundamentals; Day 2: Real-Time PCR; Day 3: Advanced PCR Techniques; Day 4: Molecular Diagnostics Applications; Day 5: Quality Control & Certification', 'https://images.unsplash.com/photo-1532094349884-543bc11b234d?w=600&h=400&fit=crop', NULL, NULL, '5 Days', 'PKR 20,000', 'Alpha Genomics Lab, Islamabad', 30, 3],
        ];
        $stmt = $db->prepare("INSERT INTO workshops (title, slug, short_description, description, objectives, syllabus, image_url, start_date, end_date, duration, price, location, max_participants, sort_order) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        foreach ($workshops as $w) {
            $stmt->execute($w);
        }
    }

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM article_categories");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $categories = [
            ['Genomics', 'genomics', 1],
            ['Bioinformatics', 'bioinformatics', 2],
            ['Molecular Biology', 'molecular-biology', 3],
            ['Research Methods', 'research-methods', 4],
            ['Case Studies', 'case-studies', 5],
        ];
        $stmt = $db->prepare("INSERT INTO article_categories (name, slug, sort_order) VALUES (?, ?, ?)");
        foreach ($categories as $c) {
            $stmt->execute($c);
        }
    }

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM articles");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $articles = [
            ['Introduction to Next-Generation Sequencing', 'introduction-to-ngs', 'A comprehensive overview of next-generation sequencing technologies and their applications in modern genomics research.', 'Next-generation sequencing (NGS) has revolutionized genomics research by enabling the sequencing of entire genomes in a matter of hours. This article provides a comprehensive introduction to the major NGS platforms, their underlying chemistry, and the diverse applications in clinical and research settings. We discuss Illumina sequencing, Oxford Nanopore, and PacBio technologies, comparing their strengths and limitations for different use cases.', 'https://images.unsplash.com/photo-1532187863486-abf9dbad1b69?w=600&h=400&fit=crop', 1, 'Alpha Genomics Team'],
            ['Understanding PCR: A Complete Guide', 'understanding-pcr-complete-guide', 'Everything you need to know about Polymerase Chain Reaction, from basic principles to advanced techniques.', 'The Polymerase Chain Reaction (PCR) is one of the most fundamental techniques in molecular biology. Since its invention by Kary Mullis in 1983, PCR has transformed the field of genetics and molecular diagnostics. This comprehensive guide covers the basic principles of PCR, the components required, cycling conditions, troubleshooting common issues, and advanced variations including real-time PCR, digital PCR, and multiplex PCR.', 'https://images.unsplash.com/photo-1576086213369-97a306d36557?w=600&h=400&fit=crop', 3, 'Dr. Ahmad Khan'],
            ['Bioinformatics Tools for Genomic Analysis', 'bioinformatics-tools-genomic-analysis', 'An overview of essential bioinformatics tools and software used for analyzing genomic sequencing data.', 'Bioinformatics has become an essential component of modern genomics research. This article reviews the most widely used bioinformatics tools for genomic data analysis, including FastQC for quality control, BWA and Bowtie for read alignment, GATK for variant calling, and various visualization tools. We provide practical tips for setting up analysis pipelines and interpreting results.', 'https://images.unsplash.com/photo-1518770660439-4636190af475?w=600&h=400&fit=crop', 2, 'Dr. Sarah Ali'],
            ['DNA Extraction Methods Compared', 'dna-extraction-methods-compared', 'A comparison of popular DNA extraction methods for different sample types and downstream applications.', 'Successful genomic analysis begins with high-quality DNA extraction. This article compares various DNA extraction methods including phenol-chloroform extraction, silica-based column purification, magnetic bead-based extraction, and newer automated methods. We discuss the advantages and disadvantages of each approach for different sample types including blood, tissue, saliva, and environmental samples.', 'https://images.unsplash.com/photo-1579154204601-01588f351e67?w=600&h=400&fit=crop', 4, 'Alpha Genomics Team'],
            ['Flow Cytometry in Immunology Research', 'flow-cytometry-immunology', 'How flow cytometry is transforming immunological research and clinical diagnostics.', 'Flow cytometry is a powerful technology that enables the analysis of multiple physical and chemical characteristics of cells simultaneously. This article explores the applications of flow cytometry in immunology research, including immunophenotyping, cell cycle analysis, apoptosis detection, and intracellular cytokine staining. We also discuss recent advances in spectral flow cytometry and mass cytometry.', 'https://images.unsplash.com/photo-1532094349884-543bc11b234d?w=600&h=400&fit=crop', 3, 'Dr. Fatima Hassan'],
        ];
        $stmt = $db->prepare("INSERT INTO articles (title, slug, excerpt, content, image_url, category_id, author) VALUES (?, ?, ?, ?, ?, ?, ?)");
        foreach ($articles as $a) {
            $stmt->execute($a);
        }
    }

    $stmt = $db->query("SELECT COUNT(*) as cnt FROM team_members");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $team = [
            ['Dr. Ibrar Ahmed', 'CEO & Founder', 'Dr. Ibrar Ahmed is the visionary founder and CEO of Alpha Genomics. With extensive experience in molecular biology and genomics, he has led the company to become a pioneer in life sciences research in Pakistan.', 'https://images.unsplash.com/photo-1472099645785-5658abf4ff4e?w=300&h=300&fit=crop', '', '', 1],
            ['Dr. Sarah Khan', 'Head of Genomics', 'Dr. Sarah Khan leads our genomics division, overseeing next-generation sequencing operations and bioinformatics analysis. She holds a PhD in Molecular Genetics.', 'https://images.unsplash.com/photo-1494790108377-be9c29b29330?w=300&h=300&fit=crop', '', '', 2],
            ['Dr. Ahmad Raza', 'Head of Research', 'Dr. Ahmad Raza heads the research and development division, focusing on innovative solutions in molecular biology and nanotechnology applications.', 'https://images.unsplash.com/photo-1507003211169-0a1dd7228f2d?w=300&h=300&fit=crop', '', '', 3],
            ['Dr. Fatima Hassan', 'Training Coordinator', 'Dr. Fatima Hassan coordinates all training and workshop programs, ensuring high-quality educational experiences for students and professionals in life sciences.', 'https://images.unsplash.com/photo-1438761681033-6461ffad8d80?w=300&h=300&fit=crop', '', '', 4],
        ];
        $stmt = $db->prepare("INSERT INTO team_members (name, position, bio, image_url, email, linkedin, sort_order) VALUES (?, ?, ?, ?, ?, ?, ?)");
        foreach ($team as $t) {
            $stmt->execute($t);
        }
    }
}

function seedAlumniData($db) {
    $stmt = $db->query("SELECT COUNT(*) as cnt FROM alumni_team");
    $row = $stmt->fetch();
    if ($row['cnt'] == 0) {
        $alumni = [
            ['Dr. Asad Mehmood', 'Former Research Scientist', 'Dr. Asad Mehmood served as a research scientist at Alpha Genomics, contributing to breakthroughs in next-generation sequencing analysis and genomic data interpretation.', 'https://images.unsplash.com/photo-1560250097-0b93528c311a?w=300&h=300&fit=crop', 'asad.mehmood@example.com', 'https://linkedin.com/in/asadmehmood', '', '', 1],
            ['Dr. Ayesha Siddiqui', 'Former Bioinformatics Lead', 'Dr. Ayesha Siddiqui led the bioinformatics division, developing custom analysis pipelines for metagenomics and transcriptomics research projects.', 'https://images.unsplash.com/photo-1573496359142-b8d87734a5a2?w=300&h=300&fit=crop', 'ayesha.siddiqui@example.com', 'https://linkedin.com/in/ayeshasiddiqui', '', '', 2],
            ['Dr. Usman Tariq', 'Former Lab Manager', 'Dr. Usman Tariq managed the molecular biology reference laboratory, ensuring quality standards and efficient workflows across all diagnostic services.', 'https://images.unsplash.com/photo-1519085360753-af0119f7cbe7?w=300&h=300&fit=crop', 'usman.tariq@example.com', 'https://linkedin.com/in/usmantariq', '', '', 3],
            ['Dr. Hira Nawaz', 'Former Training Specialist', 'Dr. Hira Nawaz designed and delivered training programs in molecular diagnostics and PCR techniques for students and professionals across Pakistan.', 'https://images.unsplash.com/photo-1580489944761-15a19d654956?w=300&h=300&fit=crop', 'hira.nawaz@example.com', 'https://linkedin.com/in/hiranawaz', '', '', 4],
        ];
        $stmt = $db->prepare("INSERT INTO alumni_team (name, position, bio, image_url, email, linkedin, twitter, facebook, sort_order) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
        foreach ($alumni as $a) {
            $stmt->execute($a);
        }
    }
}

function seedMenuItems($db) {
    $stmt = $db->query("SELECT COUNT(*) as cnt FROM menu_items");
    if ($stmt->fetch()['cnt'] == 0) {
        $items = [
            ['Home', '/', null, 1, '_self', 'fas fa-home'],
            ['About Us', '/about', null, 2, '_self', 'fas fa-info-circle'],
            ['Services', '/services', null, 3, '_self', 'fas fa-flask'],
            ['Workshops', '/workshops', null, 4, '_self', 'fas fa-chalkboard-teacher'],
            ['Library', '/articles', null, 5, '_self', 'fas fa-book'],
            ['Team', '/about#team', null, 6, '_self', 'fas fa-users'],
            ['Alumni', '/alumni', null, 7, '_self', 'fas fa-user-graduate'],
            ['Sections', '/sections', null, 8, '_self', 'fas fa-th-large'],
            ['Contact', '/contact', null, 9, '_self', 'fas fa-envelope'],
        ];
        $stmt = $db->prepare("INSERT INTO menu_items (title, url, parent_id, sort_order, target, icon) VALUES (?, ?, ?, ?, ?, ?)");
        foreach ($items as $item) {
            $stmt->execute($item);
        }
    }
}

function seedGalleryData($db) {
    $stmt = $db->query("SELECT COUNT(*) as cnt FROM gallery_categories");
    if ($stmt->fetch()['cnt'] == 0) {
        $categories = [
            ['Laboratory Equipment', 'laboratory-equipment', 'Our state-of-the-art laboratory equipment and facilities', 'https://images.unsplash.com/photo-1582719471384-894fbb16e074?w=600&h=400&fit=crop', 1],
            ['Workshops & Training', 'workshops-training', 'Photos from our training sessions and workshops', 'https://images.unsplash.com/photo-1524178232363-1fb2b075b655?w=600&h=400&fit=crop', 2],
            ['Research Activities', 'research-activities', 'Snapshots of our ongoing research projects', 'https://images.unsplash.com/photo-1532187863486-abf9dbad1b69?w=600&h=400&fit=crop', 3],
            ['Events & Conferences', 'events-conferences', 'Highlights from our events and conference participation', 'https://images.unsplash.com/photo-1540575467063-178a50c2df87?w=600&h=400&fit=crop', 4],
        ];
        $stmt = $db->prepare("INSERT INTO gallery_categories (name, slug, description, cover_image, sort_order) VALUES (?, ?, ?, ?, ?)");
        foreach ($categories as $c) {
            $stmt->execute($c);
        }

        $images = [
            [1, 'PCR Thermal Cycler', 'Our advanced PCR thermal cycler for DNA amplification', 'https://images.unsplash.com/photo-1576086213369-97a306d36557?w=800&h=600&fit=crop', 1],
            [1, 'Flow Cytometer', 'State-of-the-art flow cytometry system', 'https://images.unsplash.com/photo-1579154204601-01588f351e67?w=800&h=600&fit=crop', 2],
            [1, 'Gel Documentation', 'Gel documentation and imaging system', 'https://images.unsplash.com/photo-1582719471384-894fbb16e074?w=800&h=600&fit=crop', 3],
            [1, 'Microscope Station', 'High-powered research microscope', 'https://images.unsplash.com/photo-1516728778615-2d590ea1855e?w=800&h=600&fit=crop', 4],
            [2, 'Bioinformatics Workshop 2024', 'Participants learning bioinformatics tools', 'https://images.unsplash.com/photo-1524178232363-1fb2b075b655?w=800&h=600&fit=crop', 1],
            [2, 'PCR Training Session', 'Hands-on PCR training for students', 'https://images.unsplash.com/photo-1532094349884-543bc11b234d?w=800&h=600&fit=crop', 2],
            [2, 'NGS Workshop', 'Next-generation sequencing workshop', 'https://images.unsplash.com/photo-1532187863486-abf9dbad1b69?w=800&h=600&fit=crop', 3],
            [3, 'DNA Extraction', 'DNA extraction process in our lab', 'https://images.unsplash.com/photo-1530026405186-ed1f139313f8?w=800&h=600&fit=crop', 1],
            [3, 'Sample Processing', 'Processing biological samples', 'https://images.unsplash.com/photo-1507003211169-0a1dd7228f2d?w=800&h=600&fit=crop', 2],
            [3, 'Sequencing Run', 'Setting up an Illumina sequencing run', 'https://images.unsplash.com/photo-1518770660439-4636190af475?w=800&h=600&fit=crop', 3],
            [4, 'Annual Conference 2024', 'Alpha Genomics annual research conference', 'https://images.unsplash.com/photo-1540575467063-178a50c2df87?w=800&h=600&fit=crop', 1],
            [4, 'Research Symposium', 'Presenting at the national research symposium', 'https://images.unsplash.com/photo-1475721027785-f74eccf877e2?w=800&h=600&fit=crop', 2],
        ];
        $stmt = $db->prepare("INSERT INTO gallery_images (category_id, title, description, image_url, sort_order) VALUES (?, ?, ?, ?, ?)");
        foreach ($images as $img) {
            $stmt->execute($img);
        }
    }
}
