# -*- coding: utf-8; -*-
################################################################################
#
#  Rattail -- Retail Software Framework
#  Copyright © 2010-2023 Lance Edgar
#
#  This file is part of Rattail.
#
#  Rattail is free software: you can redistribute it and/or modify it under the
#  terms of the GNU General Public License as published by the Free Software
#  Foundation, either version 3 of the License, or (at your option) any later
#  version.
#
#  Rattail is distributed in the hope that it will be useful, but WITHOUT ANY
#  WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
#  FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
#  details.
#
#  You should have received a copy of the GNU General Public License along with
#  Rattail.  If not, see <http://www.gnu.org/licenses/>.
#
################################################################################
"""
Data model for purchase orders
"""

import datetime

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declared_attr

from rattail.db.model import Base, uuid_column, Store, Department, Vendor, Employee, User, Product
from rattail.db.types import GPCType
from rattail.util import pretty_quantity


class PurchaseBase(object):
    """
    Base class for purchases; defines common fields.
    """

    @declared_attr
    def __table_args__(cls):
        return cls.__purchase_table_args__()

    @classmethod
    def __purchase_table_args__(cls):
        return (
            sa.ForeignKeyConstraint(['store_uuid'], ['store.uuid'], name='{}_fk_store'.format(cls.__tablename__)),
            sa.ForeignKeyConstraint(['vendor_uuid'], ['vendor.uuid'], name='{}_fk_vendor'.format(cls.__tablename__)),
            sa.ForeignKeyConstraint(['department_uuid'], ['department.uuid'], name='{}_fk_department'.format(cls.__tablename__)),
            sa.ForeignKeyConstraint(['buyer_uuid'], ['employee.uuid'],
                                    name='{}_fk_buyer'.format(cls.__tablename__)),
        )

    store_uuid = sa.Column(sa.String(length=32), nullable=False)

    @declared_attr
    def store(cls):
        return orm.relationship(
            Store,
            doc="""
            Reference to the :class:`Store` for which the purchase was made.
            """)

    vendor_uuid = sa.Column(sa.String(length=32), nullable=False)

    @declared_attr
    def vendor(cls):
        return orm.relationship(
            Vendor,
            doc="""
            Reference to the :class:`Vendor` to which the purchase was made.
            """)

    department_uuid = sa.Column(sa.String(length=32), nullable=True)

    @declared_attr
    def department(cls):
        return orm.relationship(
            Department,
            doc="""
            Reference to the primary :class:`Department` for which the purchase was made.
            """)

    buyer_uuid = sa.Column(sa.String(length=32), nullable=True)

    @declared_attr
    def buyer(cls):
        return orm.relationship(
            Employee,
            doc="""
            Reference to the :class:`Employee` who placed the order with the
            vendor, if applicable/known.
            """)

    po_number = sa.Column(sa.String(length=20), nullable=True, doc="""
    Purchase order number, e.g. for cross-reference with another system.
    """)

    po_total = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total cost according to the initial purchase order.
    """)

    date_ordered = sa.Column(sa.Date(), nullable=True, doc="""
    Date on which the purchase order was first submitted to the vendor.
    """)

    ship_method = sa.Column(sa.String(length=50), nullable=True, doc="""
    Code representing the shipping method.
    """)

    notes_to_vendor = sa.Column(sa.Text(), nullable=True, doc="""
    Any arbitrary notes to the vendor, regarding the purchase.
    """)

    date_shipped = sa.Column(sa.Date(), nullable=True, doc="""
    Date on which the order was shipped from the vendor.
    """)

    date_received = sa.Column(sa.Date(), nullable=True, doc="""
    Date on which the order was received at the store.
    """)

    invoice_number = sa.Column(sa.String(length=20), nullable=True, doc="""
    Invoice number, e.g. for cross-reference with another system.
    """)

    invoice_date = sa.Column(sa.Date(), nullable=True, doc="""
    Invoice date, if applicable.
    """)

    invoice_total = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total cost according to the invoice.
    """)

    def __str__(self):
        if self.vendor and self.date_ordered:
            return "{} ({})".format(self.vendor, self.date_ordered.strftime('%Y-%m-%d'))
        if self.vendor:
            return str(self.vendor)
        return ''


