<?php

class m141031_142032_add_color_functions extends MyDbMigration
{
  public function up()
  {
    // rimuovo le altre - eventuali - versioni delle funzioni
    $this->down();
    // installo le funzioni
    $this->execute("
        CREATE FUNCTION kria_color_to_html(c long) RETURNS char(10)
        BEGIN
        declare h float;
        declare l float;
        declare s float;

        declare t1 float;
        declare t2 float;

        declare rt3 float;
        declare gt3 float;
        declare bt3 float;

        declare r long;
        declare g long;
        declare b long;
        declare rgb long;

        declare html char(10);

        set h = (((ifnull(c,0) & 0xFF000000) >> 24)+0.0)/255.0;
        set l = (((ifnull(c,0) & 0xFF0000) >> 16)+0.0)/255.0;
        set s = (((ifnull(c,0) & 0xFF00) >> 8)+0.0)/255.0;

        IF s = 0.0 THEN
        set r = l;
        set g = l;
        set b = l;
        ELSE
        if l < 0.5 then
        set t2 = l * (1.0 + s);
        else
        set t2 = (l + s) - (l * s);
        end if;
        set t1 = 2.0 * l - t2;

        set rt3 = h + 1.0/3.0;
        set gt3 = h;
        set bt3 = h - 1.0/3.0;

        if(rt3 < 0) then set rt3 = rt3 + 1.0; end if;
        if(rt3 > 1) then set rt3 = rt3 - 1.0; end if;
        if(gt3 < 0) then set gt3 = gt3 + 1.0; end if;
        if(gt3 > 1) then set gt3 = gt3 - 1.0; end if;
        if(bt3 < 0) then set bt3 = bt3 + 1.0; end if;
        if(bt3 > 1) then set bt3 = bt3 - 1.0; end if;


        if(6.0 * rt3 < 1) then set r = t1 + (t2 - t1) * 6.0 * rt3;
        elseif(2.0 * rt3 < 1) then set r = t2;
        elseif(3.0 * rt3 < 2) then set r = t1 + (t2 - t1) * ((2.0/3.0) - rt3) * 6.0;
        else set r = t1; end if;

        if(6.0 * gt3 < 1) then set g = t1 + (t2 - t1) * 6.0 * gt3;
        elseif(2.0 * gt3 < 1) then set g = t2;
        elseif(3.0 * gt3 < 2) then set g = t1 + (t2 - t1) * ((2.0/3.0) - gt3) * 6.0;
        else set g = t1; end if;

        if(6.0 * bt3 < 1) then set b = t1 + (t2 - t1) * 6.0 * bt3;
        elseif(2.0 * bt3 < 1) then set b = t2;
        elseif(3.0 * bt3 < 2) then set b = t1 + (t2 - t1) * ((2.0/3.0) - bt3) * 6.0;
        else set b = t1; end if;

        END IF;


        set r = round(255.0 * r);
        set g = round(255.0 * g);
        set b = round(255.0 * b);

        set html = concat('#',LPAD(CONV(r, 10, 16),2,'0'),LPAD(CONV(g, 10, 16),2,'0'),LPAD(CONV(b, 10, 16),2,'0'));

        RETURN html;
        END
        ");
    $this->execute("
        CREATE FUNCTION kria_color_distance(c long, cfrR int, cfrG int, cfrB int) RETURNS int(11)
        BEGIN
        declare h float;
        declare l float;
        declare s float;

        declare t1 float;
        declare t2 float;

        declare rt3 float;
        declare gt3 float;
        declare bt3 float;

        declare r long;
        declare g long;
        declare b long;
        declare rgb long;

        declare distance long;

        set h = (((ifnull(c,0) & 0xFF000000) >> 24)+0.0)/255.0;
        set l = (((ifnull(c,0) & 0xFF0000) >> 16)+0.0)/255.0;
        set s = (((ifnull(c,0) & 0xFF00) >> 8)+0.0)/255.0;

        IF s = 0.0 THEN
        set r = l;
        set g = l;
        set b = l;
        ELSE
        if l < 0.5 then
        set t2 = l * (1.0 + s);
        else
        set t2 = (l + s) - (l * s);
        end if;
        set t1 = 2.0 * l - t2;

        set rt3 = h + 1.0/3.0;
        set gt3 = h;
        set bt3 = h - 1.0/3.0;

        if(rt3 < 0) then set rt3 = rt3 + 1.0; end if;
        if(rt3 > 1) then set rt3 = rt3 - 1.0; end if;
        if(gt3 < 0) then set gt3 = gt3 + 1.0; end if;
        if(gt3 > 1) then set gt3 = gt3 - 1.0; end if;
        if(bt3 < 0) then set bt3 = bt3 + 1.0; end if;
        if(bt3 > 1) then set bt3 = bt3 - 1.0; end if;


        if(6.0 * rt3 < 1) then set r = t1 + (t2 - t1) * 6.0 * rt3;
        elseif(2.0 * rt3 < 1) then set r = t2;
        elseif(3.0 * rt3 < 2) then set r = t1 + (t2 - t1) * ((2.0/3.0) - rt3) * 6.0;
        else set r = t1; end if;

        if(6.0 * gt3 < 1) then set g = t1 + (t2 - t1) * 6.0 * gt3;
        elseif(2.0 * gt3 < 1) then set g = t2;
        elseif(3.0 * gt3 < 2) then set g = t1 + (t2 - t1) * ((2.0/3.0) - gt3) * 6.0;
        else set g = t1; end if;

        if(6.0 * bt3 < 1) then set b = t1 + (t2 - t1) * 6.0 * bt3;
        elseif(2.0 * bt3 < 1) then set b = t2;
        elseif(3.0 * bt3 < 2) then set b = t1 + (t2 - t1) * ((2.0/3.0) - bt3) * 6.0;
        else set b = t1; end if;

        END IF;


        set r = round(255.0 * r);
        set g = round(255.0 * g);
        set b = round(255.0 * b);

        set distance = sqrt(pow(cfrR-r,2)+pow(cfrG-g,2)+pow(cfrB-b,2));

        RETURN distance;
        END
        ");
    $this->execute("
        CREATE FUNCTION kria_color_to_rgb(c long) RETURNS int(11)
        BEGIN
        declare h float;
        declare l float;
        declare s float;

        declare t1 float;
        declare t2 float;

        declare rt3 float;
        declare gt3 float;
        declare bt3 float;

        declare r long;
        declare g long;
        declare b long;
        declare rgb long;

        set h = (((ifnull(c,0) & 0xFF000000) >> 24)+0.0)/255.0;
        set l = (((ifnull(c,0) & 0xFF0000) >> 16)+0.0)/255.0;
        set s = (((ifnull(c,0) & 0xFF00) >> 8)+0.0)/255.0;

        IF s = 0.0 THEN
        set r = l;
        set g = l;
        set b = l;
        ELSE
        if l < 0.5 then
        set t2 = l * (1.0 + s);
        else
        set t2 = (l + s) - (l * s);
        end if;
        set t1 = 2.0 * l - t2;

        set rt3 = h + 1.0/3.0;
        set gt3 = h;
        set bt3 = h - 1.0/3.0;

        if(rt3 < 0) then set rt3 = rt3 + 1.0; end if;
        if(rt3 > 1) then set rt3 = rt3 - 1.0; end if;
        if(gt3 < 0) then set gt3 = gt3 + 1.0; end if;
        if(gt3 > 1) then set gt3 = gt3 - 1.0; end if;
        if(bt3 < 0) then set bt3 = bt3 + 1.0; end if;
        if(bt3 > 1) then set bt3 = bt3 - 1.0; end if;


        if(6.0 * rt3 < 1) then set r = t1 + (t2 - t1) * 6.0 * rt3;
        elseif(2.0 * rt3 < 1) then set r = t2;
        elseif(3.0 * rt3 < 2) then set r = t1 + (t2 - t1) * ((2.0/3.0) - rt3) * 6.0;
        else set r = t1; end if;

        if(6.0 * gt3 < 1) then set g = t1 + (t2 - t1) * 6.0 * gt3;
        elseif(2.0 * gt3 < 1) then set g = t2;
        elseif(3.0 * gt3 < 2) then set g = t1 + (t2 - t1) * ((2.0/3.0) - gt3) * 6.0;
        else set g = t1; end if;

        if(6.0 * bt3 < 1) then set b = t1 + (t2 - t1) * 6.0 * bt3;
        elseif(2.0 * bt3 < 1) then set b = t2;
        elseif(3.0 * bt3 < 2) then set b = t1 + (t2 - t1) * ((2.0/3.0) - bt3) * 6.0;
        else set b = t1; end if;

        END IF;


        set r = round(255.0 * r);
        set g = round(255.0 * g);
        set b = round(255.0 * b);

        set rgb = b + (g << 0x8) + (r << 0x10);

        RETURN rgb;
        END
        ");
    $this->execute("
        CREATE FUNCTION kria_color_get_weight(c LONG) RETURNS int(11)
        BEGIN
        DECLARE w INTEGER;
        IF c IS NULL THEN
        SET w = 0;
        ELSE
        SET w = (c & 0xFF);
        END IF;
        RETURN w;
        END
        ");
    $this->execute("
        CREATE FUNCTION kria_color_distance_perc(c long, cfrR int, cfrG int, cfrB int) RETURNS int(11)
        BEGIN
        declare h float;
        declare l float;
        declare s float;

        declare t1 float;
        declare t2 float;

        declare rt3 float;
        declare gt3 float;
        declare bt3 float;

        declare r long;
        declare g long;
        declare b long;
        declare rgb long;

        declare distance long;

        set h = (((ifnull(c,0) & 0xFF000000) >> 24)+0.0)/255.0;
        set l = (((ifnull(c,0) & 0xFF0000) >> 16)+0.0)/255.0;
        set s = (((ifnull(c,0) & 0xFF00) >> 8)+0.0)/255.0;

        IF s = 0.0 THEN
        set r = l;
        set g = l;
        set b = l;
        ELSE
        if l < 0.5 then
        set t2 = l * (1.0 + s);
        else
        set t2 = (l + s) - (l * s);
        end if;
        set t1 = 2.0 * l - t2;

        set rt3 = h + 1.0/3.0;
        set gt3 = h;
        set bt3 = h - 1.0/3.0;

        if(rt3 < 0) then set rt3 = rt3 + 1.0; end if;
        if(rt3 > 1) then set rt3 = rt3 - 1.0; end if;
        if(gt3 < 0) then set gt3 = gt3 + 1.0; end if;
        if(gt3 > 1) then set gt3 = gt3 - 1.0; end if;
        if(bt3 < 0) then set bt3 = bt3 + 1.0; end if;
        if(bt3 > 1) then set bt3 = bt3 - 1.0; end if;


        if(6.0 * rt3 < 1) then set r = t1 + (t2 - t1) * 6.0 * rt3;
        elseif(2.0 * rt3 < 1) then set r = t2;
        elseif(3.0 * rt3 < 2) then set r = t1 + (t2 - t1) * ((2.0/3.0) - rt3) * 6.0;
        else set r = t1; end if;

        if(6.0 * gt3 < 1) then set g = t1 + (t2 - t1) * 6.0 * gt3;
        elseif(2.0 * gt3 < 1) then set g = t2;
        elseif(3.0 * gt3 < 2) then set g = t1 + (t2 - t1) * ((2.0/3.0) - gt3) * 6.0;
        else set g = t1; end if;

        if(6.0 * bt3 < 1) then set b = t1 + (t2 - t1) * 6.0 * bt3;
        elseif(2.0 * bt3 < 1) then set b = t2;
        elseif(3.0 * bt3 < 2) then set b = t1 + (t2 - t1) * ((2.0/3.0) - bt3) * 6.0;
        else set b = t1; end if;

        END IF;


        set r = round(255.0 * r);
        set g = round(255.0 * g);
        set b = round(255.0 * b);

        set distance = (sqrt(pow(cfrR-r,2)+pow(cfrG-g,2)+pow(cfrB-b,2)) / 441.67295593006372) * 100.0;

        RETURN distance;
        END
        ");
  }

  public function down()
  {
    $this->execute("DROP function IF EXISTS kria_color_distance_perc;");
    $this->execute("DROP function IF EXISTS kria_color_get_weight;");
    $this->execute("DROP function IF EXISTS kria_color_to_rgb;");
    $this->execute("DROP function IF EXISTS kria_color_distance;");
    $this->execute("DROP function IF EXISTS kria_color_to_html;");
    return true;
  }
}