ALTER TABLE users
    MODIFY role ENUM('admin', 'manager', 'agent') NOT NULL DEFAULT 'manager';

ALTER TABLE reservations
    ADD COLUMN IF NOT EXISTS source ENUM('airbnb', 'booking', 'private', 'website', 'other') NOT NULL DEFAULT 'private' AFTER status,
    ADD COLUMN IF NOT EXISTS agent_id INT UNSIGNED NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS gross_amount DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER total_amount,
    ADD COLUMN IF NOT EXISTS discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER gross_amount,
    ADD COLUMN IF NOT EXISTS final_amount DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER discount_amount,
    ADD COLUMN IF NOT EXISTS amount_paid DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER final_amount,
    ADD COLUMN IF NOT EXISTS amount_remaining DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER amount_paid,
    ADD COLUMN IF NOT EXISTS payment_status ENUM('pending', 'partial', 'paid') NOT NULL DEFAULT 'pending' AFTER amount_remaining,
    ADD INDEX IF NOT EXISTS idx_reservations_source (source),
    ADD INDEX IF NOT EXISTS idx_reservations_agent (agent_id);

ALTER TABLE payments
    MODIFY status ENUM('pending', 'partial', 'paid', 'cancelled') NOT NULL DEFAULT 'pending';

ALTER TABLE notifications
    MODIFY type ENUM(
        'new_reservation',
        'arrival_today',
        'arrival_tomorrow',
        'arrival_upcoming',
        'departure_today',
        'departure_tomorrow',
        'departure_upcoming',
        'pending_payment',
        'overdue_payment',
        'remaining_balance',
        'important_alert'
    ) NOT NULL;

CREATE TABLE IF NOT EXISTS expenses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    expense_date DATE NOT NULL,
    category ENUM('salaries', 'electricity', 'water', 'internet', 'advertising', 'cleaning', 'maintenance', 'other') NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    description TEXT NULL,
    property_id INT UNSIGNED NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_expenses_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL,
    CONSTRAINT fk_expenses_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_expenses_date (expense_date),
    INDEX idx_expenses_category (category),
    INDEX idx_expenses_property (property_id)
);

CREATE TABLE IF NOT EXISTS long_term_tenants (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(180) NOT NULL,
    phone VARCHAR(60) NULL,
    email VARCHAR(160) NULL,
    identity_number VARCHAR(120) NULL,
    address VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_ltt_name (full_name)
);

CREATE TABLE IF NOT EXISTS long_term_contracts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    monthly_rent DECIMAL(10,2) NOT NULL DEFAULT 0,
    deposit_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    observations TEXT NULL,
    status ENUM('active', 'finished', 'terminated') NOT NULL DEFAULT 'active',
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_ltc_tenant FOREIGN KEY (tenant_id) REFERENCES long_term_tenants(id) ON DELETE RESTRICT,
    CONSTRAINT fk_ltc_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE RESTRICT,
    CONSTRAINT fk_ltc_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_ltc_status (status),
    INDEX idx_ltc_dates (start_date, end_date)
);

CREATE TABLE IF NOT EXISTS long_term_payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contract_id INT UNSIGNED NOT NULL,
    paid_month CHAR(7) NOT NULL,
    due_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    amount_paid DECIMAL(10,2) NOT NULL DEFAULT 0,
    amount_remaining DECIMAL(10,2) NOT NULL DEFAULT 0,
    payment_date DATE NULL,
    status ENUM('unpaid', 'partial', 'paid') NOT NULL DEFAULT 'unpaid',
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_ltp_contract FOREIGN KEY (contract_id) REFERENCES long_term_contracts(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_ltp_contract_month (contract_id, paid_month),
    INDEX idx_ltp_month (paid_month),
    INDEX idx_ltp_status (status)
);

CREATE TABLE IF NOT EXISTS notification_settings (
    id TINYINT UNSIGNED PRIMARY KEY DEFAULT 1,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    arrival_days_before TINYINT UNSIGNED NOT NULL DEFAULT 1,
    alert_new_reservation TINYINT(1) NOT NULL DEFAULT 1,
    alert_arrival TINYINT(1) NOT NULL DEFAULT 1,
    alert_departure TINYINT(1) NOT NULL DEFAULT 1,
    alert_overdue_payment TINYINT(1) NOT NULL DEFAULT 1,
    alert_remaining_balance TINYINT(1) NOT NULL DEFAULT 1,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT IGNORE INTO notification_settings (id) VALUES (1);