-- EMS LABOR BOT V2 PRO
-- SQL base sugerido para la siguiente etapa funcional

CREATE TABLE companies (
    id BIGSERIAL PRIMARY KEY,
    legal_name VARCHAR(200) NOT NULL,
    rut VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(150),
    phone VARCHAR(50),
    address TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role VARCHAR(40) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE workers (
    id BIGSERIAL PRIMARY KEY,
    company_id BIGINT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    rut VARCHAR(20) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    personal_email VARCHAR(150),
    phone VARCHAR(50),
    status VARCHAR(40) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE(company_id, rut)
);

CREATE TABLE worker_contracts (
    id BIGSERIAL PRIMARY KEY,
    company_id BIGINT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    worker_id BIGINT NOT NULL REFERENCES workers(id) ON DELETE CASCADE,
    contract_type VARCHAR(40) NOT NULL,
    contract_channel VARCHAR(40) NOT NULL DEFAULT 'individual',
    celebration_date DATE NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    salary_base NUMERIC(14,2) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    dt_registration_status VARCHAR(30) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE contract_amendments (
    id BIGSERIAL PRIMARY KEY,
    contract_id BIGINT NOT NULL REFERENCES worker_contracts(id) ON DELETE CASCADE,
    amendment_type VARCHAR(50) NOT NULL,
    subscription_date DATE NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    dt_registration_status VARCHAR(30) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE contract_terminations (
    id BIGSERIAL PRIMARY KEY,
    contract_id BIGINT NOT NULL REFERENCES worker_contracts(id) ON DELETE CASCADE,
    termination_cause_code VARCHAR(20) NOT NULL,
    termination_date DATE NOT NULL,
    deadline_date DATE,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    dt_registration_status VARCHAR(30) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE dt_jobs (
    id BIGSERIAL PRIMARY KEY,
    company_id BIGINT NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    worker_id BIGINT REFERENCES workers(id) ON DELETE SET NULL,
    job_type VARCHAR(40) NOT NULL,
    channel VARCHAR(40) NOT NULL,
    payload_json JSONB NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'queued',
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE dt_job_logs (
    id BIGSERIAL PRIMARY KEY,
    dt_job_id BIGINT NOT NULL REFERENCES dt_jobs(id) ON DELETE CASCADE,
    step_name VARCHAR(120) NOT NULL,
    step_status VARCHAR(40) NOT NULL,
    screenshot_path TEXT,
    raw_response TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_workers_company ON workers(company_id);
CREATE INDEX idx_contracts_worker ON worker_contracts(worker_id);
CREATE INDEX idx_dt_jobs_status ON dt_jobs(status);
