--
-- Invoices Package
--
-- @author Timo Hentschel (timo@timohentschel.de)
-- @creation-date 2005-06-06
--

-- title, description
-- linked organization

create table iv_price_lists (
        list_id                 integer
                                constraint iv_price_lists_pk
                                primary key
                                constraint iv_price_lists_id_fk
                                references cr_revisions,
        currency                char(3)
                                constraint iv_prices_currency_fk
                                references currencies(codeA),
        credit_percent          numeric(12,5) default 0
                                -- %credit
);

create index iv_price_lists_currency_idx on iv_price_lists(currency);

create table iv_prices (
        price_id                integer
                                constraint iv_prices_pk
                                primary key
                                constraint iv_prices_id_fk
                                references cr_revisions,
        list_id                 integer
                                constraint iv_prices_list_fk
                                references cr_items,
        category_id             integer
                                constraint iv_prices_category_fk
                                references categories,
        amount                  numeric(12,3)
);

-- categories: type, vat_type
-- use general comments
-- title, description

create table iv_costs (
        cost_id                 integer
                                constraint iv_costs_pk
                                primary key
                                constraint iv_costs_fk
                                references cr_revisions,
        cost_nr                 varchar(400)
                                constraint iv_costs_nr_nn
                                not null,
                                -- Nr is a current number to provide a unique 
                                -- identifier of cost item for backup/restore.
        organization_id         integer
                                constraint iv_costs_organization_nn
                                not null
                                constraint iv_costs_organization_fk
                                references organizations,
                                -- who pays?
        cost_object_id          integer
                                constraint im_costs_object_fk
                                references acs_objects,
                                -- reference to object that caused this cost
        item_units              numeric(12,1),
        price_per_unit          numeric(12,3),
        currency                char(3) 
                                constraint iv_costs_currency_fk
                                references currencies(codeA),
        apply_vat_p             char(1) default 't'
                                constraint iv_costs_apply_vat_p
                                check (apply_vat_p in ('t','f')),
                                -- include in VAT calculation?
        variable_cost_p         char(1)
                                constraint iv_costs_var_ck
                                check (variable_cost_p in ('t','f'))
                                -- variable or fixed costs
				-- A variable cost is calculated by multiplying
				-- with logger units. A fixed cost a fixed single amount
);

create index iv_costs_object_idx on iv_costs(cost_object_id);
create index iv_costs_organization_idx on iv_costs(organization_id);
create index iv_costs_currency_idx on iv_costs(currency);

create sequence iv_offer_seq start with 100000;

create table iv_offers (
        offer_id                integer
                                constraint iv_offers_pk
                                primary key
                                constraint iv_offers_id_fk
                                references cr_revisions,
        offer_nr                varchar(80),
        organization_id         integer not null
                                constraint iv_offers_organization_fk
                                references organizations,
                                -- who pays?
        comment                 text,
        reservation             text,
        amount_total            numeric(12,2),
        amount_sum              numeric(12,2),
        currency                char(3)
                                constraint iv_offers_currency_fk
                                references currencies(codeA),
        finish_date             timestamptz,
        date_comment            varchar(1000),
        payment_days            integer,
        vat_percent             numeric(12,5) default 0,
                                -- %VAT
        vat                     numeric(12,2) default 0,
                                -- VAT amount
        credit_percent          numeric(12,5) default 0,
                                -- %credit
        status                  varchar(10) default 'new',
                                -- new, accepted, billed, credit
        accepted_date           timestamptz
                                -- offer accepted by customer?
);

-- offers are linked to a project via cr_items.parent_id
-- if it's a preliminary offer, it's linked to the customer project
-- if it's a final project, it's linked to the actual project

create index iv_offers_organization_idx on iv_offers(organization_id);
create index iv_offers_currency_idx on iv_offers(currency);
create index iv_offers_finish_idx on iv_offers(finish_date);
create index iv_offers_accepted_idx on iv_offers(accepted_date);

create table iv_offer_items (
        offer_item_id           integer
                                constraint iv_offer_items_pk
                                primary key
                                constraint iv_offers_items_id_fk
                                references cr_revisions,
        item_nr                 varchar(200),
        offer_id                integer not null 
                                constraint iv_offer_items_offer_fk
                                references iv_offers,
        comment                 text,
        item_units              numeric(12,1),
        price_per_unit          numeric(12,3),
        rebate                  numeric(12,2),
        file_count              integer,
        page_count              integer,
        sort_order              integer,
        vat                     numeric(12,3) default 0,
                                -- VAT amount
        parent_item_id          integer
                                constraint iv_offer_items_parent_fk
                                references iv_offer_items
                                -- Points to its parent
);

create index iv_offer_items_offer_idx on iv_offer_items(offer_id);
create index iv_offer_items_sort_idx on iv_offer_items(sort_order);
create index iv_offer_items_parent_idx on iv_offer_items(parent_item_id);

-- linked file (for the real invoice)
-- categories: payment_method
-- title, description
-- should we link organization here?

create sequence iv_invoice_seq start with 1000;

