#!/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, Route
from django.contrib.gis.geos import LineString

# 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: subcategory_areas table doesn't exist

query = """SELECT c.relname FROM pg_class c
WHERE c.relname = 'subcategory_areas';"""
cursor.execute(query)
transaction.commit_unless_managed()

row = cursor.fetchone()
if not row:
    query_create = """
CREATE TABLE "subcategory_areas" (
    "id" serial NOT NULL PRIMARY KEY,
    "subcategory_id" integer NOT NULL REFERENCES "main_subcategory" ("id")
                                                  DEFERRABLE INITIALLY DEFERRED,
    "area_id" integer NOT NULL REFERENCES "main_area" ("id")
                                                  DEFERRABLE INITIALLY DEFERRED,
    UNIQUE ("subcategory_id", "area_id"));

"""
    cursor.execute(query_create)
    transaction.commit_unless_managed()
    print " * subcategory_areas created"


# early versions before 0.1: save area with wrong SRID
# only errors with default SRID is managed adapt the script for your SRID

from osgeo import osr

srs = osr.SpatialReference()
srs.ImportFromEPSG(4326) # WGS84
ll = srs.CloneGeogCS()
srs.ImportFromEPSG(settings.EPSG_PROJECTION)
proj = osr.CoordinateTransformation(srs, ll)

changed = False
areas = Area.objects.all()
for area in areas:
    # only one test: assume each point as been save with the same SRID...
    if area.upper_left_corner.srid == 4326 and area.upper_left_corner.x > 90 \
                                           or area.upper_left_corner.x < -90:
        changed = True
        pt = proj.TransformPoint(area.upper_left_corner.y,
                                 area.upper_left_corner.x)
        area.upper_left_corner.x = pt[0]
        area.upper_left_corner.y = pt[1]
        pt = proj.TransformPoint(area.lower_right_corner.y,
                                 area.lower_right_corner.x)
        area.lower_right_corner.x = pt[0]
        area.lower_right_corner.y = pt[1]
        area.save()
if changed:
    print " * projections of areas corrected"

# early versions before 0.1: save route with wrong SRID
# only errors with default SRID is managed adapt the script for your SRID

changed = False
routes = Route.objects.all()
for route in routes:
    # only one test: assume each point as been save with the same SRID...
    if route.route and route.route.srid == 4326 and \
       route.route[0][0] > 90 or route.route[0][0] < -90:
        changed = True
        new_route = []
        for pt in route.route:
            pt = proj.TransformPoint(pt[0], pt[1])
            new_route.append((pt[0], pt[1]))
        route.route = LineString(new_route)
        route.save()
if changed:
    print " * projections of routes corrected"