# -*- 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/>.
#
################################################################################
"""
TrainWreck data models
"""

import sqlalchemy as sa
from sqlalchemy import orm
try:
    from sqlalchemy.orm import declarative_base, declared_attr
except ImportError:
    from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.ext.orderinglist import ordering_list

from rattail import enum
from rattail.db.model import uuid_column
from rattail.db.model.core import ModelBase


Base = declarative_base(cls=ModelBase)


class TransactionBase(object):
    """
    Represents a POS (or similar?) transaction.
    """
    __tablename__ = 'transaction'
    __table_args__ = (
        sa.Index('transaction_ix_start_time', 'start_time'),
        sa.Index('transaction_ix_end_time', 'end_time'),
        sa.Index('transaction_ix_upload_time', 'upload_time'),
        sa.Index('transaction_ix_receipt_number', 'receipt_number'),
    )

    uuid = uuid_column()

    system = sa.Column(sa.String(length=50), nullable=True, doc="""
    Unique string representing the system from which transaction originated.
    """)

    system_id = sa.Column(sa.String(length=50), nullable=True, doc="""
    Unique ID string for the transaction within the system.
    """)

    store_id = sa.Column(sa.String(length=10), nullable=True, doc="""
    ID of the store at which transaction occurred, if known.
    """)

    terminal_id = sa.Column(sa.String(length=20), nullable=True, doc="""
    Terminal ID from which the transaction originated, if known.
    """)

    receipt_number = sa.Column(sa.String(length=20), nullable=True, doc="""
    Receipt number for the transaction, if known.
    """)

    effective_date = sa.Column(sa.Date(), nullable=True, doc="""
    Effective date for the transaction, e.g. for reporting.
    """)

    start_time = sa.Column(sa.DateTime(), nullable=True, doc="""
    UTC timestamp when the transaction began, if known.
    """)

    end_time = sa.Column(sa.DateTime(), nullable=True, doc="""
    UTC timestamp when the transaction ended, if known.
    """)

    upload_time = sa.Column(sa.DateTime(), nullable=True, doc="""
    UTC timestamp when the transaction was uploaded to the server, if known.
    """)

    cashier_id = sa.Column(sa.String(length=20), nullable=True, doc="""
    Cashier ID string for the transaction, if known.
    """)

    cashier_name = sa.Column(sa.String(length=255), nullable=True, doc="""
    Cashier name for the transaction, if known.
    """)

    customer_id = sa.Column(sa.String(length=20), nullable=True, doc="""
    Customer ID string for the transaction, if known.  This should correspond
    to a proper customer account, as opposed to :attr:`shopper_id`.
    """)

    customer_name = sa.Column(sa.String(length=255), nullable=True, doc="""
    Customer name for the transaction, if known.  This should correspond to a
    proper customer account, as opposed to :attr:`shopper_name`.
    """)

    shopper_id = sa.Column(sa.String(length=20), nullable=True, doc="""
    Shopper ID string for the transaction, if known.  This should correspond
    to the actual shopper, as opposed to :attr:`customer_id`.
    """)

    shopper_name = sa.Column(sa.String(length=255), nullable=True, doc="""
    Shopper name for the transaction, if known.  This should correspond to the
    actual shopper, as opposed to :attr:`customer_name`.
    """)

    shopper_level_number = sa.Column(sa.Integer(), nullable=True, doc="""
    Shopper level number, if known/applicable.
    """)

    subtotal = sa.Column(sa.Numeric(precision=9, scale=2), nullable=True, doc="""
    Subtotal for the transaction, before discounts.
    """)

    discounted_subtotal = sa.Column(sa.Numeric(precision=9, scale=2), nullable=True, doc="""
    Subtotal for the transaction, after discounts.
    """)

    tax = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Tax total (for all tax levels) for the transaction.
    """)

    tax1 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 1" for the transaction.
    """)

    tax2 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 2" for the transaction.
    """)

    tax3 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 3" for the transaction.
    """)

    tax4 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 4" for the transaction.
    """)

    tax5 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 5" for the transaction.
    """)

    tax6 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 6" for the transaction.
    """)

    tax7 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 7" for the transaction.
    """)

    cashback = sa.Column(sa.Numeric(precision=9, scale=2), nullable=True, doc="""
    Total "cash back" amount for the transaction, if any.
    """)

    total = sa.Column(sa.Numeric(precision=9, scale=2), nullable=True, doc="""
    Total amount for the transaction, i.e. discounted subtotal plus tax.
    """)

    void = sa.Column(sa.Boolean(), nullable=False, default=False, doc="""
    Flag indicating if the transaction was voided.
    """)

    ##############################
    # custom data points
    ##############################

    patronage = sa.Column(sa.Numeric(precision=9, scale=2), nullable=True, doc="""
    Patronage amount for the transaction, if applicable.
    """)

    equity_current = sa.Column(sa.Boolean(), nullable=True, doc="""
    Flag indicating the member account was considered "current on
    equity" at time of the transaction.
    """)

    self_updated = sa.Column(sa.Boolean(), nullable=True, doc="""
    Flag indicating the transaction has been self-updated, if applicable.
    """)

    def __str__(self):
        system = enum.TRAINWRECK_SYSTEM.get(self.system, self.system or "Unknown")
        return "{} {}-{}".format(system, self.terminal_id or "?", self.receipt_number or "??")


