import os
import shutil
import sqlite3
from datetime import datetime, timedelta, timezone


def expire_files():
    """
    Runs every 60 minutes via APScheduler.

    Handles two distinct auto-deletion policies:

    1. ANONYMOUS USERS (user_id IS NULL)
       Deletes uploads older than `anon_auto_delete_hours` from site_settings
       (admin-configurable, default 24 h).

    2. LOGGED-IN USERS (user_id IS NOT NULL)
       Deletes uploads older than the user's plan `auto_delete_hours`.
       A plan value of 0 means "never auto-delete" (e.g. premium plans).
       Falls back to plan 1 (free plan) if the user has no plan assigned.

    For every expired upload the function:
      - Deletes the physical file/folder from static/uploads/
      - Soft-deletes the file_uploads record (sets deleted_at) so it moves
        to the "deleted" tab in the admin panel
      - Marks any still-pending/started conversions as 'expired'
    """
    _base = os.path.dirname(os.path.realpath(__file__))
    db_path = os.path.join(_base, "storage", "sqlite.db")
    upload_dir = os.path.join(_base, "static", "uploads")

    if not os.path.exists(upload_dir):
        return

    # ── 1. Read anon threshold from site_settings ─────────────────────────────
    anon_max_hours = 24
    try:
        with sqlite3.connect(db_path) as _c:
            row = _c.execute(
                "SELECT value FROM site_settings WHERE key='anon_auto_delete_hours'"
            ).fetchone()
            if row and row[0]:
                anon_max_hours = int(row[0])
    except Exception:
        pass

    # ── 2. Find expired uploads and delete them ───────────────────────────────
    try:
        with sqlite3.connect(db_path) as conn:
            conn.row_factory = sqlite3.Row

            # ── Anonymous uploads ─────────────────────────────────────────────
            anon_cutoff = (
                datetime.now(timezone.utc) - timedelta(hours=anon_max_hours)
            ).strftime('%Y-%m-%d %H:%M:%S')

            anon_rows = conn.execute(
                """
                SELECT id, file_path, file_size, user_id
                FROM file_uploads
                WHERE user_id IS NULL
                  AND deleted_at IS NULL
                  AND uploaded_at < ?
                """,
                (anon_cutoff,)
            ).fetchall()

            # ── Logged-in uploads: join through users → plans ─────────────────
            # COALESCE(u.plan_id, 1) falls back to the free plan.
            # auto_delete_hours = 0 means never auto-delete → excluded by filter.
            # Filtering is done in Python so each row can use its own plan threshold.
            now_utc = datetime.now(timezone.utc)
            expired_user_rows = []

            user_rows_with_time = conn.execute(
                """
                SELECT fu.id,
                       fu.file_path,
                       fu.file_size,
                       fu.user_id,
                       fu.uploaded_at,
                       COALESCE(p.auto_delete_hours, 24) AS plan_hours
                FROM file_uploads fu
                JOIN users u ON u.id = fu.user_id
                JOIN plans p ON p.id = COALESCE(u.plan_id, 1)
                WHERE fu.user_id IS NOT NULL
                  AND fu.deleted_at IS NULL
                  AND COALESCE(p.auto_delete_hours, 24) > 0
                """
            ).fetchall()

            for row in user_rows_with_time:
                plan_hours = int(row['plan_hours'] or 24)
                cutoff_dt = now_utc - timedelta(hours=plan_hours)
                try:
                    uploaded_at = datetime.strptime(
                        row['uploaded_at'], '%Y-%m-%d %H:%M:%S'
                    ).replace(tzinfo=timezone.utc)
                    if uploaded_at < cutoff_dt:
                        expired_user_rows.append(row)
                except Exception:
                    pass

            all_expired = list(anon_rows) + expired_user_rows
            if not all_expired:
                return

            ids_deleted = []
            for row in all_expired:
                file_path = row['file_path'] or ''
                if file_path:
                    folder_name = os.path.dirname(file_path)
                    if folder_name:
                        folder_abs = os.path.join(upload_dir, folder_name)
                        if os.path.isdir(folder_abs):
                            try:
                                shutil.rmtree(folder_abs, ignore_errors=True)
                            except OSError:
                                pass
                    else:
                        full_path = os.path.join(upload_dir, file_path)
                        if os.path.isfile(full_path):
                            try:
                                os.remove(full_path)
                            except OSError:
                                pass
                ids_deleted.append(row['id'])

            if ids_deleted:
                ph = ','.join('?' * len(ids_deleted))

                # Soft-delete file_uploads (moves to "deleted" tab in admin)
                conn.execute(
                    f"UPDATE file_uploads SET deleted_at=datetime('now') "
                    f"WHERE id IN ({ph})",
                    ids_deleted
                )

                # Mark any still-pending/started conversions as expired
                conn.execute(
                    f"""UPDATE conversions
                        SET status='expired',
                            error_message='Upload file auto-deleted by retention policy',
                            completed_at=COALESCE(completed_at, datetime('now'))
                        WHERE upload_id IN ({ph})
                          AND status IN ('pending', 'started')""",
                    ids_deleted
                )

                conn.commit()

                # Log each auto-deleted file via shared helper (imported inside function
                # to avoid circular imports at module load time)
                from helpers import log_deletion as _log_deletion
                for _row in all_expired:
                    try:
                        _fname = os.path.basename(_row['file_path'] or '') or str(_row['id'])
                        _log_deletion(
                            'file', entity_id=_row['id'], entity_name=_fname,
                            actor_username='Auto-Delete', actor_role='scheduler',
                            file_size_bytes=_row['file_size'],
                            entity_owner_user_id=_row['user_id'],
                            extra_meta={'upload_id': _row['id'], 'auto_expire': True}
                        )
                    except Exception:
                        pass

    except Exception:
        pass


