File size: 3,824 Bytes
8722fe5
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
"""
Sync all data from local SQLite (attendx.db) to Supabase PostgreSQL
"""
import sqlite3
import psycopg2
import json
import os
import sys

# Add backend directory to sys.path
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app.database.session import Base, engine
from app.models import models

# Ensure all tables exist in Supabase
print("1. Initializing schema tables in Supabase...")
Base.metadata.create_all(bind=engine)
print("Schema verified.")

# Connect to local SQLite (backend/attendx.db)
sqlite_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "attendx.db")
if not os.path.exists(sqlite_path) or os.path.getsize(sqlite_path) < 1000:
    sqlite_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), "backend", "attendx.db")

print(f"2. Connecting to SQLite at: {sqlite_path}")
sqlite_conn = sqlite3.connect(sqlite_path)
sqlite_conn.row_factory = sqlite3.Row
s_cur = sqlite_conn.cursor()

# Connect to Supabase
pg_url = "postgresql://postgres:doctorvondoom1812@db.vffgjjlucktyrfjhbqbp.supabase.co:5432/postgres"
print("3. Connecting to Supabase PostgreSQL...")
pg_conn = psycopg2.connect(pg_url)
pg_cur = pg_conn.cursor()

tables = [
    "users",
    "classes",
    "subjects",
    "students",
    "enrollments",
    "face_embeddings",
    "attendance_sessions",
    "attendance_records"
]

boolean_columns = {
    "users": ["is_active"],
    "classes": ["active"],
    "students": ["active", "face_registration_complete"]
}

total_synced = 0
for table in tables:
    try:
        s_cur.execute(f"SELECT * FROM {table}")
        rows = s_cur.fetchall()
    except Exception as e:
        print(f"Table {table} not found in SQLite: {e}")
        continue

    if not rows:
        print(f"Table {table}: 0 rows to sync")
        continue

    col_names = [description[0] for description in s_cur.description]
    cols_str = ', '.join(f'"{c}"' for c in col_names)
    placeholders = ', '.join(['%s'] * len(col_names))
    update_set = ', '.join([f'"{c}" = EXCLUDED."{c}"' for c in col_names if c != 'id'])

    count = 0
    for r in rows:
        vals = list(r)

        # Cast boolean columns
        if table in boolean_columns:
            for b_col in boolean_columns[table]:
                if b_col in col_names:
                    b_idx = col_names.index(b_col)
                    if vals[b_idx] is not None:
                        vals[b_idx] = bool(vals[b_idx])

        # Handle JSON embedding column
        if table == 'face_embeddings':
            idx = col_names.index('embedding')
            if isinstance(vals[idx], str):
                vals[idx] = json.dumps(json.loads(vals[idx]))
            elif isinstance(vals[idx], list):
                vals[idx] = json.dumps(vals[idx])

        query = f"""
            INSERT INTO {table} ({cols_str}) 
            VALUES ({placeholders}) 
            ON CONFLICT (id) DO UPDATE SET {update_set};
        """
        try:
            pg_cur.execute(query, vals)
            count += 1
        except Exception as err:
            print(f"Error syncing row {vals[0]} in {table}: {err}")
            pg_conn.rollback()
            break
    else:
        pg_conn.commit()
        # Reset serial sequence
        try:
            pg_cur.execute(f"SELECT setval(pg_get_serial_sequence('{table}', 'id'), COALESCE((SELECT MAX(id) + 1 FROM {table}), 1), false);")
            pg_conn.commit()
        except Exception:
            pg_conn.rollback()

        print(f"[OK] Synced {count} rows in table '{table}' to Supabase!")
        total_synced += count

sqlite_conn.close()
pg_conn.close()

print(f"\n[SUCCESS] COMPLETE: All {total_synced} database records, student profiles, and 3D face scan vectors are permanently saved in Supabase!")