<commit_before># -*- coding: utf-8 -*-
"""models including business logic"""

import datetime
import logging
from datetime import timedelta

import hashlib
import pytz
import sqlalchemy.types as ty
from sqlalchemy.dialects.postgresql import JSON
import sys

from pyramid.settings import asbool
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql.schema import UniqueConstraint, Index

from gengine.app.permissions import perm_global_increase_value
from gengine.base.model import ABase, exists_by_expr, datetime_trunc, calc_distance, coords, update_connection
from gengine.app.cache import cache_general, cache_goal_evaluation, cache_achievement_eval, cache_achievements_users_levels, \
    cache_achievements_by_user_for_today, cache_translations
from sqlalchemy import (
    Table,
    ForeignKey,
    func,
    select,
    and_,
    or_,
    text,
    Column
, event)
from sqlalchemy.dialects.postgresql import TIMESTAMP
from sqlalchemy.orm import (
    mapper,
    relationship as sa_relationship,
    backref as sa_backref
)
from sqlalchemy.sql import bindparam

from gengine.base.settings import get_settings
from gengine.metadata import Base, DBSession

from gengine.app.formular import evaluate_condition, evaluate_value_expression, evaluate_string

log = logging.getLogger(__name__)

t_users = Table("users", Base.metadata,
    Column('id', ty.BigInteger, primary_key = True),
    Column("lat", ty.Float(Precision=64), nullable=True),
    Column("lng", ty.Float(Precision=64), nullable=True),
    Column("language_id", ty.Integer, ForeignKey("languages.id"), nullable=True),
    Column("timezone", ty.String(), nullable=False, default="UTC"),
    Column("country", ty.String(), nullable=True, default=None),
    Column("region", ty.String(), nullable=True, default=None),
    Column("city", ty.String(), nullable=True, default=None),
    Column("additional_public_data", JSON(), nullable=True, default=None),
    Column('created_at', ty.DateTime, nullable=False, default=datetime.datetime.utcnow),
)

