-- https://www.phpmyadmin.net/
--

START TRANSACTION;


CREATE DATABASE IF NOT EXISTS `mydetail_paparazi` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `mydetail_paparazi`;
--
-- Database: `mydetail_paparazi`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `UP_GetUserStandings2` (IN `SeasonSelected` INT)  READS SQL DATA
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tempUvozTB engine = memory AS 
(
    select  NAME, username, round, Points1, Points2, Points3, PointFastLap,
WoreShirt,BillOverR140, WasPresent, 
(Points1+ Points2+ Points3+PointFastLap) as  PointsRace,
(((Points1+ Points2+ Points3) * (IFNULL(WasPresent, 0) + 1))+PointFastLap+ 
(IFNULL(BillOverR140, 0) * 10)+ 
(IFNULL(WoreShirt, 0) * 10) ) as PointsTotal
from  
(
	select z.NAME, z.username, z.Round, z.Season, z.UserPlace1, z.UserPlace2, z.UserPlace3, z.UserFatestLap,
	z.RoundPlace1, z.RoundPlace2, z.RoundPlace3, z.RoundFastLap,
	case 
		when UserPlace1 = RoundPlace1 then 25
		else 0
	end as Points1,
	case 
		when UserPlace2 = RoundPlace2 then 18
		else 0
	end as Points2,
	case when UserPlace3 = RoundPlace3 then 15
		else 0
	end as Points3,
    case when UserFatestLap = RoundFastLap then 10
		else 0
	end as PointFastLap,
	IFNULL(ws.Value, 0) as WoreShirt, 
	IFNULL(bo.Value, 0) as BillOverR140, 
	IFNULL(wp.Value, 0) as WasPresent
	from
	(

		select rr.NAME, rr.username, rr.round, rr.Season , 
		us.1stPlace as UserPlace1, us.2ndPlace as UserPlace2, us.3rdPlace as UserPlace3, us.FatestLap as UserFatestLap,
		Place1 as RoundPlace1, Place2 as RoundPlace2, Place3 as RoundPlace3,
        PlaceFastLap as RoundFastLap
		from 
		(
			select r.NAME, r.username, qr.Round, qr.Season
			from regform r
			join 
			(
				select distinct Round , Season
				from results
				where Season = SeasonSelected
			) as qr
		) as rr
		left join userselection us
			on us.UserName = rr.username
			and us.`Round` = rr.`Round`
			and us.`Season` = rr.`Season`
		left join 
		(
			SELECT r1.`Season`, r1.`Round`,
			r1.`DriverName` as Place1, 
			r2.`DriverName` as Place2, 
			r3.`DriverName` as Place3,
            fl.`DriverName` as PlaceFastLap
			FROM `results` as r1
			left join `results` as r2
				on r2.`Round` = r1.`Round`
				and r2.`Season` = r1.`Season`
				and r2.`Position` = 2
			left join `results` as r3
				on r3.`Round` = r1.`Round`
				and r3.`Season` = r1.`Season`
				and r3.`Position` = 3
            left join `results` as fl
                on fl.`Round` = r1.`Round`
                and fl.`Season` = r1.`Season`
                and fl.`Position` = 99
			WHERE r1.`Season` = SeasonSelected
			and r1.`Position` = 1
		) as b 
			on b.Round = rr.round
			and b.`Season` = rr.`Season`
		where rr.Season = SeasonSelected

	) as z
	left join userextrapoints wp
		on wp.Round = z.round
		and wp.UserName = z.username
		and wp.Season = z.Season
		and REPLACE(wp.ExtraPointsName," ","") = "WasPresent"
	left join userextrapoints bo
		on bo.Round = z.round
		and bo.UserName = z.username
		and bo.Season = z.Season
		and REPLACE(bo.ExtraPointsName," ","") = "BillOverR140"
	left join userextrapoints ws
		on ws.Round = z.round
		and ws.UserName = z.username
		and ws.Season = z.Season
		and REPLACE(ws.ExtraPointsName," ","") = "WoreShirt"
    group by z.NAME, z.username, z.round
) as mydata

);
    