def downgrade_expired_subscriptions():
    """
    Runs every 60 minutes via APScheduler.

    Finds all paid-plan users whose subscription_expires_at has passed
    and automatically downgrades them to the free plan (lowest price plan).
    Also marks their subscription_status as 'expired'.
    """
    _base = os.path.dirname(os.path.realpath(__file__))
    db_path = os.path.join(_base, "storage", "sqlite.db")

    try:
        with sqlite3.connect(db_path) as conn:
            conn.row_factory = sqlite3.Row

            # Find the free plan id (price_monthly = 0, lowest id)
            free_plan = conn.execute(
                "SELECT id FROM plans WHERE price_monthly=0 ORDER BY id ASC LIMIT 1"
            ).fetchone()
            if not free_plan:
                return
            free_plan_id = free_plan[0]

            # Find users whose subscription has expired and still on a paid plan.
            # Use date('now') so that a date-only expiry of "2026-03-27" is
            # not triggered until 2026-03-28 (i.e. the full day is given).
            # datetime-stamped entries (YYYY-MM-DD HH:MM:SS) are handled by
            # comparing the full datetime against 'now'.
            #
            # Skip users whose payment gateway is actively managing their
            # subscription (auto_renew=1 with a gateway subscription ID) —
            # the gateway will renew them and update subscription_expires_at.
            # Users with a stale gateway ID but auto_renew=0 ARE downgraded.
            now_dt = datetime.now(timezone.utc).strftime('%Y-%m-%d %H:%M:%S')
            expired_users = conn.execute(
                """
                SELECT u.id, u.username, u.email, u.plan_id
                FROM users u
                JOIN plans p ON p.id = u.plan_id
                WHERE p.price_monthly > 0
                  AND u.subscription_expires_at IS NOT NULL
                  AND u.subscription_expires_at != ''
                  AND (
                      CASE
                          WHEN length(u.subscription_expires_at) = 10
                          THEN u.subscription_expires_at || ' 23:59:59'
                          ELSE u.subscription_expires_at
                      END
                  ) < ?
                  AND u.plan_id != ?
                  AND NOT (
                      u.auto_renew = 1
                      AND (
                          (u.stripe_subscription_id IS NOT NULL AND u.stripe_subscription_id != '')
                          OR
                          (u.paypal_subscription_id IS NOT NULL AND u.paypal_subscription_id != '')
                      )
                  )
                """,
                (now_dt, free_plan_id)
            ).fetchall()

            if not expired_users:
                return

            for user in expired_users:
                conn.execute(
                    """
                    UPDATE users
                    SET plan_id = ?,
                        subscription_status = 'expired',
                        auto_renew = 0
                    WHERE id = ?
                    """,
                    (free_plan_id, user['id'])
                )

            conn.commit()

    except Exception:
        pass