t_auth_users = Table("auth_users", Base.metadata,
    Column('user_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column("email", ty.String, unique=True),
    Column("password_hash", ty.String, nullable=False),
    Column("password_salt", ty.Unicode, nullable=False),
    Column("force_password_change", ty.Boolean, nullable=False, server_default='0'),
    Column("active", ty.Boolean, nullable=False),
    Column('created_at', ty.DateTime, nullable = False, default=datetime.datetime.utcnow),
)

def get_default_token_valid_time():
    return datetime.datetime.utcnow() + datetime.timedelta(days=30)

t_auth_tokens = Table("auth_tokens", Base.metadata,
    Column("id", ty.BigInteger, primary_key=True),
    Column("user_id", ty.BigInteger, ForeignKey("auth_users.user_id", ondelete="CASCADE"), nullable=False),
    Column("token", ty.String, nullable=False),
    Column('valid_until', ty.DateTime, nullable=False, default=get_default_token_valid_time),
)

t_auth_roles = Table("auth_roles", Base.metadata,
    Column("id", ty.Integer, primary_key=True),
    Column("name", ty.String(100), unique=True),
)

t_auth_users_roles = Table("auth_users_roles", Base.metadata,
    Column("user_id", ty.BigInteger, ForeignKey("auth_users.user_id", ondelete="CASCADE"), primary_key=True, nullable=False),
    Column("role_id", ty.BigInteger, ForeignKey("auth_roles.id", ondelete="CASCADE"), primary_key=True, nullable=False),
)

t_auth_roles_permissions = Table("auth_roles_permissions", Base.metadata,
    Column("id", ty.Integer, primary_key=True),
    Column("role_id", ty.Integer, ForeignKey("auth_roles.id", use_alter=True, ondelete="CASCADE"), nullable=False, index=True),
    Column("name", ty.String(255), nullable=False),
    UniqueConstraint("role_id", "name")
)

t_users_users = Table("users_users", Base.metadata,
    Column('from_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('to_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False)
)

t_groups = Table("groups", Base.metadata,
    Column('id', ty.BigInteger, primary_key = True),
)

t_users_groups = Table("users_groups", Base.metadata,
    Column('user_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('group_id', ty.BigInteger, ForeignKey("groups.id", ondelete="CASCADE"), primary_key = True, nullable=False)
)

t_achievementcategories = Table('achievementcategories', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name', ty.String(255), nullable = False),
)

t_achievements = Table('achievements', Base.metadata,
    Column('id', ty.Integer, primary_key=True),
    Column("achievementcategory_id", ty.Integer, ForeignKey("achievementcategories.id", ondelete="SET NULL"), index=True, nullable=True),
    Column('name', ty.String(255), nullable=False), #internal use
    Column('maxlevel',ty.Integer, nullable=False, default=1),
    Column('hidden',ty.Boolean, nullable=False, default=False),
    Column('valid_start',ty.Date, nullable=True),
    Column('valid_end',ty.Date, nullable=True),
    Column("lat", ty.Float(Precision=64), nullable=True),
    Column("lng", ty.Float(Precision=64), nullable=True),
    Column("max_distance", ty.Integer, nullable=True),
    Column('priority', ty.Integer, index=True, default=0),
    Column('evaluation', ty.Enum("immediately", "daily", "weekly", "monthly", "yearly", "end", name="evaluation_types"), default="immediately", nullable=False),
    Column('evaluation_timezone', ty.String(), default=None, nullable=True),
    Column('evaluation_shift', ty.Integer(), nullable=True, default=None),
    Column('relevance', ty.Enum("global", "friends", "city", "own", name="relevance_types"), default="own"),<commit_msg>model extended groups<commit_after># -*- coding: utf-8 -*-
"""models including business logic"""

import datetime
import logging
from datetime import timedelta

import hashlib
import pytz
import sqlalchemy.types as ty
from sqlalchemy.dialects.postgresql import JSON
import sys

from pyramid.settings import asbool
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql.ddl import DDL
from sqlalchemy.sql.schema import UniqueConstraint, Index

from gengine.app.permissions import perm_global_increase_value
from gengine.base.model import ABase, exists_by_expr, datetime_trunc, calc_distance, coords, update_connection
from gengine.app.cache import cache_general, cache_goal_evaluation, cache_achievement_eval, cache_achievements_users_levels, \
    cache_achievements_by_user_for_today, cache_translations
from sqlalchemy import (
    Table,
    ForeignKey,
    func,
    select,
    and_,
    or_,
    text,
    Column
, event)
from sqlalchemy.dialects.postgresql import TIMESTAMP
from sqlalchemy.orm import (
    mapper,
    relationship as sa_relationship,
    backref as sa_backref
)
from sqlalchemy.sql import bindparam

from gengine.base.settings import get_settings
from gengine.metadata import Base, DBSession

from gengine.app.formular import evaluate_condition, evaluate_value_expression, evaluate_string

log = logging.getLogger(__name__)

t_users = Table("users", Base.metadata,
    Column('id', ty.BigInteger, primary_key = True),
    Column("lat", ty.Float(Precision=64), nullable=True),
    Column("lng", ty.Float(Precision=64), nullable=True),
    Column("language_id", ty.Integer, ForeignKey("languages.id"), nullable=True),
    Column("timezone", ty.String(), nullable=False, default="UTC"),
    Column("country", ty.String(), nullable=True, default=None),
    Column("region", ty.String(), nullable=True, default=None),
    Column("city", ty.String(), nullable=True, default=None),
    Column("additional_public_data", JSON(), nullable=True, default=None),
    Column('created_at', ty.DateTime, nullable=False, default=datetime.datetime.utcnow),
)

t_auth_users = Table("auth_users", Base.metadata,
    Column('user_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column("email", ty.String, unique=True),
    Column("password_hash", ty.String, nullable=False),
    Column("password_salt", ty.Unicode, nullable=False),
    Column("force_password_change", ty.Boolean, nullable=False, server_default='0'),
    Column("active", ty.Boolean, nullable=False),
    Column('created_at', ty.DateTime, nullable = False, default=datetime.datetime.utcnow),
)

def get_default_token_valid_time():
    return datetime.datetime.utcnow() + datetime.timedelta(days=30)

t_auth_tokens = Table("auth_tokens", Base.metadata,
    Column("id", ty.BigInteger, primary_key=True),
    Column("user_id", ty.BigInteger, ForeignKey("auth_users.user_id", ondelete="CASCADE"), nullable=False),
    Column("token", ty.String, nullable=False),
    Column('valid_until', ty.DateTime, nullable=False, default=get_default_token_valid_time),
)

t_auth_roles = Table("auth_roles", Base.metadata,
    Column("id", ty.Integer, primary_key=True),
    Column("name", ty.String(100), unique=True),
)

t_auth_users_roles = Table("auth_users_roles", Base.metadata,
    Column("user_id", ty.BigInteger, ForeignKey("auth_users.user_id", ondelete="CASCADE"), primary_key=True, nullable=False),
    Column("role_id", ty.BigInteger, ForeignKey("auth_roles.id", ondelete="CASCADE"), primary_key=True, nullable=False),
)

t_auth_roles_permissions = Table("auth_roles_permissions", Base.metadata,
    Column("id", ty.Integer, primary_key=True),
    Column("role_id", ty.Integer, ForeignKey("auth_roles.id", use_alter=True, ondelete="CASCADE"), nullable=False, index=True),
    Column("name", ty.String(255), nullable=False),
    UniqueConstraint("role_id", "name")
)

t_users_users = Table("users_users", Base.metadata,
    Column('from_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('to_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False)
)

t_groups = Table("groups", Base.metadata,
    Column('id', ty.BigInteger, primary_key = True),
    Column('grouptype_id', ty.BigInteger, ForeignKey("grouptypes.id", ondelete="CASCADE"), nullable=False, index=True)
)

t_groups_groups = Table("groups_groups", Base.metadata,
    Column('id', ty.BigInteger, primary_key=True),
    Column('group_id', ty.BigInteger, ForeignKey("groups.id", ondelete="CASCADE"), index=True, nullable=False),
    Column('part_of_id', ty.BigInteger, ForeignKey("groups.id", ondelete="CASCADE"), index=True, nullable=False),
    UniqueConstraint("group_id", "part_of_id")
)
t_groups_groups_trig_ddl = DDL("""
    CREATE OR REPLACE FUNCTION check_groups_groups_cycle() RETURNS trigger AS $$
DECLARE
    cycles INTEGER;
BEGIN
    LOCK TABLE groups_groups IN ACCESS EXCLUSIVE MODE;
    WITH RECURSIVE search_graph(part_of_id, group_id, id, depth, path, cycle) AS (
         SELECT NEW.part_of_id, NEW.group_id, NEW.id, 1,
     	 ARRAY[NEW.id], false
          UNION ALL
            SELECT g.part_of_id, g.group_id, g.id, sg.depth + 1,
     	 path || g.id,
     	 g.id = ANY(path)
            FROM groups_groups g, search_graph sg
            WHERE g.part_of_id = sg.group_id AND NOT cycle
    )
    SELECT INTO cycles COUNT(*) FROM search_graph WHERE cycle=true;
    RAISE NOTICE 'cycles: %', cycles;
    IF cycles > 0 THEN
       RAISE EXCEPTION 'cycle';
    END IF;
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_groups_groups_cycle AFTER INSERT OR UPDATE ON groups_groups
    FOR EACH ROW EXECUTE PROCEDURE check_groups_groups_cycle();
""")
event.listen(t_groups_groups, 'after_create', t_groups_groups_trig_ddl.execute_if(dialect='postgresql'))

t_grouptypes = Table("grouptypes", Base.metadata,
    Column('id', ty.BigInteger, primary_key=True),
    Column("name", ty.String(100), unique=True, nullable=False),
)

t_grouptypes_grouptypes = Table("grouptypes_grouptypes", Base.metadata,
    Column('id', ty.BigInteger, primary_key=True),
    Column('grouptype_id', ty.BigInteger, ForeignKey("grouptypes.id", ondelete="CASCADE"), index=True, nullable=False),
    Column('part_of_id', ty.BigInteger, ForeignKey("grouptypes.id", ondelete="CASCADE"), index=True, nullable=False),
    UniqueConstraint("grouptype_id", "part_of_id")
)
t_grouptypes_grouptypes_trig_ddl = DDL("""
    CREATE OR REPLACE FUNCTION check_grouptypes_grouptypes_cycle() RETURNS trigger AS $$
DECLARE
    cycles INTEGER;
BEGIN
    LOCK TABLE grouptypes_grouptypes IN ACCESS EXCLUSIVE MODE;
    WITH RECURSIVE search_graph(part_of_id, group_id, id, depth, path, cycle) AS (
         SELECT NEW.part_of_id, NEW.group_id, NEW.id, 1,
     	 ARRAY[NEW.id], false
          UNION ALL
            SELECT g.part_of_id, g.group_id, g.id, sg.depth + 1,
     	 path || g.id,
     	 g.id = ANY(path)
            FROM grouptypes_grouptypes g, search_graph sg
            WHERE g.part_of_id = sg.group_id AND NOT cycle
    )
    SELECT INTO cycles COUNT(*) FROM search_graph WHERE cycle=true;
    RAISE NOTICE 'cycles: %', cycles;
    IF cycles > 0 THEN
       RAISE EXCEPTION 'cycle';
    END IF;
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_grouptypes_grouptypes_cycle AFTER INSERT OR UPDATE ON grouptypes_grouptypes
    FOR EACH ROW EXECUTE PROCEDURE check_grouptypes_grouptypes_cycle();
""")
event.listen(t_grouptypes_grouptypes, 'after_create', t_grouptypes_grouptypes_trig_ddl.execute_if(dialect='postgresql'))


t_users_groups = Table("users_groups", Base.metadata,
    Column('user_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('group_id', ty.BigInteger, ForeignKey("groups.id", ondelete="CASCADE"), primary_key = True, nullable=False)
)

t_achievementcategories = Table('achievementcategories', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name', ty.String(255), nullable = False),
)

t_achievements = Table('achievements', Base.metadata,
    Column('id', ty.Integer, primary_key=True),
    Column("achievementcategory_id", ty.Integer, ForeignKey("achievementcategories.id", ondelete="SET NULL"), index=True, nullable=True),
    Column('name', ty.String(255), nullable=False), #internal use
    Column('maxlevel',ty.Integer, nullable=False, default=1),
    Column('hidden',ty.Boolean, nullable=False, default=False),
    Column('valid_start',ty.Date, nullable=True),
    Column('valid_end',ty.Date, nullable=True),
    Column("lat", ty.Float(Precision=64), nullable=True),
    Column("lng", ty.Float(Precision=64), nullable=True),
    Column("max_distance", ty.Integer, nullable=True),
    Column('priority', ty.Integer, index=True, default=0),
    Column('evaluation', ty.Enum("immediately", "daily", "weekly", "monthly", "yearly", "end", name="evaluation_types"), default="immediately", nullable=False),
    Column('evaluation_timezone', ty.String(), default=None, nullable=True),
    Column('evaluation_shift', ty.Integer(), nullable=True, default=None),
    Column('relevance', ty.Enum("global", "friends", "city", "own", name="relevance_types"), default="own"),
{'fraction_non_alphanumeric': 0.13884863654327492, 'fraction_numerical': 0.004017915953102358, 'mean_word_length': 5.814631956912029, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1507734', 'n_tokens_mistral': 5093, 'n_tokens_neox': 4946, 'n_words': 1001}