#coding:utf-8

"""
ID:          issue-7587
ISSUE:       https://github.com/FirebirdSQL/firebird/issues/7587
TITLE:       CALL statement
NOTES:
    [13.01.2024] pzotov
    Checked on 6.0.0.210 (intermediate build 13.01.2024, commit 74976b6d8f0a5ce7504fc05658039d16d8a83ad9)
"""

import pytest
from firebird.qa import *

db = db_factory()

test_script = """
    set list on;

    recreate table customers(
      id int generated by default as identity primary key using index pk_customers
      ,first_name varchar(30)
      ,last_name varchar(30)
      ,dob date
    );

    set term ^;
    create or alter procedure insert_customer (
        last_name varchar(30)
        ,first_name varchar(30)
        ,dob date
    ) returns (
         id int
        ,full_name varchar(62)
        ,age_on_20240101 int
    )
    as
    begin
        insert into customers (last_name, first_name, dob)
            values (:last_name, :first_name, :dob)
            returning id, last_name || '_' || first_name, datediff(year from dob to date '01.01.2024')
            into :id, :full_name, :age_on_20240101;
    end
    ^

    create or alter procedure do_something_and_insert_customer returns (
        out_id integer,
        out_full_name varchar(62)
    )
    as
        declare last_name varchar(30);
        declare first_name varchar(30);
        declare date_of_birth date = '18.12.1943';
        declare age_years int;
    begin
        call insert_customer(
            last_name => 'richards',
            first_name => 'keith',
            dob => date_of_birth,
            id => out_id,
            age_on_20240101 => age_years,
            full_name => out_full_name);

        out_full_name = reverse(out_full_name);
        out_id = -out_id;
    end
    ^
    set term ;^
    commit;

    -- Not all output parameters are necessary
    call insert_customer('ozzy','osbourne', '03.12.1948',?) ;

    -- Ignore first and second output parameter (using NULLs) and get the third
    call insert_customer('ian','gillan', '19.08.1945', null, null, ?) ;

    call insert_customer('robert','plant', '20.08.1948', id => ?, full_name => ?) ;

    -- Ignore some of output parameters:
    call insert_customer('john','bonham', '31.05.1948', full_name => ?) ;
    call insert_customer('roger','waters', '09.09.1943', age_on_20240101 => ?) ;

    -- Pass inputs and get outputs using named arguments.
    call insert_customer(last_name => 'scott',  first_name => 'bon', dob => '09.07.1946', full_name => ?, age_on_20240101 => ?);

    call do_something_and_insert_customer(out_full_name => ?, out_id => ?) ;

"""

act = isql_act('db', test_script)

expected_stdout = """
    ID                              1
    
    AGE_ON_20240101                 79

    ID                              3
    FULL_NAME                       robert_plant
    
    FULL_NAME                       john_bonham
    
    AGE_ON_20240101                 81
    
    FULL_NAME                       scott_bon
    AGE_ON_20240101                 78

    OUT_FULL_NAME                   htiek_sdrahcir
    OUT_ID                          -7
"""

@pytest.mark.version('>=6.0')
def test_1(act: Action):
    act.expected_stdout = expected_stdout
    act.execute(combine_output = True)
    assert act.clean_stdout == act.clean_expected_stdout