"""
    SQLAlchemy Database tables model for pvl.verkko
"""

import sqlalchemy
from sqlalchemy import *

import logging; log = logging.getLogger('pvl.verkko.db')

# schema
metadata = MetaData()

dhcp_hosts = Table('dhcp_hosts', metadata,
    Column('id',            Integer,    primary_key=True),

    # unique
    Column('ip',            String,     nullable=False),
    Column('mac',           String,     nullable=False),
    Column('gw',            String,     nullable=False),
    
    # updated
    Column('first_seen',    DateTime,   nullable=False),
    Column('last_seen',     DateTime,   nullable=False),
    
    # scalar; updated
    Column('name',          String,     nullable=True),
    Column('state',         String,     nullable=True),
    Column('error',         String,     nullable=True),
    
    # counters
    Column('count',         Integer,    default=1),

    UniqueConstraint('ip', 'mac', 'gw'),
)

dhcp_leases = Table('dhcp_leases', metadata,
    Column('id',            Integer,    primary_key=True),

    Column('ip',            String,     nullable=False),
    Column('mac',           String,     nullable=False),
    Column('hostname',      String,     nullable=True),

    Column('starts',        DateTime,   nullable=False),
    Column('ends',          DateTime,   nullable=True),  # never
    # TODO: renewed

    Column('state',         String,     nullable=True),
    Column('next',          String,     nullable=True),
)

wlan_sta = Table('wlan_sta', metadata,
    Column('id',            Integer,    primary_key=True),
    
    Column('ap',            String,     nullable=False),
    Column('wlan',          String,     nullable=False),
    Column('sta',           String,     nullable=False),
    
    # updated
    Column('first_seen',    DateTime,   nullable=False),
    Column('last_seen',     DateTime,   nullable=False),

    Column('count',         Integer,    default=1),
    Column('msg',           String,     nullable=True),
           
    UniqueConstraint('ap', 'wlan', 'sta'),
)

# for ORM models
from sqlalchemy.orm import mapper, sessionmaker

Session = sessionmaker()

class Database (object) :
    """
        Our underlying database.
    """
    
    def __init__ (self, database) :
        """
            database        - sqlalchemy connection URI
        """

        self.engine = create_engine(database, 
            echo    = (log.isEnabledFor(logging.DEBUG)),
        )

    # ORM
    def session (self) :
        """
            Return a new ORM session bound to our engine.

            XXX: session lifetimes? Explicit close?
        """

        return Session(bind=self.engine)
   
    # SQL
    def connect (self) :
        return self.engine.connect()

    def execute (self, query) :
        return self.engine.execute(query)

    def select (self, query) :
        return self.engine.execute(query)

    def get (self, query) :
        """
            Fetch a single row.
            
            XXX: returning None if not found
        """

        return self.select(query).fetchone()

    def insert (self, insert) :
        """
            Execute given INSERT query, returning the inserted primary key.
        """

        result = self.engine.execute(insert)

        id, = result.inserted_primary_key
        
        return id

    def update (self, update) :
        """
            Execute given UPDATE query, returning the number of matched rows.
        """

        result = self.engine.execute(update)
        
        return result.rowcount

# command-line
import optparse
import sys

def parser (parser, table=None) :
    parser.set_defaults(
            create_table    = table,
    )
    parser = optparse.OptionGroup(parser, "Verkko Database")
    parser.add_option('--database',             metavar='URI',
            help="Connect to given database")

    if table is not None :
        parser.add_option('--create',               action='store_true',
                help="Initialize database")
    
    return parser

def apply (options, required=True) :
    # db
    if not options.database and required :
        log.error("No database given")
        sys.exit(1)

    log.info("Open up database: %s", options.database)
    db = Database(options.database)

    if options.create_table is not None and options.create :
        log.info("Creating database tables: %s", options.create_table)
        options.create_table.create(db.engine, checkfirst=True)

    return db