-- TIME Phase T2: Leitstand source import tables and canonical time-entry import scaffolding.

CREATE TABLE IF NOT EXISTS TIME.ls_activity_type (
    id INTEGER PRIMARY KEY,
    l_code VARCHAR(32),
    bez VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS TIME.ls_person (
    dbk VARCHAR(24) PRIMARY KEY,
    dbk_uid VARCHAR(7), upd VARCHAR(24), upd_anz INTEGER, upd_uid VARCHAR(7), status INTEGER,
    pers_nr INTEGER, last_name VARCHAR(35), first_name VARCHAR(25), category_dbk VARCHAR(24),
    salutation VARCHAR(15), title VARCHAR(30), street VARCHAR(36), postal_code VARCHAR(6), city VARCHAR(30),
    country_code VARCHAR(4), phone VARCHAR(30), fax VARCHAR(30), email VARCHAR(50), cost_per_hour NUMERIC(8,3), organization_dbk VARCHAR(24),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_ls_person_upd ON TIME.ls_person(upd);

CREATE TABLE IF NOT EXISTS TIME.ls_organization (
    dbk VARCHAR(24) PRIMARY KEY,
    dbk_uid VARCHAR(7), upd VARCHAR(24), upd_anz INTEGER, upd_uid VARCHAR(7), status INTEGER,
    betreu VARCHAR(2), name VARCHAR(255), street VARCHAR(50), postal_code VARCHAR(6), city VARCHAR(50), phone VARCHAR(30), fax VARCHAR(30), email VARCHAR(50), org_nr INTEGER, short_name VARCHAR(30),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_ls_organization_upd ON TIME.ls_organization(upd);

CREATE TABLE IF NOT EXISTS TIME.ls_contract (
    dbk VARCHAR(24) PRIMARY KEY,
    dbk_uid VARCHAR(7), upd VARCHAR(24), upd_anz INTEGER, upd_uid VARCHAR(7), status INTEGER,
    organization_dbk VARCHAR(24), lfdnr INTEGER, name VARCHAR(255), iref VARCHAR(255), eref VARCHAR(255), description VARCHAR(255), valid_from TIMESTAMP WITH TIME ZONE, valid_to TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_ls_contract_upd ON TIME.ls_contract(upd);
CREATE INDEX IF NOT EXISTS idx_ls_contract_org_dbk ON TIME.ls_contract(organization_dbk);

CREATE TABLE IF NOT EXISTS TIME.ls_contract_position (
    dbk VARCHAR(24) PRIMARY KEY,
    dbk_uid VARCHAR(7), upd VARCHAR(24), upd_anz INTEGER, upd_uid VARCHAR(7), status INTEGER,
    contract_dbk VARCHAR(24), lfdnr INTEGER, project_name VARCHAR(255), sales_price NUMERIC(18,4), purchase_price NUMERIC(18,4), description VARCHAR(255), valid_from TIMESTAMP WITH TIME ZONE, valid_to TIMESTAMP WITH TIME ZONE,
    name VARCHAR(255), iref VARCHAR(255), eref VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_ls_contract_position_upd ON TIME.ls_contract_position(upd);
CREATE INDEX IF NOT EXISTS idx_ls_contract_position_contract_dbk ON TIME.ls_contract_position(contract_dbk);

CREATE TABLE IF NOT EXISTS TIME.ls_cost_unit (
    dbk VARCHAR(24) PRIMARY KEY,
    dbk_uid VARCHAR(7), upd VARCHAR(24), upd_anz INTEGER, upd_uid VARCHAR(7), status INTEGER,
    person_dbk VARCHAR(24), organization_dbk VARCHAR(24), contract_dbk VARCHAR(24), legacy_relation_4_dbk VARCHAR(24), legacy_relation_5_dbk VARCHAR(24), contract_position_dbk VARCHAR(24),
    project_name VARCHAR(255), project_id INTEGER, project_task INTEGER, mcl_id VARCHAR(255), mcl_name VARCHAR(255), mcl_desc VARCHAR(255), valid_from TIMESTAMP WITH TIME ZONE, valid_to TIMESTAMP WITH TIME ZONE, effort_plan NUMERIC(10,3),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_ls_cost_unit_upd ON TIME.ls_cost_unit(upd);
CREATE INDEX IF NOT EXISTS idx_ls_cost_unit_contract_dbk ON TIME.ls_cost_unit(contract_dbk);

CREATE TABLE IF NOT EXISTS TIME.ls_task (
    dbk VARCHAR(24) PRIMARY KEY,
    dbk_uid VARCHAR(7), upd VARCHAR(24), upd_anz INTEGER, upd_uid VARCHAR(7), status INTEGER,
    primary_cost_unit_dbk VARCHAR(24), secondary_cost_unit_dbk VARCHAR(24), tertiary_cost_unit_dbk VARCHAR(24), parent_task_dbk VARCHAR(24),
    lfdnr INTEGER, task_type VARCHAR(1), mcl_id VARCHAR(255), mcl_name VARCHAR(255), mcl_desc VARCHAR(8000), valid_from TIMESTAMP WITH TIME ZONE, valid_to TIMESTAMP WITH TIME ZONE, effort_plan NUMERIC(10,1),
    last_work TIMESTAMP WITH TIME ZONE, completion_percent INTEGER, done_date TIMESTAMP WITH TIME ZONE, remark VARCHAR(255), n_rid INTEGER, legacy_90702_rid INTEGER, amount NUMERIC(18,4), created_source_at TIMESTAMP WITH TIME ZONE, legacy_90700_rid INTEGER, legacy_90699_rid INTEGER, quantity INTEGER,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_ls_task_upd ON TIME.ls_task(upd);
CREATE INDEX IF NOT EXISTS idx_ls_task_primary_cost_unit_dbk ON TIME.ls_task(primary_cost_unit_dbk);

CREATE TABLE IF NOT EXISTS TIME.ls_person_task_assignment (
    dbk VARCHAR(24) PRIMARY KEY,
    dbk_uid VARCHAR(7), upd VARCHAR(24), upd_anz INTEGER, upd_uid VARCHAR(7), status INTEGER,
    task_dbk VARCHAR(24), person_dbk VARCHAR(24), cost_unit_dbk VARCHAR(24), rtype VARCHAR(1), last_work TIMESTAMP WITH TIME ZONE, effort_plan NUMERIC(19,3),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_ls_person_task_assignment_upd ON TIME.ls_person_task_assignment(upd);

CREATE TABLE IF NOT EXISTS TIME.ls_time_recording (
    dbk VARCHAR(24) PRIMARY KEY,
    time_entry_id UUID UNIQUE REFERENCES TIME.time_entry(id) ON DELETE SET NULL,
    dbk_uid VARCHAR(7), upd VARCHAR(24), upd_anz INTEGER, upd_uid VARCHAR(7), status INTEGER,
    person_dbk VARCHAR(24), lfdnr INTEGER, record_type VARCHAR(32), mcl_id VARCHAR(255), mcl_desc VARCHAR(8000), recorded_from TIMESTAMP WITH TIME ZONE, recorded_to TIMESTAMP WITH TIME ZONE, effort NUMERIC(19,3), remark VARCHAR(8000), url VARCHAR(1000), activity_type_id INTEGER,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_ls_time_recording_upd ON TIME.ls_time_recording(upd);

CREATE TABLE IF NOT EXISTS TIME.ls_time_recording_assignment (
    dbk VARCHAR(24) PRIMARY KEY,
    dbk_uid VARCHAR(7), upd VARCHAR(24), upd_anz INTEGER, upd_uid VARCHAR(7), status INTEGER,
    person_task_assignment_dbk VARCHAR(24), time_recording_dbk VARCHAR(24), mcl_desc VARCHAR(8000), effort NUMERIC(19,3), remark VARCHAR(8000),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_ls_time_recording_assignment_upd ON TIME.ls_time_recording_assignment(upd);
