CREATE OR REPLACE FUNCTION unaccent_string(TEXT) RETURNS TEXT AS $$
  SELECT translate(
      $1,
      'âãäåÁÂÃÄÅèééêëÈÉÉÊËìíîïìÌÍÎÏÌóôõöÒÓÔÕÖùúûüÙÚÛÜ',
      'aaaaAAAAAeeeeeEEEEEiiiiiIIIIIooooOOOOOuuuuUUUU'
  );
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION nb_api(TEXT, TEXT) RETURNS INTEGER AS $$
/* Returns number of api for one week and year */
DECLARE
  w ALIAS FOR $1;
  y ALIAS FOR $2;
  c INTEGER;
BEGIN
  SELECT COUNT(*) INTO c
  FROM vapi
  WHERE week=w AND year=y;
  RETURN c;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION nb_sub(TEXT, TEXT, TEXT) RETURNS INTEGER AS $$
/* Returns number of subsciption for one student for one week and year */
DECLARE
  student ALIAS FOR $1;
  w ALIAS FOR $2;
  y ALIAS FOR $3;
  c INTEGER;
BEGIN
  SELECT COUNT(*) INTO c
  FROM vsubscription
  WHERE utclogin=student AND week=w AND year=y;
  RETURN c;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION indicator(TEXT, TEXT, TEXT) RETURNS INTEGER AS $$
/* Returns indicator one student for one week and year */
DECLARE
  student ALIAS FOR $1;
  w ALIAS FOR $2;
  y ALIAS FOR $3;
  nb_sub FLOAT;
  nb_api FLOAT;
BEGIN
  nb_sub = nb_sub(student,w,y);
  nb_api = nb_api(w,y);
  IF nb_sub = 0 THEN
    RETURN -1;
  ELSIF nb_sub = 1 THEN
    RETURN 100;
  ELSE
    RETURN ROUND(100 * (1 - (nb_sub / nb_api)));
  END IF;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION is_available(TEXT, TEXT, TEXT) RETURNS BOOLEAN AS $$
/* Returns True if student has no api validated for same week and year */
DECLARE
  student ALIAS FOR $1;
  w ALIAS FOR $2;
  y ALIAS FOR $3;
  c INTEGER;
BEGIN
  SELECT COUNT(*) INTO c
  FROM vsubscription
  WHERE utclogin=student AND week=w AND year=y AND validation;
  IF c = 0 THEN
    RETURN 'TRUE';
  ELSE
    RETURN 'FALSE';
  END IF;
END;
$$ LANGUAGE PLPGSQL;

/** TODO **/
CREATE OR REPLACE FUNCTION semester(DATE) RETURNS CHAR(1) AS $$
BEGIN
  RETURN 'E';
END;
$$ LANGUAGE PLPGSQL;