CREATE TABLE IF NOT EXISTS clients (
    id SERIAL PRIMARY KEY,
    bitrix_contact_id INTEGER UNIQUE,
    first_name VARCHAR(100) NOT NULL DEFAULT '',
    last_name VARCHAR(100) NOT NULL DEFAULT '',
    email VARCHAR(190),
    phone VARCHAR(50),
    city VARCHAR(120),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS dossiers (
    id SERIAL PRIMARY KEY,
    reference VARCHAR(100) NOT NULL UNIQUE,
    bitrix_deal_id INTEGER UNIQUE,
    franchise_id INTEGER NOT NULL,
    source_ambassador_user_id INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'received',
    status_label VARCHAR(190) NULL,
    bitrix_stage_id VARCHAR(100) NULL,
    loan_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
    estimated_commission_total NUMERIC(12,2) NOT NULL DEFAULT 0,
    validated_commission_total NUMERIC(12,2) NOT NULL DEFAULT 0,
    signed_at TIMESTAMP NULL,
    paid_at TIMESTAMP NULL,
    closed_at TIMESTAMP NULL,
    closed_reason TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_dossiers_status CHECK (status IN ('received','in_study','quoted','signed','paid','close'))
);

CREATE TABLE IF NOT EXISTS dossier_clients (
    id SERIAL PRIMARY KEY,
    dossier_id INTEGER NOT NULL REFERENCES dossiers(id) ON DELETE CASCADE,
    client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL DEFAULT 'primary',
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_dossier_clients_role CHECK (role IN ('primary','co_borrower')),
    CONSTRAINT uq_dossier_client UNIQUE (dossier_id, client_id)
);

CREATE TABLE IF NOT EXISTS dossier_status_history (
    id SERIAL PRIMARY KEY,
    dossier_id INTEGER NOT NULL REFERENCES dossiers(id) ON DELETE CASCADE,
    status VARCHAR(50) NOT NULL,
    status_label VARCHAR(190) NULL,
    source VARCHAR(100) NOT NULL DEFAULT 'system',
    changed_at TIMESTAMP NOT NULL DEFAULT NOW(),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_dossier_status_history_status CHECK (status IN ('received','in_study','quoted','signed','paid','close'))
);

CREATE TABLE IF NOT EXISTS commissions (
    id SERIAL PRIMARY KEY,
    dossier_id INTEGER NOT NULL REFERENCES dossiers(id) ON DELETE CASCADE,
    beneficiary_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    source_ambassador_user_id INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    level INTEGER NOT NULL,
    gross_amount NUMERIC(12,2) NOT NULL DEFAULT 0,
    validated_amount NUMERIC(12,2) NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    validation_notes TEXT NULL,
    validated_by_user_id INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    paid_by_user_id INTEGER NULL REFERENCES users(id) ON DELETE SET NULL,
    validation_date TIMESTAMP NULL,
    payment_date TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_commissions_level CHECK (level BETWEEN 1 AND 4),
    CONSTRAINT chk_commissions_status CHECK (status IN ('pending','validated','paid','rejected')),
    CONSTRAINT uq_commission_dossier_beneficiary_level UNIQUE (dossier_id, beneficiary_user_id, level)
);

CREATE INDEX IF NOT EXISTS idx_clients_email ON clients(email);
CREATE INDEX IF NOT EXISTS idx_dossiers_franchise_id ON dossiers(franchise_id);
CREATE INDEX IF NOT EXISTS idx_dossiers_source_ambassador_user_id ON dossiers(source_ambassador_user_id);
CREATE INDEX IF NOT EXISTS idx_dossiers_status ON dossiers(status);
CREATE INDEX IF NOT EXISTS idx_dossier_clients_dossier_id ON dossier_clients(dossier_id);
CREATE INDEX IF NOT EXISTS idx_dossier_clients_client_id ON dossier_clients(client_id);
CREATE INDEX IF NOT EXISTS idx_dossier_status_history_dossier_id ON dossier_status_history(dossier_id);
CREATE INDEX IF NOT EXISTS idx_commissions_beneficiary_user_id ON commissions(beneficiary_user_id);
CREATE INDEX IF NOT EXISTS idx_commissions_source_ambassador_user_id ON commissions(source_ambassador_user_id);
CREATE INDEX IF NOT EXISTS idx_commissions_status ON commissions(status);
CREATE INDEX IF NOT EXISTS idx_commissions_dossier_id ON commissions(dossier_id);
