/*
    Mermaid + DuckDB for generating customer hierarchy diagrams

    DuckDB version: 0.10.2

    Bill Wallis, 2024-05-09
*/


select version();


create or replace table customers (
    customer_id varchar not null primary key,
    customer_type varchar not null check (
        customer_type in ('Business', 'Individual', 'Lending Group')
    ),
);

create or replace table customer_relationships (
    parent_customer_id varchar not null,
    child_customer_id varchar not null,
    /* "`child_customer_id` is a `relationship_type` of `parent_customer_id`" */
    relationship_type varchar not null check (
        relationship_type in ('Subsidiary', 'Director')
    ),

    primary key (parent_customer_id, child_customer_id),
    foreign key (parent_customer_id) references customers(customer_id),
    foreign key (child_customer_id) references customers(customer_id),
);

create or replace table loans (
    loan_id varchar not null primary key,
    loan_value real not null check (loan_value > 0),
    customer_id varchar not null references customers(customer_id),
);


insert into customers (customer_id, customer_type)
values
    ('BUS364265', 'Business'),
    ('BUS520654', 'Business'),
    ('BUS156548', 'Business'),
    ('BUS216549', 'Business'),
    ('BUS156044', 'Business'),
    ('IND154203', 'Individual'),
    ('IND549804', 'Individual'),
    ('IND996597', 'Individual'),
    ('IND450298', 'Individual'),
;

insert into customer_relationships (parent_customer_id, child_customer_id, relationship_type)
values
    ('BUS520654', 'BUS156548', 'Subsidiary'),
    ('BUS520654', 'BUS216549', 'Subsidiary'),
    ('BUS156548', 'IND154203', 'Director'),
    ('BUS156548', 'IND549804', 'Director'),
    ('BUS216549', 'IND549804', 'Director'),
    ('BUS364265', 'IND996597', 'Director'),
    ('BUS364265', 'IND450298', 'Director'),
    ('BUS156044', 'IND450298', 'Director'),
;

insert into loans (loan_id, loan_value, customer_id)
values
    ('LOA156489', 91000, 'BUS156548'),
;


/* Customer Relationships with Loans */
copy (
        select 'flowchart TD'
    union all
        select format(
            '    {:s} -- {:s} ---> {:s}',
            parent_customer_id, relationship_type, child_customer_id
        )
        from customer_relationships
    union all
        select format(
            '    {:s} --- {:s}{{{{"{:s}\n({:t,})"}}}}',
            customer_id, loan_id, loan_id, loan_value::int
        )
        from loans
)
to 'relationships-with-loans.mermaid' (
    header false, quote '', delimiter E'\n'
);