public class DB_SCHEME {

    public class usuario{
        public static final String TABLE_NAME = "usuario";

        public static final String _id = "_id";
        public static final String CN_NAME = "nombre";
        public static final String CN_LASTNAME = "apellidos";
        public static final String CN_USER = "usuario";
        public static final String CN_PASS = "contrasenia";
        public static final String CN_ROLE = "rol";
        public static final String CN_CREATE = "creacion";

    /*
        create table usuario(
                                cod_usuario integer primary key autoincrement,
                                nombre text not null,
                                apellidos text not null,
                                usuario text not null,
                                contraseña text not null,
                                rol text not null,
                                creacion integer not null check(creacion=0 or creacion=1));

     */

        public static final String CREATE_TABLE = "create table " +TABLE_NAME+ "("
                +_id+ " integer primary key autoincrement,"
                +CN_NAME+ " text not null,"
                +CN_LASTNAME+ " text not null,"
                +CN_USER+ " text not null,"
                +CN_PASS+ " text not null,"
                +CN_ROLE+ " text not null,"
                +CN_CREATE+ " integer not null check("+CN_CREATE+"=0 or "+CN_CREATE+"=1));";
    }

    public class cliente{
        public static final String TABLE_NAME = "cliente";

        public static final String _id = "_id";
        public static final String CN_NAME ="nombre_cliente";

    /*
        create table cliente(
                                cod_cliente integer primary key autoincrement,
                                nombre_cliente text not null
                            )

     */

        public static final String CREATE_TABLE = "create table " +TABLE_NAME+ "("
                +_id+ " integer primary key autoincrement,"
                +CN_NAME+ " text not null);";
    }

    public class centro_trabajo{
        public static final String TABLE_NAME = "centro_trabajo";

        public static final String _id = "_id";
        public static final String CN_NAME = "nombre";
        public static final String CN_ADDRESS = "direccion";
        public static final String CN_CITY = "localidad";
        public static final String CN_PROVINCE = "provincia";
        public static final String CN_ZIP = "CP";
        public static final String CN_TELEPHONE = "telefono";

        /*
            create table centro_trabajo(
                                        cod_ct integer primary key autoincrement,
                                        nombre text not null,
                                        direccion text not null,
                                        localidad text not null,
                                        provincia text not null,
                                        CP integer not null,
                                        telefono integer not null
                                        )

         */

        public static final String CREATE_TABLE = "create table "+TABLE_NAME+"( "
                +_id+" integer primary key autoincrement,"
                +CN_NAME+" text not null,"
                +CN_ADDRESS+" text not null,"
                +CN_CITY+" text not null,"
                +CN_PROVINCE+" text not null,"
                +CN_ZIP+" integer not null,"
                +CN_TELEPHONE+" integer not null);";
    }

    public class orden_trabajo{
        public static final String TABLE_NAME = "orden_trabajo";

        public static final String _id = "_id";
        public static final String _id_COSTUMER = "cod_cliente";
        public static final String _id_WORKCENTER = "cod_c_trabajo";
        public static final String CN_DESCRIPTION = "descripcion";
        public static final String CN_CONTACT = "persona_contacto";
        public static final String CN_DATE = "fecha";
        public static final String CN_TIME = "hora";
        public static final String CN_TIME_LIMIT = "hora_limite";
        public static final String CN_STATE = "estado";
        public static final String CN_LATITUDE = "latitud";
        public static final String CN_LONGITUD = "longitud";



        /*
            create table orden_trabajo(
                                        num_pedido integer primary key autoincrement,
                                        cod_cliente integer not null,
                                        cod_c_trabajo integer not null,
                                        descripcion text not null,
                                        persona_contacto text not null,
                                        fecha text not null,
                                        hora text not null,
                                        hora_limite text not null,
                                        estado text not null,
                                        latitud integer not null,
                                        longitud integer not null,
                                        foreign key(cod_cliente) references cliente(cod_cliente),
                                        foreign key(cod_c_trabajo) references centro_trabajo(cod_ct)
                                        );
         */

        public static final String CREATE_TABLE = "create table "+TABLE_NAME+"("
                +_id+" integer primary key autoincrement,"
                +_id_COSTUMER+" integer not null,"
                +_id_WORKCENTER+" integer not null,"
                +CN_DESCRIPTION+" text not null,"
                +CN_CONTACT+" text not null,"
                +CN_DATE+" text not null,"
                +CN_TIME+" text not null,"
                +CN_TIME_LIMIT+" text not null,"
                +CN_STATE+" text not null,"
                +CN_LATITUDE+" integer not null,"
                +CN_LONGITUD+" integer not null, " +
                "foreign key("+_id_COSTUMER+") references "+cliente.TABLE_NAME+"("+cliente._id+
                ")," +
                "foreign key("+_id_WORKCENTER+") references "+centro_trabajo.TABLE_NAME+
                "("+centro_trabajo._id+"));";
    }

    public class seguimiento_ot{
        public static final String TABLE_NAME = "seguimiento_ot";

        public static final String _id = "_id";
        public static final String _id_USER = "id_usr";
        public static final String CN_STATE = "estado";
        public static final String CN_DATE = "fecha";
        public static final String CN_TIME = "hora";
        public static final String CN_OBSERVATIONS = "observaciones";
        public static final String CN_LATITUDE = "latitud";
        public static final String CN_LONGITUDE = "longitud";

        /*
            create table seguimiento_ot(
                                        id_ot integer not null,
                                        id_usr integer not null,
                                        estado text not null,
                                        fecha text not null,
                                        hora text not null,
                                        observaciones text not null,
                                        latitud integer not null,
                                        longitud integer not null,
                                        primary key(id_ot,fecha,hora),
                                        foreign key(id_ot) references orden_trabajo(cod_ct),
                                        foreign key(id_usr) references usuario(cod_usuario));
         */

        public static final String CREATE_TABLE = "create table "+TABLE_NAME+"("
                +_id+" integer not null,"
                +_id_USER+" integer not null,"
                +CN_STATE+" text not null,"
                +CN_DATE+" text not null,"
                +CN_TIME+" text not null,"
                +CN_OBSERVATIONS+" text not null,"
                +CN_LATITUDE+" integer not null,"
                +CN_LONGITUDE+" integer not null,"+
                "primary key("+_id+","+CN_DATE+","+CN_TIME+"),"+
                "foreign key("+_id+") references "+orden_trabajo.TABLE_NAME+
                "("+orden_trabajo._id+")," +
                "foreign key("+_id_USER+") references "+usuario.TABLE_NAME+"("+usuario._id+"));"
                ;

    }
}