from django.core.management.base import BaseCommand
from django.db import connection


class Command(BaseCommand):
    help = 'Mark unsent UserSms records as sent where a duplicate sent record exists for the same telephone + message within 24 hours'

    def add_arguments(self, parser):
        parser.add_argument('--dry-run', action='store_true', help='Preview without making changes')

    def handle(self, *args, **options):
        dry_run = options['dry_run']

        count_sql = """
            SELECT COUNT(*)
            FROM "public"."user_sms" u
            WHERE u.is_sent = FALSE
              AND EXISTS (
                SELECT 1 FROM "public"."user_sms" d
                WHERE d.telephone = u.telephone
                  AND d.message = u.message
                  AND d.is_sent = TRUE
                  AND d.id != u.id
                  AND d.date_added BETWEEN u.date_added - INTERVAL '24 hours'
                                       AND u.date_added + INTERVAL '24 hours'
              )
        """

        update_sql = """
            UPDATE "public"."user_sms"
            SET is_sent = TRUE
            WHERE is_sent = FALSE
              AND EXISTS (
                SELECT 1 FROM "public"."user_sms" d
                WHERE d.telephone = "public"."user_sms".telephone
                  AND d.message = "public"."user_sms".message
                  AND d.is_sent = TRUE
                  AND d.id != "public"."user_sms".id
                  AND d.date_added BETWEEN "public"."user_sms".date_added - INTERVAL '24 hours'
                                       AND "public"."user_sms".date_added + INTERVAL '24 hours'
              )
        """

        with connection.cursor() as cursor:
            cursor.execute(count_sql)
            count = cursor.fetchone()[0]
            self.stdout.write(f"{'Would fix' if dry_run else 'Fixing'} {count} record(s).")

            if not dry_run and count > 0:
                cursor.execute(update_sql)
                self.stdout.write(self.style.SUCCESS(f"Done. {count} record(s) marked as sent."))