DEFINE TABLE OVERWRITE bank SCHEMAFULL;
DEFINE FIELD OVERWRITE site_id ON bank TYPE option<record<site>>;
DEFINE FIELD OVERWRITE name ON bank TYPE string;
DEFINE FIELD OVERWRITE sort_code ON bank TYPE int ASSERT $value > 0;
DEFINE FIELD OVERWRITE status ON bank TYPE int ASSERT $value IN [$BANK_CLOSED, $BANK_FROZEN, $BANK_ACTIVE] DEFAULT $BANK_ACTIVE;

DEFINE PARAM OVERWRITE $BANK_CLOSED VALUE 1;
DEFINE PARAM OVERWRITE $BANK_FROZEN VALUE 2;
DEFINE PARAM OVERWRITE $BANK_ACTIVE VALUE 4;

DEFINE FIELD OVERWRITE created_at ON TABLE bank TYPE datetime
  DEFAULT time::now() VALUE $before OR time::now();

DEFINE FIELD OVERWRITE updated_at ON TABLE bank TYPE datetime
  DEFAULT time::now() VALUE time::now();

DEFINE INDEX OVERWRITE bank_sort_code_idx ON bank FIELDS sort_code;



DEFINE TABLE OVERWRITE bank_account SCHEMAFULL;
DEFINE FIELD OVERWRITE user_id ON bank_account TYPE record<user>;
DEFINE FIELD OVERWRITE bank_id ON bank_account TYPE record<bank>;
DEFINE FIELD OVERWRITE description ON bank_account TYPE string DEFAULT '';
DEFINE FIELD OVERWRITE account_type ON bank_account TYPE int ASSERT $value IN [$BANK_ACCOUNT_CURRENT, $BANK_ACCOUNT_SAVINGS];
DEFINE FIELD OVERWRITE account_number ON bank_account TYPE int;
DEFINE FIELD OVERWRITE balance ON bank_account TYPE float DEFAULT 0.0;
DEFINE FIELD OVERWRITE overdraft ON bank_account TYPE float DEFAULT 0.0;
DEFINE FIELD OVERWRITE status ON bank_account TYPE int ASSERT $value IN [$BANK_ACCOUNT_CLOSED, $BANK_ACCOUNT_FROZEN, $BANK_ACCOUNT_ACTIVE] DEFAULT $BANK_ACCOUNT_ACTIVE;

DEFINE INDEX OVERWRITE user_account ON bank_account FIELDS user_id, account_type;

DEFINE TABLE OVERWRITE user_account TYPE RELATION IN user OUT bank_account SCHEMAFULL;
DEFINE INDEX OVERWRITE user_account_idx ON user_account FIELDS in, out UNIQUE;


DEFINE FIELD OVERWRITE created_at ON TABLE bank_account TYPE datetime
  DEFAULT time::now() VALUE $before OR time::now();

DEFINE FIELD OVERWRITE updated_at ON TABLE bank_account TYPE datetime
  DEFAULT time::now() VALUE time::now();

DEFINE PARAM OVERWRITE $BANK_ACCOUNT_CURRENT VALUE 1;
DEFINE PARAM OVERWRITE $BANK_ACCOUNT_SAVINGS VALUE 2;

DEFINE PARAM OVERWRITE $BANK_ACCOUNT_CLOSED VALUE 1;
DEFINE PARAM OVERWRITE $BANK_ACCOUNT_FROZEN VALUE 2;
DEFINE PARAM OVERWRITE $BANK_ACCOUNT_ACTIVE VALUE 4;

DEFINE FUNCTION OVERWRITE fn::bank::by_sort_code($sortCode: string) -> option<object>  {
    RETURN (SELECT * FROM bank WHERE sort_code = fn::bank::number::from_formatted($sortCode))[0];
};

DEFINE FUNCTION OVERWRITE fn::bank::id::by_sort_code($sortCode: string | int) -> option<record<bank>>  {
    LET $sort_code = IF type::is_int($sortCode) { $sortCode } ELSE { fn::bank::number::from_formatted($sortCode) };
    RETURN (SELECT id FROM bank WHERE sort_code = $sort_code)[0].id;
};

DEFINE FUNCTION OVERWRITE fn::bank::get::sort_code($bankId: record<bank>) -> option<string> {
    RETURN (SELECT fn::bank::number::formatted(sort_code) as code FROM $bankId)[0].code;
};

