from __future__ import absolute_import
from __future__ import division
from __future__ import unicode_literals

from sqlalchemy.schema import FetchedValue
from sqlalchemy.dialects import postgresql as pg
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.orm import relationship
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.sql.expression import text

from warehouse import db
from warehouse.database.mixins import UUIDPrimaryKeyMixin, TimeStampedMixin
from warehouse.database.schema import TableDDL
from warehouse.database.types import Enum
from warehouse.database.utils import table_args


classifiers = db.Table("version_classifiers",
    db.Column("classifier_id",
        pg.UUID(as_uuid=True),
        db.ForeignKey("classifiers.id",
            onupdate="CASCADE",
            ondelete="CASCADE"
        ),
        primary_key=True,
    ),
    db.Column("version_id",
        pg.UUID(as_uuid=True),
        db.ForeignKey("versions.id",
            onupdate="CASCADE",
            ondelete="CASCADE"
        ),
        primary_key=True,
    ),
)


class Classifier(UUIDPrimaryKeyMixin, db.Model):

    __tablename__ = "classifiers"

    trove = db.Column(db.UnicodeText, unique=True, nullable=False)

    def __init__(self, trove):
        self.trove = trove

    def __repr__(self):
        return "<Classifier: {trove}>".format(trove=self.trove)

    @classmethod
    def get_or_create(cls, trove):
        try:
            obj = cls.query.filter_by(trove=trove).one()
        except NoResultFound:
            obj = cls(trove)
        return obj


class Project(UUIDPrimaryKeyMixin, TimeStampedMixin, db.Model):

    __tablename__ = "projects"
    __table_args__ = declared_attr(table_args((
        TableDDL("""
            CREATE OR REPLACE FUNCTION normalize_name()
            RETURNS trigger AS $$
            BEGIN
                NEW.normalized = lower(regexp_replace(new.name, '[^A-Za-z0-9.]+', '-'));
                return NEW;
            END;
            $$ LANGUAGE plpgsql;

            CREATE TRIGGER %(table)s_normalize_name
            BEFORE INSERT OR UPDATE
            ON %(table)s
            FOR EACH ROW
            EXECUTE PROCEDURE normalize_name();
        """),
    )))

    name = db.Column(db.UnicodeText, unique=True, nullable=False)
    normalized = db.Column(db.UnicodeText,
                    unique=True,
                    nullable=False,
                    server_default=FetchedValue(),
                    server_onupdate=FetchedValue()
                )

    versions = relationship("Version", backref="project")

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return "<Project: {name}>".format(name=self.name)


class Version(UUIDPrimaryKeyMixin, TimeStampedMixin, db.Model):

    __tablename__ = "versions"
    __table_args__ = declared_attr(table_args((
        db.Index("idx_project_version", "project_id", "version", unique=True),
    )))

    project_id = db.Column(pg.UUID(as_uuid=True),
                    db.ForeignKey("projects.id", ondelete="RESTRICT"),
                    nullable=False
                )
    version = db.Column(db.UnicodeText, nullable=False)

    summary = db.Column(db.UnicodeText, nullable=False, server_default="")
    description = db.Column(db.UnicodeText, nullable=False, server_default="")

    keywords = db.Column(pg.ARRAY(db.UnicodeText, dimensions=1),
                    nullable=False,
                    server_default="{}"
                )

    author = db.Column(db.UnicodeText, nullable=False, server_default="")
    author_email = db.Column(db.UnicodeText, nullable=False, server_default="")

    maintainer = db.Column(db.UnicodeText, nullable=False, server_default="")
    maintainer_email = db.Column(db.UnicodeText,
                            nullable=False,
                            server_default=""
                        )

    license = db.Column(db.UnicodeText, nullable=False, server_default="")

    # URIs
    uris = db.Column(MutableDict.as_mutable(pg.HSTORE),
                nullable=False,
                server_default=text("''::hstore")
            )
    download_uri = db.Column(db.UnicodeText, nullable=False, server_default="")

    # Requirements
    requires_python = db.Column(db.UnicodeText,
                            nullable=False,
                            server_default=""
                        )
    requires_external = db.Column(pg.ARRAY(db.UnicodeText, dimensions=1),
                            nullable=False,
                            server_default="{}"
                        )

    _classifiers = relationship("Classifier",
                        secondary=classifiers,
                        backref=db.backref("versions", lazy='dynamic')
                    )
    classifiers = association_proxy("_classifiers", "trove",
                        creator=Classifier.get_or_create
                    )
    files = relationship("File", backref="version", )

    def __repr__(self):
        ctx = {"name": self.project.name, "version": self.version}
        return "<Version: {name} {version}>".format(**ctx)


class FileType(Enum):
    source = "sdist", "Source"
    egg = "bdist_egg", "Egg"
    msi = "bdist_msi", "MSI"
    dmg = "bdist_dmg", "DMG"
    rpm = "bdist_rpm", "RPM",
    dumb = "bdist_dumb", "Dumb Binary Distribution"
    windows_installer = "bdist_wininst", "Windows Installer"
    wheel = "bdist_wheel", "Wheel"


class File(UUIDPrimaryKeyMixin, TimeStampedMixin, db.Model):

    __tablename__ = "files"

    version_id = db.Column(pg.UUID(as_uuid=True),
                        db.ForeignKey("versions.id", ondelete="RESTRICT"),
                        nullable=False
                    )

    file = db.Column(db.UnicodeText, nullable=False, unique=True)

    filename = db.Column(db.UnicodeText, nullable=False, unique=True)
    filesize = db.Column(db.Integer, nullable=False)

    type = db.Column(FileType.db_type(), nullable=False)

    python_version = db.Column(db.UnicodeText,
                        nullable=False,
                        server_default=""
                    )

    comment = db.Column(db.UnicodeText, nullable=False, server_default="")

    hashes = db.Column(MutableDict.as_mutable(pg.HSTORE),
                    nullable=False,
                    server_default=text("''::hstore")
                )

@@ -5090,18 +5090,16 @@
 version%22
-, 
 )%0A%0A    d

Cleanup a trailing comma