-- User Table
CREATE TABLE IF NOT EXISTS 'user' (
    'id' INTEGER NOT NULL  PRIMARY KEY AUTOINCREMENT,
    'remote_id' INTEGER DEFAULT 0,
    'host' VARCHAR(35) DEFAULT 0,
    'port' INTEGER DEFAULT 0,
    'database' VARCHAR(35) DEFAULT NULL,
    'username' VARCHAR(35) DEFAULT NULL,
    'password' VARCHAR(35) DEFAULT NULL
);

-- Partner Table
CREATE TABLE IF NOT EXISTS 'partner' (
    'id' INTEGER DEFAULT NULL PRIMARY KEY AUTOINCREMENT,
    'remote_id' INTEGER DEFAULT 0,
    'modified' INTEGER DEFAULT 0,
    'modified_date' TEXT DEFAULT CURRENT_TIMESTAMP,
    'name' VARCHAR(35) DEFAULT NULL,
    'city' VARCHAR(35) DEFAULT NULL,
    'mobile' VARCHAR(20) DEFAULT NULL,
    'phone' VARCHAR(20) DEFAULT NULL,
    'fax' VARCHAR(20) DEFAULT NULL,
    'email' VARCHAR(100) DEFAULT NULL,
    'street' VARCHAR(35) DEFAULT NULL,
    'street2' VARCHAR(35) DEFAULT NULL,
    'image_medium' BLOB DEFAULT NULL,
    'image_small' BLOB DEFAULT NULL,
    'comment' VARCHAR(160) DEFAULT NULL,
    'customer' INTEGER DEFAULT 0,
    'employee' INTEGER DEFAULT 0,
    'is_company' INTEGER DEFAULT 0,
    'debit' INTEGER DEFAULT 0,
    'debit_limit' INTEGER DEFAULT 0,
    'opportunity_count' INTEGER DEFAULT 0,
    'contracts_count' INTEGER DEFAULT 0,
    'journal_item_count' INTEGER DEFAULT 0,
    'meeting_count' INTEGER DEFAULT 0,
    'phonecall_count' INTEGER DEFAULT 0,
    'sale_order_count' INTEGER DEFAULT NULL,
    'total_invoiced' INTEGER DEFAULT NULL
);


-- Lead Table
CREATE TABLE IF NOT EXISTS 'crm_lead' (
    'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    'remote_id' INTEGER DEFAULT 0,
    'modified' INTEGER DEFAULT 0,
    'modified_date' TEXT DEFAULT CURRENT_TIMESTAMP,
    'name' VARCHAR(35) DEFAULT NULL,
    'description' VARCHAR(100) DEFAULT NULL,
    'contact_name' VARCHAR(100) DEFAULT NULL,
    'phone' VARCHAR(20) DEFAULT NULL,
    'mobile' VARCHAR(20) DEFAULT NULL,
    'fax' VARCHAR(20) DEFAULT NULL,
    'street' VARCHAR(35) DEFAULT NULL,
    'street2' VARCHAR(35) DEFAULT NULL,
    'meeting_count' INTEGER DEFAULT 0,
    'message_bounce' INTEGER DEFAULT 0,
    'planned_cost' INTEGER DEFAULT 0,
    'planned_revenue' INTEGER DEFAULT 0,
    'priority' INTEGER DEFAULT 0,
    'probability' INTEGER DEFAULT 0,
    'type' VARCHAR(20) DEFAULT NULL,
    'stage_id' INTEGER DEFAULT NULL,
    'user_id' INTEGER DEFAULT 0,
    'partner_id' INTEGER DEFAULT 0
);

-- CRM Stages Table
CREATE TABLE IF NOT EXISTS 'crm_stage' (
    'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    'remote_id' INTEGER DEFAULT 0,
    'modified' INTEGER DEFAULT 0,
    'modified_date' TEXT DEFAULT CURRENT_TIMESTAMP,
    'name' VARCHAR(35) DEFAULT NULL,
    'probability' REAL DEFAULT 0,
    'type' VARCHAR(100) DEFAULT 'both'
);