class PurchaseItemBase(object):
    """
    Base class for purchase line items.
    """

    @declared_attr
    def __table_args__(cls):
        return cls.__purchaseitem_table_args__()

    @classmethod
    def __purchaseitem_table_args__(cls):
        return (
            sa.ForeignKeyConstraint(['product_uuid'], ['product.uuid'], name='{}_fk_product'.format(cls.__tablename__)),
        )

    sequence = sa.Column(sa.Integer(), nullable=True, doc="""
    Effectively the (internal) line number for the purchase line item, using 1-based indexing.
    """)

    vendor_code = sa.Column(sa.String(length=20), nullable=True, doc="""
    Vendor item code for the product.
    """)

    product_uuid = sa.Column(sa.String(length=32), nullable=True)

    @declared_attr
    def product(cls):
        return orm.relationship(
            Product,
            doc="""
            Reference to the :class:`Product` which the line item contains / represents.
            """,
            backref=orm.backref(
                '_{}_records'.format(cls.__tablename__),
                cascade_backrefs=False,
                doc="""
                List of ``{}`` records associated with the product.
                """.format(cls.__tablename__)))

    upc = sa.Column(GPCType(), nullable=True, doc="""
    Product UPC for the line item.
    """)

    item_id = sa.Column(sa.String(length=20), nullable=True, doc="""
    Generic ID string for the item.
    """)

    brand_name = sa.Column(sa.String(length=100), nullable=True, doc="""
    Brand name for the line item.
    """)

    description = sa.Column(sa.String(length=60), nullable=False, default='', doc="""
    Product description for the line item.
    """)

    size = sa.Column(sa.String(length=255), nullable=True, doc="""
    Product size for the line item.
    """)

    department_number = sa.Column(sa.Integer(), nullable=True, doc="""
    Number of the department to which the product belongs.
    """)

    department_name = sa.Column(sa.String(length=30), nullable=True, doc="""
    Name of the department to which the product belongs.
    """)

    case_quantity = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Number of units in a single case of product.
    """)

    cases_ordered = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of cases of product which were initially ordered.
    """)

    units_ordered = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of units of product which were initially ordered.
    """)

    po_line_number = sa.Column(sa.Integer(), nullable=True, doc="""
    Line number from the PO if known, for cross-reference.
    """)

    catalog_unit_cost = sa.Column(sa.Numeric(precision=10, scale=5), nullable=True, doc="""
    This corresponds to the ``ProductCost.unit_cost`` (aka. "catalog" or
    wholesale cost) amount for the product/vendor, if applicable.
    """)

    po_unit_cost = sa.Column(sa.Numeric(precision=10, scale=5), nullable=True, doc="""
    Expected cost per single unit of product, as of initial order placement.
    """)

    po_case_size = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Number of units in a single case of product, according to PO.
    """)

    po_total = sa.Column(sa.Numeric(precision=7, scale=2), nullable=True, doc="""
    Total cost for the line item, according to the initial purchase order.
    """)

    cases_shipped = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of cases of product which were supposedly shipped by/from the vendor.
    """)

    units_shipped = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of units of product which were supposedly shipped by/from the vendor.
    """)

    cases_received = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of cases of product which were ultimately received.
    """)

    units_received = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of units of product which were ultimately received.
    """)

    out_of_stock = sa.Column(sa.Boolean(), nullable=True, doc="""
    Flag indicating whether the item was known to be "out of stock" per the
    vendor invoice.
    """)

    invoice_date = sa.Column(sa.Date(), nullable=True, doc="""
    Invoice date, if applicable.
    """)

    invoice_number = sa.Column(sa.String(length=20), nullable=True, doc="""
    Invoice number, e.g. for cross-reference with another system.
    """)

    invoice_line_number = sa.Column(sa.Integer(), nullable=True, doc="""
    Line number from the invoice if known, for cross-reference.
    """)

    invoice_case_cost = sa.Column(sa.Numeric(precision=10, scale=5), nullable=True, doc="""
    Actual cost per case of product, per invoice.
    """)

    invoice_unit_cost = sa.Column(sa.Numeric(precision=10, scale=5), nullable=True, doc="""
    Actual cost per single unit of product, per invoice.
    """)

    invoice_case_size = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Number of units in a single case of product, according to invoice.
    """)

    invoice_total = sa.Column(sa.Numeric(precision=7, scale=2), nullable=True, doc="""
    Total cost for the line item, according to the invoice.
    """)

    cases_damaged = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of cases of product which were shipped damaged.
    """)

    units_damaged = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of units of product which were shipped damaged.
    """)

    cases_expired = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of cases of product which were shipped expired.
    """)

    units_expired = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of units of product which were shipped expired.
    """)

    cases_mispick = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of cases of product for which mispick was shipped.
    """)

    units_mispick = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of units of product for which mispick was shipped.
    """)

    cases_missing = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of cases of product which were ultimately missing.
    """)

    units_missing = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of units of product which were ultimately missing.
    """)


class PurchaseCreditBase(object):
    """
    Base class for purchase credits.
    """

    @declared_attr
    def __table_args__(cls):
        return cls.__purchasecredit_table_args__()

    @classmethod
    def __purchasecredit_table_args__(cls):
        return (
            sa.ForeignKeyConstraint(['store_uuid'], ['store.uuid'], name='{}_fk_store'.format(cls.__tablename__)),
            sa.ForeignKeyConstraint(['vendor_uuid'], ['vendor.uuid'], name='{}_fk_vendor'.format(cls.__tablename__)),
            sa.ForeignKeyConstraint(['product_uuid'], ['product.uuid'], name='{}_fk_product'.format(cls.__tablename__)),
            sa.ForeignKeyConstraint(['mispick_product_uuid'], ['product.uuid'], name='{}_fk_mispick_product'.format(cls.__tablename__)),
        )

    store_uuid = sa.Column(sa.String(length=32), nullable=False)

    @declared_attr
    def store(cls):
        return orm.relationship(
            Store,
            doc="""
            Reference to the :class:`Store` for which the purchase was made.
            """)

    vendor_uuid = sa.Column(sa.String(length=32), nullable=False)

    @declared_attr
    def vendor(cls):
        return orm.relationship(
            Vendor,
            doc="""
            Reference to the :class:`Vendor` to which the purchase was made.
            """)

    date_ordered = sa.Column(sa.Date(), nullable=True, doc="""
    Date on which the purchase order was first submitted to the vendor.
    """)

    date_shipped = sa.Column(sa.Date(), nullable=True, doc="""
    Date on which the order was shipped from the vendor.
    """)

    date_received = sa.Column(sa.Date(), nullable=True, doc="""
    Date on which the order was received at the store.
    """)

    invoice_number = sa.Column(sa.String(length=20), nullable=True, doc="""
    Invoice number, e.g. for cross-reference with another system.
    """)

    invoice_date = sa.Column(sa.Date(), nullable=True, doc="""
    Invoice date, if applicable.
    """)

    credit_type = sa.Column(sa.String(length=20), nullable=False, doc="""
    Type of the credit, i.e. damaged/expired/mispick/missing
    """)

    product_uuid = sa.Column(sa.String(length=32), nullable=True)

    @declared_attr
    def product(cls):
        return orm.relationship(
            Product,
            primaryjoin='Product.uuid == {}.product_uuid'.format(cls.__name__),
            doc="""
            Reference to the :class:`Product` with which the credit is associated.
            """)

    upc = sa.Column(GPCType(), nullable=True, doc="""
    Product UPC for the credit item.
    """)

    vendor_item_code = sa.Column(sa.String(length=20), nullable=True, doc="""
    Vendor-specific code for the credit item.
    """)

    brand_name = sa.Column(sa.String(length=100), nullable=True, doc="""
    Brand name for the credit item.
    """)

    description = sa.Column(sa.String(length=60), nullable=False, default='', doc="""
    Product description for the credit item.
    """)

    size = sa.Column(sa.String(length=255), nullable=True, doc="""
    Product size for the credit item.
    """)

    department_number = sa.Column(sa.Integer(), nullable=True, doc="""
    Number of the department to which the product belongs.
    """)

    department_name = sa.Column(sa.String(length=30), nullable=True, doc="""
    Name of the department to which the product belongs.
    """)

    case_quantity = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Number of units in a single case of product.
    """)

    cases_shorted = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of cases of product which were ordered but not received.
    """)

    units_shorted = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Number of cases of product which were ordered but not received.
    """)

    product_discarded = sa.Column(sa.Boolean(), nullable=True, doc="""
    Indicates the associated product was discarded, and cannot be returned to vendor.
    """)

    expiration_date = sa.Column(sa.Date(), nullable=True, doc="""
    Expiration date marked on expired product, if applicable.
    """)

    invoice_line_number = sa.Column(sa.Integer(), nullable=True, doc="""
    Line number from the invoice if known, for cross-reference.
    """)

    invoice_case_cost = sa.Column(sa.Numeric(precision=10, scale=5), nullable=True, doc="""
    Actual cost per case of product, per invoice.
    """)

    invoice_unit_cost = sa.Column(sa.Numeric(precision=10, scale=5), nullable=True, doc="""
    Actual cost per single unit of product, per invoice.
    """)

    invoice_total = sa.Column(sa.Numeric(precision=7, scale=2), nullable=True, doc="""
    Actual total cost for line item, per invoice.
    """)

    credit_total = sa.Column(sa.Numeric(precision=7, scale=2), nullable=True, doc="""
    Actual total cost for credit, i.e. value of missing/damaged product.
    """)

    mispick_product_uuid = sa.Column(sa.String(length=32), nullable=True)

    @declared_attr
    def mispick_product(cls):
        return orm.relationship(
            Product,
            primaryjoin='Product.uuid == {}.mispick_product_uuid'.format(cls.__name__),
            doc="""
            Reference to the :class:`Product` which was shipped in place of the
            one which was ordered.
            """)

    mispick_upc = sa.Column(GPCType(), nullable=True, doc="""
    Product UPC for the mispick item.
    """)

    mispick_brand_name = sa.Column(sa.String(length=100), nullable=True, doc="""
    Brand name for the mispick item.
    """)

    mispick_description = sa.Column(sa.String(length=60), nullable=True, default='', doc="""
    Product description for the mispick item.
    """)

    mispick_size = sa.Column(sa.String(length=255), nullable=True, doc="""
    Product size for the mispick item.
    """)

    def __str__(self):
        if self.cases_shorted is not None and self.units_shorted is not None:
            qty = "{} cases, {} units".format(
                pretty_quantity(self.cases_shorted),
                pretty_quantity(self.units_shorted))
        elif self.cases_shorted is not None:
            qty = "{} cases".format(pretty_quantity(self.cases_shorted))
        elif self.units_shorted is not None:
            qty = "{} units".format(pretty_quantity(self.units_shorted))
        else:
            qty = "??"
        qty += " {}".format(self.credit_type)
        if self.credit_type == 'expired' and self.expiration_date:
            qty += " ({})".format(self.expiration_date)
        if self.credit_type == 'mispick' and self.mispick_product:
            qty += " ({})".format(self.mispick_product)
        if self.invoice_total:
            return "{} = ${:0.2f}".format(qty, self.credit_total)
        return qty


