#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys
sys.path.append('.')

from django.core.management import setup_environ
import settings

setup_environ(settings)

from django.db import connection, transaction

cursor = connection.cursor()

from main.models import Area


# early versions before 0.1: urn field doesn't exist for area

import htmlentitydefs, re

def slugfy(text, separator):
    ret = u""
    text = text.strip()
    for c in text.lower():
        try:
           ret += htmlentitydefs.codepoint2name[ord(c)][0]
        except:
            ret += c
    ret = re.sub("\W", " ", ret)
    ret = re.sub(" +", separator, ret)
    return ret.strip()

query = """SELECT a.attname AS field FROM pg_class c, pg_attribute a
    WHERE c.relname = 'main_area' AND a.attnum > 0 AND a.attrelid = c.oid
          AND a.attname='urn';"""
cursor.execute(query)
transaction.commit_unless_managed()

row = cursor.fetchone()
if not row:
    query_update = "ALTER TABLE main_area ADD COLUMN urn VARCHAR(50) \
UNIQUE"
    cursor.execute(query_update)
    transaction.commit_unless_managed()
    areas = Area.objects.all()
    print " * urn field created in table main_area"
    for area in areas:
        urn = slugfy(area.name, "-")
        area.urn = urn
        area.save()
        print " * area %s urn is now: %s" % (area.name, area.urn)

    query = "ALTER TABLE main_area ALTER COLUMN urn SET not null;"
    cursor.execute(query)
    transaction.commit_unless_managed()
    print " * urn field has now the constraint NOT NULL"

# early versions before 0.1: save area with wrong SRID

query = "SELECT AsText(ST_Transform(upper_left_corner, 4326)) from main_area;"