import db


def get_policy(app_id: str, channel: str):
    conn = db.get_conn()
    row = conn.execute("SELECT * FROM version_policies WHERE app_id=? AND channel=?", (app_id, channel)).fetchone()
    conn.close()
    return row


def save_policy(
    app_id: str,
    channel: str,
    force_update: bool,
    allow_rollback: bool,
    offline_allowed: bool,
    valid_until: str,
    min_supported_version: str,
    disabled_versions_json: str,
    git_tags_enabled: bool,
    message: str,
) -> tuple[str, int | None]:
    conn = db.get_conn()
    try:
        channel_row = conn.execute("SELECT * FROM channels WHERE app_id=? AND channel_code=?", (app_id, channel)).fetchone()
        if not channel_row:
            return "channel_not_found", None
        if not channel_row["enabled"]:
            return "channel_disabled", None
        row = conn.execute("SELECT policy_seq FROM version_policies WHERE app_id=? AND channel=?", (app_id, channel)).fetchone()
        next_seq = (int(row["policy_seq"]) + 1) if row else 1
        values = (
            next_seq,
            int(bool(force_update)),
            int(bool(allow_rollback)),
            int(bool(offline_allowed)),
            valid_until,
            min_supported_version,
            disabled_versions_json,
            int(bool(git_tags_enabled)),
            message,
            app_id,
            channel,
        )
        conn.execute(
            """
            INSERT INTO version_policies(policy_seq,force_update,allow_rollback,offline_allowed,valid_until,
              min_supported_version,disabled_versions,git_tags_enabled,message,app_id,channel)
            VALUES(?,?,?,?,?,?,?,?,?,?,?)
            ON CONFLICT(app_id,channel) DO UPDATE SET policy_seq=excluded.policy_seq,
              force_update=excluded.force_update,allow_rollback=excluded.allow_rollback,
              offline_allowed=excluded.offline_allowed,valid_until=excluded.valid_until,
              min_supported_version=excluded.min_supported_version,disabled_versions=excluded.disabled_versions,
              git_tags_enabled=excluded.git_tags_enabled,message=excluded.message,updated_at=datetime('now')
            """,
            values,
        )
        conn.commit()
        return "ok", next_seq
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()