class Purchase(PurchaseBase, Base):
    """
    Represents a purchase made by a store.
    """
    __tablename__ = 'purchase'

    @declared_attr
    def __table_args__(cls):
        return cls.__purchase_table_args__() + (
            sa.ForeignKeyConstraint(['created_by_uuid'], ['user.uuid'], name='purchase_fk_created_by'),
        )

    uuid = uuid_column()

    id = sa.Column(sa.Integer(), sa.Sequence('batch_id_seq'), nullable=True, doc="""
    Numeric ID for the purchase.  This is auto-increment from the general Batch
    ID sequence.
    """)

    status = sa.Column(sa.Integer(), nullable=False, doc="""
    Numeric code used to signify current status for the purchase, e.g. placed
    or paid etc.
    """)

    created = sa.Column(sa.DateTime(), nullable=False, default=datetime.datetime.utcnow, doc="""
    Timestamp when the purchase was first created within this system.
    """)

    created_by_uuid = sa.Column(sa.String(length=32), nullable=False)

    created_by = orm.relationship(
        User,
        primaryjoin=User.uuid == created_by_uuid,
        foreign_keys=[created_by_uuid],
        doc="""
        Reference to the :class:`User` who first created the purchase within
        this system.
        """)

    @property
    def id_str(self):
        from rattail.batch import batch_id_str

        if self.id:
            return batch_id_str(self.id)
        return ""