DEFINE FUNCTION OVERWRITE fn::bank::get::sort_code_number($bankId: record<bank>) -> option<int> {
    RETURN (SELECT sort_code FROM $bankId)[0].sort_code;
};

DEFINE FUNCTION OVERWRITE fn::bank::get::account_string($bankId: record<bank> | int, $accountId: record<bank_account> | int) -> option<string> {
    LET $sort_code = IF type::is_int($bankId) { fn::bank::number::formatted($bankId) } ELSE { (SELECT fn::bank::number::formatted(sort_code) as code FROM $bankId)[0].code };
    IF ($sort_code IS NONE) {
        RETURN NONE;
    };
    LET $account = IF type::is_int($accountId) { fn::bank_account::number::formatted($accountId) } ELSE { (SELECT fn::bank_account::number::formatted(account_number) as acc FROM $accountId)[0].acc };
    IF ($account IS NONE) {
        RETURN NONE;
    };
    RETURN string::concat($sort_code , ' ' , $account);
};

DEFINE FUNCTION OVERWRITE fn::bank::create($site: option<record<site>>, $name: option<string>, $status: option<int>) -> {
    success: bool,
    errors: option<array<string>>,
    bank: option<object>
} {
    LET $result = (INSERT INTO bank (site_id, name, sort_code, status) VALUES ($site, $name ?? 'The Bank', fn::counters::inc('banks', 1), $status ?? $BANK_ACTIVE))[0];
    IF ($result == NONE) {
        RETURN { success: false, errors: ['Unable to create bank']};
    } ELSE {
        RETURN { success: true, bank: $result };
    }
};

DEFINE FUNCTION OVERWRITE fn::bank::exists($name: string, $site: option<record<site>>) -> bool {
    IF ($site == NONE) {
        RETURN NONE != (SELECT id FROM bank WHERE string::lowercase(name) == string::lowercase(string::trim($name)))[0];
    } ELSE {
        RETURN NONE != (SELECT id FROM bank WHERE site_id = $site AND string::lowercase(name) == string::lowercase(string::trim($name)))[0];    
    };
};

DEFINE FUNCTION OVERWRITE fn::bank::by_sort_code($sortCode: string) {
    RETURN (SELECT * FROM bank WHERE sort_code = fn::bank::number::from_formatted($sortCode))[0];
};

DEFINE FUNCTION OVERWRITE fn::bank::number::formatted($num: int) -> string {
    LET $strNum = string::concat('000000', <string>$num);
    LET $paddedNum = string::slice($strNum, -6);
    RETURN string::slice($paddedNum, 0, 2) + '-' + string::slice($paddedNum, 2, 2) + '-' + string::slice($paddedNum, 4, 2);
};

DEFINE FUNCTION OVERWRITE fn::bank::number::from_formatted($num: string) -> int {
    RETURN <int>string::replace($num, '-', '');
};

DEFINE FUNCTION OVERWRITE fn::bank_account::number::formatted($num: int) -> string {
    LET $strNum = string::concat('00000000', <string>$num);
    RETURN string::slice($strNum, -8);
};

