-- Migracao 008: habilita operacao manual de assinaturas e cobrancas

USE saas_barbearia;

ALTER TABLE memberships
    ADD COLUMN IF NOT EXISTS payment_mode ENUM('manual', 'celcoin') NOT NULL DEFAULT 'manual' AFTER subscription_id,
    ADD COLUMN IF NOT EXISTS external_subscription_id VARCHAR(255) NULL AFTER payment_mode;

ALTER TABLE membership_cycles
    ADD COLUMN IF NOT EXISTS source VARCHAR(50) NOT NULL DEFAULT 'manual' AFTER transaction_id,
    ADD COLUMN IF NOT EXISTS external_transaction_id VARCHAR(255) NULL AFTER source;

ALTER TABLE accounts_receivable
    ADD COLUMN IF NOT EXISTS membership_cycle_id INT NULL AFTER membership_id,
    ADD COLUMN IF NOT EXISTS source VARCHAR(50) NOT NULL DEFAULT 'manual' AFTER transaction_id,
    ADD COLUMN IF NOT EXISTS external_transaction_id VARCHAR(255) NULL AFTER source;

SET @fk_exists = (
    SELECT COUNT(*)
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME = 'accounts_receivable'
      AND CONSTRAINT_NAME = 'fk_accounts_receivable_membership_cycle'
);

SET @fk_sql = IF(
    @fk_exists = 0,
    'ALTER TABLE accounts_receivable ADD CONSTRAINT fk_accounts_receivable_membership_cycle FOREIGN KEY (membership_cycle_id) REFERENCES membership_cycles(id) ON DELETE SET NULL',
    'SELECT 1'
);

PREPARE membership_cycle_fk_stmt FROM @fk_sql;
EXECUTE membership_cycle_fk_stmt;
DEALLOCATE PREPARE membership_cycle_fk_stmt;

CREATE INDEX IF NOT EXISTS idx_memberships_payment_mode
    ON memberships(company_id, payment_mode, status);

CREATE INDEX IF NOT EXISTS idx_membership_cycles_source
    ON membership_cycles(membership_id, source, status);

CREATE INDEX IF NOT EXISTS idx_accounts_receivable_membership_cycle
    ON accounts_receivable(company_id, membership_cycle_id, status);