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

export async function createProductsReviewsTable() {
  try {
    const tableQuery = `
    CREATE TABLE IF NOT EXISTS products_reviews (
      id UUID DEFAULT gen_random_uuid() PRIMARY KEY,

      -- References
      creator_id UUID NOT NULL,
      product_id UUID NOT NULL,

      -- Review content
      title VARCHAR(200),
      content TEXT NOT NULL CHECK (char_length(trim(content)) > 0),
      rating DECIMAL(3,2) NOT NULL CHECK (rating >= 0 AND rating <= 5),

      -- Optional pros/cons
      pros TEXT[] DEFAULT '{}',
      cons TEXT[] DEFAULT '{}',

      -- Helpfulness counters
      helpful_count INTEGER DEFAULT 0 CHECK (helpful_count >= 0),
      not_helpful_count INTEGER DEFAULT 0 CHECK (not_helpful_count >= 0),

      -- Review status / workflow
      status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'approved', 'rejected')),

      -- Verified purchase (optional)
      is_verified_purchase BOOLEAN DEFAULT false,

      -- Media (images/videos)
      images JSONB DEFAULT '[]'::JSONB,
      videos JSONB DEFAULT '[]'::JSONB,

      -- Recommendation yes/no
      would_recommend BOOLEAN,

      created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
      published_at TIMESTAMPTZ,

      -- Unique rules
      CONSTRAINT unique_creator_product_review UNIQUE (creator_id, product_id),

      FOREIGN KEY (creator_id) REFERENCES accounts(id) ON DELETE CASCADE,
      FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
    );`;

    await database.query(tableQuery);

    // Essential indexes
    const indexQueries = [
      `CREATE INDEX IF NOT EXISTS idx_reviews_product_id ON products_reviews(product_id);`,
      `CREATE INDEX IF NOT EXISTS idx_reviews_status ON products_reviews(status);`,
      `CREATE INDEX IF NOT EXISTS idx_reviews_rating ON products_reviews(rating);`,
      `CREATE INDEX IF NOT EXISTS idx_reviews_created_at ON products_reviews(created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_reviews_helpful ON products_reviews(helpful_count DESC);`
    ];

    for (const q of indexQueries) {
      await database.query(q);
    }

    // Drop old triggers/functions if exist
    await database.query(`DROP TRIGGER IF EXISTS trg_reviews_timestamp ON products_reviews;`);
    await database.query(`DROP FUNCTION IF EXISTS update_reviews_timestamp();`);
    await database.query(`DROP TRIGGER IF EXISTS trg_update_product_rating ON products_reviews;`);
    await database.query(`DROP FUNCTION IF EXISTS update_product_rating();`);

    // Auto updated_at (+ published_at for approved reviews)
    await database.query(`
      CREATE OR REPLACE FUNCTION update_reviews_timestamp()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = CURRENT_TIMESTAMP;

        IF NEW.status = 'approved' AND OLD.status != 'approved' THEN
          NEW.published_at = CURRENT_TIMESTAMP;
        END IF;

        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `);

    await database.query(`
      CREATE TRIGGER trg_reviews_timestamp
      BEFORE UPDATE ON products_reviews
      FOR EACH ROW
      EXECUTE FUNCTION update_reviews_timestamp();
    `);

    // Product rating auto-calc
    await database.query(`
      CREATE OR REPLACE FUNCTION update_product_rating()
      RETURNS TRIGGER AS $$
      DECLARE avg_rating DECIMAL(3,2);
      BEGIN
        SELECT AVG(rating)::DECIMAL(3,2)
        INTO avg_rating
        FROM products_reviews
        WHERE product_id = COALESCE(NEW.product_id, OLD.product_id)
          AND status = 'approved';

        UPDATE products
        SET rating = COALESCE(avg_rating, 0)
        WHERE id = COALESCE(NEW.product_id, OLD.product_id);

        RETURN NULL;
      END;
      $$ LANGUAGE plpgsql;
    `);

    await database.query(`
      CREATE TRIGGER trg_update_product_rating
      AFTER INSERT OR UPDATE OR DELETE ON products_reviews
      FOR EACH ROW
      EXECUTE FUNCTION update_product_rating();
    `);

    console.log("✅ Products Reviews table created successfully");
  } catch (error) {
    console.error("Failed to create Products Reviews table.", error);
    process.exit(1);
  }
}
