import database from "../../../config/db";

export async function createProductsUpdatesTable() {
    try {
        // Table creation
        const tableQuery = `
        CREATE TABLE IF NOT EXISTS products_updates (
         id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

         -- Product reference
         product_id UUID NOT NULL,

         -- Update information
         version VARCHAR(50) NOT NULL,
         version_type VARCHAR(20) NOT NULL 
           CHECK (version_type IN ('major', 'minor', 'patch', 'beta', 'alpha')),
         title VARCHAR(200) NOT NULL,
         description TEXT,
         
         -- Change categories (inspired by your React component)
         new_features JSONB DEFAULT '[]'::JSONB,
         improvements JSONB DEFAULT '[]'::JSONB,
         bug_fixes JSONB DEFAULT '[]'::JSONB,
         breaking_changes JSONB DEFAULT '[]'::JSONB,
         
         -- Highlights (short summary points)
         highlights JSONB DEFAULT '[]'::JSONB,
         
         -- Technical details
         release_notes TEXT,
         
         -- Release information
         release_date DATE NOT NULL,
         is_latest BOOLEAN DEFAULT false,
         is_beta BOOLEAN DEFAULT false,
         is_public BOOLEAN DEFAULT true,
         
         -- Stats and engagement
         upvotes INTEGER DEFAULT 0 CHECK (upvotes >= 0),
         views INTEGER DEFAULT 0 CHECK (views >= 0),
         
         -- Status
         status VARCHAR(20) DEFAULT 'published' 
           CHECK (status IN ('draft', 'published', 'archived', 'scheduled')),
         
         -- Metadata
         created_by UUID,
         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         published_at TIMESTAMP,
         
         -- SEO
         slug VARCHAR(120) UNIQUE,
         meta_title VARCHAR(255),
         meta_description TEXT,
         
         -- Foreign key constraint
         FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
         FOREIGN KEY (created_by) REFERENCES accounts(id) ON DELETE SET NULL,
         
         -- Version format validation
         CONSTRAINT valid_version_format 
           CHECK (version ~ '^v?[0-9]+\.[0-9]+\.[0-9]+(-[a-zA-Z0-9\.]+)?$'),
         
         -- Ensure published_at is set when status is published
         CONSTRAINT published_requires_date 
           CHECK (status != 'published' OR published_at IS NOT NULL)
        );`;

        await database.query(tableQuery);

        // Index creation for better performance
        const indexes = [
            // Foreign key index
            `CREATE INDEX IF NOT EXISTS idx_updates_product_id ON products_updates(product_id);`,
            `CREATE INDEX IF NOT EXISTS idx_updates_created_by ON products_updates(created_by);`,
            
            // For version sorting and filtering
            `CREATE INDEX IF NOT EXISTS idx_updates_version ON products_updates(version);`,
            `CREATE INDEX IF NOT EXISTS idx_updates_version_type ON products_updates(version_type);`,
            `CREATE INDEX IF NOT EXISTS idx_updates_release_date ON products_updates(release_date DESC);`,
            
            // For status and visibility
            `CREATE INDEX IF NOT EXISTS idx_updates_status ON products_updates(status) WHERE status = 'published';`,
            `CREATE INDEX IF NOT EXISTS idx_updates_is_public ON products_updates(is_public) WHERE is_public = true;`,
            `CREATE INDEX IF NOT EXISTS idx_updates_is_latest ON products_updates(is_latest) WHERE is_latest = true;`,
            `CREATE INDEX IF NOT EXISTS idx_updates_is_beta ON products_updates(is_beta) WHERE is_beta = true;`,
            
            // For engagement metrics
            `CREATE INDEX IF NOT EXISTS idx_updates_upvotes ON products_updates(upvotes DESC);`,
            `CREATE INDEX IF NOT EXISTS idx_updates_views ON products_updates(views DESC);`,
            
            // For slug and search
            `CREATE INDEX IF NOT EXISTS idx_updates_slug ON products_updates(slug);`,
            
            // Full-text search indexes
            `CREATE INDEX IF NOT EXISTS idx_updates_search_title ON products_updates USING gin(to_tsvector('english', title));`,
            `CREATE INDEX IF NOT EXISTS idx_updates_search_release_notes ON products_updates USING gin(to_tsvector('english', release_notes));`,
            
            // For highlights array search
            `CREATE INDEX IF NOT EXISTS idx_updates_highlights ON products_updates USING gin(highlights);`,
            
            // Composite indexes for common queries
            `CREATE INDEX IF NOT EXISTS idx_updates_product_public ON products_updates(product_id, release_date DESC) WHERE status = 'published' AND is_public = true;`,
            `CREATE INDEX IF NOT EXISTS idx_updates_product_type ON products_updates(product_id, version_type, release_date DESC);`,
            `CREATE INDEX IF NOT EXISTS idx_updates_product_latest ON products_updates(product_id) WHERE is_latest = true;`,
            
            // For JSONB fields queries
            `CREATE INDEX IF NOT EXISTS idx_updates_new_features ON products_updates USING GIN(new_features);`,
            `CREATE INDEX IF NOT EXISTS idx_updates_improvements ON products_updates USING GIN(improvements);`,
            `CREATE INDEX IF NOT EXISTS idx_updates_bug_fixes ON products_updates USING GIN(bug_fixes);`,
        ];

        for (const indexQuery of indexes) {
            try {
                await database.query(indexQuery);
            } catch (err) {
                // Index already exists, ignore
                if (!(err instanceof Error) || !err.message.includes("already exists")) {
                    throw err;
                }
            }
        }

        // Drop existing functions and triggers first to avoid conflicts
        const cleanupQueries = [
            `DROP TRIGGER IF EXISTS set_updates_updated_at ON products_updates;`,
            `DROP TRIGGER IF EXISTS update_product_version ON products_updates;`,
            `DROP TRIGGER IF EXISTS ensure_single_latest_version ON products_updates;`,
            `DROP FUNCTION IF EXISTS update_updates_updated_at();`,
            `DROP FUNCTION IF EXISTS update_product_last_version();`,
            `DROP FUNCTION IF EXISTS handle_latest_version_update();`,
        ];

        for (const cleanupQuery of cleanupQueries) {
            try {
                await database.query(cleanupQuery);
            } catch (err) {
                // Ignore errors if functions/triggers don't exist
            }
        }

        // Create function to automatically update updated_at
        const updateTriggerFunction = `
          CREATE OR REPLACE FUNCTION update_updates_updated_at()
          RETURNS TRIGGER AS $$
          BEGIN
            NEW.updated_at = CURRENT_TIMESTAMP;
            IF NEW.status = 'published' AND OLD.status != 'published' THEN
              NEW.published_at = CURRENT_TIMESTAMP;
            END IF;
            RETURN NEW;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(updateTriggerFunction);

        // Create trigger for automatic updated_at
        const updateTrigger = `
          CREATE TRIGGER set_updates_updated_at
          BEFORE UPDATE ON products_updates
          FOR EACH ROW
          EXECUTE FUNCTION update_updates_updated_at();
        `;

        await database.query(updateTrigger);

        // Create function to handle single latest version per product
        const handleLatestVersionFunction = `
          CREATE OR REPLACE FUNCTION handle_latest_version_update()
          RETURNS TRIGGER AS $$
          BEGIN
            IF NEW.is_latest = true THEN
              -- Remove latest flag from other versions of the same product
              UPDATE products_updates 
              SET is_latest = false 
              WHERE product_id = NEW.product_id 
                AND id != NEW.id
                AND is_latest = true;
            END IF;
            RETURN NEW;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(handleLatestVersionFunction);

        // Create trigger for single latest version
        const latestVersionTrigger = `
          CREATE TRIGGER ensure_single_latest_version
          BEFORE INSERT OR UPDATE ON products_updates
          FOR EACH ROW
          WHEN (NEW.is_latest = true)
          EXECUTE FUNCTION handle_latest_version_update();
        `;

        await database.query(latestVersionTrigger);

        // Create function to update product's last version information
        const updateProductVersionFunction = `
          CREATE OR REPLACE FUNCTION update_product_last_version()
          RETURNS TRIGGER AS $$
          BEGIN
            IF NEW.is_latest = true AND NEW.status = 'published' THEN
              UPDATE products 
              SET 
                version = NEW.version,
                last_release_date = NEW.release_date,
                updated_at = CURRENT_TIMESTAMP
              WHERE id = NEW.product_id;
            END IF;
            RETURN NEW;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(updateProductVersionFunction);

        // Create trigger for updating product version
        const productVersionTrigger = `
          CREATE TRIGGER update_product_version
          AFTER INSERT OR UPDATE ON products_updates
          FOR EACH ROW
          WHEN (NEW.is_latest = true AND NEW.status = 'published')
          EXECUTE FUNCTION update_product_last_version();
        `;

        await database.query(productVersionTrigger);

        // Create function to generate slug from version and title
        const slugFunction = `
          CREATE OR REPLACE FUNCTION generate_update_slug(version TEXT, title TEXT)
          RETURNS TEXT AS $$
          BEGIN
            RETURN lower(
              regexp_replace(
                regexp_replace(
                  version || '-' || title,
                  '[^a-zA-Z0-9\\s-]', '', 'g'
                ),
                '\\s+', '-', 'g'
              )
            );
          END;
          $$ LANGUAGE plpgsql IMMUTABLE;
        `;

        await database.query(slugFunction);

        // Create partial unique index for single latest version per product
        const uniqueLatestIndex = `
          CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_latest_per_product 
          ON products_updates(product_id) 
          WHERE is_latest = true;
        `;

        await database.query(uniqueLatestIndex);

        console.log("✅ Products updates table created successfully");
    } catch (error) {
        console.error("❌ Failed To Create Products Updates Table.", error);
        process.exit(1);
    }
}