create table iv_invoices (
        invoice_id              integer
                                constraint iv_invoices_pk
                                primary key
                                constraint iv_invoices_id_fk
                                references cr_revisions,
        invoice_nr              varchar(80),
        parent_invoice_id       integer
                                constraint iv_invoices_parent_id_fk
                                references iv_invoices,
                                -- to reference a storno,
        organization_id         integer not null
                                constraint iv_invoices_organization_fk
                                references organizations,
                                -- who pays?
        contact_id              integer not null
                                constraint iv_invoices_contact_fk
                                references parties,
                                -- who receives email?
        recipient_id            integer not null
                                constraint iv_invoices_recipient_fk
                                references parties,
                                -- who receives invoice?
        total_amount            numeric(12,2),
        amount_sum              numeric(12,2),
        currency                char(3)
                                constraint iv_invoices_currency_fk
                                references currencies(codeA),
        paid_amount             numeric(12,2),
                                -- coming from table iv_payments
        paid_currency           char(3)
                                constraint iv_invoices_paid_currency_fk
                                references currencies(codeA),
        due_date                timestamptz,
        payment_days            integer,
        vat_percent             numeric(12,5) default 0,
                                -- %VAT
        vat                     numeric(12,2) default 0,
                                -- VAT amount
        status                  varchar(10) default 'new',
                                -- new, sent, cancelled, billed, paid
        cancelled_p             char(1) default 'f'
                                constraint iv_invoices_cancelled_p
                                check (cancelled_p in ('t','f')),
                                -- is this invoice already cancelled?
        pdf_status              varchar(10) default 'new',
                                -- new, created, sent
        pdf_file_id             integer
                                constraint iv_invoices_pdf_file_fk
                                references cr_items
);

create index iv_invoices_parent_idx on iv_invoices(parent_invoice_id);
create index iv_invoices_organization_idx on iv_invoices(organization_id);
create index iv_invoices_recipient_idx on iv_invoices(recipient_id);
create index iv_invoices_currency_idx on iv_invoices(currency);
create index iv_invoices_paid_currency_idx on iv_invoices(paid_currency);
create index iv_invoices_due_idx on iv_invoices(due_date);

-- linked project_item_id
-- categories: type, status
-- title, description

create table iv_invoice_items (
        iv_item_id              integer
                                constraint iv_invoice_items_pk
                                primary key
                                constraint iv_invoice_items_id_fk
                                references cr_revisions,
        item_nr                 varchar(200),
        invoice_id              integer not null 
                                constraint iv_invoice_items_invoice_fk
                                references iv_invoices,
        offer_item_id           integer
                                constraint iv_invoice_items_offer_fk
                                references iv_offer_items,
        item_units              numeric(12,1),
        price_per_unit          numeric(12,3),
        rebate                  numeric(12,2),
        amount_total            numeric(12,2),
        sort_order              integer,
        vat                     numeric(12,3) default 0,
                                -- VAT amount
        parent_item_id          integer
                                constraint iv_invoice_items_parent_fk
                                references iv_invoice_items
                                -- Points to its parent
);

create index iv_invoice_items_invoice_idx on iv_invoice_items(invoice_id);
create index iv_invoice_items_offer_idx on iv_invoice_items(offer_item_id);
create index iv_invoice_items_parent_idx on iv_invoice_items(parent_item_id);

-- categories type, status
-- with general comments
-- title, description
-- should we link organization here?

create table iv_payments (
        payment_id              integer not null 
                                constraint iv_payments_pk
                                primary key
                                constraint iv_payments_id_fk
                                references cr_revisions,
        invoice_id              integer
                                constraint iv_payments_invoice_fk
                                references iv_invoices,
                                -- what is paid?
        organization_id         integer not null
                                constraint iv_payments_organization_fk
                                references organizations,
                                -- who pays?
        received_date           timestamptz,
        amount                  numeric(12,2),
        currency                char(3) 
                                constraint iv_payments_currency_fk
                                references currencies(codeA)
);

create index iv_payments_invoice_idx on iv_payments(invoice_id);
create index iv_payments_organization_idx on iv_payments(organization_id);
create index iv_payments_currency_idx on iv_payments(currency);
create index iv_payments_date_idx on iv_payments(received_date);

create table iv_default_objects (
        package_id              integer
                                constraint iv_default_objects_pk
                                primary key
                                constraint iv_default_objects_fk
                                references apm_packages,
        list_id                 integer
                                constraint iv_default_objects_list_fk
                                references acs_objects,
        price_id                integer
                                constraint iv_default_objects_price_fk
                                references acs_objects,
        cost_id                 integer
                                constraint iv_default_objects_cost_fk
                                references acs_objects,
        offer_id                integer
                                constraint iv_default_objects_offer_fk
                                references acs_objects,
        offer_item_id           integer
                                constraint iv_default_objects_offer_item_fk
                                references acs_objects,
        offer_item_title_id     integer
                                constraint iv_default_objects_offer_title_fk
                                references acs_objects,
        invoice_id              integer
                                constraint iv_default_objects_invoice_fk
                                references acs_objects,
        invoice_item_id         integer
                                constraint iv_default_objects_invoice_item_fk
                                references acs_objects,
        payment_id              integer
                                constraint iv_default_objects_payment_fk
                                references acs_objects
);

create table iv_journals (
        file_id                 integer
                                constraint iv_journals_pk
                                primary key,
        creation_date           timestamptz
                                constraint iv_journals_date_nn
                                not null
);

create table iv_joined_invoices (
        file_id                 integer
                                constraint iv_joined_invoices_pk
                                primary key,
        creation_date           timestamptz
                                constraint iv_joined_invoices_date_nn
                                not null
);

insert into iv_joined_invoices (file_id, creation_date) values (0, now());

create table iv_journal_country_codes (
        iso_code                char(2)
                                constraint iv_journal_country_codes_pk
                                primary key
                                constraint iv_journal_country_codes_iso_code_fk
                                references countries,
        journal_code            varchar(4)
                                constraint iv_journal_country_codes_journal_code_nn
                                not null
);


begin;
    select acs_privilege__create_privilege('invoice_cancel',null,null);
    select acs_privilege__create_privilege('invoice_export',null,null);

    -- add children
    select acs_privilege__add_child('admin','invoice_cancel');
    select acs_privilege__add_child('admin','invoice_export');
end;
