"""Course model: a reduced copy of the invc package of examples/test_invoice_pg.

Declarations that the current release refuses on writes (encryption, numbering,
validate_*, dynamic_fields, pyColumn, #PREF) are left out. Lessons that need
other options add them to their own recipe.
"""
from genro_bag.resolvers import EnvResolver
from asqueel import SqlDatabaseConfig


def region(pkg):
    tbl = pkg.table('region', pkey='code', name_long='Region')
    tbl.options().sys_fields(id=False)
    tbl.column('code', size=':5')
    tbl.column('name', size=':50')


def state(pkg):
    tbl = pkg.table('state', pkey='code', name_long='State', caption_field='code')
    tbl.options().sys_fields(id=False)
    tbl.column('code', size=':5')
    tbl.column('name', size=':100')
    tbl.column('region_code', size=':5').relation(
        'region.code', relation_name='states', onDelete='raise', foreign_key=True)


def customer_type(pkg):
    tbl = pkg.table('customer_type', pkey='code', name_long='Customer type')
    tbl.options().sys_fields(id=False)
    tbl.column('code', size=':5')
    tbl.column('description')


def vat_type(pkg):
    tbl = pkg.table('vat_type', pkey='code', name_long='VAT type')
    tbl.options().sys_fields(id=False)
    tbl.column('code', size=':5')
    tbl.column('description')
    tbl.column('vat_rate', dtype='perc')


def customer(pkg):
    tbl = pkg.table('customer', pkey='id', name_long='Customer', caption_field='account_name')
    tbl.options().sys_fields(draft=True)
    tbl.column('account_name')
    tbl.column('street_address')
    tbl.column('suburb')
    tbl.column('email')
    tbl.column('state', size=':5').relation(
        'state.code', relation_name='clients', onDelete='raise', foreign_key=True)
    tbl.column('customer_type_code', size=':5').relation(
        'customer_type.code', relation_name='customers', onDelete='raise', foreign_key=True)
    tbl.aliasColumn('state_name', relation_path='@state.name')
    tbl.aliasColumn('region_name', relation_path='@state.@region_code.name')
    tbl.formulaColumn('full_address', sql_formula="$street_address || ', ' || $suburb", dtype='T')
    tbl.formulaColumn('n_invoices', select=dict(
        table='invc.invoice', columns='COUNT(*)', where='$customer_id=#THIS.id'), dtype='L')
    tbl.formulaColumn('has_invoices', exists=dict(
        table='invc.invoice', where='$customer_id=#THIS.id'), dtype='B')


def product(pkg):
    tbl = pkg.table('product', pkey='id', name_long='Product', caption_field='description')
    tbl.options().sys_fields()
    tbl.column('code', size=':10')
    tbl.column('description', size=':80')
    tbl.column('unit_price', dtype='money')
    tbl.column('vat_type_code', size=':5').relation(
        'vat_type.code', relation_name='products', onDelete='raise', foreign_key=True)
    tbl.formulaColumn('code_and_desc', sql_formula="$code || ' - ' || $description", dtype='T')


def invoice(pkg):
    tbl = pkg.table('invoice', pkey='id', name_long='Invoice', caption_field='inv_number')
    tbl.options().sys_fields()
    tbl.column('inv_number', size=':10', unique=True)
    tbl.column('customer_id', size='22').relation(
        'customer.id', relation_name='invoices', onDelete='raise', foreign_key=True)
    tbl.column('date', dtype='D')
    tbl.column('total', dtype='money')
    tbl.column('vat_total', dtype='money')
    tbl.column('gross_total', dtype='money')
    tbl.aliasColumn('customer_name', relation_path='@customer_id.account_name')
    tbl.formulaColumn('value_category', sql_formula=(
        "CASE WHEN $total > 1000 THEN 'High' WHEN $total > 100 THEN 'Medium' ELSE 'Low' END"), dtype='A')
    tbl.formulaColumn('is_recent', sql_formula="$date >= :env_workdate - INTERVAL '90 days'", dtype='B')


def invoice_row(pkg):
    tbl = pkg.table('invoice_row', pkey='id', name_long='Invoice row')
    tbl.options().sys_fields()
    tbl.column('invoice_id', size='22').relation(
        'invoice.id', relation_name='rows', onDelete='cascade', foreign_key=True)
    tbl.column('product_id', size='22').relation(
        'product.id', relation_name='invoice_rows', onDelete='raise', foreign_key=True)
    tbl.column('quantity', dtype='I')
    tbl.column('unit_price', dtype='money')
    tbl.column('vat_rate', dtype='perc')
    tbl.aliasColumn('product_name', relation_path='@product_id.description')
    tbl.formulaColumn('line_total', sql_formula='$quantity * $unit_price', dtype='N')


def invoice_note(pkg):
    tbl = pkg.table('invoice_note', pkey='id', name_long='Invoice note')
    tbl.options().sys_fields()
    tbl.column('invoice_id', size='22').relation(
        'invoice.id', relation_name='notes', onDelete='cascade', foreign_key=True)
    tbl.column('note_type', size=':20')
    tbl.column('note_text')
    tbl.column('priority', dtype='I')


class Invoicing(SqlDatabaseConfig):
    def main(self, root):
        db = root.db('course', implementation='postgres')
        db.connection(name='main', implementation='postgres',
                      dsn=EnvResolver('ASQUEEL_DSN', default='dbname=asqueel_course'))
        invc = db.packages().package('invc', sqlschema='invc', sqlprefix=True)
        invc.options().type('money', dtype='N', size='14,2')
        invc.options().type('perc', dtype='N', size='5,2')
        for declare in (region, state, customer_type, vat_type, customer,
                        product, invoice, invoice_row, invoice_note):
            declare(invc)