DELIMITER $$

DROP PROCEDURE IF EXISTS SP_IWMT_GetPileByName $$
CREATE PROCEDURE SP_IWMT_GetPileByName (Name Varchar(10))
  DETERMINISTIC
  READS SQL DATA
  SQL SECURITY INVOKER
  COMMENT 'Gets Pile Data based on Pile Type'

  BEGIN
    -- Use a cursor to move through our temp table to gather additional information
    DECLARE vFileID INT;
    DECLARE vFileMimeType INT;
    DECLARE vFileSize INT;
    DECLARE vFileTitle VARCHAR(255);
    DECLARE vSubmitterID INT;
    DECLARE vSubmitDate DATE;
    DECLARE vSubmitter VARCHAR(32);
    DECLARE vGoodVotes INT;
    DECLARE vBadVotes INT;
    DECLARE vOffensiveVotes INT;
    DECLARE vFileComments INT;


    DECLARE no_more_rows BOOLEAN;
    DECLARE fCursor CURSOR FOR
      SELECT a.file_id, a.file_mime_type, a.file_size, a.file_title, a.file_submitter, a.submit_date, c.user_name as submitter
      FROM iwmt_files a, iwmt_users c
      WHERE a.FILE_MIME_TYPE IN (
        SELECT DISTINCT b.FILE_MIME_TYPE
        FROM iwmt_file_mime_types b
        WHERE b.PILE_TYPE = Name)
      AND c.user_id = a.file_submitter
      ORDER BY a.SUBMIT_DATE DESC
      LIMIT 0,20;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
      SET no_more_rows := TRUE;

    -- Create a temporary table to hold our base data
    DROP TEMPORARY TABLE IF EXISTS Pile;
    CREATE TEMPORARY TABLE Pile (
      file_id INT
      ,file_mime_type INT
      ,file_size INT
      ,file_title VARCHAR(255)
      ,file_submitter VARCHAR(32)
      ,file_submitter_id INT
      ,submit_date DATE
      ,good_votes INT
      ,bad_votes INT
      ,offensive_votes INT
      ,file_comments INT
    );

    -- Move through the temp table and update columns
    OPEN fCursor;
    loop_fCursor: LOOP
      FETCH fCursor
        INTO vFileID
             ,vFileMimeType
             ,vFileSize
             ,vFileTitle
             ,vSubmitterID
             ,vSubmitDate
             ,vSubmitter;
      IF no_more_rows THEN
        CLOSE fCursor;
        LEAVE loop_fCursor;
      END IF;

      -- Update columns
      SELECT vGoodVotes = COUNT(vote) FROM iwmt_file_comments WHERE file_id = vFileID AND vote > 0;
      SELECT vBadVotes  = COUNT(vote) FROM iwmt_file_comments WHERE file_id = vFileID AND vote < 0;
      SELECT vOffensiveVotes = COUNT(offensive_flag) FROM iwmt_file_comments WHERE file_id = vFileID AND offensive_flag = 1;
      SELECT vFileComments = COUNT(comment_id) FROM iwmt_file_comments WHERE file_id = vFileID;

      -- Insert into our temp table
      INSERT INTO Pile (file_id, file_mime_type, file_size, file_title, file_submitter_id, file_submitter, submit_date, good_votes, bad_votes, offensive_votes, file_comments)
        VALUES(vFileID
            ,vFileMimeType
            ,vFileSize
            ,vFileTitle
            ,vSubmitterID
            ,vSubmitter
            ,vSubmitDate
            ,vGoodVotes
            ,vBadVotes
            ,vOffensiveVotes
            ,vFileComments
        );
    END LOOP loop_fCursor;

    -- Return our final resultset
    SELECT * FROM Pile;

  END $$

  DELIMITER ;