crm_db_setup.py

import sqlite3

# Δημιουργία/σύνδεση βάσης
conn = sqlite3.connect('crm_kentro_meletis.db')
cursor = conn.cursor()

# 1. Πίνακας Πελατών (Clients)
cursor.execute("""
CREATE TABLE IF NOT EXISTS clients (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    onoma TEXT,
    tilefono TEXT,
    email TEXT,
    diefthinsi TEXT,
    hmerominia_eggrafis DATE,
    hmerominia_gennisis DATE,
    amka TEXT,
    paratiriseis TEXT
)
""")

# 2. Πίνακας Υπηρεσιών (Services)
cursor.execute("""
CREATE TABLE IF NOT EXISTS services (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    onoma TEXT,
    perigrafi TEXT,
    timi REAL,
    eidos TEXT
)
""")

# 3. Πίνακας Υπαλλήλων (Employees/Users)
cursor.execute("""
CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    onoma TEXT,
    email TEXT,
    rolos TEXT,
    username TEXT,
    password TEXT
)
""")

# 4. Πίνακας Αιθουσών (Rooms)
cursor.execute("""
CREATE TABLE IF NOT EXISTS rooms (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    onoma TEXT,
    xorhtikotita INTEGER,
    exoplismos TEXT
)
""")

# 5. Πίνακας Τμημάτων (Sections/Groups)
cursor.execute("""
CREATE TABLE IF NOT EXISTS sections (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    onoma TEXT,
    typos TEXT,
    ypefthinos INTEGER,
    FOREIGN KEY (ypefthinos) REFERENCES employees(id)
)
""")

# 5A. Συσχέτιση Τμήματος-Πελάτη (Section_Client)
cursor.execute("""
CREATE TABLE IF NOT EXISTS section_clients (
    section_id INTEGER,
    client_id INTEGER,
    FOREIGN KEY (section_id) REFERENCES sections(id),
    FOREIGN KEY (client_id) REFERENCES clients(id),
    PRIMARY KEY (section_id, client_id)
)
""")

# 6. Πίνακας Ωρολογίου Προγράμματος (Schedule)
cursor.execute("""
CREATE TABLE IF NOT EXISTS schedule (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    section_id INTEGER,
    room_id INTEGER,
    mera TEXT,
    ora_enarxis TEXT,
    ora_lixis TEXT,
    service_id INTEGER,
    employee_id INTEGER,
    client_id INTEGER,
    paratiriseis TEXT,
    FOREIGN KEY (section_id) REFERENCES sections(id),
    FOREIGN KEY (room_id) REFERENCES rooms(id),
    FOREIGN KEY (service_id) REFERENCES services(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (client_id) REFERENCES clients(id)
)
""")

# 7. Πίνακας Συνεδριών/Ραντεβού (Sessions/Appointments)
cursor.execute("""
CREATE TABLE IF NOT EXISTS sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date DATE,
    time TEXT,
    client_id INTEGER,
    service_id INTEGER,
    employee_id INTEGER,
    room_id INTEGER,
    section_id INTEGER,
    status TEXT,
    paratiriseis TEXT,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (service_id) REFERENCES services(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (room_id) REFERENCES rooms(id),
    FOREIGN KEY (section_id) REFERENCES sections(id)
)
""")

# 8. Πίνακας Αποδείξεων (Receipts/Invoices)
cursor.execute("""
CREATE TABLE IF NOT EXISTS receipts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date DATE,
    arithmos TEXT,
    client_id INTEGER,
    synoliko_poso REAL,
    services TEXT,
    status TEXT,
    file_path TEXT,
    eopyy BOOLEAN,
    FOREIGN KEY (client_id) REFERENCES clients(id)
)
""")

conn.commit()

# === Demo: Εισαγωγή εγγραφών ===
cursor.execute("INSERT INTO clients (onoma, tilefono, email, diefthinsi, hmerominia_eggrafis) VALUES (?, ?, ?, ?, date('now'))", 
               ('Γιάννης Παπαδόπουλος', '6900000001', 'giannis@email.com', 'Αθήνα'))
cursor.execute("INSERT INTO employees (onoma, email, rolos, username, password) VALUES (?, ?, ?, ?, ?)",
               ('Ελένη Καθηγήτρια', 'eleni@school.com', 'therapist', 'eleni', 'password123'))
cursor.execute("INSERT INTO rooms (onoma, xorhtikotita, exoplismos) VALUES (?, ?, ?)",
               ('Αίθουσα 1', 8, 'Λευκοπίνακας, Τραπέζι'))
cursor.execute("INSERT INTO services (onoma, perigrafi, timi, eidos) VALUES (?, ?, ?, ?)",
               ('Εργοθεραπεία', 'Συνεδρία εργοθεραπείας', 30.0, 'Ειδική αγωγή'))
cursor.execute("INSERT INTO sections (onoma, typos, ypefthinos) VALUES (?, ?, ?)",
               ('Τμήμα Α Δημοτικού', 'Μελέτη', 1))
conn.commit()

# === Demo: Προβολή πελατών ===
print("\n--- Πελάτες ---")
for row in cursor.execute("SELECT * FROM clients"):
    print(row)

# === Demo: Προβολή υπηρεσιών ===
print("\n--- Υπηρεσίες ---")
for row in cursor.execute("SELECT * FROM services"):
    print(row)

# === Demo: Προβολή τμημάτων ===
print("\n--- Τμήματα ---")
for row in cursor.execute("SELECT * FROM sections"):
    print(row)

# Κλείσιμο βάσης
conn.close()