
    h                     |    S SK Jr  S SKrS SKJr  S SKr\" 5       rS r " S S5      r " S S5      r " S	 S
5      r	g)    )MySQLN)datetimec                  |    [         R                  n U R                  5       nUR                  S5        UR                  S5        UR                  S5        UR                  S5        UR	                  5       S   S:X  a  UR                  S5        UR                  S5        UR	                  5       S   S:X  aO  [
        R                  " SR                  S	5      [
        R                  " 5       5      nUR                  S
SSUS45        UR                  S5        UR	                  5       S   S:X  aP  [
        R                  " SR                  S	5      [
        R                  " 5       5      nUR                  SSSUSS45        U R                  5         [        S5        g! [         a  n[        SU 35         SnAgSnAff = f)z(Initialize database with required tablesax  
            CREATE TABLE IF NOT EXISTS plans (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                speed INT NOT NULL,
                price DECIMAL(10,2) NOT NULL,
                data_limit INT,
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        a  
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE,
                email VARCHAR(100) NOT NULL UNIQUE,
                password_hash VARCHAR(255) NOT NULL,
                role ENUM('admin', 'client') DEFAULT 'client',
                plan_id INT DEFAULT 1,
                status ENUM('active', 'suspended', 'inactive') DEFAULT 'active',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (plan_id) REFERENCES plans(id)
            )
        a  
            CREATE TABLE IF NOT EXISTS invoices (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT,
                amount DECIMAL(10,2) NOT NULL,
                due_date DATE NOT NULL,
                status ENUM('pending', 'paid', 'overdue') DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        zSELECT COUNT(*) FROM plansr   a\  
                INSERT INTO plans (name, speed, price, data_limit, description) VALUES
                ('Basic 100Mbps', 100, 49.99, 500, 'Basic plan for light users'),
                ('Premium 500Mbps', 500, 79.99, 1000, 'Premium plan for families'),
                ('Business 1Gbps', 1000, 149.99, NULL, 'Unlimited business plan')
            z8SELECT COUNT(*) FROM users WHERE email = 'admin@isp.com'admin123utf-8z
                INSERT INTO users (username, email, password_hash, role) 
                VALUES (%s, %s, %s, %s)
            adminzadmin@isp.comz9SELECT COUNT(*) FROM users WHERE email = 'client@isp.com'	client123z
                INSERT INTO users (username, email, password_hash, role, plan_id) 
                VALUES (%s, %s, %s, %s, %s)
            clientzclient@isp.com   z"Database initialized successfully!zError initializing database: N)mysql
connectioncursorexecutefetchonebcrypthashpwencodegensaltcommitprint	Exception)conncurhashed_passwordes       JC:\Users\hp\OneDrive\Desktop\botted project\isp-portal\backend\database.pyinit_dbr      s   L3kkm 	 
 
	 	  	 	 
 
	 	01<<>!!KK   	NO<<>!!$mmJ,=,=g,FHXYOKK  ?OWEG 	OP<<>!!$mmK,>,>w,GIYZOKK  ,oxKM
 	23 3-aS1223s   FF 
F;#F66F;c                   H    \ rS rSr\SS j5       r\S 5       r\S 5       rSrg)UserX   c           
      t   [         R                  " UR                  S5      [         R                  " 5       5      n[        R
                  R                  5       nUR                  SXUR                  S5      X4[        R                  " 5       45        [        R
                  R                  5         UR                  $ )Nr   z
            INSERT INTO users (username, email, password_hash, role, plan_id, created_at)
            VALUES (%s, %s, %s, %s, %s, %s)
        )r   r   r   r   r   r   r   r   decoder   nowr   	lastrowid)usernameemailpasswordroleplan_idr   r   s          r   create_userUser.create_userY   s     --(@&..BRS%%'  55g>x||~^	` 	!}}    c                    [         R                  R                  5       nUR                  SU 45        UR	                  5       nU(       a?  [
        R                  " UR                  S5      US   R                  S5      5      (       a  U$ g )Nz$SELECT * FROM users WHERE email = %sr   password_hash)r   r   r   r   r   r   checkpwr   )r&   r'   r   users       r   verify_userUser.verify_usere   sg    %%':UHE||~FNN8??7#;T/=R=Y=YZa=bccKr,   c                     [         R                  R                  5       nUR                  SU 45        UR	                  5       nU(       a  [        U5      $ S $ )Nz
            SELECT u.*, p.name as plan_name, p.price as plan_price, p.speed as plan_speed
            FROM users u 
            LEFT JOIN plans p ON u.plan_id = p.id 
            WHERE u.id = %s
        )r   r   r   r   r   dict)user_idr   r0   s      r   get_user_by_idUser.get_user_by_ido   sL    %%' 
 Z	 ||~!tDz+t+r,    N)r
   r   )	__name__
