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

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

         -- References (many-to-many relationship between products and accounts)
         product_id UUID NOT NULL,
         creator_id UUID NOT NULL,
         
         -- Role and permissions
         role VARCHAR(50) NOT NULL DEFAULT 'member' 
           CHECK (role IN ('founder', 'co-founder', 'admin', 'member', 'contributor', 'adviser')),
         title VARCHAR(100),
         department VARCHAR(100),
         
         -- Team member details specific to this product
         is_public BOOLEAN DEFAULT true,
         is_active BOOLEAN DEFAULT true,
         display_order INTEGER DEFAULT 0,
         
         -- Bio specific to this product role
         bio TEXT,
         responsibilities TEXT[] DEFAULT '{}',
         expertise TEXT[] DEFAULT '{}',
         
         -- Availability and contact
         available_for_contact BOOLEAN DEFAULT false,
         contact_email VARCHAR(255),
         
         -- Stats specific to this product
         contributed_at TIMESTAMP,
         last_active_at TIMESTAMP,
         commitment_level VARCHAR(20) DEFAULT 'full-time' 
           CHECK (commitment_level IN ('full-time', 'part-time', 'contractor', 'advisor')),
         
         -- Badges and recognition
         badges JSONB DEFAULT '[]'::JSONB,
         is_featured BOOLEAN DEFAULT false,
         
         -- Verification
         is_verified BOOLEAN DEFAULT false,
         verification_note TEXT,
         
         -- Timestamps
         joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         left_at TIMESTAMP,
         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         
         -- Foreign key constraints
         FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
         FOREIGN KEY (creator_id) REFERENCES accounts(id) ON DELETE CASCADE,
         
         -- Ensure unique user per product (one user can't be added twice to same product)
         CONSTRAINT unique_user_per_product UNIQUE (product_id, creator_id),
         
         -- Valid display order
         CONSTRAINT valid_display_order CHECK (display_order >= 0)
        );`;

        await database.query(tableQuery);

        // Index creation for better performance
        const indexes = [
            // Foreign key indexes
            `CREATE INDEX IF NOT EXISTS idx_teams_product_id ON products_teams(product_id);`,
            `CREATE INDEX IF NOT EXISTS idx_teams_creator_id ON products_teams(creator_id);`,
            
            // For role-based queries
            `CREATE INDEX IF NOT EXISTS idx_teams_role ON products_teams(role);`,
            `CREATE INDEX IF NOT EXISTS idx_teams_department ON products_teams(department);`,
            
            // For visibility and status
            `CREATE INDEX IF NOT EXISTS idx_teams_is_public ON products_teams(is_public) WHERE is_public = true;`,
            `CREATE INDEX IF NOT EXISTS idx_teams_is_active ON products_teams(is_active) WHERE is_active = true;`,
            `CREATE INDEX IF NOT EXISTS idx_teams_is_featured ON products_teams(is_featured) WHERE is_featured = true;`,
            `CREATE INDEX IF NOT EXISTS idx_teams_is_verified ON products_teams(is_verified) WHERE is_verified = true;`,
            
            // For sorting and ordering
            `CREATE INDEX IF NOT EXISTS idx_teams_display_order ON products_teams(display_order);`,
            `CREATE INDEX IF NOT EXISTS idx_teams_joined_at ON products_teams(joined_at DESC);`,
            `CREATE INDEX IF NOT EXISTS idx_teams_created_at ON products_teams(created_at DESC);`,
            
            // For commitment level
            `CREATE INDEX IF NOT EXISTS idx_teams_commitment ON products_teams(commitment_level);`,
            
            // For array field searches
            `CREATE INDEX IF NOT EXISTS idx_teams_responsibilities ON products_teams USING gin(responsibilities);`,
            `CREATE INDEX IF NOT EXISTS idx_teams_expertise ON products_teams USING gin(expertise);`,
            `CREATE INDEX IF NOT EXISTS idx_teams_badges ON products_teams USING gin(badges);`,
            
            // Composite indexes for common queries
            `CREATE INDEX IF NOT EXISTS idx_teams_product_active_public ON products_teams(product_id, is_active, is_public) WHERE is_active = true AND is_public = true;`,
            `CREATE INDEX IF NOT EXISTS idx_teams_product_role ON products_teams(product_id, role, display_order);`,
            `CREATE INDEX IF NOT EXISTS idx_teams_creator_product_active ON products_teams(creator_id, product_id, is_active) WHERE is_active = true;`,
            `CREATE INDEX IF NOT EXISTS idx_teams_product_featured ON products_teams(product_id, display_order) WHERE is_featured = true;`,
            
            // For analytics
            `CREATE INDEX IF NOT EXISTS idx_teams_last_active ON products_teams(last_active_at DESC NULLS LAST);`,
            `CREATE INDEX IF NOT EXISTS idx_teams_contributed_at ON products_teams(contributed_at DESC NULLS LAST);`,
            
            // For full-text search
            `CREATE INDEX IF NOT EXISTS idx_teams_search_bio ON products_teams USING gin(to_tsvector('english', bio));`,
            `CREATE INDEX IF NOT EXISTS idx_teams_search_title ON products_teams USING gin(to_tsvector('english', title));`,
        ];

        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;
                }
            }
        }

        // Create view for enriched team data (joins with accounts table)
        const teamViewQuery = `
          CREATE OR REPLACE VIEW products_teams_enriched AS
          SELECT 
            pt.id,
            pt.product_id,
            pt.creator_id,
            pt.role,
            pt.title,
            pt.department,
            pt.is_public,
            pt.is_active,
            pt.display_order,
            -- pt.twitter_handle,
            -- pt.linkedin_url,
            -- pt.personal_website,
            pt.bio,
            pt.responsibilities,
            pt.expertise,
            pt.available_for_contact,
            pt.contact_email,
            pt.contributed_at,
            pt.last_active_at,
            pt.commitment_level,
            pt.badges,
            pt.is_featured,
            pt.is_verified,
            pt.verification_note,
            pt.joined_at,
            pt.left_at,
            pt.created_at,
            pt.updated_at,
            
            -- User data from accounts table
            a.user_full_name as user_name,
            a.email as user_email,
            a.avatar as user_avatar,
            -- a.bio as user_bio,
            -- a.location as user_location,
            a.created_at as user_created_at,
            
            -- Product data from products table
            p.name as product_name,
            p.slug as product_slug,
            p.logo as product_logo,
            p.description as product_description
            
          FROM products_teams pt
          LEFT JOIN accounts a ON pt.creator_id = a.id
          LEFT JOIN products p ON pt.product_id = p.id
          WHERE pt.is_active = true AND pt.is_public = true;
        `;

        await database.query(teamViewQuery);

        // Create view for user's product portfolio (all products a user is part of)
        const userPortfolioView = `
          CREATE OR REPLACE VIEW user_product_portfolio AS
          SELECT 
            pt.creator_id,
            pt.role,
            pt.title,
            pt.joined_at,
            pt.is_public,
            pt.is_active,
            
            -- Product details
            p.id as product_id,
            p.name as product_name,
            p.slug as product_slug,
            p.logo as product_logo,
            p.description as product_description,
            -- p.category as product_category,
            p.status as product_status,
            p.launch_date as product_launch_date,
            p.rating as product_rating,
            p.upvotes as product_upvotes,
            p.followers as product_followers
            
          FROM products_teams pt
          JOIN products p ON pt.product_id = p.id
          WHERE pt.is_active = true 
            AND pt.is_public = true 
            AND p.status = 'active';
        `;

        await database.query(userPortfolioView);

        // Drop existing functions and triggers first to avoid conflicts
        const cleanupQueries = [
            `DROP TRIGGER IF EXISTS set_teams_updated_at ON products_teams;`,
            `DROP TRIGGER IF EXISTS update_user_last_active ON products_teams;`,
            `DROP FUNCTION IF EXISTS update_teams_updated_at();`,
            `DROP FUNCTION IF EXISTS update_user_last_active_time();`,
            `DROP FUNCTION IF EXISTS get_team_member_stats();`,
        ];

        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_teams_updated_at()
          RETURNS TRIGGER AS $$
          BEGIN
            NEW.updated_at = CURRENT_TIMESTAMP;
            RETURN NEW;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(updateTriggerFunction);

        // Create trigger for automatic updated_at
        const updateTrigger = `
          CREATE TRIGGER set_teams_updated_at
          BEFORE UPDATE ON products_teams
          FOR EACH ROW
          EXECUTE FUNCTION update_teams_updated_at();
        `;

        await database.query(updateTrigger);

        // Create function to update user's last active time
        const lastActiveFunction = `
          CREATE OR REPLACE FUNCTION update_user_last_active_time()
          RETURNS TRIGGER AS $$
          BEGIN
            IF NEW.is_active = true THEN
              NEW.last_active_at = CURRENT_TIMESTAMP;
            END IF;
            RETURN NEW;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(lastActiveFunction);

        // Create trigger for updating last active time
        const lastActiveTrigger = `
          CREATE TRIGGER update_user_last_active
          BEFORE INSERT OR UPDATE ON products_teams
          FOR EACH ROW
          EXECUTE FUNCTION update_user_last_active_time();
        `;

        await database.query(lastActiveTrigger);

        // Create function to get team member stats
        const teamStatsFunction = `
          CREATE OR REPLACE FUNCTION get_team_member_stats(user_uuid UUID)
          RETURNS TABLE (
            total_products INTEGER,
            current_products INTEGER,
            founder_count INTEGER,
            active_since TIMESTAMP,
            latest_product_name VARCHAR(255)
          ) AS $$
          BEGIN
            RETURN QUERY
            SELECT 
              COUNT(DISTINCT pt.product_id)::INTEGER as total_products,
              COUNT(DISTINCT CASE WHEN pt.is_active = true THEN pt.product_id END)::INTEGER as current_products,
              COUNT(DISTINCT CASE WHEN pt.role IN ('founder', 'co-founder') THEN pt.product_id END)::INTEGER as founder_count,
              MIN(pt.joined_at) as active_since,
              MAX(p.name) as latest_product_name
            FROM products_teams pt
            LEFT JOIN products p ON pt.product_id = p.id
            WHERE pt.creator_id = user_uuid
            AND pt.is_public = true
            GROUP BY pt.creator_id;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(teamStatsFunction);

        // Create function to get product team summary
        const productTeamSummaryFunction = `
          CREATE OR REPLACE FUNCTION get_product_team_summary(product_uuid UUID)
          RETURNS TABLE (
            total_members INTEGER,
            founders_count INTEGER,
            active_members INTEGER,
            departments TEXT[],
            latest_join TIMESTAMP
          ) AS $$
          BEGIN
            RETURN QUERY
            SELECT 
              COUNT(*)::INTEGER as total_members,
              COUNT(CASE WHEN role IN ('founder', 'co-founder') THEN 1 END)::INTEGER as founders_count,
              COUNT(CASE WHEN is_active = true THEN 1 END)::INTEGER as active_members,
              ARRAY_AGG(DISTINCT department) FILTER (WHERE department IS NOT NULL) as departments,
              MAX(joined_at) as latest_join
            FROM products_teams
            WHERE product_id = product_uuid
            AND is_public = true;
          END;
          $$ LANGUAGE plpgsql;
        `;

        await database.query(productTeamSummaryFunction);

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