DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE people (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
INSERT INTO people (name) VALUES ('John'),('Jane'),('Jill'),('James');

CREATE TABLE opportunities (
  id INT(11) NOT NULL AUTO_INCREMENT,
  owner_id INT(11) NOT NULL,
  amount DECIMAL(63,5) NOT NULL,
  code VARCHAR(3) NOT NULL,
  probability INT(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
INSERT INTO opportunities (owner_id, amount, code, probability) VALUES (1,123.45,'GBP',25),(2,234.56,'USD',50),(3,345.67,'EUR',75),(4,456.78,'JPY',100);

CREATE FUNCTION details_row() RETURNS int DETERMINISTIC RETURN @details_row:=@details_row+1;
  
CREATE VIEW details AS
SELECT
  people.name AS person_name,
  opportunities.amount AS opportunity_amount,
  CONCAT_WS(',', opportunities.code) AS opportunity_code,
  ROUND(IFNULL(opportunities.probability, 0) * IFNULL(opportunities.amount, 0) / 100, 0) AS weighted_amount,
  CONCAT_WS(',', opportunities.code) AS weighted_code,
  details_row() AS id
FROM opportunities
LEFT JOIN people ON people.id = opportunities.owner_id;

CREATE FUNCTION summary_row() RETURNS int DETERMINISTIC RETURN @summary_row:=@summary_row+1;
  
CREATE VIEW summary AS
SELECT
  details.person_name AS person_name,
  SUM(details.weighted_amount) AS total_weighted_amount,
  CONCAT_WS(',', GROUP_CONCAT(DISTINCT details.weighted_code SEPARATOR ',')) AS total_weighted_code,
  SUM(details.opportunity_amount) AS total_amount,
  CONCAT_WS(',', GROUP_CONCAT(DISTINCT details.opportunity_code SEPARATOR ',')) AS total_code,
  summary_row() AS id
FROM details
GROUP BY IFNULL(details.person_name, '');

SET @details_row = 0,
    @summary_row = 0;
    
DROP TEMPORARY TABLE IF EXISTS summary_temporary;
CREATE TEMPORARY TABLE summary_temporary (
  INDEX index_on_person_name (person_name(10)),
  INDEX index_on_total_amount (total_amount),
  INDEX index_on_total_weighted_amount (total_weighted_amount)
  ) ENGINE=Aria
  SELECT * FROM summary;
