"""Verify extended legacy import counts."""
import os
import sqlite3

import django

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'config.settings')
django.setup()

from accounts.models import User, UserNotification
from companies.models import Company
from home.models import DailyVisitorCount, SiteContact
from jobs.models import Job, JobApplication, JobView
from podcast.models import PodcastCategory
from seekers.models import (
    PortfolioStat,
    ProfileMessage,
    ProfileMessageReply,
    ProfileView,
    SeekerInterest,
    SeekerProfile,
)
from subscriptions.models import CompanySubscription, SeekerSubscription

LEGACY = r'D:\db.sqlite3'
legacy = sqlite3.connect(LEGACY)
legacy.row_factory = sqlite3.Row
cur = legacy.cursor()


def legacy_count(table, where=''):
    q = f'SELECT COUNT(*) FROM {table}'
    if where:
        q += f' WHERE {where}'
    cur.execute(q)
    return cur.fetchone()[0]


checks = [
    ('accounts_user', User.objects.count(), 'users'),
    ('business_businessaccount', Company.objects.count(), 'companies'),
    ('store_item', Job.objects.count(), 'jobs'),
    ('store_jobapplicant', JobApplication.objects.count(), 'applications'),
    ('accounts_user', SeekerProfile.objects.count(), 'seeker profiles', "account_type='job_seeker'"),
    ('profile_store_interest', SeekerInterest.objects.count(), 'interests'),
    ('portfolios_portfoliostat', PortfolioStat.objects.count(), 'portfolio stats'),
    ('profile_store_profileview', ProfileView.objects.count(), 'profile views'),
    ('profile_store_message', ProfileMessage.objects.count(), 'profile messages'),
    ('profile_store_messagereply', ProfileMessageReply.objects.count(), 'profile replies'),
    ('profile_store_notification', UserNotification.objects.count(), 'notifications'),
    ('chat_contact', SiteContact.objects.count(), 'site contacts'),
    ('accounts_visitortraffic', DailyVisitorCount.objects.count(), 'visitor days'),
    ('podcast_podcastcategory', PodcastCategory.objects.count(), 'podcast categories'),
    ('store_itemviewcountall', JobView.objects.filter(is_unique=False).count(), 'job views (all)'),
    ('store_itemviewcountunique', JobView.objects.filter(is_unique=True).count(), 'job views (unique)'),
]

print('=== EXTENDED IMPORT VERIFICATION ===')
for item in checks:
    table, new_cnt, label = item[0], item[1], item[2]
    where = item[3] if len(item) > 3 else ''
    leg_cnt = legacy_count(table, where)
    status = 'OK' if leg_cnt == new_cnt else f'legacy={leg_cnt}'
    print(f'{label:24} imported={new_cnt:6}  {status}')

print(f'\nseeker subscriptions:     {SeekerSubscription.objects.count()}')
print(f'company subscriptions:    {CompanySubscription.objects.count()}')
cur.execute("SELECT COUNT(DISTINCT user_id) FROM profile_store_subscription WHERE status IN ('paid','delivered')")
print(f'legacy paid subs (users): {cur.fetchone()[0]}')

matched = 0
cur.execute('SELECT id, password FROM accounts_user')
for row in cur.fetchall():
    u = User.objects.filter(pk=row['id']).first()
    if u and u.password == row['password']:
        matched += 1
print(f'\npassword hashes matching: {matched}/{legacy_count("accounts_user")}')

legacy.close()