scripts/create_test_db.py

# Credit/vehicle financing test database generator
import sqlite3
import os
import random
import string
from pathlib import Path
from datetime import datetime, timedelta

# --- CONFIGURATION ---
PROJECT_ROOT = Path(__file__).parent.parent
DB_PATH = PROJECT_ROOT / "data" / "test_database.db"
SCRIPTS_DB_PATH = Path(__file__).parent / "data" / "test_database.db"

def create_schema(cursor):
    # Drop old tables if they exist
    for table in ["Insurance", "PolicyStatus", "Contract", "DecisionResult", "CreditFileElementVehicle", "CreditFileElement", "CreditFileVersion", "Currency", "CreditFileStatus"]:
        cursor.execute(f"DROP TABLE IF EXISTS {table}")

    # CreditFileVersion - credit application versions with audit trail
    cursor.executescript("""
        CREATE TABLE CreditFileVersion (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            CreateTime TEXT NOT NULL,
            UpdateTime TEXT NOT NULL,
            UpdatedBy VARCHAR(250) NOT NULL,
            IsPassive INTEGER NOT NULL CHECK (IsPassive IN (0, 1)),
            CreditFileId INTEGER NOT NULL,
            IsCurrentVersion INTEGER NOT NULL CHECK (IsCurrentVersion IN (0, 1)),
            VersionNumber INTEGER NOT NULL,
            CreditFileMainStatusId INTEGER NOT NULL,
            CreditFileSubStatusId INTEGER,
            DealerId INTEGER NOT NULL,
            DealerSalesPersonId INTEGER NOT NULL,
            CurrencyId INTEGER NOT NULL,
            IsDealerVehicle INTEGER NOT NULL CHECK (IsDealerVehicle IN (0, 1)),
            LeadId TEXT
        );
         INSERT INTO CreditFileVersion (CreateTime, UpdateTime, UpdatedBy, IsPassive,CreditFileId, IsCurrentVersion, VersionNumber, CreditFileMainStatusId,CreditFileSubStatusId, DealerId, DealerSalesPersonId, CurrencyId, IsDealerVehicle, LeadId)
           VALUES   ('2026-02-17 09:15:00', '2026-02-17 09:15:00', 'sistem', 0, 26000046, 1, 1, 800, 501, 82, 1235, 1, 0,'L-P009502428'),
                    ('2026-02-17 09:15:00', '2026-02-17 09:15:00', 'sistem', 0, 26000040, 1, 1, 1500, NULL, 51,1230, 2, 0,'L-P009493568'),
                    ('2026-02-17 09:15:00', '2026-02-17 09:15:00', 'sistem', 0, 26000041, 1, 1, 1200, NULL, 82, 1231, 1, 0,'L-P009490722'),
                    ('2026-02-17 09:15:00', '2026-02-17 09:15:00', 'sistem', 0, 26000042, 0, 1, 400, NULL, 82, 1231, 1, 0,'L-P009490718'),
                    ('2026-02-17 09:15:00', '2026-02-17 09:15:00', 'sistem', 0, 26000042, 1, 2, 100, NULL, 82, 1231, 1, 0,'L-P009490718'),
                    ('2026-02-17 09:15:00', '2026-02-17 09:15:00', 'sistem', 0, 26000099, 1, 1, 500, NULL, 82, 1235, 1, 0,'L-P009502999')
    """)

        # Currency table for reference (linked via FK to CreditFileVersion)
    cursor.executescript("""
        CREATE TABLE Currency (
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        CreateTime TEXT NOT NULL,
        UpdateTime TEXT NOT NULL,
        UpdatedBy VARCHAR(250) NOT NULL,
        IsPassive INTEGER NOT NULL CHECK (IsPassive IN (0, 1)),
        Code VARCHAR(250) NOT NULL,
        Name VARCHAR(250) NOT NULL
        );
        INSERT INTO Currency (CreateTime, UpdateTime, UpdatedBy, IsPassive, Code, Name)
                       VALUES   ('2026-02-17 09:45:00', '2026-02-17 09:45:00', 'system', 0, 'TRY', 'Türk Lirası'),
                                ('2026-02-17 09:45:00', '2026-02-17 09:45:00', 'system', 0, 'EUR', 'Euro'),
                                ('2026-02-17 09:45:00', '2026-02-17 09:45:00', 'system', 0, 'USD', 'Amerikan Doları')      

    """)
    # CreditFileStatus - possible statuses for credit files (linked via FK to CreditFileVersion)
    cursor.executescript("""
        CREATE TABLE CreditFileStatus (
           Id INTEGER PRIMARY KEY AUTOINCREMENT,
           CreateTime TEXT NOT NULL,
           UpdateTime TEXT NOT NULL,
           UpdatedBy VARCHAR(250) NOT NULL,
           IsPassive INTEGER NOT NULL CHECK (IsPassive IN (0, 1)),
           Name VARCHAR(250) NOT NULL
        );
         INSERT INTO CreditFileStatus (Id, CreateTime, UpdateTime, UpdatedBy, IsPassive, Name)
                       VALUES   (100, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Teklif'),
                                (200, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Eksik Başvuru Evrağı'),
                                (300, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Satış Yorumu'),
                                (400, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Başvuru Evrak Kontrolü'),
                                (500, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Değerlendirmede'),
                                (501, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Bekliyor'),
                                (502, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Analiz Ediliyor'),
                                (503, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'İmza Bekliyor'),
                                (600, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'İlave Evrak'),
                                (700, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Değerlendirilmiş Kredi'),
                                (800, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Sözleşme ve Ekleri Hazır'),
                                (900, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Ödeme Evrak Kontrolü'),
                                (1000, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Eksik Ödeme Evrağı'),
                                (1100, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Ödeme Onayı Verilmiş Kredi'),
                                (1200, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Ödeme Evrak Onayı'),
                                (1300, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Aktif Sözleşme'),
                                (1500, '2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'İptal')                   
    """)

    # CreditFileElement - vehicle financing elements within a version
    cursor.executescript("""
        CREATE TABLE CreditFileElement (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            CreateTime TEXT NOT NULL,
            UpdateTime TEXT NOT NULL,
            UpdatedBy VARCHAR(250) NOT NULL,
            IsPassive INTEGER NOT NULL CHECK (IsPassive IN (0, 1)),
            CreditFileVersionId INTEGER NOT NULL,
            CreditTypeId INTEGER NOT NULL,
            SegmentId INTEGER NOT NULL,
            VehicleStateId INTEGER,
            BrandId INTEGER NOT NULL,
            SerieId INTEGER NOT NULL,
            ModelTypeId INTEGER NOT NULL,
            ModelId INTEGER NOT NULL,
            VehicleId INTEGER,
            ModelYear INTEGER,
            UnitCount INTEGER NOT NULL,
            UnitPrice NUMERIC(30, 2),
            UnitPriceCurrencyId INTEGER,
            Term INTEGER NOT NULL,
            CreditAmount NUMERIC(30, 2) NOT NULL,
            DownPayment NUMERIC(30, 2) NOT NULL,
            ElementOrder INTEGER NOT NULL,
            FOREIGN KEY (CreditFileVersionId) REFERENCES CreditFileVersion(Id) ON DELETE CASCADE
        );
             INSERT INTO CreditFileElement 
            (CreateTime, UpdateTime, UpdatedBy, IsPassive, CreditFileVersionId, CreditTypeId, SegmentId, VehicleStateId, BrandId, SerieId, ModelTypeId, ModelId, VehicleId, ModelYear, UnitCount, UnitPrice, UnitPriceCurrencyId, Term, CreditAmount, DownPayment, ElementOrder)
             VALUES 
            ('2026-02-17 10:00:00', '2026-02-17 10:00:00', 'sistem', 0, 1, 1, 1, 1, 1, 1, 1, 1, NULL, 2024, 1, 1500000.00, 1, 36, 1200000.00, 300000.00, 1),
            ('2026-02-17 10:05:00', '2026-02-17 10:05:00', 'sistem', 0, 2, 1, 1, NULL, 2, 2, 2, 2, NULL, 2023, 1, 2000000.00, 1, 48, 1600000.00, 400000.00, 2),
            ('2026-02-17 10:10:00', '2026-02-17 10:10:00', 'sistem', 0, 3, 2, 1, NULL, 3, 3, 3, 3, NULL, 2022, 1, 1000000.00, 1, 24, 750000.00, 250000.00, 3),
            ('2026-02-17 10:15:00', '2026-02-17 10:15:00', 'sistem', 0, 4, 2, 2, NULL, 4, 4, 4, 4, NULL, 2025, 2, 1800000.00, 1, 60, 1400000.00, 400000.00, 4);

    """)

    # CreditFileElementVehicle - actual vehicle identification for used vehicles
    cursor.executescript("""
        CREATE TABLE CreditFileElementVehicle (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            CreateTime TEXT NOT NULL,
            UpdateTime TEXT NOT NULL,
            UpdatedBy VARCHAR(250) NOT NULL,
            IsPassive INTEGER NOT NULL CHECK (IsPassive IN (0, 1)),
            CreditFileElementId INTEGER NOT NULL,
            Plate VARCHAR(50),
            EngineNo VARCHAR(50) NOT NULL,
            ChassisNo VARCHAR(50) NOT NULL,
            Brand VARCHAR(250) NOT NULL,
            Seri VARCHAR(250),
            Model VARCHAR(250) NOT NULL,
            ModelYear INTEGER NOT NULL,
            VehiclePrice NUMERIC(18, 2),
            CurrencyId INTEGER,
            StsId TEXT,
            IsLien INTEGER NOT NULL DEFAULT 0 CHECK (IsLien IN (0, 1)),
            FOREIGN KEY (CreditFileElementId) REFERENCES CreditFileElement(Id) ON DELETE CASCADE
        );
            INSERT INTO CreditFileElementVehicle
            (CreateTime, UpdateTime, UpdatedBy, IsPassive, CreditFileElementId, Plate, EngineNo, ChassisNo, Brand, Seri, Model, ModelYear, VehiclePrice, CurrencyId, StsId, IsLien)
            VALUES
            ('2026-02-17 11:00:00', '2026-02-17 11:00:00', 'sistem', 0, 1, '34MB001', 'ENGMB000001', 'CHSMB000001', 'Mercedes-Benz', 'C-Serisi', 'C200 AMG', 2024, 1500000.00, 1, 'TR2610000289', 1),
            ('2026-02-17 11:05:00', '2026-02-17 11:05:00', 'sistem', 0, 2, '34MB002', 'ENGMB000002', 'CHSMB000002', 'Mercedes-Benz', 'E-Serisi', 'E300 Exclusive', 2023, 2000000.00, 1, 'TR2610000153', 0),
            ('2026-02-17 11:10:00', '2026-02-17 11:10:00', 'sistem', 0, 3, '34MB003', 'ENGMB000003', 'CHSMB000003', 'Mercedes-Benz', 'GLC', 'GLC 300 4MATIC', 2022, 1800000.00, 1, 'TR2610000254', 0),
            ('2026-02-17 11:15:00', '2026-02-17 11:15:00', 'sistem', 0, 4, '34MB004', 'ENGMB000004', 'CHSMB000004', 'Mercedes-Benz', 'S-Serisi', 'S400d 4MATIC', 2025, 3500000.00, 1, 'TR2610000951', 0);
                 
    """)

    # Contract - finalized contracts with activation details
    # WARNING: Cbo and Tbo are internal-only columns. They must NEVER be exposed via any view.
    cursor.executescript("""
    CREATE TABLE Contract (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    CreateTime TEXT NOT NULL,
    UpdateTime TEXT NOT NULL,
    UpdatedBy VARCHAR(250) NOT NULL,
    IsPassive INTEGER NOT NULL CHECK (IsPassive IN (0, 1)),
    CreditFileVersionId INTEGER NOT NULL,
    ActivationDate TEXT,
    ContractCreationDate TEXT,
    PaymentApprove INTEGER NOT NULL CHECK (PaymentApprove IN (0, 1)),
    Cbo NUMERIC(30, 4) NOT NULL,
    Tbo NUMERIC(30, 4) NOT NULL,
    IsSentSwtDownPayment INTEGER NOT NULL CHECK (IsSentSwtDownPayment IN (0, 1)),
    IsReceivedSwtDownpayment INTEGER NOT NULL CHECK (IsReceivedSwtDownpayment IN (0, 1)),
    FOREIGN KEY (CreditFileVersionId) REFERENCES CreditFileVersion(Id) ON DELETE RESTRICT
);
            INSERT INTO Contract
            (CreateTime, UpdateTime, UpdatedBy, IsPassive, CreditFileVersionId, ActivationDate, ContractCreationDate, PaymentApprove, Cbo, Tbo,IsSentSwtDownPayment,IsReceivedSwtDownpayment)
            VALUES
            ('2026-02-19 12:35:57.580', '2026-02-19 13:57:23.860', 'Sistem', 0, 1, '2026-02-19 13:57:20.850', '2026-02-18 13:57:20.850', 1, 4676000.0000, 4676000.0000,1,1),
            ('2026-02-17 14:54:36.837', '2026-02-19 08:35:14.750', 'Sistem', 0, 2, '2026-02-18 08:31:24.183', '2026-02-17 08:31:24.183', 1, 1000000000.0000, 1000000000.0000,1,1),
            ('2026-02-17 10:02:25.483', '2026-02-17 10:02:27.547', 'Sistem', 0, 3, NULL, '2026-02-01 08:31:24.183', 0, 0.0000, 0.00000,1,1),
            ('2026-02-16 19:55:32.500', '2026-02-19 08:35:14.750', 'Sistem', 0, 4, '2026-02-16 20:03:48.250', '2026-02-15 20:03:48.250', 1, 100000.0000, 100000.0000,0,0),
            ('2026-02-16 18:47:00.927', '2026-02-16 19:32:19.790', 'Sistem', 0, 5, NULL, '2026-02-02 20:03:48.250', 0, 0.0000, 0.0000,0,0),
            ('2026-02-19 14:00:00.000', '2026-02-19 14:00:00.000', 'Sistem', 0, 6, '2026-02-19 14:30:00.000', '2026-02-19 14:00:00.000', 1, 3200000.0000, 3200000.0000,1,1);
""")

    cursor.executescript("""
    CREATE TABLE DecisionResult (
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        CreateTime TEXT NOT NULL,
        UpdateTime TEXT NOT NULL,
        UpdatedBy VARCHAR(250) NOT NULL,
        IsPassive INTEGER NOT NULL CHECK (IsPassive IN (0, 1)),
        DecisionResult TEXT,
        DecisionReason TEXT,
        CreditFileVersionId INTEGER NOT NULL,
        FOREIGN KEY (CreditFileVersionId) REFERENCES CreditFileVersion(Id) ON DELETE CASCADE
    );
            INSERT INTO DecisionResult
                (CreateTime, UpdateTime, UpdatedBy, IsPassive, DecisionResult, DecisionReason, CreditFileVersionId)
            VALUES
                ('2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Şartlı Onay', 'Peşinat yetersiz', 1),
                ('2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Onay', 'Tüm kriterler karşılandı', 2),
                ('2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Onay', 'Gelir yeterli, araç değeri uygun', 3),
                ('2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, NULL, NULL, 4),
                ('2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, NULL, NULL, 5),
                ('2015-11-06 00:00:00.000', '2015-11-06 00:00:00.000', 'sistem', 0, 'Onay', 'Tüm kriterler karşılandı', 6);
""")


    cursor.executescript("""
        CREATE TABLE PolicyStatus (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            CreateTime TEXT NOT NULL,
            UpdateTime TEXT NOT NULL,
            UpdatedBy VARCHAR(250) NOT NULL,
            IsPassive INTEGER NOT NULL CHECK (IsPassive IN (0, 1)),
            Name VARCHAR(250) NOT NULL
        );
        INSERT INTO PolicyStatus (Id, CreateTime, UpdateTime, UpdatedBy, IsPassive, Name)
        VALUES
            (1, '2026-01-01 00:00:00', '2026-01-01 00:00:00', 'sistem', 0, 'Aktif'),
            (2, '2026-01-01 00:00:00', '2026-01-01 00:00:00', 'sistem', 0, 'Pasif'),
            (3, '2026-01-01 00:00:00', '2026-01-01 00:00:00', 'sistem', 0, 'İptal'),
            (4, '2026-01-01 00:00:00', '2026-01-01 00:00:00', 'sistem', 0, 'Bekleniyor'),
            (5, '2026-01-01 00:00:00', '2026-01-01 00:00:00', 'sistem', 0, 'Süresi Dolmuş');
    """)

    cursor.executescript("""
        CREATE TABLE Insurance (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            CreateTime TEXT NOT NULL,
            UpdateTime TEXT NOT NULL,
            UpdatedBy VARCHAR(250) NOT NULL,
            IsPassive INTEGER NOT NULL CHECK (IsPassive IN (0, 1)),
            ProposalNo INTEGER NOT NULL,
            Startdate TEXT NOT NULL,
            Enddate TEXT NOT NULL,
            PolicyStatusId INTEGER NOT NULL,
            Amount NUMERIC(18, 2) NOT NULL,
            FOREIGN KEY (PolicyStatusId) REFERENCES PolicyStatus(Id)
        );
        INSERT INTO Insurance
            (CreateTime, UpdateTime, UpdatedBy, IsPassive, ProposalNo, Startdate, Enddate, PolicyStatusId, Amount)
        VALUES
            ('2026-02-19 13:00:00', '2026-02-19 13:00:00', 'sistem', 0, 266071006966, '2026-02-19', '2027-02-19', 1, 45000.00),
            ('2026-02-18 10:00:00', '2026-02-18 10:00:00', 'sistem', 0, 266071006963, '2026-02-18', '2027-02-18', 1, 72000.00),
            ('2026-02-17 11:00:00', '2026-02-17 11:00:00', 'sistem', 0, 266071006962, '2026-02-17', '2025-08-17', 5, 33000.00),
            ('2026-02-16 09:00:00', '2026-02-16 09:00:00', 'sistem', 0, 266071006965, '2026-02-16', '2027-02-16', 2, 61000.00),
            ('2026-02-19 14:00:00', '2026-02-19 14:00:00', 'sistem', 0, 266071006960, '2026-02-19', '2027-02-19', 4, 28000.00),
            ('2025-10-05 09:00:00', '2025-10-05 09:00:00', 'sistem', 0, 266071007001, '2025-10-05', '2026-10-05', 1, 38000.00),
            ('2025-10-18 10:00:00', '2025-10-18 10:00:00', 'sistem', 0, 266071007002, '2025-10-18', '2026-10-18', 1, 52000.00),
            ('2025-11-02 11:00:00', '2025-11-02 11:00:00', 'sistem', 0, 266071007003, '2025-11-02', '2026-11-02', 1, 41000.00),
            ('2025-11-14 09:30:00', '2025-11-14 09:30:00', 'sistem', 0, 266071007004, '2025-11-14', '2026-11-14', 3, 55000.00),
            ('2025-11-28 14:00:00', '2025-11-28 14:00:00', 'sistem', 0, 266071007005, '2025-11-28', '2026-11-28', 1, 47000.00),
            ('2025-12-03 08:00:00', '2025-12-03 08:00:00', 'sistem', 0, 266071007006, '2025-12-03', '2026-12-03', 1, 63000.00),
            ('2025-12-11 10:00:00', '2025-12-11 10:00:00', 'sistem', 0, 266071007007, '2025-12-11', '2026-12-11', 2, 29000.00),
            ('2025-12-19 12:00:00', '2025-12-19 12:00:00', 'sistem', 0, 266071007008, '2025-12-19', '2026-12-19', 1, 58000.00),
            ('2025-12-28 15:00:00', '2025-12-28 15:00:00', 'sistem', 0, 266071007009, '2025-12-28', '2026-12-28', 4, 35000.00),
            ('2026-01-07 09:00:00', '2026-01-07 09:00:00', 'sistem', 0, 266071007010, '2026-01-07', '2027-01-07', 1, 44000.00),
            ('2026-01-15 11:00:00', '2026-01-15 11:00:00', 'sistem', 0, 266071007011, '2026-01-15', '2027-01-15', 1, 67000.00),
            ('2026-01-25 13:00:00', '2026-01-25 13:00:00', 'sistem', 0, 266071007012, '2026-01-25', '2027-01-25', 5, 31000.00),
            ('2026-03-01 08:00:00', '2026-03-01 08:00:00', 'sistem', 0, 266071007013, '2026-03-01', '2027-03-01', 1, 49000.00),
            ('2026-03-05 10:00:00', '2026-03-05 10:00:00', 'sistem', 0, 266071007014, '2026-03-05', '2027-03-05', 1, 56000.00),
            ('2026-03-09 14:00:00', '2026-03-09 14:00:00', 'sistem', 0, 266071007015, '2026-03-09', '2027-03-09', 4, 42000.00);
    """)

    cursor.executescript("""
        CREATE VIEW IF NOT EXISTS v_CurrentCreditFileStatus AS
        SELECT
            cfv.CreditFileId,
            COALESCE(cfs.Name, 'Bilinmeyen Statü') AS StatusName
        FROM CreditFileVersion cfv
        JOIN CreditFileStatus cfs 
            ON cfs.Id = cfv.CreditFileMainStatusId
        WHERE
            cfv.IsCurrentVersion = 1
            AND cfv.IsPassive = 0;
        """)
    
    cursor.executescript("""
        DROP VIEW IF EXISTS v_CurrentDecisionResult;

        CREATE VIEW v_CurrentDecisionResult AS
        SELECT
            cfv.CreditFileId,
            COALESCE(dr.DecisionResult, 'Kredi Kararı Henüz Verilmedi') AS DecisionResultName,
            dr.DecisionReason AS DecisionReason
        FROM CreditFileVersion cfv
        JOIN DecisionResult dr
            ON dr.CreditFileVersionId = cfv.Id
            AND dr.IsPassive = 0
        WHERE
            cfv.IsCurrentVersion = 1
            AND cfv.IsPassive = 0;
    """)
    
    cursor.executescript("""
        CREATE VIEW v_CurrentDownPaymentStatus AS
            SELECT
                cfv.CreditFileId,
                c.IsReceivedSwtDownpayment,
                CASE
                    WHEN c.IsReceivedSwtDownpayment = 1 THEN 'Peşinat tamamlandı'
                    ELSE 'Peşinat tamamlanmadı'
                END AS PesinatDurumu
            FROM Contract c
            INNER JOIN CreditFileVersion cfv
                ON c.CreditFileVersionId = cfv.Id
            WHERE cfv.IsCurrentVersion = 1
            AND cfv.IsPassive = 0;
        """)
    
    cursor.executescript("""
        DROP VIEW IF EXISTS v_CurrentPaymentApprove;

        CREATE VIEW v_CurrentPaymentApprove AS
        SELECT
            cfv.CreditFileId          AS CreditFileId,
            c.PaymentApprove          AS PaymentApprove,
            c.ContractCreationDate    AS ContractCreationDate
        FROM CreditFileVersion cfv
        LEFT JOIN Contract c
            ON c.CreditFileVersionId = cfv.Id
        AND c.IsPassive = 0
        WHERE
            cfv.IsPassive = 0
            AND cfv.IsCurrentVersion = 1;
        """)
   

    # Create indexes for query performance
    cursor.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_cfv_file_version ON CreditFileVersion(CreditFileId, VersionNumber)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_cfv_create_time ON CreditFileVersion(CreateTime)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_cfv_status ON CreditFileVersion(CreditFileMainStatusId)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_cfv_dealer ON CreditFileVersion(DealerId)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_cfe_version ON CreditFileElement(CreditFileVersionId)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_cfe_brand ON CreditFileElement(BrandId, SerieId)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_cfe_credit_type ON CreditFileElement(CreditTypeId)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_cfev_element ON CreditFileElementVehicle(CreditFileElementId)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_cfev_chassis ON CreditFileElementVehicle(ChassisNo)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_cfev_plate ON CreditFileElementVehicle(Plate)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_contract_version ON Contract(CreditFileVersionId)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_contract_activation ON Contract(ActivationDate)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_cfv_lead ON CreditFileVersion(LeadId)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_insurance_proposal ON Insurance(ProposalNo)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_insurance_status ON Insurance(PolicyStatusId)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_insurance_dates ON Insurance(Startdate, Enddate)")

    cursor.executescript("""
        CREATE VIEW IF NOT EXISTS v_LeadCreditFileMapping AS
        SELECT
            cfv.LeadId AS LeadId,
            cfv.CreditFileId AS CreditFileId,
            cfv.CreateTime AS LeadCreatedAt,
            cfv.UpdateTime AS CreditFileCreatedAt,
            cfs.Name AS LeadStatus,
            'Müşteri ' || cfv.CreditFileId AS CustomerName
        FROM CreditFileVersion cfv
        JOIN CreditFileStatus cfs ON cfs.Id = cfv.CreditFileMainStatusId
        WHERE cfv.IsCurrentVersion = 1
          AND cfv.IsPassive = 0
          AND cfv.LeadId IS NOT NULL;
    """)

    cursor.executescript("""
        DROP VIEW IF EXISTS v_InsurancePolicyStatus;

        CREATE VIEW v_InsurancePolicyStatus AS
        SELECT
            ins.ProposalNo                  AS ProposalNo,
            ins.Startdate                   AS PolicyStartDate,
            ins.Enddate                     AS PolicyEndDate,
            ps.Name                         AS PolicyStatusName,
            ins.Amount                      AS InsuranceAmount
        FROM Insurance ins
        JOIN PolicyStatus ps
            ON ps.Id = ins.PolicyStatusId
        WHERE ins.IsPassive = 0;
    """)

    cursor.executescript("""
        DROP VIEW IF EXISTS v_ActiveInsuranceSummary;

        CREATE VIEW v_ActiveInsuranceSummary AS
        SELECT
            ins.ProposalNo                  AS ProposalNo,
            ins.Startdate                   AS PolicyStartDate,
            ins.Enddate                     AS PolicyEndDate,
            ps.Name                         AS PolicyStatusName,
            ins.Amount                      AS InsuranceAmount
        FROM Insurance ins
        JOIN PolicyStatus ps
            ON ps.Id = ins.PolicyStatusId
        WHERE ins.IsPassive = 0
          AND ps.Id = 1;
    """)

    cursor.executescript("""
        DROP VIEW IF EXISTS v_InsuranceCountByMonth;

        CREATE VIEW v_InsuranceCountByMonth AS
        SELECT
            strftime('%Y-%m', Startdate) AS PolicyMonth,
            COUNT(*)                     AS InsuranceCount
        FROM Insurance
        WHERE IsPassive = 0
        GROUP BY strftime('%Y-%m', Startdate)
        ORDER BY PolicyMonth ASC;
    """)

    cursor.executescript("""
        CREATE VIEW IF NOT EXISTS v_VehicleLienStatus AS
        SELECT
            cfv.CreditFileId,
            cfev.Plate AS PlateNumber,
            cfev.IsLien,
            cfev.Brand AS VehicleBrand,
            cfev.Model AS VehicleModel,
            cfev.ModelYear AS VehicleYear
        FROM CreditFileElementVehicle cfev
        JOIN CreditFileElement cfe ON cfe.Id = cfev.CreditFileElementId
        JOIN CreditFileVersion cfv ON cfv.Id = cfe.CreditFileVersionId
        WHERE cfv.IsCurrentVersion = 1 AND cfv.IsPassive = 0;
    """)

def main():
    # Ensure directories exist
    os.makedirs(DB_PATH.parent, exist_ok=True)
    os.makedirs(SCRIPTS_DB_PATH.parent, exist_ok=True)

    if DB_PATH.exists():
        os.remove(DB_PATH)
        print("Removed existing database.")

    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    try:
        create_schema(cursor)
        conn.commit()
        print(f"\nSUCCESS! Database created at: {DB_PATH}")
        #verify_data(cursor)
    except Exception as e:
        print(f"Error: {e}")
        conn.rollback()
        raise
    finally:
        conn.close()

    # Copy to scripts/data for test compatibility
    import shutil
    shutil.copy2(DB_PATH, SCRIPTS_DB_PATH)
    print(f"Copied to: {SCRIPTS_DB_PATH}")


if __name__ == "__main__":
    main()