
CREATE DATABASE finances;



CREATE TABLE IF NOT exists purchase( 
        id int not null primary key auto_increment, 
        userId int,
        appUserID varchar(200),
        isPro varchar(50),
        createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP,
        updatedAt datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 );

 CREATE TABLE IF NOT exists monthRegisterAutimatic( 
        id int not null primary key auto_increment, 
        month varchar(10), 
        createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP
 );


DROP TABLE users;

CREATE TABLE IF NOT exists users( 
        id int not null primary key auto_increment, 
        idCode int,
        name varchar(500), 
        nameGroup varchar(500), 
        email varchar(255),
        cpf varchar(50),
        phone varchar(20),
        password varchar(500), 
        typeUser varchar(20), 
        status int,
        tokenFirebase varchar(500), 
        codeVersion varchar(10),
        createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
        updatedAt datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 );

DROP TABLE address;

 CREATE TABLE IF NOT exists address( 
        id int not null primary key auto_increment,
        idUser int,
        street varchar(255),
        number varchar(20),
        neighborhood varchar(255),
        city varchar(300),
        state varchar(20),
        zipeCode varchar(11),
        stateAbbreviation varchar(10),
        createdAt timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
        updatedAt datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
       FOREIGN KEY (idUser) REFERENCES users (id)
 );


 

 
 SELECT 
        s.id,
        s.idUser,
        s.dateStore,
        s.corporateName,            
        s.fantasyName,  
        s.phone, 
        s.cnpj,
        s.instagram,            
        s.facebook,
        IF(f.isFavorite =1,1,0) as isFavorite,
        s.titulo,
        s.horario,
        s.category,
        s.lat,
        s.lng,         
        s.url, 
        s.imgLogo,
        s.imgBanner,
        a.idAddress,
        a.street,
        a.number,
        a.neighborhood,
        a.city,
        a.state,
        a.zipeCode,
        a.stateAbbreviation
    FROM stores as s
    LEFT JOIN address  AS a ON s.idUser = a.idUser
    LEFT JOIN favoritesStores  AS f ON f.idStore = s.id
    AND f.idUser = 19
    WHERE situation=1;
    
    
      SELECT
         t.id,
         t.idUser,
         t.idStore,
         t.dataTrasaction,
         t.operation,
         t.typeOperation,
         t.saleValue,
         t.rescueValue,
         t.percentCashback,
         t.valueCashback,
         t.codeIndication,
         t.valueCashbackIndicator,
         t.statusTransaction,
         t.createdAt,
         s.lat,
         s.lng,
         s.titulo,
         s.category,
         s.imgLogo
    FROM transactions  as t
    LEFT JOIN stores  AS s ON s.id = t.idStore
    WHERE t.idUser = ?;


-- new version

ALTER TABLE categorias
ADD COLUMN favorite VARCHAR(500) AFTER categoria;

ALTER TABLE categorias
ADD COLUMN paymentFavorite VARCHAR(500) AFTER categoria;

ALTER TABLE lancamentos
ADD COLUMN image VARCHAR(500) AFTER tipo_pagamento,
ADD COLUMN lat double AFTER tipo_pagamento,
ADD COLUMN lng double AFTER tipo_pagamento,
ADD COLUMN remember int(11) AFTER tipo_pagamento;


-- refazer--demais acima ok
UPDATE lancamentos SET remember= 0 WHERE 1;

ALTER TABLE users
ADD COLUMN typeSystem int(11) AFTER web;

CREATE VIEW monthYear  AS SELECT DISTINCT lancamentos.mes_ano_venc AS month, lancamentos.id_usuario AS idUser FROM lancamentos;

CREATE VIEW `resumo_mes_despesa`  AS SELECT `lancamentos`.`mes_ano_venc` AS `mes_ano`, `lancamentos`.`id_usuario` AS `id_usuario`, sum(`lancamentos`.`valor`) AS `despesa` FROM `lancamentos` WHERE (`lancamentos`.`tipo_lanc` = 'Despesa') GROUP BY `lancamentos`.`mes_ano_venc`, `lancamentos`.`mes_ano_venc`, `lancamentos`.`id_usuario` ;

-- Tabela para armazenar contexto de conversação do agente
CREATE TABLE IF NOT EXISTS agent_context (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    phone VARCHAR(20) NOT NULL,
    context TEXT,
    last_message TEXT,
    last_ai TEXT,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone (phone)
);






--