#select * from tempUvozTB;

SET @SQL = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
		'sum(case when Round = ',
		dn,
		' then r.PointsTotal else 0 end) AS `R',
		dn, '`'
		)
) INTO @SQL
FROM
(
	SELECT DISTINCT Round dn
	FROM tempUvozTB r
	ORDER BY r.Round
) d;

SET @SQL 
 = CONCAT('select  @curRank := @curRank + 1 AS rank, aaa.*   from (SELECT NAME,  ', @SQL, 
 ', sum(r.PointsTotal) as Total from tempUvozTB r group by r.`NAME`) as aaa, (SELECT @curRank := 0) rr ORDER by Total DESC, NAME ASC; ');
 

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

DROP TEMPORARY TABLE IF EXISTS tempUvozTB;

END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `drivers`
--

CREATE TABLE `drivers` (
  `Season` int(11) DEFAULT NULL,
  `Name` varchar(100) NOT NULL,
  `Number` int(11) DEFAULT NULL,
  `Nationality` varchar(50) NOT NULL,
  `DOB` date DEFAULT NULL,
  `Info` varchar(100) DEFAULT NULL,
  `Picture` blob
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `drivers`
--

INSERT INTO `drivers` (`Season`, `Name`, `Number`, `Nationality`, `DOB`, `Info`, `Picture`) VALUES
(2018, 'Antonio Giovinazzi', 36, 'Italian', '1993-12-14', 'en.wikipedia.org/wiki/Antonio_Giovinazzi', 0x[encoded data, 9430 characters]),
(2018, 'Brendon Hartley', 39, 'New Zealander', '1989-11-10', 'en.wikipedia.org/wiki/Brendon_Hartley', 0x[encoded data, 13490 characters]),
(2018, 'Carlos Sainz', 55, 'Spanish', '1994-09-01', 'en.wikipedia.org/wiki/Carlos_Sainz_Jr.', 0x[encoded data, 10116 characters]);
INSERT INTO `drivers` (`Season`, `Name`, `Number`, `Nationality`, `DOB`, `Info`, `Picture`) VALUES
(2018, 'Charles Leclerc', 16, 'Monegasque', '1997-10-16', 'en.wikipedia.org/wiki/Charles_Leclerc_%28racing_driver%29', 0x[encoded data, 44544 characters]);
INSERT INTO `drivers` (`Season`, `Name`, `Number`, `Nationality`, `DOB`, `Info`, `Picture`) VALUES
(2018, 'Daniel Ricciardo', 3, 'Australian', '1989-07-01', 'en.wikipedia.org/wiki/Daniel_Ricciardo', 0x[encoded data, 8942 characters]),
(2018, 'Daniil Kvyat', 26, 'Russian', '1994-04-26', 'en.wikipedia.org/wiki/Daniil_Kvyat', 0x[encoded data, 10448 characters]),
(2018, 'Esteban Ocon', 31, 'French', '1996-09-17', 'en.wikipedia.org/wiki/Esteban_Ocon', 0x[encoded data, 12746 characters]),
(2018, 'Felipe Massa', 19, 'Brazilian', '1981-04-25', 'en.wikipedia.org/wiki/Felipe_Massa', 0x[encoded data, 12908 characters]);
INSERT INTO `drivers` (`Season`, `Name`, `Number`, `Nationality`, `DOB`, `Info`, `Picture`) VALUES
(2018, 'Fernando Alonso', 14, 'Spanish', '1981-07-29', 'en.wikipedia.org/wiki/Fernando_Alonso', 0x[encoded data, 11606 characters]),
(2018, 'Jenson Button', 22, 'British', '1980-01-19', 'en.wikipedia.org/wiki/Jenson_Button', 0x[encoded data, 17718 characters]),
(2018, 'Jolyon Palmer', 30, 'British', '1991-01-20', 'en.wikipedia.org/wiki/Jolyon_Palmer', 0x[encoded data, 10948 characters]);
INSERT INTO `drivers` (`Season`, `Name`, `Number`, `Nationality`, `DOB`, `Info`, `Picture`) VALUES
(2018, 'Kevin Magnussen', 20, 'Danish', '1992-10-05', 'en.wikipedia.org/wiki/Kevin_Magnussen', 0x[encoded data, 13282 characters]),
(2018, 'Kimi Räikkönen', 7, 'Finnish', '1979-10-17', 'en.wikipedia.org/wiki/Kimi_R%C3%A4ikk%C3%B6nen', 0x[encoded data, 15146 characters]),
(2018, 'Lance Stroll', 18, 'Canadian', '1998-10-29', 'en.wikipedia.org/wiki/Lance_Stroll', 0x[encoded data, 6062 characters]),
(2018, 'Lewis Hamilton', 44, 'British', '1985-01-07', 'en.wikipedia.org/wiki/Lewis_Hamilton', 0x[encoded data, 10992 characters]);
INSERT INTO `drivers` (`Season`, `Name`, `Number`, `Nationality`, `DOB`, `Info`, `Picture`) VALUES
(2018, 'Marcus Ericsson', 9, 'Swedish', '1990-09-02', 'en.wikipedia.org/wiki/Marcus_Ericsson', 0x[encoded data, 9150 characters]),
(2018, 'Max Verstappen', 33, 'Dutch', '1997-09-30', 'en.wikipedia.org/wiki/Max_Verstappen', 0x[encoded data, 8770 characters]),
(2018, 'Nico Hülkenberg', 27, 'German', '1987-08-19', 'en.wikipedia.org/wiki/Nico_H%C3%BClkenberg', 0x[encoded data, 10620 characters]),
(2018, 'Pascal Wehrlein', 94, 'German', '1994-10-18', 'en.wikipedia.org/wiki/Pascal_Wehrlein', 0x[encoded data, 8148 characters]);
INSERT INTO `drivers` (`Season`, `Name`, `Number`, `Nationality`, `DOB`, `Info`, `Picture`) VALUES
(2018, 'Paul di Resta', 0, 'British', '1986-04-16', 'en.wikipedia.org/wiki/Paul_di_Resta', 0x[encoded data, 13240 characters]),
(2018, 'Pierre Gasly', 10, 'French', '1996-02-07', 'en.wikipedia.org/wiki/Pierre_Gasly', 0x[encoded data, 8486 characters]),
(2018, 'Romain Grosjean', 8, 'French', '1986-04-17', 'en.wikipedia.org/wiki/Romain_Grosjean', 0x[encoded data, 14138 characters]),
(2018, 'Sebastian Vettel', 5, 'German', '1987-07-03', 'en.wikipedia.org/wiki/Sebastian_Vettel', 0x[encoded data, 8564 characters]);
INSERT INTO `drivers` (`Season`, `Name`, `Number`, `Nationality`, `DOB`, `Info`, `Picture`) VALUES
(2018, 'Sergey Sirotkin\r\n', 35, 'Russian', '1995-08-27', 'en.wikipedia.org/wiki/Sergey_Sirotkin_(racing_driver)\r\n', 0x[encoded data, 39200 characters]);
INSERT INTO `drivers` (`Season`, `Name`, `Number`, `Nationality`, `DOB`, `Info`, `Picture`) VALUES
(2018, 'Sergio Pérez', 11, 'Mexican', '1990-01-26', 'en.wikipedia.org/wiki/Sergio_P%C3%A9rez', 0x[encoded data, 11484 characters]),
(2018, 'Stoffel Vandoorne', 2, 'Belgian', '1992-03-26', 'en.wikipedia.org/wiki/Stoffel_Vandoorne', 0x[encoded data, 10760 characters]),
(2018, 'Valtteri Bottas', 77, 'Finnish', '1989-08-28', 'en.wikipedia.org/wiki/Valtteri_Bottas', 0x[encoded data, 10116 characters]);

-- --------------------------------------------------------

--