class TransactionOrderMarkerBase(object):
    """
    Represents a "marker" to cross-reference one or more customer
    orders with a given POS transaction.
    """
    __tablename__ = 'transaction_order_marker'

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

    @classmethod
    def __txnmarker_table_args__(cls):
        txn_table = cls.__txn_class__.__tablename__
        marker_table = cls.__tablename__
        return (
            sa.ForeignKeyConstraint(['transaction_uuid'], ['{}.uuid'.format(txn_table)],
                                    name='{}_fk_transaction'.format(marker_table)),
        )

    uuid = uuid_column()

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

    @declared_attr
    def transaction(cls):
        txn_class = cls.__txn_class__
        marker_class = cls
        # txn_class.order_xref_marker_class = marker_class

        # Must establish `Transaction.items` here instead of from within
        # Transaction itself, because the item class doesn't yet exist (really,
        # it can't even be "known") when Transaction is being created.
        txn_class.custorder_xref_markers = orm.relationship(
            marker_class,
            cascade='all, delete-orphan',
            doc="""
            Set of "order xref markers" for the transaction.
            """,
            back_populates='transaction')

        # Now, here's the `TransactionOrderMarker.transaction` reference.
        return orm.relationship(
            txn_class,
            back_populates='custorder_xref_markers',
            doc="""
            Reference to the order maker's parent transaction.
            """)

    custorder_xref = sa.Column(sa.String(length=50), nullable=True, doc="""
    Cross-reference identifier for a "customer order" which
    corresponds to this transaction, if applicable.  More than one
    such order xref can exist for a transaction.
    """)

    custorder_item_xref = sa.Column(sa.String(length=50), nullable=True, doc="""
    Cross-reference identifier for a "customer order item" which
    corresponds to this transaction, if applicable.  More than one
    such order xref can exist for a transaction.
    """)

    def __str__(self):
        xref = self.custorder_xref or "??"
        if self.custorder_item_xref:
            xref = "{} / {}".format(xref, self.custorder_item_xref)
        return "{} / {}".format(self.transaction, xref)