def send_manual_renewal_reminders():
    """
    Runs once per day via APScheduler.

    Finds paid-plan users whose subscription expires within the next 3 days
    (or has already expired in the last 24 hours) AND who have auto_renew=1
    but no active Stripe/PayPal subscription ID (i.e. manual-payment users
    who intend to renew).  Sends them a reminder e-mail so they can renew
    before — or immediately after — access lapses.

    A reminder is sent at most once per day — we record the last reminder
    date in the site_settings table (key: 'renewal_reminder_last_run') so
    that multiple scheduler ticks on the same day do not send duplicate mails.
    """
    _base = os.path.dirname(os.path.realpath(__file__))
    db_path = os.path.join(_base, "storage", "sqlite.db")

    try:
        with sqlite3.connect(db_path) as conn:
            conn.row_factory = sqlite3.Row

            # ── Site settings needed for the e-mail ──────────────────────────
            settings_rows = conn.execute(
                "SELECT key, value FROM site_settings WHERE key IN ('site_name', 'site_url', 'renewal_reminder_last_run')"
            ).fetchall()
            settings = {r['key']: r['value'] for r in settings_rows}
            site_name = settings.get('site_name', 'OnlineConvert')
            site_url  = settings.get('site_url', '')

            # Only run once per day
            today_str = datetime.now(timezone.utc).strftime('%Y-%m-%d')
            last_run = settings.get('renewal_reminder_last_run', '')
            if last_run == today_str:
                return

            # ── Find eligible users ───────────────────────────────────────────
            # Window: subscriptions that expired up to 24 h ago or will expire
            # within the next 3 days (both ends inclusive).
            now_dt      = datetime.now(timezone.utc)
            window_start = (now_dt - timedelta(hours=24)).strftime('%Y-%m-%d %H:%M:%S')
            window_end   = (now_dt + timedelta(days=3)).strftime('%Y-%m-%d %H:%M:%S')

            candidates = conn.execute(
                """
                SELECT u.id, u.email, u.username, p.name AS plan_name,
                       u.subscription_expires_at
                FROM users u
                JOIN plans p ON p.id = u.plan_id
                WHERE p.price_monthly > 0
                  AND u.auto_renew = 1
                  AND u.subscription_expires_at IS NOT NULL
                  AND u.subscription_expires_at != ''
                  AND (
                      CASE
                          WHEN length(u.subscription_expires_at) = 10
                          THEN u.subscription_expires_at || ' 23:59:59'
                          ELSE u.subscription_expires_at
                      END
                  ) BETWEEN ? AND ?
                  AND (u.stripe_subscription_id IS NULL OR u.stripe_subscription_id = '')
                  AND (u.paypal_subscription_id IS NULL OR u.paypal_subscription_id = '')
                """,
                (window_start, window_end)
            ).fetchall()

            # ── Send reminder e-mails ─────────────────────────────────────────
            if candidates:
                try:
                    from helpers import send_email as _send_email
                except ImportError:
                    _send_email = None

                for user in candidates:
                    if not _send_email or not user['email']:
                        continue
                    expires = user['subscription_expires_at']
                    subject = f"[{site_name}] Your {user['plan_name']} subscription expires soon"
                    pricing_url = f"{site_url}/pricing" if site_url else "/pricing"
                    body = (
                        f"<div style='font-family:Arial,sans-serif;max-width:520px;margin:0 auto;'>"
                        f"<h2 style='color:#4f46e5;'>Subscription Renewal Reminder</h2>"
                        f"<p>Hi {user['username']},</p>"
                        f"<p>Your <strong>{user['plan_name']}</strong> subscription on "
                        f"<strong>{site_name}</strong> will expire on "
                        f"<strong>{expires[:10]}</strong>.</p>"
                        f"<p>To continue enjoying uninterrupted access, please renew "
                        f"your plan before the expiry date.</p>"
                        f"<p><a href='{pricing_url}' style='display:inline-block;padding:10px 20px;"
                        f"background:#4f46e5;color:#fff;text-decoration:none;border-radius:6px;"
                        f"font-weight:bold;'>Renew Now</a></p>"
                        f"<p style='color:#6b7280;font-size:12px;margin-top:24px;'>You are receiving "
                        f"this because auto-renew is enabled on your account.</p>"
                        f"</div>"
                    )
                    try:
                        _send_email(user['email'], subject, body)
                    except Exception:
                        pass

            # ── Record today as last-run date ─────────────────────────────────
            conn.execute(
                """INSERT INTO site_settings (key, value)
                   VALUES ('renewal_reminder_last_run', ?)
                   ON CONFLICT(key) DO UPDATE SET value=excluded.value""",
                (today_str,)
            )
            conn.commit()

    except Exception:
        pass