CREATE TABLE `lspg_gateways` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(255) NOT NULL,
	`code` varchar(255) NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `lspg_gateways_id` PRIMARY KEY(`id`),
	CONSTRAINT `lspg_gateways_code_unique` UNIQUE(`code`)
);
--> statement-breakpoint
CREATE TABLE `lspg_organization_gateway_mappings` (
	`id` int AUTO_INCREMENT NOT NULL,
	`organization_id` int NOT NULL,
	`gateway_id` int NOT NULL,
	`credentials` json NOT NULL,
	`status` enum('active','inactive') NOT NULL DEFAULT 'active',
	`mode` enum('sandbox','production') NOT NULL DEFAULT 'sandbox',
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `lspg_organization_gateway_mappings_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `lspg_organizations` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(255) NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `lspg_organizations_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `lspg_payment_profiles` (
	`id` int AUTO_INCREMENT NOT NULL,
	`organization_gateway_mapping_id` int NOT NULL,
	`customer_profile_id` varchar(255),
	`payment_profile_id` varchar(255),
	`payment_profile_token` text,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `lspg_payment_profiles_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `lspg_transactions_logs` (
	`id` int AUTO_INCREMENT NOT NULL,
	`txn_id` varchar(255) NOT NULL,
	`organization_gateway_mapping_id` int NOT NULL,
	`amount` decimal(10,2) NOT NULL,
	`type` enum('paid','remaining','refunded','declined','voided','refund-voided') NOT NULL DEFAULT 'remaining',
	`status` enum('pending','success','failed','processing','cancelled') NOT NULL DEFAULT 'pending',
	`req_to_gateway` json NOT NULL,
	`res_from_gateway` json NOT NULL,
	`req_time` timestamp NOT NULL,
	`res_time` timestamp NOT NULL,
	`comments` text,
	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `lspg_transactions_logs_id` PRIMARY KEY(`id`),
	CONSTRAINT `lspg_transactions_logs_txn_id_unique` UNIQUE(`txn_id`)
);
--> statement-breakpoint
ALTER TABLE `lspg_organization_gateway_mappings` ADD CONSTRAINT `fk_ogm_organization_id` FOREIGN KEY (`organization_id`) REFERENCES `lspg_organizations`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `lspg_organization_gateway_mappings` ADD CONSTRAINT `fk_ogm_gateway_id` FOREIGN KEY (`gateway_id`) REFERENCES `lspg_gateways`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `lspg_payment_profiles` ADD CONSTRAINT `fk_pp_ogm_id` FOREIGN KEY (`organization_gateway_mapping_id`) REFERENCES `lspg_organization_gateway_mappings`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `lspg_transactions_logs` ADD CONSTRAINT `fk_txn_ogm_id` FOREIGN KEY (`organization_gateway_mapping_id`) REFERENCES `lspg_organization_gateway_mappings`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `idx_ogm_org_status` ON `lspg_organization_gateway_mappings` (`organization_id`,`status`);