-- Tabela de consultórios
CREATE TABLE `offices` (
    `id` CHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de especialidades
CREATE TABLE `specialties` (
    `id` CHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de procedimentos
CREATE TABLE `procedures` (
    `id` CHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `value` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `commission` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de formas de pagamento
CREATE TABLE `payment_methods` (
    `id` CHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `discount` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `tax` DECIMAL(5,2) NOT NULL DEFAULT '0.00',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de usuários
CREATE TABLE `users` (
    `id` CHAR(36) NOT NULL,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `type` ENUM('admin','doctor') NOT NULL,
    `office_id` CHAR(36) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`),
    KEY `office_id` (`office_id`),
    CONSTRAINT `users_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de médicos
CREATE TABLE `doctors` (
    `id` CHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `specialty_id` CHAR(36) NULL,
    `whatsapp` VARCHAR(20) NULL,
    `email` VARCHAR(100) NULL,
    `office_id` CHAR(36) NULL,
    `consultation_value` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `consultation_commission` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `specialty_id` (`specialty_id`),
    KEY `office_id` (`office_id`),
    CONSTRAINT `doctors_ibfk_1` FOREIGN KEY (`id`) REFERENCES `users` (`id`),
    CONSTRAINT `doctors_ibfk_2` FOREIGN KEY (`specialty_id`) REFERENCES `specialties` (`id`),
    CONSTRAINT `doctors_ibfk_3` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de procedimentos do médico
CREATE TABLE `doctor_procedures` (
    `doctor_id` CHAR(36) NOT NULL,
    `procedure_id` CHAR(36) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`doctor_id`,`procedure_id`),
    KEY `procedure_id` (`procedure_id`),
    CONSTRAINT `doctor_procedures_ibfk_1` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`),
    CONSTRAINT `doctor_procedures_ibfk_2` FOREIGN KEY (`procedure_id`) REFERENCES `procedures` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de pacientes
CREATE TABLE `patients` (
    `id` CHAR(36) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `office_id` CHAR(36) NULL,
    `priority` ENUM('Alta','Media','Padrao') DEFAULT 'Padrao',
    `attendance_type` VARCHAR(50) NULL,
    `procedure_id` CHAR(36) NULL,
    `procedure_value` DECIMAL(10,2) NULL,
    `payment_method` VARCHAR(50) NULL,
    `paid_amount` DECIMAL(10,2) NULL,
    `arrival_time` TIME NOT NULL,
    `called` TINYINT(1) DEFAULT '0',
    `called_time` TIME NULL,
    `waiting_time` VARCHAR(8) NULL,
    `displayed` TINYINT(1) DEFAULT '0',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `office_id` (`office_id`),
    KEY `procedure_id` (`procedure_id`),
    KEY `idx_patients_called` (`called`),
    KEY `idx_patients_created` (`created_at`),
    CONSTRAINT `patients_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `offices` (`id`),
    CONSTRAINT `patients_ibfk_2` FOREIGN KEY (`procedure_id`) REFERENCES `procedures` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inserir um consultório inicial
INSERT INTO `offices` (`id`, `name`) VALUES 
(UUID(), 'Consultório Principal');

-- Inserir usuário admin inicial (senha: admin123)
INSERT INTO `users` (`id`, `username`, `password`, `name`, `type`) VALUES 
(UUID(), 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrador', 'admin');