class PurchaseItem(PurchaseItemBase, Base):
    """
    Represents a line item on a purchase.
    """
    __tablename__ = 'purchase_item'

    @declared_attr
    def __table_args__(cls):
        return cls.__purchaseitem_table_args__() + (
            sa.ForeignKeyConstraint(['purchase_uuid'], ['purchase.uuid'], name='purchase_item_fk_purchase'),
        )

    uuid = uuid_column()

    purchase_uuid = sa.Column(sa.String(length=32), nullable=False)

    purchase = orm.relationship(
        Purchase,
        doc="""
        Reference to the :class:`Purchase` to which the item belongs.
        """,
        backref=orm.backref(
            'items',
            cascade='all',
            cascade_backrefs=False,
            doc="""
            List of :class:`PurchaseItem` instances for the purchase.
            """))

    status = sa.Column(sa.Integer(), nullable=True, doc="""
    Numeric code used to signify current status for the line item, e.g. for
    highlighting rows when invoice cost differed from expected/PO cost (?)
    """)


class PurchaseCredit(PurchaseCreditBase, Base):
    """
    Represents a purchase credit item.
    """
    __tablename__ = 'purchase_credit'

    @declared_attr
    def __table_args__(cls):
        return cls.__purchasecredit_table_args__() + (
            sa.ForeignKeyConstraint(['purchase_uuid'], ['purchase.uuid'], name='purchase_credit_fk_purchase'),
            sa.ForeignKeyConstraint(['item_uuid'], ['purchase_item.uuid'], name='purchase_credit_fk_item'),
        )

    uuid = uuid_column()

    purchase_uuid = sa.Column(sa.String(length=32), nullable=True)

    purchase = orm.relationship(
        Purchase,
        doc="""
        Reference to the :class:`Purchase` to which the credit applies.
        """,
        backref=orm.backref(
            'credits',
            cascade_backrefs=False,
            doc="""
            List of :class:`PurchaseCredit` instances for the purchase.
            """))

    item_uuid = sa.Column(sa.String(length=32), nullable=True)

    item = orm.relationship(
        PurchaseItem,
        doc="""
        Reference to the purchase item with which the credit is associated.
        """)

    status = sa.Column(sa.Integer(), nullable=True, doc="""
    Numeric code used to signify current status for the credit.
    """)