DEFINE FUNCTION OVERWRITE fn::bank_account::number::from_string($num: string) -> int {
    RETURN <int>$num;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::number::split($num: string) -> { sort_code: int, account_number: int } {
    IF (!string::matches($num, '^[0-9]{2}-[0-9]{2}-[0-9]{2} [0-9]{8}$')) {
        THROW 'Invalid Bank Account Number';
    };
    LET $sort_code = <int>string::replace(string::slice($num, 0, 8), '-', '');
    LET $account_number = <int>string::slice($num, -8);
    RETURN {
        sort_code: $sort_code,
        account_number: $account_number
    };
};

DEFINE FUNCTION OVERWRITE fn::bank_account::status::set($user: record<user>, $type: int, $status: int) {
    UPDATE bank_account SET status = $status WHERE user_id = $user AND account_type = $type;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::close::all($user: record<user>) {
    UPDATE bank_account SET status = $BANK_ACCOUNT_CLOSED WHERE user_id = $user;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::freeze::all($user: record<user>) {
    UPDATE bank_account SET status = $BANK_ACCOUNT_FROZEN WHERE user_id = $user;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::activate::all($user: record<user>) {
    UPDATE bank_account SET status = $BANK_ACCOUNT_ACTIVE WHERE user_id = $user;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::close($account: string) {
    LET $details = fn::bank_account::number::split($account);
    LET $bankId = fn::bank::id::by_sort_code($details.sort_code);
    RETURN UPDATE bank_account SET status = $BANK_ACCOUNT_CLOSED
    WHERE account_number = $details.account_number
    AND bank_id = $bankId;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::freeze($account: string) {
    LET $details = fn::bank_account::number::split($account);
    LET $bankId = fn::bank::id::by_sort_code($details.sort_code);
    RETURN UPDATE bank_account SET status = $BANK_ACCOUNT_FROZEN
    WHERE account_number = $details.account_number
    AND bank_id = $bankId;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::activate($account: string) {
    LET $details = fn::bank_account::number::split($account);
    LET $bankId = fn::bank::id::by_sort_code($details.sort_code);
    RETURN UPDATE bank_account SET status = $BANK_ACCOUNT_ACTIVE
    WHERE account_number = $details.account_number
    AND bank_id = $bankId;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::details($account: string) {
    LET $details = fn::bank_account::number::split($account);
    LET $bankId = fn::bank::id::by_sort_code($details.sort_code);
    LET $bank_account = (
        SELECT * FROM bank_account
        WHERE account_number = $details.account_number
        AND bank_id = $bankId
    )[0];
    RETURN $bank_account;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::open($bank: option<record<bank> | string>, $user: record<user>, $type: int, $balance: option<float>, $overdraft: option<float>, $status: option<int>, $description: option<string>) {
    LET $bank_id = IF type::is_string($bank) { fn::bank::by_sort_code($bank).id } ELSE { $bank };
    LET $bank_account_type = IF $type = 1 { 'Current Account' } ELSE IF $type = 2 { 'Savings Account' } ELSE { 'Bank Account' };
    LET $acc_no = fn::counters::inc(<string>$bank_id);
    BEGIN;
    LET $result = (INSERT INTO bank_account (bank_id, user_id, account_number, description, account_type, balance, status, overdraft) VALUES ($bank_id, $user, $acc_no,
    $description ?? $bank_account_type, $type, <float>($balance ?? 0), $status ?? $BANK_ACCOUNT_ACTIVE, <float>($overdraft ?? 0)))[0];
    IF ($result == NONE) {
        CANCEL;
        RETURN { success: false, errors: ['Unable to create bank account']};
    } ELSE {
        fn::bank_account::link($user, $result.id);
        COMMIT;
        RETURN { success: true, bank_account: $result, bank_id: $bank_id
      --  , account: fn::bank::get::account_string(fn::bank::get::sort_code_number($bank_id), $result.bank_account.account_number)
         };
    }
};

DEFINE FUNCTION OVERWRITE fn::bank_account::funds($account: string) -> float {
    LET $details = fn::bank_account::number::split($account);
    RETURN SELECT balance FROM bank_account
    WHERE account_number = $details.account_number
    AND bank_id.sort_code = $details.sort_code;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::float::add($first: float, $second: float) -> float {
    RETURN FUNCTION ($first, $second) {
        return (arguments[0] + arguments[1]);
    };
};

DEFINE FUNCTION OVERWRITE fn::bank_account::float::subtract($first: float, $second: float) -> float {
    RETURN FUNCTION ($first, $second) {
        return (arguments[0] - arguments[1]);
    };
};

DEFINE FUNCTION OVERWRITE fn::bank_account::funds::transfer($amount: float, $from_account: string, $to_account: string) {
    LET $from = fn::bank_account::number::split($from_account);
    LET $to = fn::bank_account::number::split($to_account);

    IF $from.account_number = $to.account_number AND $from.sort_code = $to.sort_code {
        RETURN {
            success: false,
            message: 'Cannot transfer to the same bank_account',
            error: 1000
        };
    };

    LET $from_account = (SELECT * FROM bank_account WHERE account_number = $from.account_number AND bank_id.sort_code = $from.sort_code)[0];
    LET $to_account = (SELECT * FROM bank_account WHERE account_number = $to.account_number AND bank_id.sort_code = $to.sort_code)[0];

    IF ($from_account.status != $BANK_ACCOUNT_ACTIVE) {
        IF ($from_account.status == $BANK_ACCOUNT_CLOSED) {
            RETURN {
                success: false,
                message: 'Account is closed',
                error: 1001
            };
        };
        IF ($from_account.status == $BANK_ACCOUNT_FROZEN) {
            RETURN {
                success: false,
                message: 'Account is frozen',
                error: 1002
            };
        }
    };


    IF ($to_account.status != $BANK_ACCOUNT_ACTIVE) {
        IF ($to_account.status == $BANK_ACCOUNT_CLOSED) {
            RETURN {
                success: false,
                message: 'Recipient bank_account is closed',
                error: 1003
            };
        };
        IF ($to_account.status == $BANK_ACCOUNT_FROZEN) {
            RETURN {
                success: false,
                message: 'Recipient bank_account is frozen',
                error: 1004
            };
        }
    };

    IF (fn::bank_account::float::add($from_account.balance , $from_account.overdraft) >= $amount) {
        BEGIN;
        UPDATE bank_account SET balance = fn::bank_account::float::subtract(balance, $amount) WHERE id = $from_account.id;
        UPDATE bank_account SET balance = fn::bank_account::float::add(balance, $amount) WHERE id = $to_account.id;
        COMMIT;
        RETURN {
            success: true,
            message:'success'
         };
    } ELSE {
        RETURN {
            success: false,
            message: 'Insufficient funds',
            error: 1005
        };
    };
};

DEFINE FUNCTION OVERWRITE fn::bank_account::savings::apply::interest($rate: float) {
    LET $chosen_rate = IF $rate = NONE { 0 } ELSE { $rate };
    LET $savings_accounts = SELECT * FROM bank_account
    WHERE account_type = $BANK_ACCOUNT_SAVINGS;
    FOR $bank_account IN $savings_accounts {
        LET $new_balance = $bank_account.balance * (1 + $chosen_rate);
        UPDATE $bank_account SET balance = $new_balance;
    };
};

DEFINE FUNCTION OVERWRITE fn::bank_account::apply::charges($rate: float, $fee: float, $type: int) {
    LET $chosen_rate = IF $rate = NONE { 0 } ELSE { $rate };
    LET $chosen_fee = IF $fee = NONE { 0 } ELSE { $fee };
    LET $chosen_type = IF $type = NONE { $BANK_ACCOUNT_CURRENT } ELSE { $BANK_ACCOUNT_SAVINGS };
    LET $bank_accounts = SELECT * FROM bank_account
    WHERE account_type = $chosen_type;
    FOR $bank_account IN $bank_accounts {
        LET $new_balance = ($bank_account.balance * (1 + $chosen_rate)) - $chosen_fee;
        UPDATE $bank_account SET balance = $new_balance;
    };
};

-- Graph relation functions for accounts
DEFINE FUNCTION OVERWRITE fn::bank_account::link($user: record<user>, $bank_account: record<bank_account>) {
    LET $result = (RELATE $user->user_account->$bank_account)[0];
    RETURN $result;
};

DEFINE FUNCTION OVERWRITE fn::bank_account::get_by_user($user: record<user>) -> array {
    LET $bank_accounts = (
        SELECT ->user_account->bank_account.* as accounts
        FROM $user
    )[0].accounts;
    RETURN IF $bank_accounts == NONE { [] } ELSE { $bank_accounts };
};

DEFINE FUNCTION OVERWRITE fn::bank_account::get_user($bank_account: record<bank_account>) -> option<object> {
    LET $results = (
        SELECT <-user_account<-user.* as user
        FROM $bank_account
    );
    LET $user = $results[0].user;
    RETURN IF array::len($user) == 0 { NONE } ELSE { $user[0] };
};

DEFINE FUNCTION OVERWRITE fn::bank_account::unlink($user: record<user>, $bank_account: record<bank_account>) {
    LET $before = SELECT * FROM user_account WHERE in = $user AND out = $bank_account;
    IF array::len($before) > 0 {
        DELETE user_account WHERE in = $user AND out = $bank_account;
        LET $after = SELECT * FROM user_account WHERE in = $user AND out = $bank_account;
        RETURN array::len($after) == 0;
    };
    RETURN true;
};