__module____qualname____firstlineno__staticmethodr*   r1   r6   __static_attributes__r8   r,   r   r   r   X   s<    	 	   	, 	,r,   r   c                   4    \ rS rSr\S 5       r\S 5       rSrg)Admin{   c                     [         R                  R                  5       n U R                  S5        U R	                  5       S   nU R                  S5        U R	                  5       S   nU R                  S5        U R	                  5       nUS   (       a  US   OSnU R                  S5        U R	                  5       S	   nUU[        U5      US
.$ )Nz?SELECT COUNT(*) as total_users FROM users WHERE role = 'client'total_userszVSELECT COUNT(*) as active_users FROM users WHERE status = 'active' AND role = 'client'active_usersz
            SELECT SUM(amount) as monthly_revenue 
            FROM invoices 
            WHERE MONTH(created_at) = MONTH(CURRENT_DATE()) 
            AND YEAR(created_at) = YEAR(CURRENT_DATE())
        monthly_revenuer   zJSELECT COUNT(*) as pending_payments FROM invoices WHERE status = 'pending'pending_payments)rC   rD   rE   rF   )r   r   r   r   r   float)r   rC   rD   resultrE   rF   s         r   get_dashboard_statsAdmin.get_dashboard_stats|   s    %%' 	UVlln]3 	lm||~n5 	  	 7=>O7P&!23VW 	`a<<>*<= '($_5 0	
 	
r,   c                      [         R                  R                  5       n U R                  S5        U R	                  5       nU Vs/ s H  n[        U5      PM     sn$ s  snf )Nz
            SELECT u.*, p.name as plan_name, p.price as plan_price
            FROM users u 
            LEFT JOIN plans p ON u.plan_id = p.id 
            WHERE u.role = 'client'
            ORDER BY u.created_at DESC
        r   r   r   r   fetchallr4   )r   usersr0   s      r   get_all_usersAdmin.get_all_users   sP    %%'  	 ',-utT
u---s   Ar8   N)r9   r:   r;   r<   r=   rI   rO   r>   r8   r,   r   r@   r@   {   s)    
 
@ 
. 
.r,   r@   c                   $    \ rS rSr\S 5       rSrg)Billing   c                     [         R                  R                  5       nUR                  SU 45        UR	                  5       nU Vs/ s H  n[        U5      PM     sn$ s  snf )Nzr
            SELECT * FROM invoices 
            WHERE user_id = %s 
            ORDER BY created_at DESC
        rL   )r5   r   invoicesinvoices       r   get_user_invoicesBilling.get_user_invoices   sW    %%'  Z		
 <<>-56X'WX666s   Ar8   N)r9   r:   r;   r<   r=   rW   r>   r8   r,   r   rR   rR      s    7 7r,   rR   )
flask_mysqldbr   r   r   pymysqlr   r   r   r@   rR   r8   r,   r   <module>r[      s?       N3`!, !,F-. -.^
7 
7r,   