-- ============================================================================
-- Migration v6: multiple rent payments per member per month + related fixes
-- Run this ONCE on your existing database (phpMyAdmin > SQL tab).
-- Safe to run more than once.
-- ============================================================================

CREATE TABLE IF NOT EXISTS rent_payment_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mess_id INT NOT NULL,
    member_id INT NOT NULL,
    bill_month VARCHAR(7) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    payment_date DATE DEFAULT NULL,
    payment_method VARCHAR(50) DEFAULT NULL,
    receipt_no VARCHAR(100) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mess_id) REFERENCES mess_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
    KEY idx_member_month (member_id, bill_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backfill: every existing rent_payments row with paid_amount > 0 becomes its
-- first payment entry, so no payment history already in your system is lost
-- or hidden by this upgrade. Only runs for rows that don't already have a
-- matching entry (safe to re-run).
INSERT INTO rent_payment_entries (mess_id, member_id, bill_month, amount, payment_date, payment_method, receipt_no)
SELECT rp.mess_id, rp.member_id, rp.bill_month, rp.paid_amount, rp.payment_date, rp.payment_method, rp.receipt_no
FROM rent_payments rp
WHERE rp.paid_amount > 0
  AND NOT EXISTS (
      SELECT 1 FROM rent_payment_entries e
      WHERE e.member_id = rp.member_id AND e.bill_month = rp.bill_month
  );

-- ---------------------------------------------------------------------------
-- Ad campaign scheduling: daily time window + minimum repeat interval
-- ---------------------------------------------------------------------------
ALTER TABLE ad_campaigns
    ADD COLUMN IF NOT EXISTS daily_start_time TIME DEFAULT NULL AFTER end_datetime,
    ADD COLUMN IF NOT EXISTS daily_end_time TIME DEFAULT NULL AFTER daily_start_time,
    ADD COLUMN IF NOT EXISTS repeat_interval_seconds INT NOT NULL DEFAULT 0 AFTER daily_end_time;