class TransactionItemBase(object):
    """
    Represents a line item within a transaction.
    """
    __tablename__ = 'transaction_item'

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

    @classmethod
    def __txnitem_table_args__(cls):
        txn_table = cls.__txn_class__.__tablename__
        item_table = cls.__tablename__
        return (
            sa.ForeignKeyConstraint(['transaction_uuid'], ['{}.uuid'.format(txn_table)],
                                    name='{}_fk_transaction'.format(item_table)),
            sa.Index('{}_ix_transaction_uuid'.format(item_table), 'transaction_uuid'),
            sa.Index('{}_ix_item_id'.format(item_table), 'item_id'),
        )

    uuid = uuid_column()

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

    @declared_attr
    def transaction(cls):
        txn_class = cls.__txn_class__
        item_class = cls
        txn_class.item_class = item_class

        # Must establish `Transaction.items` here instead of from within
        # Transaction itself, because the item class doesn't yet exist (really,
        # it can't even be "known") when Transaction is being created.
        txn_class.items = orm.relationship(
            item_class,
            order_by=lambda: item_class.sequence,
            collection_class=ordering_list('sequence', count_from=1),
            cascade='all, delete-orphan',
            doc="""
            Sequence of line items for the transaction.
            """,
            back_populates='transaction')

        # Now, here's the `TransactionItem.transaction` reference.
        return orm.relationship(txn_class, back_populates='items', doc="""
        Reference to the line item's parent transaction.
        """)

    sequence = sa.Column(sa.Integer(), nullable=False, doc="""
    Sequence number for this line item, within the parent transaction.
    """)

    item_type = sa.Column(sa.String(length=50), nullable=True, doc="""
    Type of line item this is, if relevant/known.  Value and interpretation
    thereof are not defined by the schema, but rather the (custom) app logic...
    """)

    item_scancode = sa.Column(sa.String(length=20), nullable=False, doc="""
    Scancode for the item, if applicable / known.
    """)

    item_id = sa.Column(sa.String(length=50), nullable=True, doc="""
    Item ID for the line item.  This is meant to be used to supplement the
    scancode, for systems where multiple items may have the same scancode.
    """)

    custorder_item_xref = sa.Column(sa.String(length=50), nullable=True, doc="""
    Cross-reference identifier for the "customer order item" which
    corresponds to this line item, if applicable.  Can be useful to
    tie these things together for sake of reporting.
    """)

    department_number = sa.Column(sa.Integer(), nullable=True, doc="""
    Department number for the line item, if known.
    """)

    department_name = sa.Column(sa.String(length=30), nullable=True, doc="""
    Department name for the line item, if known.
    """)

    subdepartment_number = sa.Column(sa.Integer(), nullable=True, doc="""
    Subdepartment number for the line item, if known.
    """)

    subdepartment_name = sa.Column(sa.String(length=30), nullable=True, doc="""
    Subdepartment name for the line item, if known.
    """)

    exempt_from_gross_sales = sa.Column(sa.Boolean(), nullable=True, doc="""
    Flag indicating whether item should (not) contribute to gross sales
    figures.  The SIL book says this about it:

       Dollar sales are not to be included in gross sales. (i.e., Postage
       Stamps, Lottery Tickets, etc.).
    """)

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

    description = sa.Column(sa.String(length=255), nullable=False, default="", doc="""
    Description for the line item; defaults to empty string.
    """)

    unit_price = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Effective unit price for the item, before discounts.
    """)

    unit_regular_price = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Regular unit price for the item, e.g. as opposed to effective price.
    """)

    unit_discounted_price = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Effective unit price for the item, after discounts.
    """)

    unit_quantity = sa.Column(sa.Numeric(precision=10, scale=4), nullable=True, doc="""
    Unity quantity for the line item, i.e. how much of product is being sold.
    """)

    subtotal = sa.Column(sa.Numeric(precision=9, scale=2), nullable=True, doc="""
    Subtotal for the line item, i.e. unit quantity times price, before discounts.
    """)

    discounted_subtotal = sa.Column(sa.Numeric(precision=9, scale=2), nullable=True, doc="""
    Subtotal for the line item, i.e. quantity times unit price.
    """)

    gross_sales = sa.Column(sa.Numeric(precision=9, scale=2), nullable=True, doc="""
    Gross sales amount for the line item.  Meaning of this may vary slightly?
    """)

    net_sales = sa.Column(sa.Numeric(precision=9, scale=2), nullable=True, doc="""
    Net sales amount for the line item.  Meaning of this may vary slightly?
    """)

    tax = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Tax total (for all tax levels) for the line item.
    """)

    tax1 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 1" for the line item.
    """)

    tax2 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 2" for the line item.
    """)

    tax3 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 3" for the line item.
    """)

    tax4 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 4" for the line item.
    """)

    tax5 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 5" for the line item.
    """)

    tax6 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 6" for the line item.
    """)

    tax7 = sa.Column(sa.Numeric(precision=8, scale=2), nullable=True, doc="""
    Total of "tax level 7" for the line item.
    """)

    total = sa.Column(sa.Numeric(precision=9, scale=2), nullable=True, doc="""
    Total amount for the line item, i.e. discounted subtotal plus tax.
    """)

    void = sa.Column(sa.Boolean(), nullable=False, default=False, doc="""
    Flag indicating if the line item was voided.
    """)

    error_correct = sa.Column(sa.Boolean(), nullable=False, default=False, doc="""
    Flag indicating if the line item was error-corrected.
    """)

    def __str__(self):
        return self.description


class TransactionItemDiscountBase(object):
    """
    Represents a discount on a line item.
    """
    __tablename__ = 'transaction_item_discount'

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

    @classmethod
    def __txnitemdiscount_table_args__(cls):
        item_table = cls.__txn_item_class__.__tablename__
        discount_table = cls.__tablename__
        return (
            sa.ForeignKeyConstraint(['item_uuid'], ['{}.uuid'.format(item_table)],
                                    name='{}_fk_item'.format(discount_table)),
        )

    uuid = uuid_column()

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

    @declared_attr
    def item(cls):
        item_class = cls.__txn_item_class__
        discount_class = cls

        item_class.discounts = orm.relationship(
            discount_class,
            order_by=lambda: discount_class.sequence,
            collection_class=ordering_list('sequence', count_from=1),
            cascade='all, delete-orphan',
            doc="""
            List of discounts for the line item.
            """,
            back_populates='item')

        return orm.relationship(item_class, back_populates='discounts', doc="""
        Reference to the parent line item.
        """)

    sequence = sa.Column(sa.Integer(), nullable=False, doc="""
    Sequence number for this discount, within the line item context.
    """)

    discount_type = sa.Column(sa.String(length=50), nullable=True, doc="""
    Type of the discount, if applicable.
    """)

    description = sa.Column(sa.String(length=50), nullable=False, doc="""
    Description for the discount.
    """)

    amount = sa.Column(sa.Numeric(precision=9, scale=2), nullable=False, doc="""
    Amount of the discount.
    """)

    def __str__(self):
        return "{} ${:0.2f}".format(self.description, self.amount)
