-- 017_portfolio_transfer.sql

-- Colonnes sur users pour la synchro Bitrix
ALTER TABLE users ADD COLUMN IF NOT EXISTS bitrix_user_id INTEGER;
ALTER TABLE users ADD COLUMN IF NOT EXISTS bitrix_contact_id INTEGER;

-- Audit du transfert (une ligne par opération de transfert)
CREATE TABLE IF NOT EXISTS portfolio_transfers (
    id SERIAL PRIMARY KEY,
    from_user_id INTEGER NOT NULL REFERENCES users(id),
    initiated_by_user_id INTEGER NOT NULL REFERENCES users(id),
    transferred_at TIMESTAMP NOT NULL DEFAULT NOW(),
    notes TEXT
);

-- Détail de chaque entité transférée (qui va où + statut synchro Bitrix)
CREATE TABLE IF NOT EXISTS portfolio_transfer_assignments (
    id SERIAL PRIMARY KEY,
    transfer_id INTEGER NOT NULL REFERENCES portfolio_transfers(id),
    to_user_id INTEGER NOT NULL REFERENCES users(id),
    entity_type VARCHAR(20) NOT NULL CHECK (entity_type IN ('ambassador', 'client')),
    entity_id INTEGER NOT NULL,
    bitrix_synced BOOLEAN NOT NULL DEFAULT FALSE,
    bitrix_error TEXT
);

-- Suivi des emails post-transfert
CREATE TABLE IF NOT EXISTS portfolio_transfer_communications (
    id SERIAL PRIMARY KEY,
    transfer_id INTEGER NOT NULL REFERENCES portfolio_transfers(id),
    recipient_user_id INTEGER REFERENCES users(id),
    recipient_email VARCHAR(255) NOT NULL,
    group_type VARCHAR(30) NOT NULL CHECK (group_type IN ('ambassador_active', 'ambassador_pending', 'client')),
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'failed')),
    cc_user_id INTEGER REFERENCES users(id),
    sent_at TIMESTAMP,
    attempts INTEGER NOT NULL DEFAULT 0,
    last_error TEXT,
    UNIQUE (transfer_id, recipient_email, group_type)
);

CREATE INDEX IF NOT EXISTS idx_ptransfer_from_user ON portfolio_transfers(from_user_id);
CREATE INDEX IF NOT EXISTS idx_ptassign_transfer_id ON portfolio_transfer_assignments(transfer_id);
CREATE INDEX IF NOT EXISTS idx_ptcomm_transfer_status ON portfolio_transfer_communications(transfer_id, status);
