<?php
const tbl_file = db_prefix . 'file_tbl';
const tbl_usr = db_prefix . 'usr_tbl';
const tbl_price_list = db_prefix . 'price_list_tbl';
const tbl_work_order = db_prefix . 'work_order_tbl';
const tbl_profit = db_prefix . 'profit_tbl';
const tbl_debit_credit = db_prefix . 'debit_credit_tbl';
const tbl_zip_state = db_prefix . 'zip_state_tbl'; //Created on Feb 24
const tbl_user_docs = db_prefix . 'docs_tbl'; //Created on March 3
const tbl_notes = db_prefix . 'note_tbl'; //Created on May 3

$tbl_file_sql = /** @lang text */
    "CREATE TABLE IF NOT EXISTS " . tbl_file . " (
     id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     uid varchar(255) NOT NULL,
     file_name varchar(255) NOT NULL,
     status enum('true','false') NOT NULL DEFAULT 'true',
     date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";

$tbl_usr_sql = /** @lang text */
    "CREATE TABLE IF NOT EXISTS " . tbl_usr . " (
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    uid varchar(255) NOT NULL,
    authorize_token longtext NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    img_id varchar(255) NOT NULL DEFAULT '',    
    mail varchar(255) NOT NULL UNIQUE,
    alias longtext NOT NULL DEFAULT '',
    password varchar(255) NOT NULL DEFAULT '',
    reset_code varchar(255) NOT NULL DEFAULT '',
    reset_time varchar(255) NOT NULL DEFAULT '',
    reset_status enum('true','false') NOT NULL DEFAULT 'false',
    verification_code varchar(255) NOT NULL,
    verification_status enum('true','false') NOT NULL DEFAULT 'false',
    access_level ENUM('true','false','null') NOT NULL, 
    usr_type enum('admin', 'manager', 'inspector') NOT NULL,
    status enum('true','false','delete') NOT NULL DEFAULT 'true',
    date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";

$tbl_price_list_sql = /** @lang text */
    "CREATE TABLE IF NOT EXISTS " . tbl_price_list . " (
     id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     uid varchar(255) NOT NULL,
     work_code varchar(255) NOT NULL,
     amount varchar(255) NOT NULL,
     zip_code varchar(255) NOT NULL,
     status enum('true','false') NOT NULL DEFAULT 'true',
     date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP); 
    ALTER TABLE " . tbl_price_list . " ADD UNIQUE 
    unique_index (work_code, zip_code);";


$tbl_work_order_sql = /** @lang text */
    "CREATE TABLE IF NOT EXISTS " . tbl_work_order . " (
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    uid varchar(255) NOT NULL,
    work_order_id varchar(255) NOT NULL UNIQUE,
    invoice_id varchar(255) NOT NULL,
    work_code varchar(255) NOT NULL,
    amount varchar(255) NOT NULL,
    alias varchar(255) NOT NULL,
    address varchar(255) NOT NULL,
    city varchar(255) NOT NULL,
    zip_code varchar(255) NOT NULL,
    invoice_date varchar(255) NOT NULL,
    completion_date varchar(255) NOT NULL,
    status enum('true','false') NOT NULL DEFAULT 'true',
    date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";


$tbl_profit_sql = /** @lang text */
    "CREATE TABLE IF NOT EXISTS " . tbl_profit . " (
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    uid varchar(255) NOT NULL,
    work_order_id varchar(255) NOT NULL UNIQUE,
    check_no varchar(255) NOT NULL,
    work_code varchar(255) NOT NULL,
    expected_amount varchar(255) NOT NULL,
    actual_amount varchar(255) NOT NULL,
    main_vendor varchar(255) NOT NULL,
    sub_vendor varchar(255) NOT NULL,
    address varchar(255) NOT NULL,
    state varchar(255) NOT NULL,
    city varchar(255) NOT NULL,
    zone varchar(255) NOT NULL,
    zip_code varchar(255) NOT NULL,
    check_number varchar(255) NOT NULL,
    order_date varchar(255) NOT NULL,
    reassigned_date varchar(255) NOT NULL,
    complete_date varchar(255) NOT NULL,
    due_date varchar(255) NOT NULL,
    billed_date varchar(255) NOT NULL,
    check_date varchar(255) NOT NULL,
    status enum('true','false') NOT NULL DEFAULT 'true',
    date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";


$tbl_debit_credit_sql = /** @lang text */
    "CREATE TABLE IF NOT EXISTS " . tbl_debit_credit . " (
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    uid varchar(255) NOT NULL,
    usr_id varchar(255) NOT NULL,
    dc_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    dc_type enum('debit','credit') NOT NULL,
    dc_amount varchar(255) NOT NULL,
    dc_comment text NOT NULL,
    status enum('true','false') NOT NULL DEFAULT 'true',
    date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";